[Commits] Rev 2816: Fixed second part of the LP BUG#615760 - incorrect parameters of the temporary heap table index (unique & nulls are equal). in file:///home/bell/maria/bzr/work-maria-5.3-lb615760/

sanja at askmonty.org sanja at askmonty.org
Tue Sep 7 12:54:22 EEST 2010


At file:///home/bell/maria/bzr/work-maria-5.3-lb615760/

------------------------------------------------------------
revno: 2816
revision-id: sanja at askmonty.org-20100907095420-xw2a3w8864bkm16k
parent: sanja at askmonty.org-20100906123424-2sco3ghittvidm6l
committer: sanja at askmonty.org
branch nick: work-maria-5.3-lb615760
timestamp: Tue 2010-09-07 12:54:20 +0300
message:
  Fixed second part of the LP BUG#615760 - incorrect parameters of the temporary heap table index (unique & nulls are equal).
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_cache.result'
--- a/mysql-test/r/subselect_cache.result	2010-09-06 12:34:24 +0000
+++ b/mysql-test/r/subselect_cache.result	2010-09-07 09:54:20 +0000
@@ -3183,7 +3183,7 @@ NULL
 NULL
 drop table t1,t2,t3;
 set @@optimizer_switch= default;
-# LP BUG#615760 (double transformation)
+# LP BUG#615760 (part 1: double transformation)
 create table t1 (a int);
 insert into t1 values (1),(2);
 create table t2 (b int);
