Bug #48049 SELECT MIN(1) reports differently for MERGE tables in 5.0 and 5.1
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.