MySQL Bugs: #47005: Same JOIN query on MyISAM table with AUTO

The bug was updated successfully. The following people were notified: the MySQL developers, the bug reporter, the assigned developer, the assigned lead, interested observers, and nobody else.

Bug #47005	Same JOIN query on MyISAM table with AUTO_INCREMENT gives different results
Submitted:	31 Aug 2009 8:30	Modified:	27 Sep 2010 12:39
Reporter:	Roel Van de Paar	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.37, 5.1.39-bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
mysql> select * from t1 as m right join t2 as s on m.id = s.id where s.id IS NULL; /* Produces correct result */
+----+----+
| id | id |
+----+----+
|  1 |  1 |
+----+----+
1 row in set (0.00 sec)

mysql> select * from t1 as m right join t2 as s on m.id = s.id where s.id IS NULL; /* Same query, empty result */
Empty set (0.00 sec)

How to repeat:
drop table if exists t1;
drop table if exists t2;
create table t1(`id` int AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM;
create table t2(`id` int AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM;
insert into t1 values (NULL),(NULL);
insert into t2 values (NULL);
select * from t1;
select * from t2;
select * from t1 as m right join t2 as s on m.id = s.id where s.id IS NULL; /* Produces correct result */
select * from t1 as m right join t2 as s on m.id = s.id where s.id IS NULL; /* Same query, empty result */

Suggested fix:
Same query should return same result

Verifying as D2, able to produce bug on Windows and Unix

Verified with recent 5.1.39 from bzr on Mac OS X also. query_cache_size = 0. Note this:

mysql> explain select * from t1 as m right join t2 as s on m.id = s.id where s.id IS NULL; 
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | s     | system | PRIMARY       | NULL    | NULL    | NULL  |    1 |             |
|  1 | SIMPLE      | m     | const  | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select * from t1 as m right join t2 as s on m.id = s.id where s.id IS NULL; 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

Looks like the documented ODBC hack for getting last inserted id?
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-usagenotes-functionality.html

Try reconnecting after the inserts and you'll notice both queries return the same.

In the original description:
s/Produces correct result/Produces incorrect result/ (it should not give 1/1 because of the WHERE clause)

Some more testing:

'insert into t1 values (1),(2); insert into t2 values (1);'       => Both results are empty.
'insert into t1 values (1),(NULL); insert into t2 values (1);'    => Both results are empty.
'insert into t1 values (1),(NULL); insert into t2 values (NULL);' => First result is 1/1, second is empty.
'insert into t1 values (1),(2); insert into t2 values (NULL);'    => First result is 1/1, second is empty.
'insert into t1 values (NULL),(2); insert into t2 values (NULL);' => Both results are empty.
'insert into t1 values (NULL),(2); insert into t2 values (1);'    => First result is 1/1, second is empty.
'insert into t1 values (NULL),(NULL); insert into t2 values (1);' => First result is 1/1, second is empty.

Sorry, it is actually NOT a bug, but a result of a documented "feature" (http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-usagenotes-functionality.html and http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is-null):

"- You can find the row that contains the most recent AUTO_INCREMENT value by issuing a statement of the following form immediately after generating the value:

SELECT * FROM tbl_name WHERE auto_col IS NULL"

Look:

mysql> set session sql_auto_is_null = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(`id` int AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> create table t2(`id` int AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t1 values (NULL),(NULL);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> select * from t2;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select * from t1 as m right join t2 as s on m.id = s.id where s.id IS NULL; 
Empty set (0.00 sec)

Yes looks like this is the reason.

Interestingly, the manual specifies:
'by issuing a statement of the following form *immediately* after generating the value'

But, I am able to run many instructions and then issue a WHERE ... IS NULL, and it remembers it.

This means that if someone inserts something into a table 500 statements ago, and then uses an WHERE ... IS NULL it remembers the id:

drop table if exists t1;
drop table if exists dummy;
create table t1(`id` int AUTO_INCREMENT, id2 int, PRIMARY KEY (`id`)) ENGINE=MyISAM;
insert into t1 values (NULL,1);
insert into t1 values (NULL,2);
insert into t1 values (NULL,NULL);
insert into t1 values (NULL,4);
insert into t1 values (5,NULL);
insert into t1 values (6,NULL);
create table dummy(`id` int); /* dummy statements */
insert into dummy values (1); /* dummy statements */
insert into dummy values (2); /* dummy statements */
insert into dummy values (3); /* dummy statements */
select * from dummy;          /* dummy statements */
select * from dummy join t1;  /* dummy statements */
select * from t1 join dummy;  /* dummy statements */
select * from t1 where id2 IS NULL;
select * from t1 where id IS NULL;

The last statement on first run returns:

+----+------+
| id | id2  |
+----+------+
|  4 |    4 |
+----+------+
1 row in set (0.00 sec)

This would seem very confusing for the user.

Also, the hack was created specifically for 'Certain ODBC applications (including Delphi and Access) [that] may have trouble obtaining the auto-increment value' but it ends up affecting other instructions.

Also:

--------------
DROP TABLE IF EXISTS ta;
DROP TABLE IF EXISTS tb;
CREATE TABLE ta (row INT AUTO_INCREMENT PRIMARY KEY,firstname CHAR(32)) ENGINE=MyISAM;
CREATE TABLE tb (row INT AUTO_INCREMENT PRIMARY KEY,firstname CHAR(32)) ENGINE=MyISAM;
INSERT INTO ta VALUES (NULL,'');
INSERT INTO tb VALUES (NULL,'');
EXPLAIN SELECT * FROM ta RIGHT JOIN tb ON ta.row = tb.row WHERE ta.row IS NULL;
EXPLAIN SELECT * FROM ta RIGHT JOIN tb ON ta.row = tb.row WHERE ta.row IS NULL;
EXPLAIN SELECT * FROM ta WHERE ta.row IS NULL;
EXPLAIN SELECT * FROM ta WHERE ta.row IS NULL;

Gives: 
mysql> EXPLAIN SELECT * FROM ta RIGHT JOIN tb ON ta.row = tb.row WHERE ta.row IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
mysql> EXPLAIN SELECT * FROM ta RIGHT JOIN tb ON ta.row = tb.row WHERE ta.row IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
mysql> EXPLAIN SELECT * FROM ta WHERE ta.row IS NULL;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | ta    | system | PRIMARY       | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
mysql> EXPLAIN SELECT * FROM ta WHERE ta.row IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

Doesn't look right/consistent.

Also, when executed (re-run all of the above but drop EXPLAIN) it executes fine without warnings:

mysql> SELECT * FROM ta RIGHT JOIN tb ON ta.row = tb.row WHERE ta.row IS NULL;
Empty set (0.07 sec)

Why is there no error or warning?
--------------

Workaround:
set session sql_auto_is_null = 0;

Adjusted to S3/D4

Bug #41371 : same issue, a bug in the 'hack', target 5.4
Bug #18922 : same issue, marked 'not a bug'
Bug #29859 : same issue, marked 'not a bug'
Bug #43939 : same issue, marked 'not a bug'
Bug #44694 : same issue, marked 'closed'
Bug #41282 : same issue, marked 'duplicate' (of 29859)
Bug #15249 : related
Bug #38491 : related

Feature request to have this hack to be removed or "off" by default:
http://bugs.mysql.com/bug.php?id=47119

I am not sure this is 'docu' only.

Valeriy, could you please check my findings in comment on [1 Sep 6:50]?

Please note:

1. The inconsistent results in the ta/tb tables example
2. The missing error/warning after that

Please let me know your findings.

Roel,
I can answer if you clarify your questions:

> 1. The inconsistent results in the ta/tb tables example

What is inconsistent about the example? The fact that it work with outer joins (where I can see an actual, non-documented behavioural problem), that it works with joins in the first place or that two subsequent EXPLAINs in a row has the same effect as two subsequent SELECTs?

> 2. The missing error/warning after that

What warning or error is it that you feel is missing?

Since you filed a feature request, namely to remove this feature, which is assigned a developer, I suggest not to touch this bug until we see where Bug#47719 is heading.

> What is inconsistent about the example?

Thinking about it again, I'd answer:

A. EXPLAIN #3 and #4, though exactly the same, yield difference results. 
B. If the 'IS NULL' would be used for retrieving the AUTO_INCREMENT value, why is it not being returned for the first explain but only in the third one?

However, after some analysis of two of your points:

1. The fact that it work with outer joins (where I can see an actual, non-documented behavioural problem), 
2. that it works with joins in the first place or 

I found that:

#1, it does not work with outer joins
#2, it does not work with joins at all

(verified using EXPLAIN EXTENDED / SHOW WARNINGS)

So point A above is because of the same 'feature/hack', already discussed.
And, point B above is because of it not working for outer joins.

So, it looks like it's not inconsistent, just 'very confusing' (again) because of the same 'feature/hack'

> What warning or error is it that you feel is missing?

This one (logged a new bug):
http://bugs.mysql.com/bug.php?id=47296

> Since you filed a feature request, namely to remove this feature, which is assigned a developer, I suggest not to touch this bug until we see where Bug#47719 is heading.

I think you mean bug #47119. 

Yes, agreed.

> #1, it does not work with outer joins
> #2, it does not work with joins at all

Correction/improvement on this: 

The feature/hack *does* work with joins, as per the original description, but *when an error is found in the join, and a warning is returned* (as in the example we're discussing) it does *not* work with joins, but it *does work* with a normal select, even though it's faulty, as per the new bug :)

Or, said another way: the feature/hack does *not* work when a statement is found 'faulty' (a warning is returned). 

And, likely, in this case, for the third statement, since no correct warning is returned (incorrectly, as per the new bug) the feature/hack activates.

Fixed as Bug#41371.