At 12:57 PM -0700 11/15/01, Mark A Ohrenschall wrote: >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).
Try substituting <=> NULL for IS NULL and see what happens. You may have run into the same bug I ran into recently. This should be fixed soon. > >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 --------------------------------------------------------------------- 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
