Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

NULL becomes 0 in outer join #6456

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

NULL becomes 0 in outer join #6456

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2017-10-31 11:21:58 +0100
From: @joerivanruth
To: SQL devs <>
Version: 11.27.9 (Jul2017-SP2)
CC: @njnes

Last updated: 2017-12-14 14:46:04 +0100

Comment 25825

Date: 2017-10-31 11:21:58 +0100
From: @joerivanruth

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36
Build Identifier:

I have three databases running on

 mapi:monetdb://localhost:50001/slave1
 mapi:monetdb://localhost:50002/slave2
 mapi:monetdb://localhost:50003/master

They are initialized with the following scripts

 -- all of them
 DROP SCHEMA IF EXISTS foo;
 CREATE SCHEMA foo;
 SET SCHEMA foo;
 
 CREATE TABLE data_local (origin CHAR(5), dest CHAR(5));
 INSERT INTO data_local
 VALUES
 	-- flights from foo to bar and vice versa
 	('foo', 'bar'), ('bar', 'foo'),
 	('foo', 'bar'),
 	-- but one way only from foo to baz
 	('foo', 'baz');
 

 -- master
 CREATE REMOTE TABLE data_slave1 (LIKE data_local)
 ON 'mapi:monetdb://localhost:50001/slave1';
 
 CREATE REMOTE TABLE data_slave2 (LIKE data_local)
 ON 'mapi:monetdb://localhost:50002/slave2';
 
 CREATE MERGE TABLE data_1merged (LIKE data_local);
 ALTER TABLE data_1merged ADD TABLE data_slave1;
 
 CREATE MERGE TABLE data_2merged (LIKE data_local);
 ALTER TABLE data_2merged ADD TABLE data_slave1;
 ALTER TABLE data_2merged ADD TABLE data_slave2;


 -- slave1
 CREATE TABLE data_slave1 AS SELECT * FROM data_local WITH DATA;
 
 
 -- slave2
 CREATE TABLE data_slave2 AS SELECT * FROM data_local WITH DATA;

If I then run the following queries

 -- inc
 SELECT origin, count(*) as incoming
 FROM data_2merged
 WHERE dest = 'foo'
 GROUP BY origin
 ;
 
 -- outg
 SELECT dest, count(*) as outgoing
 FROM data_2merged
 WHERE origin = 'foo'
 GROUP BY dest
 ;
 
 WITH inc AS (
     SELECT origin, count(*) as incoming
     FROM data_2merged
     WHERE dest = 'foo'
     GROUP BY origin
 ),
 outg AS (
     SELECT dest, count(*) as outgoing
     FROM data_2merged
     WHERE origin = 'foo'
     GROUP BY dest
 )
 SELECT *
 FROM inc FULL OUTER JOIN outg ON inc.origin = outg.dest
 ;

I expect the output

 +--------+----------+
 | origin | incoming |
 +========+==========+
 | bar    |        2 |
 +--------+----------+
 1 tuple
 +-------+----------+
 | dest  | outgoing |
 +=======+==========+
 | bar   |        4 |
 | baz   |        2 |
 +-------+----------+
 2 tuples
 +--------+----------+-------+----------+
 | origin | incoming | dest  | outgoing |
 +========+==========+=======+==========+
 | bar    |        2 | bar   |        4 |
 | null   |     null | baz   |        2 |
 +--------+----------+-------+----------+

But on the last line I get

 | null   |        0 | baz   |        2 |

instead.

Reproducible: Always

Comment 25853

Date: 2017-11-08 21:59:01 +0100
From: MonetDB Mercurial Repository <>

Changeset 0d8fd1c7710c made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=0d8fd1c7710c

Changeset description:

fixed bug #6459
and bug #6456

Comment 25855

Date: 2017-11-08 21:59:51 +0100
From: @njnes

fixed, removed flag for changing null into 0 incase of outer joins

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant