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 <mysql-thread91419@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
