List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 15 2001 8:11pm
Subject:Re: Problem with left join on not null datetime
View as plain text  
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

Thread
Problem with left join on not null datetimeMark A Ohrenschall15 Nov
  • Re: Problem with left join on not null datetimePaul DuBois15 Nov