maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

At file:///home/psergey/dev/maria-5.1/

------------------------------------------------------------
revno: 2818
revision-id: psergey@xxxxxxxxxxxx-20100218120352-fn9i4z9611nrkps5
parent: monty@xxxxxxxxxxxx-20100212142113-wdv50xx19quursaf
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
branch nick: maria-5.1
timestamp: Thu 2010-02-18 15:03:52 +0300
message:
  LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB
  - When analying multiple equalities, take into account that they 
    may not have a single table field that belongs to one of the tables
    that we're trying to eliminate (and they are not useful for table
    elimination in that case)
=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result	2009-09-16 19:05:03 +0000
+++ b/mysql-test/r/table_elim.result	2010-02-18 12:03:52 +0000
@@ -418,3 +418,49 @@
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where
 drop table t1, t2;
+#
+# LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB
+#
+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_date_key` date DEFAULT NULL,
+`col_date_nokey` date DEFAULT NULL,
+`col_time_key` time DEFAULT NULL,
+`col_time_nokey` time DEFAULT NULL,
+`col_datetime_key` datetime DEFAULT NULL,
+`col_datetime_nokey` datetime 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_date_key` (`col_date_key`),
+KEY `col_time_key` (`col_time_key`),
+KEY `col_datetime_key` (`col_datetime_key`),
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+);
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t1 VALUES 
+(10,7,8,NULL,NULL,'01:27:35','01:27:35','2002-02-26 06:14:37','2002-02-26 06:14:37','v','v'),
+(11,1,9,'2006-06-14','2006-06-14','19:48:31','19:48:31','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r');
+INSERT INTO t2 SELECT * FROM t1;
+SELECT table2.col_int_key AS field1 
+FROM ( 
+t2 AS table1 
+RIGHT OUTER JOIN 
+( 
+( t1 AS table2 STRAIGHT_JOIN 
+t1 AS table3 ON (
+(table3.col_varchar_nokey = table2.col_varchar_key ) AND 
+(table3.pk = table2.col_int_key)) 
+)
+) ON 
+(
+(table3.col_varchar_key = table2.col_varchar_key) OR 
+(table3.col_int_key = table2.pk)
+)
+)
+HAVING field1 < 216;
+field1
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/table_elim.test'
--- a/mysql-test/t/table_elim.test	2009-09-16 19:05:03 +0000
+++ b/mysql-test/t/table_elim.test	2010-02-18 12:03:52 +0000
@@ -336,3 +336,54 @@
 from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3);
 
 drop table t1, t2;
+
+--echo #
+--echo # LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB
+--echo #
+
+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_date_key` date DEFAULT NULL,
+  `col_date_nokey` date DEFAULT NULL,
+  `col_time_key` time DEFAULT NULL,
+  `col_time_nokey` time DEFAULT NULL,
+  `col_datetime_key` datetime DEFAULT NULL,
+  `col_datetime_nokey` datetime 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_date_key` (`col_date_key`),
+  KEY `col_time_key` (`col_time_key`),
+  KEY `col_datetime_key` (`col_datetime_key`),
+  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
+);
+CREATE TABLE t2 LIKE t1;
+
+INSERT INTO t1 VALUES 
+ (10,7,8,NULL,NULL,'01:27:35','01:27:35','2002-02-26 06:14:37','2002-02-26 06:14:37','v','v'),
+ (11,1,9,'2006-06-14','2006-06-14','19:48:31','19:48:31','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r');
+INSERT INTO t2 SELECT * FROM t1;
+
+SELECT table2.col_int_key AS field1 
+FROM ( 
+  t2 AS table1 
+  RIGHT OUTER JOIN 
+  ( 
+    ( t1 AS table2 STRAIGHT_JOIN 
+      t1 AS table3 ON (
+               (table3.col_varchar_nokey = table2.col_varchar_key ) AND 
+               (table3.pk = table2.col_int_key)) 
+    )
+  ) ON 
+    (
+      (table3.col_varchar_key = table2.col_varchar_key) OR 
+      (table3.col_int_key = table2.pk)
+    )
+)
+HAVING field1 < 216;
+
+DROP TABLE t1, t2;
+

=== modified file 'sql/opt_table_elimination.cc'
--- a/sql/opt_table_elimination.cc	2009-09-16 19:05:03 +0000
+++ b/sql/opt_table_elimination.cc	2010-02-18 12:03:52 +0000
@@ -1220,8 +1220,16 @@
           bound_item= item;
       }
     }
-    exchange_sort<Dep_value_field>(fvl, compare_field_values, NULL);
-    add_module_expr(ctx, eq_mod, *and_level, NULL, bound_item, fvl);
+    /* 
+      Multiple equality is only useful if it includes at least one field from
+      the table that we could potentially eliminate:
+    */
+    if (fvl->elements)
+    {
+      
+      exchange_sort<Dep_value_field>(fvl, compare_field_values, NULL);
+      add_module_expr(ctx, eq_mod, *and_level, NULL, bound_item, fvl);
+    }
     break;
   }
   default: