Bug #52174 Sometimes wrong plan when reading a MAX value from non-NULL index
Submitted: 18 Mar 2010 10:59 Modified: 6 Apr 2010 13:04
Reporter: Martin Hansson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[18 Mar 2010 10:59] Martin Hansson
Description:
Bug was exposed by merging fix for Bug#47762 to mysql-pe.

The optimization to read a MIN/MAX value directly from an index gets disabled for the NULL safe operator <=> the first time around. If a query triggering this optimization is executed first, the bug does not appear.

The behavior is different on 64 vs. 32-bit. On 32-bit we see the behavior below. On 64 bit, the plan does not mend itself after executing a query.

How to repeat:
CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (1), (2), (3);
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
x	x	x	x	x	x	x	x	x	Select tables optimized away

-- bug appeared above.

SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
MIN( a )
NULL

-- note, correct result.

EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row

-- Now the plan is correct.
[18 Mar 2010 22:06] Miguel Solorzano
Thank you for the bug report. Sorry I am a little confused it is the below output on X86_64 machine right?:

[miguel@hegel ~]$ file dbs/6.0/libexec/mysqld
dbs/6.0/libexec/mysqld: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped
[miguel@hegel ~]$ dbs/6.0/libexec/mysqld 
100318 18:58:17  InnoDB: Started; log sequence number 0 46409
100318 18:58:17 [Note] Event Scheduler: Loaded 0 events
100318 18:58:17 [Note] dbs/6.0/libexec/mysqld: ready for connections.
Version: '6.0.12-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

[miguel@hegel ~]$ dbs/6.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: 6.0.12-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> EXPLAIN
    -> SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.01 sec)

mysql>
[19 Mar 2010 8:09] Martin Hansson
Hi Miguel,
the behavior you are observing is common for all systems. I.e 32-bit Linux, 64 bit Mac OS, 64 bit Linux. Could you please be so kind so try and run the following on 64 bit Linux?

CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (1), (2), (3);
EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <=> NULL;

Please be sure to include both EXPLAIN's. This would be very helpful to me.

Thank you.
[19 Mar 2010 8:13] Martin Hansson
The bug appears on 5.1 as well.
[6 Apr 2010 13:04] Miguel Solorzano
Sorry for my delayed reply, below what you asked on Mandriva 2010 X86_64:

[miguel@hegel ~]$ dbs/6.0/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 6.0.12-alpha-debug Source distribution   

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 ( a INT NOT NULL, KEY(a) );
Query OK, 0 rows affected (0.12 sec)

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

mysql> EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+----------+
| MIN( a ) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <=> NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>