MySQL Bugs: #28571: LEFT JOIN with a const false condition produces no rows instead of 1 NULL row

Bug #28571	LEFT JOIN with a const false condition produces no rows instead of 1 NULL row
Submitted:	21 May 2007 17:56	Modified:	19 Jun 2007 1:05
Reporter:	Daniel Grace	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.44-BK, 5.0.37-log	OS:	Any
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
(Not tested in 5.0.41 as it introduces a more severe (fixed in not-yet-released 5.0.44) bug #28337 which affects me.  The changelog for 5.0.41 does not seem to suggest this is fixed.)

SELECT ... FROM a INNER JOIN b ON a.value=<some const reference to a> will return no rows when the equality value in the ON clause is always false but at least one row exists in a

How to repeat:
CREATE TABLE c (
	c_id INT UNSIGNED NOT NULL,
	enable INT UNSIGNED NOT NULL,
	PRIMARY KEY(c_id)
) Type=MyISAM;
INSERT INTO c(c_id, enable) VALUES (1,0),(2,1);

CREATE TABLE d (
	d_id INT UNSIGNED NOT NULL,
	PRIMARY KEY(d_id)
) Type=MyISAM;
INSERT INTO d(d_id) VALUES (1), (2), (3);

-- neither of these return any rows
SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1 AND d.d_id<=>NULL;
SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1 AND d.d_id IS NULL;
-- this returns one row with a d_id=NULL column...
SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1;

-- neither of these return any rows (correct behavior)
SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=2 AND d.d_id<=>NULL;
SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=2 AND d.d_id IS NULL;
-- returns 3 rows (correct behavior)
SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=2;

-- worakround for the first three queries
SELECT * FROM (SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1) t WHERE d_id IS NULL;

Thank you for a bug report. Verified just as described with latest 5.0.44-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE c (
    -> c_id INT UNSIGNED NOT NULL,
    -> enable INT UNSIGNED NOT NULL,
    -> PRIMARY KEY(c_id)
    -> ) Type=MyISAM;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO c(c_id, enable) VALUES (1,0),(2,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE d (
    -> d_id INT UNSIGNED NOT NULL,
    -> PRIMARY KEY(d_id)
    -> ) Type=MyISAM;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO d(d_id) VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> -- neither of these return any rows
mysql> SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1 AND d.d_id<=>N
ULL;
Empty set (0.00 sec)

mysql> SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1 AND d.d_id IS
NULL;
Empty set (0.00 sec)

mysql> -- this returns one row with a d_id=NULL column...
mysql> SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1;
+------+--------+------+
| c_id | enable | d_id |
+------+--------+------+
|    1 |      0 | NULL |
+------+--------+------+
1 row in set (0.01 sec)

mysql> -- neither of these return any rows (correct behavior)
mysql> SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=2 AND d.d_id<=>N
ULL;
Empty set (0.00 sec)

mysql> SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=2 AND d.d_id IS
NULL;
Empty set (0.00 sec)

mysql> -- returns 3 rows (correct behavior)
mysql> SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=2;
+------+--------+------+
| c_id | enable | d_id |
+------+--------+------+
|    2 |      1 |    1 |
|    2 |      1 |    2 |
|    2 |      1 |    3 |
+------+--------+------+
3 rows in set (0.00 sec)

mysql>
mysql> -- worakround for the first three queries
mysql> SELECT * FROM (SELECT * FROM c LEFT JOIN d ON c.enable<>0 WHERE c.c_id=1
) t
    -> WHERE d_id IS NULL;
+------+--------+------+
| c_id | enable | d_id |
+------+--------+------+
|    1 |      0 | NULL |
+------+--------+------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27392

ChangeSet@1.2503, 2007-05-26 10:33:01-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #28571. Outer join queries with ON conditions over
  constant outer tables did not return null complemented
  rows when conditions were evaluated to FALSE.
  Wrong results were returned because the conditions over constant
  outer tables, when being pushed down, were erroneously enclosed 
  into the guard function used for WHERE conditions.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27393

ChangeSet@1.2503, 2007-05-26 10:13:28-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #28571. Outer join queries with ON conditions over
  constant outer tables did not return null complemented
  rows when conditions were evaluated to FALSE.
  Wrong results were returned because the conditions over constant
  outer tables, when being pushed down, were erroneously enclosed 
  into the guard function used for WHERE conditions.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.