[Commits] Rev 3504: 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 04:37:17 EEST 2012


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

------------------------------------------------------------
revno: 3504
revision-id: psergey at askmonty.org-20120419013716-aks63fg46gxjasex
parent: psergey at askmonty.org-20120419005032-3bdekvc9chicebd2
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look4-cp
timestamp: Thu 2012-04-19 05:37:16 +0400
message:
  BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
  - Part#2: Don't try to construct a LooseScan access on indexes that do not guarantee 
    index-ordered reads.
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2012-04-19 00:50:32 +0000
+++ b/mysql-test/r/subselect_sj.result	2012-04-19 01:37:16 +0000
@@ -2680,10 +2680,23 @@
 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
+1	PRIMARY	t1_1	ALL	NULL	NULL	NULL	NULL	11	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1_1.a	2	Start temporary
+1	PRIMARY	t1_2	ALL	NULL	NULL	NULL	NULL	11	Using where; End temporary
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
+a	b	a	b
+3	1	9	1
+5	8	4	0
+3	9	9	1
+2	4	6	8
+2	4	4	0
+2	6	6	8
+2	6	4	0
+5	4	4	0
+7	7	7	7
+5	4	4	0
 DROP VIEW v1;
 DROP TABLE t1;
 set @@join_cache_level= @tmp_jcl_978479;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2012-04-19 00:50:32 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-04-19 01:37:16 +0000
@@ -2694,10 +2694,23 @@
 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
+1	PRIMARY	t1_1	ALL	NULL	NULL	NULL	NULL	11	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1_1.a	2	Start temporary
+1	PRIMARY	t1_2	ALL	NULL	NULL	NULL	NULL	11	Using where; End temporary
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
+a	b	a	b
+3	1	9	1
+5	8	4	0
+3	9	9	1
+2	4	6	8
+2	4	4	0
+2	6	6	8
+2	6	4	0
+5	4	4	0
+7	7	7	7
+5	4	4	0
 DROP VIEW v1;
 DROP TABLE t1;
 set @@join_cache_level= @tmp_jcl_978479;

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2012-04-19 00:50:32 +0000
+++ b/mysql-test/t/subselect_sj.test	2012-04-19 01:37:16 +0000
@@ -2390,6 +2390,9 @@
 SELECT * FROM t1 AS t1_1, t1 AS t1_2
   WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
 
+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;

=== modified file 'sql/opt_subselect.h'
--- a/sql/opt_subselect.h	2011-12-14 00:39:29 +0000
+++ b/sql/opt_subselect.h	2012-04-19 01:37:16 +0000
@@ -130,7 +130,9 @@
   
   void add_keyuse(table_map remaining_tables, KEYUSE *keyuse)
   {
-    if (try_loosescan && keyuse->sj_pred_no != UINT_MAX)
+    if (try_loosescan && keyuse->sj_pred_no != UINT_MAX &&
+        (keyuse->table->file->index_flags(keyuse->key, 0, 1 ) & HA_READ_ORDER))
+
     {
       if (!(remaining_tables & keyuse->used_tables))
       {



More information about the commits mailing list