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