I'm having a problem with Distrib 3.23.42 with the following query: select x1.*,x2.* from x1 left join x2 on x1.d1=x2.d2 where x2.d2 is null;
I'm finding that a left join on datetime fields that do not allow nulls is not working when I'm trying to emulate a subselect to find non-matching values (taken right out of the "Solutions Miscellany" section of the Paul DuBois MySQL book -- in my example below, I'm paralleling the example given in the book). Bizarrely, if I negate the x2.d2 column in the where clause then the query works (see in my example below). Also, if I alter the two tables to redefine the datetime fields to allow nulls then the query works (also see in my example below). mysql> desc x1; +-------+----------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------------------+-------+ | d1 | datetime | | | 0000-00-00 00:00:00 | | | c1 | char(1) | YES | | NULL | | +-------+----------+------+-----+---------------------+-------+ 2 rows in set (0.00 sec) mysql> select * from x1; +---------------------+------+ | d1 | c1 | +---------------------+------+ | 2001-11-15 00:01:00 | a | | 2001-11-15 00:02:00 | b | | 2001-11-15 00:03:00 | c | +---------------------+------+ 3 rows in set (0.00 sec) mysql> desc x2; +-------+----------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------------------+-------+ | d2 | datetime | | | 0000-00-00 00:00:00 | | | c2 | char(1) | YES | | NULL | | +-------+----------+------+-----+---------------------+-------+ 2 rows in set (0.00 sec) mysql> select * from x2; +---------------------+------+ | d2 | c2 | +---------------------+------+ | 2001-11-15 00:02:00 | c | | 2001-11-15 00:03:00 | b | | 2001-11-15 00:04:00 | a | +---------------------+------+ 3 rows in set (0.00 sec) mysql> select x1.*,x2.* from x1 left join x2 on x1.d1=x2.d2; +---------------------+------+---------------------+------+ | d1 | c1 | d2 | c2 | +---------------------+------+---------------------+------+ | 2001-11-15 00:01:00 | a | NULL | NULL | | 2001-11-15 00:02:00 | b | 2001-11-15 00:02:00 | c | | 2001-11-15 00:03:00 | c | 2001-11-15 00:03:00 | b | +---------------------+------+---------------------+------+ 3 rows in set (0.00 sec) mysql> select x1.*,x2.* from x1 left join x2 on x1.d1=x2.d2 where x2.d2 is null; Empty set (0.00 sec) mysql> select x1.*,x2.* from x1 left join x2 on x1.d1=x2.d2 where !x2.d2 is null; +---------------------+------+------+------+ | d1 | c1 | d2 | c2 | +---------------------+------+------+------+ | 2001-11-15 00:01:00 | a | NULL | NULL | +---------------------+------+------+------+ 1 row in set (0.00 sec) mysql> alter table x1 modify d1 datetime null; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> alter table x2 modify d2 datetime null; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select x1.*,x2.* from x1 left join x2 on x1.d1=x2.d2 where x2.d2 is null; +---------------------+------+------+------+ | d1 | c1 | d2 | c2 | +---------------------+------+------+------+ | 2001-11-15 00:01:00 | a | NULL | NULL | +---------------------+------+------+------+ 1 row in set (0.00 sec) If anyone can explain this I'd be grateful. Kind regards, Mark Ohrenschall --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
