[Commits] Rev 3624: Merge. in file:///home/igor/maria/maria-5.3-merge/

Igor Babaev igor at askmonty.org
Tue Feb 12 21:49:47 EET 2013


At file:///home/igor/maria/maria-5.3-merge/

------------------------------------------------------------
revno: 3624 [merge]
revision-id: igor at askmonty.org-20130212194946-azyoaim04r3b8evx
parent: sanja at askmonty.org-20130211085558-xk7c32q4l8aaah2t
parent: igor at askmonty.org-20130208054602-fr43en5qrvp02qa3
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-merge
timestamp: Tue 2013-02-12 11:49:46 -0800
message:
  Merge.
modified:
  mysql-test/r/subselect_sj2_mat.result subselect_sj2_mat.re-20110203133247-60oo2ydzq1r2g7ib-1
  mysql-test/t/subselect_sj2_mat.test subselect_sj2_mat.te-20110203133247-60oo2ydzq1r2g7ib-2
  sql/opt_subselect.cc           opt_subselect.cc-20100215190428-nekkl8wisp0k6nlk-1
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/sql_select.h               sp1f-sql_select.h-19700101030959-oqegfxr76xlgmrzd6qlevonoibfnwzoz
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_sj2_mat.result'
--- a/mysql-test/r/subselect_sj2_mat.result	2012-04-04 17:35:34 +0000
+++ b/mysql-test/r/subselect_sj2_mat.result	2013-02-08 05:46:02 +0000
@@ -1127,3 +1127,105 @@
 ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
 DROP TABLE t1, t2;
 set max_join_size= @tmp_906385;
