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 19:56	Modified:	19 Jun 2007 3:05
Reporter:	Daniel Grace
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.44-BK, 5.0.37-log	OS:	Any
Assigned to:	Bugs System	Target Version:

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.