| Bug #15106 | EXPLAIN output differs for almost same query | ||
|---|---|---|---|
| Submitted: | 21 Nov 2005 19:32 | Modified: | 21 Dec 2005 10:44 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.15, 5.0.17-bk | OS: | Linux (Linux, Windows) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[22 Nov 2005 0:17]
Hartmut Holzgraefe
verified, i also get different result sets: mysql> SELECT * FROM t1 JOIN t2 ON t2.c1 = t1.c2 JOIN t3 ON t3.c1 = t2.c1 WHERE t1.c2 = '1'; +-----+----+-----+------------+------------+------+----+----+-----+----+----+-----+----+-----+----+ | c1 | c2 | c3 | c4 | c5 | c6 | c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4 | c5 | +-----+----+-----+------------+------------+------+----+----+-----+----+----+-----+----+-----+----+ | Pv9 | Q | puZ | 1471135022 | 1997-06-10 | -20 | Q | D | 7oT | gw | Q | Rev | dn | NVn | lR | +-----+----+-----+------------+------------+------+----+----+-----+----+----+-----+----+-----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t1 JOIN t2 ON t1.c2 = t2.c1 JOIN t3 ON t3.c1 = t2.c1 WHERE t1.c2 = '1'; Empty set (0.00 sec)
[24 Nov 2005 23:01]
Connor Harty
In regards to the comment above, the column used in the join would appear to be optimized away:
SELECT t1.* FROM t1 JOIN t2 ON t2.c1 = t1.c2 JOIN t3 ON t3.c1 = t2.c1 where t1.c1 = 'PV9' and t1.c2='I feel ignored';
+-----+----+-----+------------+------------+------+
| c1 | c2 | c3 | c4 | c5 | c6 |
+-----+----+-----+------------+------------+------+
| Pv9 | Q | puZ | 1471135022 | 1997-06-10 | -20 |
+-----+----+-----+------------+------------+------+
While
SELECT t1.* FROM t1 JOIN t2 ON t2.c1 = t1.c2 JOIN t3 ON t3.c1 = t2.c1 where t1.c1 = 'Not me' and t1.c2='Q';
yields:
Empty set (0.00 sec)
Another example:
DROP TABLE IF EXISTS `a`;
CREATE table a (key_a int4 not null, optimus varchar(32), PRIMARY KEY(key_a));
INSERT INTO a VALUES (0,'');
INSERT INTO a VALUES (1,'i');
INSERT INTO a VALUES (2,'j');
INSERT INTO a VALUES (3,'k');
DROP TABLE IF EXISTS `aprime`;
CREATE table aprime (key_a int4 not null, prime varchar(32), PRIMARY KEY(key_a));
INSERT INTO aprime VALUES (1,'r');
INSERT INTO aprime VALUES (2,'s');
INSERT INTO aprime VALUES (3,'t');
DROP TABLE IF EXISTS `b`;
CREATE table b (key_a int4 not null, key_b int4 not null, foo varchar(32) , PRIMARY KEY(key_a,key_b));
INSERT INTO b VALUES (1,5,'x');
INSERT INTO b VALUES (1,6,'y');
INSERT INTO b VALUES (2,5,'xx');
INSERT INTO b VALUES (2,6,'yy');
INSERT INTO b VALUES (2,7,'zz');
INSERT INTO b VALUES (3,5,'xxx');
SELECT aprime.key_a,foo
FROM a inner join aprime on a.key_a = aprime.key_a
inner join b on a.key_a = b.key_a WHERE aprime.key_a = 2 and key_b=5;
gives:
+-------+------+
| key_a | foo |
+-------+------+
| 1 | x |
| 2 | xx |
| 3 | xxx |
+-------+------+
EXPLAIN:
+----+-------------+--------+--------+---------------+---------+---------+-----------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+---------------+---------+---------+-----------------------------+------+--------------------------+
| 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 4 | NULL | 4 | Using index |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 8 | mysql_bugtest.a.key_a,const | 1 | |
| 1 | SIMPLE | aprime | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where; Using index |
+----+-------------+--------+--------+---------------+---------+---------+-----------------------------+------+--------------------------+
while:
SELECT aprime.key_a,foo
FROM a inner join aprime on aprime.key_a = a.key_a
inner join b on a.key_a = b.key_a WHERE aprime.key_a = 2 and key_b=5;
gives:
+-------+------+
| key_a | foo |
+-------+------+
| 2 | xx |
+-------+------+
EXPLAIN:
+----+-------------+--------+-------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------------+------+-------------+
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | aprime | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 8 | const,const | 1 | |
+----+-------------+--------+-------+---------------+---------+---------+-------------+------+-------------+
[26 Nov 2005 2:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32731
[12 Dec 2005 6:43]
Igor Babaev
ChangeSet 1.2012 05/11/25 18:51:44 igor@rurik.mysql.com +3 -0 Fixed bug #15106. A typo bug caused loss of a predicate of the form field=const in some cases. sql/item_cmpfunc.cc 1.186 05/11/25 18:51:39 igor@rurik.mysql.com +1 -1 Fixed bug #15106. A typo bug caused loss of a predicate of the form field=const in some cases. mysql-test/t/select.test 1.93 05/11/25 18:51:39 igor@rurik.mysql.com +45 -0 Added a test case for bug #15106. mysql-test/r/select.result 1.114 05/11/25 18:51:39 igor@rurik.mysql.com +46 -0 Added a test case for bug #15106. The fix will appear in 5.0.18.
[21 Dec 2005 10:44]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Documented in 5.0.18 changelog. Closed.
Description: Given the same query, with subtle difference, EXPLAIN output is different. Only thing that changed is the order of operands for the equal sign in the join. e.g. t1.id=t2.id instead of t2.id=t1.id This problem exists on 5.0.15-nt and todays 5.0.17-bk on linux. 4.1.14-nt had correct behaviour, with both EXPLAIN outputs being identical. No difference whether the tables are InnoDB/MyISAM. /*BAD*/ mysql> EXPLAIN SELECT * FROM -> t1 JOIN t2 -> ON -> t2.c1 = t1.c2 -> JOIN t3 ON -> t3.c1 = t2.c1 -> WHERE -> t1.c2 = '1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: idx1,idx2 key: NULL <------------------See here. Didn't use an index key_len: NULL ref: NULL rows: 10 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: test.t1.c2 rows: 1 Extra: Using index *************************** 3. row *************************** id: 1 select_type: SIMPLE table: t3 type: ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: test.t1.c2 rows: 1 Extra: Using index 3 rows in set (0.00 sec) /*GOOD*/ mysql> EXPLAIN SELECT * FROM -> t1 JOIN t2 -> ON -> t1.c2 = t2.c1 -> JOIN t3 ON -> t3.c1 = t2.c1 -> WHERE -> t1.c2 = '1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: idx1,idx2 key: idx1<------------------See here. Uses an index key_len: 1 ref: const rows: 1 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: const rows: 1 Extra: Using where; Using index *************************** 3. row *************************** id: 1 select_type: SIMPLE table: t3 type: ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: const rows: 1 Extra: Using where; Using index 3 rows in set (0.00 sec) mysql> How to repeat: Cut'n paste the below SQL, and examine the last two EXPLAIN outputs for table t1. --------------------- DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `c1` char(6) NOT NULL default '', `c2` char(1) NOT NULL default '', `c3` char(6) NOT NULL default '', `c4` int(11) default '0', `c5` date default NULL, `c6` tinyint(4) default NULL, PRIMARY KEY (`c1`,`c2`,`c3`), KEY `idx1` (`c2`,`c5`,`c6`), KEY `idx2` (`c2`,`c4`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `t1` VALUES ('6Cc6C','B','HB',4469598,'1900-01-21',0),('79','5','r',-1460837217,'1952-12-31',-32),('a67cZ','L','rA8',-30263936,'1931-12-25',20),('b5b','g','N2',-192787920,'1928-02-04',-121),('FY','u','kC3',1897465773,'1956-07-10',-14),('g8I','l','mzb',38226,'1859-08-31',-60),('jjs','0','6gE',-12666654,'1845-09-19',50),('ml','D','RupR',1801032310,'1971-06-09',3),('Pv9','Q','puZ',1471135022,'1997-06-10',-20),('y9b7','Y','bj2a',158672415,'1902-05-27',48); DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `c1` char(1) NOT NULL default '', `c2` char(6) NOT NULL default '', `c3` char(6) NOT NULL default '', `c4` char(6) NOT NULL default '', PRIMARY KEY (`c1`,`c2`,`c3`,`c4`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `t2` VALUES ('5','FFdo','P8','0m'),('8','r7','8is','PF'),('k','7o','OtI','c'),('k','Ql7vg','Q','hA'),('L','7','z1Dc','TAwR'),('L','Y','vn','nh7Z'),('n','ya55','ET3','Azj'),('Q','D','7oT','gw'),('R','5C','j','1i'),('u','T0BSXU','oQC84S','lx'); DROP TABLE IF EXISTS `t3`; CREATE TABLE `t3` ( `c1` char(1) NOT NULL default '', `c2` char(6) NOT NULL default '', `c3` char(6) NOT NULL default '', `c4` char(3) NOT NULL default '', `c5` char(6) NOT NULL default '', PRIMARY KEY (`c1`,`c2`,`c3`,`c4`,`c5`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `t3` VALUES ('7','Xplh','lnB','7','Au'),('h','iMCp','sJ','j','Fr'),('I','mIbp','PiAr','L','mzqG'),('j','SQZ','N','7','rjGG'),('m','VFn','fCg','v','9P'),('P','cs','fYz','U','mhq8'),('p','iQV','GmZtu7','W','lzYiK'),('Q','Rev','dn','NVn','lR'),('S','di','p','BM','C0b'),('Y','FwHri','qsm','Si','yrb'); EXPLAIN SELECT * FROM t1 JOIN t2 ON t2.c1 = t1.c2 JOIN t3 ON t3.c1 = t2.c1 WHERE t1.c2 = '1'\G EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c2 = t2.c1 JOIN t3 ON t3.c1 = t2.c1 WHERE t1.c2 = '1'\G ------------- Suggested fix: not sure...