MySQL Bugs: #6307: select ... left join .. where right_tbl.col is null doesn't work with unique

Bug #6307	select ... left join .. where right_tbl.col is null doesn't work with unique
Submitted:	28 Oct 2004 15:32	Modified:	9 Nov 2004 3:02
Reporter:	Ephraim Dan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.7	OS:	Any (Any)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:

I am attempting to perform a select using a left join on 2 tables, and find rows in the left table that have no corresponding row in the right table, using "WHERE right_table.column IS NULL" (as documented in mysql manual).

I am finding that the "IS NULL" part of the where clause is not working correctly, but only when the right table has a unique key!  Also, a "WHERE right_table.column = 'value'" clause works.  it's the combination of IS NULL and UNIQUE index on the right column that doesn't work.

How to repeat:

mysql> show create table left_side;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                              |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| left_side | CREATE TABLE `left_side` (
  `ts_id` bigint(20) default NULL,
  `inst_id` tinyint(4) default NULL,
  `flag_name` varchar(64) default NULL,
  `flag_value` text,
  UNIQUE KEY `ts_id` (`ts_id`,`inst_id`,`flag_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table right_side;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                               |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| right_side | CREATE TABLE `right_side` (
  `ts_id` bigint(20) default NULL,
  `inst_id` tinyint(4) default NULL,
  `flag_name` varchar(64) default NULL,
  `flag_value` text,
  UNIQUE KEY `ts_id` (`ts_id`,`inst_id`,`flag_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * FROM left_side;
+-----------------+---------+------------+------------+
| ts_id           | inst_id | flag_name  | flag_value |
+-----------------+---------+------------+------------+
| 111056548820001 |       0 | flag1      | NULL       |
| 111056548820001 |       0 | flag2      | NULL       |
|               2 |       0 | other_flag | NULL       |
+-----------------+---------+------------+------------+
3 rows in set (0.00 sec)

mysql> select * FROM right_side;
+-----------------+---------+-----------+------------+
| ts_id           | inst_id | flag_name | flag_value |
+-----------------+---------+-----------+------------+
| 111056548820001 |       3 | flag1     | sss        |
+-----------------+---------+-----------+------------+
1 row in set (0.00 sec)

# WITH NO "IS NULL" to see all rows it's okay:

mysql> select left_side.flag_name,right_side.flag_value from left_side left join right_side on (left_side.ts_id = right_side.ts_id and left_side.flag_name = right_side.flag_name and right_side.inst_id = 3) WHERE left_side.inst_id = 0 and left_side.ts_id=111056548820001 ;
+-----------+------------+
| flag_name | flag_value |
+-----------+------------+
| flag1     | sss        |
| flag2     | NULL       |
+-----------+------------+
2 rows in set (0.00 sec)

#
# THIS IS THE QUERY THAT DOESN'T WORK:
#  I want to get just the second row from the last query

mysql> select left_side.flag_name,right_side.flag_value from left_side left join right_side on (left_side.ts_id = right_side.ts_id and left_side.flag_name = right_side.flag_name and right_side.inst_id = 3) WHERE left_side.inst_id = 0 and left_side.ts_id=111056548820001 and right_side.flag_value is  null;
Empty set (0.00 sec)

# WITHOUT UNIQUE, IT WORKS...

mysql> alter table right_side drop index ts_id;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select left_side.flag_name,right_side.flag_value from left_side left join right_side on (left_side.ts_id = right_side.ts_id and left_side.flag_name = right_side.flag_name and right_side.inst_id = 3) WHERE left_side.inst_id = 0 and left_side.ts_id=111056548820001 and right_side.flag_value is  null;
+-----------+------------+
| flag_name | flag_value |
+-----------+------------+
| flag2     | NULL       |
+-----------+------------+
1 row in set (0.00 sec)

Verified with 4.1.8-debug-log
Works fine with 4.0.x

I am looking for a workaround for this bug.  I am using the problematic query as a subquery, so I need an equivalent query to get me the same results.  Also, I am actually doing a "SELECT COUNT(*) ...", so I can't use "HAVING right_table.column IS NULL" (which I believe would work) instead of "WHERE right_table.column IS NULL", since HAVING would require that I actually select the column that I need the "IS NULL" constraint on.

I came up with the following as a substitute for the "WHERE right_table.column IS NULL" constraint, and it seems to work, but I want to verify with someone at MySQL that it is a valid workaround (i.e. that it should return the correct results):

WHERE (COALESCE(right_table.column) IS NULL

If this is not a good workaround, please advise as to what the recommended workaround is.

Update:  The workaround doesn't work :(

If anyone has a working workaround, please let me know.

The reported wrong result sets were due to the fact that the added call of the fix_fields method for the built AND condition that joined WHERE and ON conditions
broke ON expression, as it removed extra AND levels in the built condition. 

It looks like that no attributes of the built condition are needed, so we don't have to call fix_fields here.

ChangeSet
  1.2091 04/11/05 22:15:24 igor@rurik.mysql.com +3 -0
  join_outer.result, join_outer.test:
  sql_select.cc:

The fix also resolved the problem of bug #6440.

Hi,

How will I know when the fix to this bug is released?

I have a bug very similar.
I noticed that when I create a table with 2 index (primary + key) the Cardinality of the primary 
key is 0 but the Cardinality of the key is null.
If I insert a record in the table the Cardinality of the primary key is 1 but the Cardinality of the 
key is null.
If I do an analyze TABLE ... the  Cardinality of both index is OK and the query works but with a 
truncate table ...  the Cardinality of the key is null.
If I create the table without the second index, add some records and add the second index the 
cardinality is null.

I suggest a workaround:
- Everytime you modify the table do an analyze.
- If you truncate the table, do an analyze after you insert the first record (analyze doesn't fix the 
problem without records.
If your problems occurs in other situation: 
- Do an analyze after every operation on table 

A section of my application uses this select to booking some room (conference) and when I 
upgraded to 4.1.7 from 4.0.20 I found this problem.
At the moment I drop the second index from the production server and I'm investigating in my 
development server.
At the moment I found that if I define a composite primary key ( old PRIMARY+KEY) the query 
works without the analyze.

I have a sql script that shows this problem.

Santino Cusimano