At file:///home/psergey/dev/maria-5.1/
------------------------------------------------------------
revno: 2818
revision-id: psergey@askmonty.org-20100218120352-fn9i4z9611nrkps5
parent: monty@askmonty.org-20100212142113-wdv50xx19quursaf
committer: Sergey Petrunya
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(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(fvl, compare_field_values, NULL);
+ add_module_expr(ctx, eq_mod, *and_level, NULL, bound_item, fvl);
+ }
break;
}
default:
Sergey Petrunya