MySQL Bugs: #52174: Sometimes wrong plan when reading a MAX value from non-NULL index

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

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.

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>

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.

The bug appears on 5.1 as well.

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>