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 wrote:

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.