Bug #48049 SELECT MIN(1) reports differently for MERGE tables in 5.0 and 5.1

Submitted: 14 Oct 2009 16:46
Reporter: Kyle Joiner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.37, 5.5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: index used, optimized away, Optimizer, regression

[14 Oct 2009 16:46] Kyle Joiner
Description:
in 5.0:
SELECT MIN(1) FROM myisam_table  -- is optimized away
SELECT MIN(1) FROM merge_table -- is optimized away

in 5.1:
SELECT MIN(1) FROM myisam_table -- is optimized away.
SELECT MIN(1) FROM merge_table -- uses index.

In consistent results between 5.0 and 5.1 

How to repeat:
mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.0.74-enterprise-gpl | 
+-----------------------+
1 row in set (0.00 sec)

mysql> create table test.t1 (a int primary key) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select min(1) from test.t1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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> create table test.t1m (a int primary key) engine=merge insert_method=last union=(test.t1);
Query OK, 0 rows affected (0.00 sec)

mysql> explain select min(1) from test.t1m;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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 version();
+-----------------------------------+
| version()                         |
+-----------------------------------+
| 5.1.37sp1-enterprise-gpl-advanced | 
+-----------------------------------+
1 row in set (0.00 sec)

mysql> create table test.t1 (a int primary key) engine=myisam;
Query OK, 0 rows affected (0.07 sec)

mysql> explain select min(1) from test.t1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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> create table test.t1m (a int primary key) engine=merge insert_method=last union=(test.t1);
Query OK, 0 rows affected (0.07 sec)

mysql> explain select min(1) from test.t1m;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1m   | index | NULL          | PRIMARY | 4       | NULL |    0 | Using index | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Suggested fix:
Make the results consistent between 5.0 and 5.1

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.