@@ -3198,3 +3198,77 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`))))
 drop table t1,t2;
 set @@optimizer_switch= default;
+# LP BUG#615760 (part 2: incorrect heap table index flags)
+SET SESSION optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_condition_pushdown=off,firstmatch=off,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=on,table_elimination=off';
+CREATE TABLE `t1` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_int_nokey` int(11) DEFAULT NULL,
+`col_int_key` int(11) DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `col_int_key` (`col_int_key`),
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MARIA AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
+INSERT INTO `t1` VALUES (10,7,8,'v','v');
+INSERT INTO `t1` VALUES (11,1,9,'r','r');
+INSERT INTO `t1` VALUES (12,5,9,'a','a');
+INSERT INTO `t1` VALUES (13,3,186,'m','m');
+INSERT INTO `t1` VALUES (14,6,NULL,'y','y');
+INSERT INTO `t1` VALUES (15,92,2,'j','j');
+INSERT INTO `t1` VALUES (16,7,3,'d','d');
+INSERT INTO `t1` VALUES (17,NULL,0,'z','z');
+INSERT INTO `t1` VALUES (18,3,133,'e','e');
+INSERT INTO `t1` VALUES (19,5,1,'h','h');
+INSERT INTO `t1` VALUES (20,1,8,'b','b');
+INSERT INTO `t1` VALUES (21,2,5,'s','s');
+INSERT INTO `t1` VALUES (22,NULL,5,'e','e');
+INSERT INTO `t1` VALUES (23,1,8,'j','j');
+INSERT INTO `t1` VALUES (24,0,6,'e','e');
+INSERT INTO `t1` VALUES (25,210,51,'f','f');
+INSERT INTO `t1` VALUES (26,8,4,'v','v');
+INSERT INTO `t1` VALUES (27,7,7,'x','x');
+INSERT INTO `t1` VALUES (28,5,6,'m','m');
+INSERT INTO `t1` VALUES (29,NULL,4,'c','c');
+CREATE TABLE `t2` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_int_nokey` int(11) DEFAULT NULL,
+`col_int_key` int(11) DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `col_int_key` (`col_int_key`),
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MARIA AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
+INSERT INTO `t2` VALUES (1,NULL,2,'w','w');
+INSERT INTO `t2` VALUES (2,7,9,'m','m');
+INSERT INTO `t2` VALUES (3,9,3,'m','m');
+INSERT INTO `t2` VALUES (4,7,9,'k','k');
+INSERT INTO `t2` VALUES (5,4,NULL,'r','r');
+INSERT INTO `t2` VALUES (6,2,9,'t','t');
+INSERT INTO `t2` VALUES (7,6,3,'j','j');
+INSERT INTO `t2` VALUES (8,8,8,'u','u');
+INSERT INTO `t2` VALUES (9,NULL,8,'h','h');
+INSERT INTO `t2` VALUES (10,5,53,'o','o');
+INSERT INTO `t2` VALUES (11,NULL,0,NULL,NULL);
+INSERT INTO `t2` VALUES (12,6,5,'k','k');
+INSERT INTO `t2` VALUES (13,188,166,'e','e');
+INSERT INTO `t2` VALUES (14,2,3,'n','n');
+INSERT INTO `t2` VALUES (15,1,0,'t','t');
+INSERT INTO `t2` VALUES (16,1,1,'c','c');
+INSERT INTO `t2` VALUES (17,0,9,'m','m');
+INSERT INTO `t2` VALUES (18,9,5,'y','y');
+INSERT INTO `t2` VALUES (19,NULL,6,'f','f');
+INSERT INTO `t2` VALUES (20,4,2,'d','d');
+SELECT table1 .`col_varchar_nokey`
+FROM t2 table1 RIGHT JOIN t1 LEFT JOIN (
+SELECT SUBQUERY1_t2 .*
+FROM t1 SUBQUERY1_t1 LEFT JOIN t2 SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( (
+SELECT *
+FROM t1 ) table4 JOIN ( t1 table5 JOIN t2 table6 ON table5 .`pk` ) ON table5 .`col_varchar_nokey` ) ON table6 .`pk` = table5 .`col_int_key` ON table5 .`col_varchar_nokey` ON table5 .`col_varchar_key`
+WHERE table3 .`col_varchar_key` IN (
+SELECT `col_varchar_key`
+FROM t2 ) AND table1 .`col_varchar_key` OR table1 .`pk` ;
+col_varchar_nokey
+drop table t1,t2;
+set @@optimizer_switch= default;

=== modified file 'mysql-test/t/subselect_cache.test'
--- a/mysql-test/t/subselect_cache.test	2010-09-06 12:34:24 +0000
+++ b/mysql-test/t/subselect_cache.test	2010-09-07 09:54:20 +0000
@@ -1472,7 +1472,7 @@ drop table t1,t2,t3;
 set @@optimizer_switch= default;
 
 #
---echo # LP BUG#615760 (double transformation)
+--echo # LP BUG#615760 (part 1: double transformation)
 #
 create table t1 (a int);
 insert into t1 values (1),(2);
@@ -1485,3 +1485,84 @@ select * from t1 where a in (select b fr
 
 drop table t1,t2;
 set @@optimizer_switch= default;
+
+#
+--echo # LP BUG#615760 (part 2: incorrect heap table index flags)
+#
+SET SESSION optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_condition_pushdown=off,firstmatch=off,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=on,table_elimination=off';
+
+CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_int_nokey` int(11) DEFAULT NULL,
+  `col_int_key` int(11) DEFAULT NULL,
+  `col_varchar_key` varchar(1) DEFAULT NULL,
+  `col_varchar_nokey` varchar(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `col_int_key` (`col_int_key`),
+  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MARIA AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
+INSERT INTO `t1` VALUES (10,7,8,'v','v');
+INSERT INTO `t1` VALUES (11,1,9,'r','r');
+INSERT INTO `t1` VALUES (12,5,9,'a','a');
+INSERT INTO `t1` VALUES (13,3,186,'m','m');
+INSERT INTO `t1` VALUES (14,6,NULL,'y','y');
+INSERT INTO `t1` VALUES (15,92,2,'j','j');
+INSERT INTO `t1` VALUES (16,7,3,'d','d');
+INSERT INTO `t1` VALUES (17,NULL,0,'z','z');
+INSERT INTO `t1` VALUES (18,3,133,'e','e');
+INSERT INTO `t1` VALUES (19,5,1,'h','h');
+INSERT INTO `t1` VALUES (20,1,8,'b','b');
+INSERT INTO `t1` VALUES (21,2,5,'s','s');
+INSERT INTO `t1` VALUES (22,NULL,5,'e','e');
+INSERT INTO `t1` VALUES (23,1,8,'j','j');
+INSERT INTO `t1` VALUES (24,0,6,'e','e');
+INSERT INTO `t1` VALUES (25,210,51,'f','f');
+INSERT INTO `t1` VALUES (26,8,4,'v','v');
+INSERT INTO `t1` VALUES (27,7,7,'x','x');
+INSERT INTO `t1` VALUES (28,5,6,'m','m');
+INSERT INTO `t1` VALUES (29,NULL,4,'c','c');
+CREATE TABLE `t2` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_int_nokey` int(11) DEFAULT NULL,
+  `col_int_key` int(11) DEFAULT NULL,
+  `col_varchar_key` varchar(1) DEFAULT NULL,
+  `col_varchar_nokey` varchar(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `col_int_key` (`col_int_key`),
+  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MARIA AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
+INSERT INTO `t2` VALUES (1,NULL,2,'w','w');
+INSERT INTO `t2` VALUES (2,7,9,'m','m');
+INSERT INTO `t2` VALUES (3,9,3,'m','m');
+INSERT INTO `t2` VALUES (4,7,9,'k','k');
+INSERT INTO `t2` VALUES (5,4,NULL,'r','r');
+INSERT INTO `t2` VALUES (6,2,9,'t','t');
+INSERT INTO `t2` VALUES (7,6,3,'j','j');
+INSERT INTO `t2` VALUES (8,8,8,'u','u');
+INSERT INTO `t2` VALUES (9,NULL,8,'h','h');
+INSERT INTO `t2` VALUES (10,5,53,'o','o');
+INSERT INTO `t2` VALUES (11,NULL,0,NULL,NULL);
+INSERT INTO `t2` VALUES (12,6,5,'k','k');
+INSERT INTO `t2` VALUES (13,188,166,'e','e');
+INSERT INTO `t2` VALUES (14,2,3,'n','n');
+INSERT INTO `t2` VALUES (15,1,0,'t','t');
+INSERT INTO `t2` VALUES (16,1,1,'c','c');
+INSERT INTO `t2` VALUES (17,0,9,'m','m');
+INSERT INTO `t2` VALUES (18,9,5,'y','y');
+INSERT INTO `t2` VALUES (19,NULL,6,'f','f');
+INSERT INTO `t2` VALUES (20,4,2,'d','d');
+
+# Here we just need plenty of different parameters to overflow
+# temporary heap table of expression cache
+SELECT table1 .`col_varchar_nokey`
+FROM t2 table1 RIGHT JOIN t1 LEFT JOIN (
+SELECT SUBQUERY1_t2 .*
+FROM t1 SUBQUERY1_t1 LEFT JOIN t2 SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( (
+SELECT *
+FROM t1 ) table4 JOIN ( t1 table5 JOIN t2 table6 ON table5 .`pk` ) ON table5 .`col_varchar_nokey` ) ON table6 .`pk` = table5 .`col_int_key` ON table5 .`col_varchar_nokey` ON table5 .`col_varchar_key`
+WHERE table3 .`col_varchar_key` IN (
+SELECT `col_varchar_key`
+FROM t2 ) AND table1 .`col_varchar_key` OR table1 .`pk` ;
+
+drop table t1,t2;
+set @@optimizer_switch= default;

=== modified file 'sql/sql_expression_cache.cc'
--- a/sql/sql_expression_cache.cc	2010-09-06 12:34:24 +0000
+++ b/sql/sql_expression_cache.cc	2010-09-07 09:54:20 +0000
@@ -12,7 +12,7 @@ Expression_cache_tmptable::Expression_ca
                                                  List<Item*> &dependants,
                                                  Item *value)
   :cache_table(NULL), table_thd(thd), list(&dependants), val(value),
-   equalities(NULL), inited (0)
+   inited (0)
 {
   DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable");
   DBUG_VOID_RETURN;
@@ -20,56 +20,6 @@ Expression_cache_tmptable::Expression_ca
 
 
 /**
-  Build and of equalities for the expression's parameters of certain types
-
-  @details
-  If the temporary table used as an expression cache contains fields of
-  certain types then it's not enough to perform a lookup into the table to
-  verify that there is no row in the table for a given set of parameters.
-  Additionally for those fields we have to check equalities of the form
-  fld=val, where val is the value of the parameter stored in the column
-  fld.
-  The function generates a conjunction of all such equality predicates
-  and saves a pointer to it in the field 'equalities'.
-
-  @retval FALSE OK
-  @retval TRUE  Error
-*/
-
-bool Expression_cache_tmptable::make_equalities()
-{
-  List<Item> args;
-  List_iterator_fast<Item*> li(*list);
-  Item **ref;
-  DBUG_ENTER("Expression_cache_tmptable::make_equalities");
-
-  for (uint i= 1 /* skip result filed */; (ref= li++); i++)
-  {
-    Field *fld= cache_table->field[i];
-    /* Only some field types should be checked after lookup */
-    if (fld->type() == MYSQL_TYPE_VARCHAR ||
-        fld->type() == MYSQL_TYPE_TINY_BLOB ||
-        fld->type() == MYSQL_TYPE_MEDIUM_BLOB ||
-        fld->type() == MYSQL_TYPE_LONG_BLOB ||
-        fld->type() == MYSQL_TYPE_BLOB ||
-        fld->type() == MYSQL_TYPE_VAR_STRING ||
-        fld->type() == MYSQL_TYPE_STRING ||
-        fld->type() == MYSQL_TYPE_NEWDECIMAL ||
-        fld->type() == MYSQL_TYPE_DECIMAL)
-    {
-      args.push_front(new Item_func_eq(*ref, new Item_field(fld)));
-    }
-  }
-  if (args.elements == 1)
-    equalities= args.head();
-  else
-    equalities= new Item_cond_and(args);
-
-  DBUG_RETURN(equalities->fix_fields(table_thd, &equalities));
-}
-
-
-/**
   Field enumerator for TABLE::add_tmp_key
 
   @param arg             reference variable with current field number
@@ -193,12 +143,6 @@ void Expression_cache_tmptable::init()
     goto error;
   }
 
-  if (make_equalities())
-  {
-    DBUG_PRINT("error", ("Creating equalities failed"));
-    goto error;
-  }
-
   DBUG_VOID_RETURN;
 
 error:
@@ -249,7 +193,7 @@ Expression_cache::result Expression_cach
                         (uint)cache_table->status, (uint)ref.has_record));
     if ((res= join_read_key2(table_thd, NULL, cache_table, &ref)) == 1)
       DBUG_RETURN(ERROR);
-    if (res || (equalities && !equalities->val_int()))
+    if (res)
     {
       subquery_cache_miss++;
       DBUG_RETURN(MISS);

=== modified file 'sql/sql_expression_cache.h'
--- a/sql/sql_expression_cache.h	2010-09-06 12:34:24 +0000
+++ b/sql/sql_expression_cache.h	2010-09-07 09:54:20 +0000
@@ -60,7 +60,6 @@ public:
 
 private:
   void init();
-  bool make_equalities();
 
   /* tmp table parameters */
   TMP_TABLE_PARAM cache_table_param;
@@ -78,8 +77,6 @@ private:
   List<Item> items;
   /* Value Item example */
   Item *val;
-  /* Expression to check after index lookup */
-  Item *equalities;
   /* Set on if the object has been succesfully initialized with init() */
   bool inited;
 };

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2010-07-10 10:37:30 +0000
+++ b/sql/table.cc	2010-09-07 09:54:20 +0000
@@ -5191,7 +5191,7 @@ bool TABLE::add_tmp_key(uint key, uint k
   keyinfo->usable_key_parts= keyinfo->key_parts = key_parts;
   keyinfo->key_length=0;
   keyinfo->algorithm= HA_KEY_ALG_UNDEF;
-  keyinfo->flags= HA_GENERATED_KEY;
+  keyinfo->flags= HA_GENERATED_KEY | HA_NOSAME;
   sprintf(buf, "key%i", key);
   if (!(keyinfo->name= strdup_root(&mem_root, buf)))
     return TRUE;
@@ -5230,6 +5230,7 @@ bool TABLE::add_tmp_key(uint key, uint k
     {
       key_part_info->store_length+= HA_KEY_NULL_LENGTH;
       keyinfo->key_length+= HA_KEY_NULL_LENGTH;
+      keyinfo->flags|= HA_NULL_ARE_EQUAL;	// def. that NULL == NULL
     }
     if ((*reg_field)->type() == MYSQL_TYPE_BLOB || 
         (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR)



More information about the commits mailing list