Sorry for the typo in the previous mail.
The expected output would be
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
| 3 | krish |
+------+----------------------------------------------------------------------------------------------------+
6 tuples (5.605ms)
Is this really a bug? Else, what would be the logic behind this?
Thanks.
Thanks & Regards,
Vijayakrishna.P.
Mobile : (+91) 9500402305.
On Wed, Apr 8, 2015 at 7:10 PM, Vijay Krishna
Hi,
I am using MonetDB Jan2014 SP1, running on MacOS. I have a table like this.
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.