+# 
+# mdev-3995: Wrong result for semijoin with materialization  
+# 
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (
+cat_id int(10) unsigned NOT NULL,
+PRIMARY KEY (cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(709411),(709412),(709413),(709414),(709416),(709417),(709418),(709419),(709421),(709422),
+(709424),(709425),(709427),(709428),(709429),(709431),(709432),(709433),(709434),(709435),
+(709438),(709439),(709441),(709442),(709443),(709444),(709445),(709446),(709447),(709450),
+(709451),(709454),(709455),(709456),(709457),(709459),(709460),(709461),(709462),(709463),
+(709464),(709465),(709467),(709469),(709470),(709471),(709472),(709473),(709474),(709475),
+(709476),(709477),(709478),(709479),(709480),(709481),(709483),(709484),(709485),(709487),
+(709490),(709491),(709492),(709493),(709494),(709495),(709496),(709497),(709498),(709499),
+(709500),(709501),(709502),(709503),(709504),(709505),(709506),(709507),(709509),(709510),
+(709511),(709512),(709513),(709514),(709515),(709516),(709517),(709518),(709519),(709520),
+(709521),(709522),(709523),(709524),(709525),(709526),(709527),(709528),(709529),(709530),
+(709531),(709532),(709533),(709534),(709535),(709536),(709537),(709538),(709539),(709540),
+(709541),(709542),(709543),(709544),(709545),(709546),(709548),(709549),(709551),(709552),
+(709553),(709555),(709556),(709557),(709558),(709559),(709560),(709561),(709562),(709563),
+(709564),(709565),(709566),(709567),(709568),(709569),(709570),(709571),(709572),(709573),
+(709574),(709575),(709576),(709577),(709578),(709579),(709580),(709581),(709582),(709583),
+(709584),(709585),(709586),(709587),(709588),(709590),(709591),(709592),(709593),(709594),
+(709595),(709596),(709597),(709598),(709600),(709601),(709602),(709603),(709604),(709605),
+(709606),(709608),(709609),(709610),(709611),(709612),(709613),(709614),(709615),(709616),
+(709617),(709618),(709619),(709620),(709621),(709622),(709623),(709624),(709625),(709626),
+(709627),(709628),(709629),(709630),(709631),(709632),(709633),(709634),(709635),(709637),
+(709638),(709639),(709640),(709641),(709642),(709643),(709644),(709645),(709646),(709649),
+(709650),(709651),(709652),(709653),(709654),(709655),(709656),(709657),(709658),(709659);
+CREATE TABLE t2 (
+cat_id int(10) NOT NULL,
+KEY cat_id (cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(708742),(708755),(708759),(708761),(708766),(708769),(708796),(708798),(708824),(708825),
+(708838),(708844),(708861),(708882),(708887),(708889),(708890),(709586),(709626);
+CREATE TABLE t3 (
+sack_id int(10) unsigned NOT NULL,
+kit_id tinyint(3) unsigned NOT NULL DEFAULT '0',
+cat_id int(10) unsigned NOT NULL,
+PRIMARY KEY (sack_id,kit_id,cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+(33479,6,708523),(33479,6,708632),(33479,6,709085),(33479,6,709586),(33479,6,709626);
+CREATE TABLE t4 (
+cat_id int(10) unsigned NOT NULL,
+KEY cat_id (cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t4 (cat_id) SELECT cat_id from t2;
+set optimizer_switch='materialization=off';
+EXPLAIN
+SELECT count(*) FROM t1, t3 
+WHERE  t1.cat_id = t3.cat_id AND 
+t3.cat_id IN (SELECT cat_id FROM t2) AND 
+t3.sack_id = 33479 AND t3.kit_id = 6;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ref	PRIMARY	PRIMARY	5	const,const	4	Using index
+1	PRIMARY	t2	ref	cat_id	cat_id	4	test.t3.cat_id	2	Using where; Using index; FirstMatch(t3)
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.cat_id	1	Using where; Using index
+SELECT count(*) FROM t1, t3 
+WHERE  t1.cat_id = t3.cat_id AND 
+t3.cat_id IN (SELECT cat_id FROM t2) AND 
+t3.sack_id = 33479 AND t3.kit_id = 6;
+count(*)
+2
+set optimizer_switch='materialization=on';
+EXPLAIN
+SELECT count(*) FROM t1, t3 
+WHERE  t1.cat_id = t3.cat_id AND 
+t3.cat_id IN (SELECT cat_id FROM t4) AND 
+t3.sack_id = 33479 AND t3.kit_id = 6;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ref	PRIMARY	PRIMARY	5	const,const	4	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.cat_id	1	Using index
+2	MATERIALIZED	t4	index	cat_id	cat_id	4	NULL	19	Using index
+SELECT count(*) FROM t1, t3 
+WHERE  t1.cat_id = t3.cat_id AND 
+t3.cat_id IN (SELECT cat_id FROM t4) AND 
+t3.sack_id = 33479 AND t3.kit_id = 6;
+count(*)
+2
+EXPLAIN
+SELECT count(*) FROM t1, t3 
+WHERE  t1.cat_id = t3.cat_id AND 
+t3.cat_id IN (SELECT cat_id FROM t2) AND 
+t3.sack_id = 33479 AND t3.kit_id = 6;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ref	PRIMARY	PRIMARY	5	const,const	4	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.cat_id	1	Using index
+2	MATERIALIZED	t2	index	cat_id	cat_id	4	NULL	19	Using index
+SELECT count(*) FROM t1, t3 
+WHERE  t1.cat_id = t3.cat_id AND 
+t3.cat_id IN (SELECT cat_id FROM t2) AND 
+t3.sack_id = 33479 AND t3.kit_id = 6;
+count(*)
+2
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/subselect_sj2_mat.test'
--- a/mysql-test/t/subselect_sj2_mat.test	2011-12-19 18:24:10 +0000
+++ b/mysql-test/t/subselect_sj2_mat.test	2013-02-08 05:46:02 +0000
@@ -37,3 +37,105 @@
 set max_join_size= @tmp_906385;
 
 
+--echo # 
+--echo # mdev-3995: Wrong result for semijoin with materialization  
+--echo # 
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+CREATE TABLE t1 (
+  cat_id int(10) unsigned NOT NULL,
+  PRIMARY KEY (cat_id)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES
+(709411),(709412),(709413),(709414),(709416),(709417),(709418),(709419),(709421),(709422),
+(709424),(709425),(709427),(709428),(709429),(709431),(709432),(709433),(709434),(709435),
+(709438),(709439),(709441),(709442),(709443),(709444),(709445),(709446),(709447),(709450),
+(709451),(709454),(709455),(709456),(709457),(709459),(709460),(709461),(709462),(709463),
+(709464),(709465),(709467),(709469),(709470),(709471),(709472),(709473),(709474),(709475),
+(709476),(709477),(709478),(709479),(709480),(709481),(709483),(709484),(709485),(709487),
+(709490),(709491),(709492),(709493),(709494),(709495),(709496),(709497),(709498),(709499),
+(709500),(709501),(709502),(709503),(709504),(709505),(709506),(709507),(709509),(709510),
+(709511),(709512),(709513),(709514),(709515),(709516),(709517),(709518),(709519),(709520),
+(709521),(709522),(709523),(709524),(709525),(709526),(709527),(709528),(709529),(709530),
+(709531),(709532),(709533),(709534),(709535),(709536),(709537),(709538),(709539),(709540),
+(709541),(709542),(709543),(709544),(709545),(709546),(709548),(709549),(709551),(709552),
+(709553),(709555),(709556),(709557),(709558),(709559),(709560),(709561),(709562),(709563),
+(709564),(709565),(709566),(709567),(709568),(709569),(709570),(709571),(709572),(709573),
+(709574),(709575),(709576),(709577),(709578),(709579),(709580),(709581),(709582),(709583),
+(709584),(709585),(709586),(709587),(709588),(709590),(709591),(709592),(709593),(709594),
+(709595),(709596),(709597),(709598),(709600),(709601),(709602),(709603),(709604),(709605),
+(709606),(709608),(709609),(709610),(709611),(709612),(709613),(709614),(709615),(709616),
+(709617),(709618),(709619),(709620),(709621),(709622),(709623),(709624),(709625),(709626),
+(709627),(709628),(709629),(709630),(709631),(709632),(709633),(709634),(709635),(709637),
+(709638),(709639),(709640),(709641),(709642),(709643),(709644),(709645),(709646),(709649),
+(709650),(709651),(709652),(709653),(709654),(709655),(709656),(709657),(709658),(709659);
+
+CREATE TABLE t2 (
+  cat_id int(10) NOT NULL,
+  KEY cat_id (cat_id)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES
+(708742),(708755),(708759),(708761),(708766),(708769),(708796),(708798),(708824),(708825),
+(708838),(708844),(708861),(708882),(708887),(708889),(708890),(709586),(709626);
+
+CREATE TABLE t3 (
+  sack_id int(10) unsigned NOT NULL,
+  kit_id tinyint(3) unsigned NOT NULL DEFAULT '0',
+  cat_id int(10) unsigned NOT NULL,
+  PRIMARY KEY (sack_id,kit_id,cat_id)
+) ENGINE=MyISAM;
+
+INSERT INTO t3 VALUES
+(33479,6,708523),(33479,6,708632),(33479,6,709085),(33479,6,709586),(33479,6,709626);
+
+CREATE TABLE t4 (
+  cat_id int(10) unsigned NOT NULL,
+  KEY cat_id (cat_id)
+) ENGINE=MyISAM;
+
+INSERT INTO t4 (cat_id) SELECT cat_id from t2; 
+
+set optimizer_switch='materialization=off';
+
+EXPLAIN
+SELECT count(*) FROM t1, t3 
+  WHERE  t1.cat_id = t3.cat_id AND 
+         t3.cat_id IN (SELECT cat_id FROM t2) AND 
+         t3.sack_id = 33479 AND t3.kit_id = 6;
+
+SELECT count(*) FROM t1, t3 
+  WHERE  t1.cat_id = t3.cat_id AND 
+         t3.cat_id IN (SELECT cat_id FROM t2) AND 
+         t3.sack_id = 33479 AND t3.kit_id = 6;
+
+set optimizer_switch='materialization=on';
+
+EXPLAIN
+SELECT count(*) FROM t1, t3 
+  WHERE  t1.cat_id = t3.cat_id AND 
+         t3.cat_id IN (SELECT cat_id FROM t4) AND 
+         t3.sack_id = 33479 AND t3.kit_id = 6;
+
+SELECT count(*) FROM t1, t3 
+  WHERE  t1.cat_id = t3.cat_id AND 
+         t3.cat_id IN (SELECT cat_id FROM t4) AND 
+         t3.sack_id = 33479 AND t3.kit_id = 6;
+
+EXPLAIN
+SELECT count(*) FROM t1, t3 
+  WHERE  t1.cat_id = t3.cat_id AND 
+         t3.cat_id IN (SELECT cat_id FROM t2) AND 
+         t3.sack_id = 33479 AND t3.kit_id = 6;
+
+SELECT count(*) FROM t1, t3 
+  WHERE  t1.cat_id = t3.cat_id AND 
+         t3.cat_id IN (SELECT cat_id FROM t2) AND 
+         t3.sack_id = 33479 AND t3.kit_id = 6;
+
+
+DROP TABLE t1,t2,t3,t4;
+
+set optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-08-21 18:24:34 +0000
+++ b/sql/opt_subselect.cc	2013-02-08 05:46:02 +0000
@@ -2526,6 +2526,10 @@
           /* Mark strategy as used */ 
           (*strategy)->mark_used();
           pos->sj_strategy= sj_strategy;
+          if (sj_strategy == SJ_OPT_MATERIALIZE)
+            join->sjm_lookup_tables |= handled_fanout;
+          else
+            join->sjm_lookup_tables &= ~handled_fanout;
           *current_read_time= read_time;
           *current_record_count= rec_count;
           join->cur_dups_producing_tables &= ~handled_fanout;
@@ -3050,6 +3054,13 @@
                                   const JOIN_TAB *tab, uint idx)
 {
   TABLE_LIST *emb_sj_nest;
+
+  if (tab->emb_sj_nest)
+  {
+    table_map subq_tables= tab->emb_sj_nest->sj_inner_tables;
+    tab->join->sjm_lookup_tables &= ~subq_tables;
+  }
+
   if ((emb_sj_nest= tab->emb_sj_nest))
   {
     /* If we're removing the last SJ-inner table, remove the sj-nest */
@@ -3227,6 +3238,7 @@
   uint tablenr;
   table_map remaining_tables= 0;
   table_map handled_tabs= 0;
+  join->sjm_lookup_tables= 0;
   for (tablenr= table_count - 1 ; tablenr != join->const_tables - 1; tablenr--)
   {
     POSITION *pos= join->best_positions + tablenr;
@@ -3252,6 +3264,7 @@
       first= tablenr - sjm->tables + 1;
       join->best_positions[first].n_sj_tables= sjm->tables;
       join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE;
+      join->sjm_lookup_tables|= s->table->map;
     }
     else if (pos->sj_strategy == SJ_OPT_MATERIALIZE_SCAN)
     {

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-01-26 21:33:18 +0000
+++ b/sql/sql_select.cc	2013-02-12 19:49:46 +0000
@@ -4924,6 +4924,7 @@
 }
 
 
+
 /**
   Discover the indexes that can be used for GROUP BY or DISTINCT queries.
 
@@ -5166,6 +5167,8 @@
                2. we won't get two ref-or-null's
           */
           if (!(remaining_tables & keyuse->used_tables) &&
+              s->access_from_tables_is_allowed(keyuse->used_tables,
+                                               join->sjm_lookup_tables) &&
               !(ref_or_null_part && (keyuse->optimize &
                                      KEY_OPTIMIZE_REF_OR_NULL)))
           {
@@ -7655,7 +7658,9 @@
     */
     do
     {
-      if (!(~used_tables & keyuse->used_tables))
+      if (!(~used_tables & keyuse->used_tables) &&
+          j->access_from_tables_is_allowed(keyuse->used_tables,
+                                           join->sjm_lookup_tables))
       {
         if  (are_tables_local(j, keyuse->val->used_tables()))
         {
@@ -7723,7 +7728,9 @@
     uint i;
     for (i=0 ; i < keyparts ; keyuse++,i++)
     {
-      while (((~used_tables) & keyuse->used_tables) || 
+      while (((~used_tables) & keyuse->used_tables) ||
+	     !j->access_from_tables_is_allowed(keyuse->used_tables,
+                                               join->sjm_lookup_tables) ||    
              keyuse->keypart == NO_KEYPART ||
 	     (keyuse->keypart != 
               (is_hash_join_key_no(key) ?
@@ -10183,7 +10190,6 @@
 }
 
 
-
 /**
   Build a TABLE_REF structure for index lookup in the temporary table
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-05-08 17:58:41 +0000
+++ b/sql/sql_select.h	2013-02-08 05:46:02 +0000
@@ -515,6 +515,16 @@
   bool preread_init();
 
   bool is_sjm_nest() { return test(bush_children); }
+
+  bool access_from_tables_is_allowed(table_map used_tables,
+                                     table_map sjm_lookup_tables)
+  {
+    table_map used_sjm_lookup_tables= used_tables & sjm_lookup_tables;
+    return !used_sjm_lookup_tables ||
+           (emb_sj_nest && 
+            !(used_sjm_lookup_tables & ~emb_sj_nest->sj_inner_tables));
+  }
+
 } JOIN_TAB;
 
 
@@ -947,6 +957,11 @@
   */
   bool     resume_nested_loop;
   table_map const_table_map;
+  /** 
+    Bitmap of semijoin tables that the current partial plan decided
+    to materialize and access by lookups
+  */
+  table_map sjm_lookup_tables;
   /*
     Constant tables for which we have found a row (as opposed to those for
     which we didn't).
@@ -1268,6 +1283,8 @@
     outer_ref_cond= pseudo_bits_cond= NULL;
     in_to_exists_where= NULL;
     in_to_exists_having= NULL;
+    emb_sjm_nest= NULL;
+    sjm_lookup_tables= 0;
   }
 
   int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num,



More information about the commits mailing list