On Wed, Apr 08, 2015 at 07:10:35PM +0530, Vijay Krishna wrote:
Hi,
I am using MonetDB Jan2014 SP1, running on MacOS. I have a table like this. Please file a bug report, including this example (ie create table etc) statements.
Niels
sql>select * from two; +------+---------------------------------------------------------------------------------------------- | id | name | istrue | joindate | jointime | count | +======+==================================================== | 1 | null | false | 2017-12-08 | 2014-06-30 14:05:31.000000 | 2 | | 2 | vijay | false | 2017-12-08 | 2014-06-30 14:05:59.000000 | 3 | | 3 | krish | true | 2017-12-08 | 2014-06-30 14:06:17.000000 | 5 | | 4 | bat | true | null | null | null | | 5 | gotham | false | null | null | null | | 6 | wayne | false | null | null | 3 | +------+------------------------------------------------------------------------------------------------- 6 tuples (3.504ms)
I tried to do a join on this table with the same table with the following query and got this result.
sql>select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count= twoAlias.id;
+------+------------------------------------------- -------------------------------------------------------+
| id | name | count | id2 | name2
+=============================================================+
| 1 | null | 2 | 2 | vijay |
| 2 | vijay | 3 | 3 | krish |
| 3 | krish | 5 | 5 | gotham |
| 4 | bat | null | 3 | krish |
| 5 | gotham | null | null | null |
| 6 | wayne | 3 | null | null |
+------+-------------------------------- --------------------------------------------------------------------+
6 tuples (5.605ms)
As it could be seen clearly, this join is clearly resulting in a wrong output at the 4th row. The join criterion two.count=twoAlias.id is satisfied in the first 3 rows, but are clearly violated in the 4th row, where in the values in the join columns (count and id2 here) are clearly found to be different. Hence the join rows are not matching.
I expected that the output would be
+------+------------------------------------------- -------------------------------------------------------+
| id | name | count | id2 | name2
+=============================================================+
| 1 | null | 2 | 2 | vijay |
| 2 | vijay | 3 | 3 | krish |
| 3 | krish | 5 | 5 | gotham |
| 4 | bat | null | null | null |
| 5 | gotham | null | null | null |
| 6 | wayne | 3 | null | null |
+------+-------------------------------- --------------------------------------------------------------------+
6 tuples (5.605ms)
Is this really a bug?? Else, what would be the logic behind this?
Thanks in advance.
With Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl