[Maria-developers] Rev 2810: Fix for LP bug#611625: Removing NULL references from subquery parameter list added. in file:///home/bell/maria/bzr/work-maria-5.3-lb611625/

9 Aug 2010

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

------------------------------------------------------------
revno: 2810
revision-id: sanja@askmonty.org-20100809100058-qqmlj17yajig4gwj
parent: sanja@askmonty.org-20100805142348-wdtwgvj5tm2si5gt
committer: sanja@askmonty.org
branch nick: work-maria-5.3-lb611625
timestamp: Mon 2010-08-09 13:00:58 +0300
message:
  Fix for LP bug#611625: Removing NULL references from subquery parameter list added.
  
  Incorrect limitation on number of parameters removed.

=== modified file 'mysql-test/r/subselect_cache.result'

--- a/mysql-test/r/subselect_cache.result	2010-08-05 14:23:48 +0000
+++ b/mysql-test/r/subselect_cache.result	2010-08-09 10:00:58 +0000
@@ -2985,3 +2985,201 @@
 1	NULL	f
 drop table t1,t2,t3,t4;
 set @@optimizer_switch= default;
+#launchpad BUG#611625
+CREATE TABLE `t1` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_int_nokey` int(11) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO `t1` VALUES (1,NULL,'w');
+INSERT INTO `t1` VALUES (2,7,'m');
+INSERT INTO `t1` VALUES (3,9,'m');
+INSERT INTO `t1` VALUES (4,7,'k');
+INSERT INTO `t1` VALUES (5,4,'r');
+INSERT INTO `t1` VALUES (6,2,'t');
+INSERT INTO `t1` VALUES (7,6,'j');
+INSERT INTO `t1` VALUES (8,8,'u');
+INSERT INTO `t1` VALUES (9,NULL,'h');
+INSERT INTO `t1` VALUES (10,5,'o');
+INSERT INTO `t1` VALUES (11,NULL,NULL);
+INSERT INTO `t1` VALUES (12,6,'k');
+INSERT INTO `t1` VALUES (13,188,'e');
+INSERT INTO `t1` VALUES (14,2,'n');
+INSERT INTO `t1` VALUES (15,1,'t');
+INSERT INTO `t1` VALUES (16,1,'c');
+INSERT INTO `t1` VALUES (17,0,'m');
+INSERT INTO `t1` VALUES (18,9,'y');
+INSERT INTO `t1` VALUES (19,NULL,'f');
+INSERT INTO `t1` VALUES (20,4,'d');
+CREATE TABLE `t3` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_int_nokey` int(11) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+INSERT INTO `t3` VALUES (1,6,'r');
+INSERT INTO `t3` VALUES (2,8,'c');
+INSERT INTO `t3` VALUES (3,6,'o');
+INSERT INTO `t3` VALUES (4,6,'c');
+INSERT INTO `t3` VALUES (5,3,'d');
+INSERT INTO `t3` VALUES (6,9,'v');
+INSERT INTO `t3` VALUES (7,2,'m');
+INSERT INTO `t3` VALUES (8,1,'j');
+INSERT INTO `t3` VALUES (9,8,'f');
+INSERT INTO `t3` VALUES (10,0,'n');
+INSERT INTO `t3` VALUES (11,9,'z');
+INSERT INTO `t3` VALUES (12,8,'h');
+INSERT INTO `t3` VALUES (13,NULL,'q');
+INSERT INTO `t3` VALUES (14,0,'w');
+INSERT INTO `t3` VALUES (15,5,'z');
+INSERT INTO `t3` VALUES (16,1,'j');
+INSERT INTO `t3` VALUES (17,1,'a');
+INSERT INTO `t3` VALUES (18,6,'m');
+INSERT INTO `t3` VALUES (19,6,'n');
+INSERT INTO `t3` VALUES (20,1,'e');
+INSERT INTO `t3` VALUES (21,8,'u');
+INSERT INTO `t3` VALUES (22,1,'s');
+INSERT INTO `t3` VALUES (23,0,'u');
+INSERT INTO `t3` VALUES (24,4,'r');
+INSERT INTO `t3` VALUES (25,9,'g');
+INSERT INTO `t3` VALUES (26,8,'o');
+INSERT INTO `t3` VALUES (27,5,'w');
+INSERT INTO `t3` VALUES (28,9,'b');
+INSERT INTO `t3` VALUES (29,5,NULL);
+INSERT INTO `t3` VALUES (30,NULL,'y');
+INSERT INTO `t3` VALUES (31,NULL,'y');
+INSERT INTO `t3` VALUES (32,105,'u');
+INSERT INTO `t3` VALUES (33,0,'p');
+INSERT INTO `t3` VALUES (34,3,'s');
+INSERT INTO `t3` VALUES (35,1,'e');
+INSERT INTO `t3` VALUES (36,75,'d');
+INSERT INTO `t3` VALUES (37,9,'d');
+INSERT INTO `t3` VALUES (38,7,'c');
+INSERT INTO `t3` VALUES (39,NULL,'b');
+INSERT INTO `t3` VALUES (40,NULL,'t');
+INSERT INTO `t3` VALUES (41,4,NULL);
+INSERT INTO `t3` VALUES (42,0,'y');
+INSERT INTO `t3` VALUES (43,204,'c');
+INSERT INTO `t3` VALUES (44,0,'d');
+INSERT INTO `t3` VALUES (45,9,'x');
+INSERT INTO `t3` VALUES (46,8,'p');
+INSERT INTO `t3` VALUES (47,7,'e');
+INSERT INTO `t3` VALUES (48,8,'g');
+INSERT INTO `t3` VALUES (49,NULL,'x');
+INSERT INTO `t3` VALUES (50,6,'s');
+INSERT INTO `t3` VALUES (51,5,'e');
+INSERT INTO `t3` VALUES (52,2,'l');
+INSERT INTO `t3` VALUES (53,3,'p');
+INSERT INTO `t3` VALUES (54,7,'h');
+INSERT INTO `t3` VALUES (55,NULL,'m');
+INSERT INTO `t3` VALUES (56,145,'n');
+INSERT INTO `t3` VALUES (57,0,'v');
+INSERT INTO `t3` VALUES (58,1,'b');
+INSERT INTO `t3` VALUES (59,7,'x');
+INSERT INTO `t3` VALUES (60,3,'r');
+INSERT INTO `t3` VALUES (61,NULL,'t');
+INSERT INTO `t3` VALUES (62,2,'w');
+INSERT INTO `t3` VALUES (63,2,'w');
+INSERT INTO `t3` VALUES (64,2,'k');
+INSERT INTO `t3` VALUES (65,8,'a');
+INSERT INTO `t3` VALUES (66,6,'t');
+INSERT INTO `t3` VALUES (67,1,'z');
+INSERT INTO `t3` VALUES (68,NULL,'e');
+INSERT INTO `t3` VALUES (69,1,'q');
+INSERT INTO `t3` VALUES (70,0,'e');
+INSERT INTO `t3` VALUES (71,4,'v');
+INSERT INTO `t3` VALUES (72,1,'d');
+INSERT INTO `t3` VALUES (73,1,'u');
+INSERT INTO `t3` VALUES (74,27,'o');
+INSERT INTO `t3` VALUES (75,4,'b');
+INSERT INTO `t3` VALUES (76,6,'c');
+INSERT INTO `t3` VALUES (77,2,'q');
+INSERT INTO `t3` VALUES (78,248,NULL);
+INSERT INTO `t3` VALUES (79,NULL,'h');
+INSERT INTO `t3` VALUES (80,9,'d');
+INSERT INTO `t3` VALUES (81,75,'w');
+INSERT INTO `t3` VALUES (82,2,'m');
+INSERT INTO `t3` VALUES (83,9,'i');
+INSERT INTO `t3` VALUES (84,4,'w');
+INSERT INTO `t3` VALUES (85,0,'f');
+INSERT INTO `t3` VALUES (86,0,'k');
+INSERT INTO `t3` VALUES (87,1,'v');
+INSERT INTO `t3` VALUES (88,119,'c');
+INSERT INTO `t3` VALUES (89,1,'y');
+INSERT INTO `t3` VALUES (90,7,'h');
+INSERT INTO `t3` VALUES (91,2,NULL);
+INSERT INTO `t3` VALUES (92,7,'t');
+INSERT INTO `t3` VALUES (93,2,'l');
+INSERT INTO `t3` VALUES (94,6,'a');
+INSERT INTO `t3` VALUES (95,4,'r');
+INSERT INTO `t3` VALUES (96,5,'s');
+INSERT INTO `t3` VALUES (97,7,'z');
+INSERT INTO `t3` VALUES (98,1,'j');
+INSERT INTO `t3` VALUES (99,7,'c');
+INSERT INTO `t3` VALUES (100,2,'f');
+CREATE TABLE `t2` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_int_nokey` int(11) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`)
+) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+INSERT INTO `t2` VALUES (10,8,NULL);
+set optimizer_switch='subquery_cache=off';
+SELECT (
+SELECT `col_int_nokey`
+FROM t3
+WHERE table1 .`col_varchar_nokey` ) field13
+FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
+ORDER BY field13;
+field13
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+set optimizer_switch='subquery_cache=on';
+SELECT 
+(SELECT `col_int_nokey`
+  FROM t3
+WHERE table1 .`col_varchar_nokey` ) field13
+FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
+ORDER BY field13;
+field13
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+drop table t1,t2,t3;
+set @@optimizer_switch= default;

=== modified file 'mysql-test/t/subselect_cache.test'
--- a/mysql-test/t/subselect_cache.test	2010-08-05 14:23:48 +0000
+++ b/mysql-test/t/subselect_cache.test	2010-08-09 10:00:58 +0000
@@ -1306,3 +1306,167 @@
 
 drop table t1,t2,t3,t4;
 set @@optimizer_switch= default;
+
+#
+--echo #launchpad BUG#611625
+#
+CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_int_nokey` int(11) DEFAULT NULL,
+  `col_varchar_nokey` varchar(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO `t1` VALUES (1,NULL,'w');
+INSERT INTO `t1` VALUES (2,7,'m');
+INSERT INTO `t1` VALUES (3,9,'m');
+INSERT INTO `t1` VALUES (4,7,'k');
+INSERT INTO `t1` VALUES (5,4,'r');
+INSERT INTO `t1` VALUES (6,2,'t');
+INSERT INTO `t1` VALUES (7,6,'j');
+INSERT INTO `t1` VALUES (8,8,'u');
+INSERT INTO `t1` VALUES (9,NULL,'h');
+INSERT INTO `t1` VALUES (10,5,'o');
+INSERT INTO `t1` VALUES (11,NULL,NULL);
+INSERT INTO `t1` VALUES (12,6,'k');
+INSERT INTO `t1` VALUES (13,188,'e');
+INSERT INTO `t1` VALUES (14,2,'n');
+INSERT INTO `t1` VALUES (15,1,'t');
+INSERT INTO `t1` VALUES (16,1,'c');
+INSERT INTO `t1` VALUES (17,0,'m');
+INSERT INTO `t1` VALUES (18,9,'y');
+INSERT INTO `t1` VALUES (19,NULL,'f');
+INSERT INTO `t1` VALUES (20,4,'d');
+CREATE TABLE `t3` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_int_nokey` int(11) DEFAULT NULL,
+  `col_varchar_nokey` varchar(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+INSERT INTO `t3` VALUES (1,6,'r');
+INSERT INTO `t3` VALUES (2,8,'c');
+INSERT INTO `t3` VALUES (3,6,'o');
+INSERT INTO `t3` VALUES (4,6,'c');
+INSERT INTO `t3` VALUES (5,3,'d');
+INSERT INTO `t3` VALUES (6,9,'v');
+INSERT INTO `t3` VALUES (7,2,'m');
+INSERT INTO `t3` VALUES (8,1,'j');
+INSERT INTO `t3` VALUES (9,8,'f');
+INSERT INTO `t3` VALUES (10,0,'n');
+INSERT INTO `t3` VALUES (11,9,'z');
+INSERT INTO `t3` VALUES (12,8,'h');
+INSERT INTO `t3` VALUES (13,NULL,'q');
+INSERT INTO `t3` VALUES (14,0,'w');
+INSERT INTO `t3` VALUES (15,5,'z');
+INSERT INTO `t3` VALUES (16,1,'j');
+INSERT INTO `t3` VALUES (17,1,'a');
+INSERT INTO `t3` VALUES (18,6,'m');
+INSERT INTO `t3` VALUES (19,6,'n');
+INSERT INTO `t3` VALUES (20,1,'e');
+INSERT INTO `t3` VALUES (21,8,'u');
+INSERT INTO `t3` VALUES (22,1,'s');
+INSERT INTO `t3` VALUES (23,0,'u');
+INSERT INTO `t3` VALUES (24,4,'r');
+INSERT INTO `t3` VALUES (25,9,'g');
+INSERT INTO `t3` VALUES (26,8,'o');
+INSERT INTO `t3` VALUES (27,5,'w');
+INSERT INTO `t3` VALUES (28,9,'b');
+INSERT INTO `t3` VALUES (29,5,NULL);
+INSERT INTO `t3` VALUES (30,NULL,'y');
+INSERT INTO `t3` VALUES (31,NULL,'y');
+INSERT INTO `t3` VALUES (32,105,'u');
+INSERT INTO `t3` VALUES (33,0,'p');
+INSERT INTO `t3` VALUES (34,3,'s');
+INSERT INTO `t3` VALUES (35,1,'e');
+INSERT INTO `t3` VALUES (36,75,'d');
+INSERT INTO `t3` VALUES (37,9,'d');
+INSERT INTO `t3` VALUES (38,7,'c');
+INSERT INTO `t3` VALUES (39,NULL,'b');
+INSERT INTO `t3` VALUES (40,NULL,'t');
+INSERT INTO `t3` VALUES (41,4,NULL);
+INSERT INTO `t3` VALUES (42,0,'y');
+INSERT INTO `t3` VALUES (43,204,'c');
+INSERT INTO `t3` VALUES (44,0,'d');
+INSERT INTO `t3` VALUES (45,9,'x');
+INSERT INTO `t3` VALUES (46,8,'p');
+INSERT INTO `t3` VALUES (47,7,'e');
+INSERT INTO `t3` VALUES (48,8,'g');
+INSERT INTO `t3` VALUES (49,NULL,'x');
+INSERT INTO `t3` VALUES (50,6,'s');
+INSERT INTO `t3` VALUES (51,5,'e');
+INSERT INTO `t3` VALUES (52,2,'l');
+INSERT INTO `t3` VALUES (53,3,'p');
+INSERT INTO `t3` VALUES (54,7,'h');
+INSERT INTO `t3` VALUES (55,NULL,'m');
+INSERT INTO `t3` VALUES (56,145,'n');
+INSERT INTO `t3` VALUES (57,0,'v');
+INSERT INTO `t3` VALUES (58,1,'b');
+INSERT INTO `t3` VALUES (59,7,'x');
+INSERT INTO `t3` VALUES (60,3,'r');
+INSERT INTO `t3` VALUES (61,NULL,'t');
+INSERT INTO `t3` VALUES (62,2,'w');
+INSERT INTO `t3` VALUES (63,2,'w');
+INSERT INTO `t3` VALUES (64,2,'k');
+INSERT INTO `t3` VALUES (65,8,'a');
+INSERT INTO `t3` VALUES (66,6,'t');
+INSERT INTO `t3` VALUES (67,1,'z');
+INSERT INTO `t3` VALUES (68,NULL,'e');
+INSERT INTO `t3` VALUES (69,1,'q');
+INSERT INTO `t3` VALUES (70,0,'e');
+INSERT INTO `t3` VALUES (71,4,'v');
+INSERT INTO `t3` VALUES (72,1,'d');
+INSERT INTO `t3` VALUES (73,1,'u');
+INSERT INTO `t3` VALUES (74,27,'o');
+INSERT INTO `t3` VALUES (75,4,'b');
+INSERT INTO `t3` VALUES (76,6,'c');
+INSERT INTO `t3` VALUES (77,2,'q');
+INSERT INTO `t3` VALUES (78,248,NULL);
+INSERT INTO `t3` VALUES (79,NULL,'h');
+INSERT INTO `t3` VALUES (80,9,'d');
+INSERT INTO `t3` VALUES (81,75,'w');
+INSERT INTO `t3` VALUES (82,2,'m');
+INSERT INTO `t3` VALUES (83,9,'i');
+INSERT INTO `t3` VALUES (84,4,'w');
+INSERT INTO `t3` VALUES (85,0,'f');
+INSERT INTO `t3` VALUES (86,0,'k');
+INSERT INTO `t3` VALUES (87,1,'v');
+INSERT INTO `t3` VALUES (88,119,'c');
+INSERT INTO `t3` VALUES (89,1,'y');
+INSERT INTO `t3` VALUES (90,7,'h');
+INSERT INTO `t3` VALUES (91,2,NULL);
+INSERT INTO `t3` VALUES (92,7,'t');
+INSERT INTO `t3` VALUES (93,2,'l');
+INSERT INTO `t3` VALUES (94,6,'a');
+INSERT INTO `t3` VALUES (95,4,'r');
+INSERT INTO `t3` VALUES (96,5,'s');
+INSERT INTO `t3` VALUES (97,7,'z');
+INSERT INTO `t3` VALUES (98,1,'j');
+INSERT INTO `t3` VALUES (99,7,'c');
+INSERT INTO `t3` VALUES (100,2,'f');
+CREATE TABLE `t2` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_int_nokey` int(11) DEFAULT NULL,
+  `col_varchar_nokey` varchar(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+INSERT INTO `t2` VALUES (10,8,NULL);
+
+set optimizer_switch='subquery_cache=off';
+
+SELECT (
+SELECT `col_int_nokey`
+FROM t3
+WHERE table1 .`col_varchar_nokey` ) field13
+FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
+ORDER BY field13;
+
+set optimizer_switch='subquery_cache=on';
+
+SELECT 
+ (SELECT `col_int_nokey`
+  FROM t3
+  WHERE table1 .`col_varchar_nokey` ) field13
+FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
+ORDER BY field13;
+
+drop table t1,t2,t3;
+set @@optimizer_switch= default;

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2010-07-16 11:02:15 +0000
+++ b/sql/sql_class.h	2010-08-09 10:00:58 +0000
@@ -62,9 +62,9 @@
 
 class Item_iterator_ref_list: public Item_iterator
 {
-  List_iterator_fast list;
+  List_iterator list;
 public:
-  Item_iterator_ref_list(List_iterator_fast &arg_list):
+  Item_iterator_ref_list(List_iterator &arg_list):
     list(arg_list) {}
   void open() { list.rewind(); }
   Item *next() { return *(list++); }

=== modified file 'sql/sql_expression_cache.cc'
--- a/sql/sql_expression_cache.cc	2010-07-30 04:16:58 +0000
+++ b/sql/sql_expression_cache.cc	2010-08-09 10:00:58 +0000
@@ -96,22 +96,39 @@
 
 void Expression_cache_tmptable::init()
 {
-  List_iterator_fast li(*list);
+  List_iterator li(*list);
   Item_iterator_ref_list it(li);
   Item **item;
   uint field_counter;
   DBUG_ENTER("Expression_cache_tmptable::init");
   DBUG_ASSERT(!inited);
   inited= TRUE;
-
-  if (!(ULONGLONG_MAX >> (list->elements + 1)))
-  {
-    DBUG_PRINT("info", ("Too many dependencies"));
+  cache_table= NULL;
+
+  while ((item= li++))
+  {
+    DBUG_ASSERT(item);
+    if (*item)
+    {
+      DBUG_ASSERT((*item)->fixed);
+      items.push_back((*item));
+    }
+    else
+    {
+      /*
+        This is possible when optimizer already executed this subquery and
+        optimized out the condition predicate.
+      */
+      li.remove();
+    }
+  }
+
+  if (list->elements == 0)
+  {
+    DBUG_PRINT("info", ("All parameters were removed by optimizer."));
     DBUG_VOID_RETURN;
   }
 
-  cache_table= NULL;
-
   cache_table_param.init();
   /* dependent items and result */
   cache_table_param.field_count= list->elements + 1;
@@ -119,13 +136,6 @@
   cache_table_param.skip_create_table= 1;
   cache_table= NULL;
 
-  while ((item= li++))
-  {
-    DBUG_ASSERT(item);
-    DBUG_ASSERT(*item);
-    DBUG_ASSERT((*item)->fixed);
-    items.push_back((*item));
-  }
   items.push_front(val);
 
   if (!(cache_table= create_tmp_table(table_thd, &cache_table_param,

    

[Maria-developers] Rev 2810: Fix for LP bug#611625: Removing NULL references from subquery parameter list added. in file:///home/bell/maria/bzr/work-maria-5.3-lb611625/

sanja＠askmonty.org