[Commits] Rev 2999: BUG#675118: Elimination of a table results in an invalid execution plan in file:///home/psergey/dev2/5.1-bug675118/

Sergey Petrunya psergey at askmonty.org
Mon Jan 10 11:39:57 EET 2011


At file:///home/psergey/dev2/5.1-bug675118/

------------------------------------------------------------
revno: 2999
revision-id: psergey at askmonty.org-20110110093954-vr3nvhtzmbz8cgif
parent: igor at askmonty.org-20101224233023-80fkq8zbf9xzh2vl
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.1-bug675118
timestamp: Mon 2011-01-10 11:39:54 +0200
message:
  BUG#675118: Elimination of a table results in an invalid execution plan
  - Fix for MySQL BUG#52357 added NESTED_JOIN::is_fully_covered() which would 
    not take into account that MariaDB's table elimination could eliminate tables
    from join plan (and so, from join nest).
    Fixed the check in the function to compare post-table-elimination numbers.
=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result	2010-09-11 15:20:35 +0000
+++ b/mysql-test/r/table_elim.result	2011-01-10 09:39:54 +0000
@@ -535,3 +535,35 @@
 field4 != 6;
 field1	field2	field3	field4	field5	field6
 drop table t0,t1,t2,t3,t4,t5,t6;
+#
+# BUG#675118: Elimination of a table results in an invalid execution plan
+#
+CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ;
+CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ;
+Warnings:
+Warning	1071	Specified key was too long; max key length is 1000 bytes
+INSERT IGNORE INTO t2 VALUES ('xcddwntkbxyorzdv'),
+('cnxxcddwntkbxyor'),('r'),('r'), ('did'),('I'),('when'),
+('hczkfqjeggivdvac'),('e'),('okay'),('up');
+CREATE TABLE t3 (f4 varchar(1024), f1 int(11), f2 int(11)) ;
+INSERT IGNORE INTO t3 VALUES ('f','4','0'),('n','5','-996540416');
+CREATE TABLE t4 (f1 int(11), f3 varchar(10)) ;
+INSERT IGNORE INTO t4 VALUES ('8','n'),('9','nwzcerzsgx'),('10','c');
+CREATE TABLE t5 (f5 int(11), KEY (f5)) ;
+EXPLAIN 
+SELECT alias2.f2
+FROM t2 AS alias1
+LEFT JOIN t3 AS alias2
+LEFT JOIN t4 AS alias3
+LEFT JOIN t1 AS alias4 ON alias3.f1 = alias4.f1
+JOIN t5 AS alias5
+ON alias3.f3 ON alias2.f1 = alias5.f5 ON alias1.f4 = alias2.f4
+WHERE alias2.f2 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	alias2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	alias5	ref	f5	f5	5	test.alias2.f1	2	Using index
+1	SIMPLE	alias3	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	alias1	ALL	f4	NULL	NULL	NULL	11	Using where; Using join buffer
+# ^^ The above must not produce a QEP of alias2,alias5,alias1,alias3
+#    as that violates the "no interleaving of outer join nests" rule.
+DROP TABLE t1,t2,t3,t4,t5;

=== modified file 'mysql-test/t/table_elim.test'
--- a/mysql-test/t/table_elim.test	2010-03-06 12:09:02 +0000
+++ b/mysql-test/t/table_elim.test	2011-01-10 09:39:54 +0000
@@ -467,3 +467,38 @@
   field4 != 6;
 
 drop table t0,t1,t2,t3,t4,t5,t6;
+
+--echo #
+--echo # BUG#675118: Elimination of a table results in an invalid execution plan
+--echo #
+CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ;
+
+CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ;
+INSERT IGNORE INTO t2 VALUES ('xcddwntkbxyorzdv'),
+   ('cnxxcddwntkbxyor'),('r'),('r'), ('did'),('I'),('when'),
+   ('hczkfqjeggivdvac'),('e'),('okay'),('up');
+
+CREATE TABLE t3 (f4 varchar(1024), f1 int(11), f2 int(11)) ;
+INSERT IGNORE INTO t3 VALUES ('f','4','0'),('n','5','-996540416');
+
+CREATE TABLE t4 (f1 int(11), f3 varchar(10)) ;
+INSERT IGNORE INTO t4 VALUES ('8','n'),('9','nwzcerzsgx'),('10','c');
+
+CREATE TABLE t5 (f5 int(11), KEY (f5)) ;
+
+EXPLAIN 
+SELECT alias2.f2
+FROM t2 AS alias1
+LEFT JOIN t3 AS alias2
+LEFT JOIN t4 AS alias3
+LEFT JOIN t1 AS alias4 ON alias3.f1 = alias4.f1
+JOIN t5 AS alias5
+ON alias3.f3 ON alias2.f1 = alias5.f5 ON alias1.f4 = alias2.f4
+WHERE alias2.f2 ;
+--echo # ^^ The above must not produce a QEP of alias2,alias5,alias1,alias3
+--echo #    as that violates the "no interleaving of outer join nests" rule.
+
+DROP TABLE t1,t2,t3,t4,t5;
+
+
+

=== modified file 'sql/table.h'
--- a/sql/table.h	2010-11-23 21:39:59 +0000
+++ b/sql/table.h	2011-01-10 09:39:54 +0000
@@ -1736,7 +1736,7 @@
 
      2. All child join nest nodes are fully covered.
    */
-  bool is_fully_covered() const { return join_list.elements == counter; }
+  bool is_fully_covered() const { return n_tables == counter; }
 } NESTED_JOIN;
 
 



More information about the commits mailing list