[Commits] Rev 3503: BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF in file:///home/psergey/dev2/5.3-look4-cp/

Sergey Petrunya psergey at askmonty.org
Thu Apr 19 03:50:36 EEST 2012


At file:///home/psergey/dev2/5.3-look4-cp/

------------------------------------------------------------
revno: 3503
revision-id: psergey at askmonty.org-20120419005032-3bdekvc9chicebd2
parent: wlad at montyprogram.com-20120416213538-761a4vkzg1sokgo2
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look4-cp
timestamp: Thu 2012-04-19 04:50:32 +0400
message:
  BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
  Part#1: make EXPLAIN's plan match the one by actual execution: 
  Item_subselect::used_tables() should return the same value irrespectively 
  of whether we're running an EXPLAIN or a SELECT.
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2012-04-02 17:41:54 +0000
+++ b/mysql-test/r/subselect_sj.result	2012-04-19 00:50:32 +0000
@@ -2661,4 +2661,31 @@
 DEALLOCATE PREPARE pstmt;
 DROP VIEW v1;
 DROP TABLE t1, t2;
+#
+# BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
+#
+set @tmp_jcl_978479= @@join_cache_level;
+set join_cache_level=0;
+set @tmp_os_978479= @@optimizer_switch;
+set optimizer_switch = 'derived_with_keys=on,loosescan=on,semijoin=on,materialization=off';
+# Part#1: make sure EXPLAIN is using LooseScan:
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES
+(4,0),(6,8),(3,1),(5,8),(3,9),(2,4),
+(2,6),(9,1),(5,4),(7,7),(5,4);
+CREATE ALGORITHM=TEMPTABLE
+VIEW v1 AS SELECT * FROM t1;
+# This will use LooseScan:
+EXPLAIN
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1	ALL	NULL	NULL	NULL	NULL	11	
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	11	Using where; LooseScan
+1	PRIMARY	t1_2	ALL	NULL	NULL	NULL	NULL	11	Using where
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	
+DROP VIEW v1;
+DROP TABLE t1;
+set @@join_cache_level= @tmp_jcl_978479;
+set @@optimizer_switch= @tmp_os_978479;
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2012-04-02 17:41:54 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-04-19 00:50:32 +0000
@@ -2675,6 +2675,33 @@
 DEALLOCATE PREPARE pstmt;
 DROP VIEW v1;
 DROP TABLE t1, t2;
+#
+# BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
+#
+set @tmp_jcl_978479= @@join_cache_level;
+set join_cache_level=0;
+set @tmp_os_978479= @@optimizer_switch;
+set optimizer_switch = 'derived_with_keys=on,loosescan=on,semijoin=on,materialization=off';
+# Part#1: make sure EXPLAIN is using LooseScan:
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES
+(4,0),(6,8),(3,1),(5,8),(3,9),(2,4),
+(2,6),(9,1),(5,4),(7,7),(5,4);
+CREATE ALGORITHM=TEMPTABLE
+VIEW v1 AS SELECT * FROM t1;
+# This will use LooseScan:
+EXPLAIN
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1	ALL	NULL	NULL	NULL	NULL	11	
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	11	Using where; LooseScan
+1	PRIMARY	t1_2	ALL	NULL	NULL	NULL	NULL	11	Using where
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	
+DROP VIEW v1;
+DROP TABLE t1;
+set @@join_cache_level= @tmp_jcl_978479;
+set @@optimizer_switch= @tmp_os_978479;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2012-03-21 07:18:20 +0000
+++ b/mysql-test/t/subselect_sj.test	2012-04-19 00:50:32 +0000
@@ -2366,5 +2366,35 @@
 DROP VIEW v1;
 DROP TABLE t1, t2;
 
+--echo #
+--echo # BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
+--echo #
+
+set @tmp_jcl_978479= @@join_cache_level;
+set join_cache_level=0;
+
+set @tmp_os_978479= @@optimizer_switch;
+set optimizer_switch = 'derived_with_keys=on,loosescan=on,semijoin=on,materialization=off';
+
+--echo # Part#1: make sure EXPLAIN is using LooseScan:
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES
+  (4,0),(6,8),(3,1),(5,8),(3,9),(2,4),
+  (2,6),(9,1),(5,4),(7,7),(5,4);
+
+CREATE ALGORITHM=TEMPTABLE
+  VIEW v1 AS SELECT * FROM t1;
+
+--echo # This will use LooseScan:
+EXPLAIN
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+  WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
+
+DROP VIEW v1;
+DROP TABLE t1;
+set @@join_cache_level= @tmp_jcl_978479;
+set @@optimizer_switch= @tmp_os_978479;
+
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-04-08 00:27:00 +0000
+++ b/sql/item_subselect.cc	2012-04-19 00:50:32 +0000
@@ -757,7 +757,8 @@
 
 table_map Item_subselect::used_tables() const
 {
-  return (table_map) (engine->uncacheable() ? used_tables_cache : 0L);
+  return (table_map) ((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN)? 
+                      used_tables_cache : 0L);
 }
 
 



More information about the commits mailing list