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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.1.37, 5.5.7
• OS: Any
• 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