#At file:///work/bzr_trees/45266-bug-azalea/ based on revid:alik@stripped
2796 Evgeny Potemkin 2009-06-25
Bug#45266: Uninitialized variable lead to an empty result.
The TABLE::reginfo.impossible_range is used by the optimizer to indicate
that the condition applied to the table is impossible. It wasn't initialized
at table opening and this might lead to an empty result on complex queries:
a query might set the impossible_range flag on a table and when the query finishes,
all tables are returned back to the table cache. The next query that uses the table
with the impossible_range flag set and an index over the table will see the flag
and thus return an empty result.
The open_table function now initializes the TABLE::reginfo.impossible_range
variable.
@ mysql-test/r/select.result
A test case for the bug#45266: Uninitialized variable lead to an empty result.
@ mysql-test/r/select_jcl6.result
A test case result adjusted after fix for the bug#45266.
@ mysql-test/t/select.test
A test case for the bug#45266: Uninitialized variable lead to an empty result.
@ sql/sql_base.cc
Bug#45266: Uninitialized variable lead to an empty result.
The open_table function now initializes the TABLE::reginfo.impossible_range
variable.
@ sql/sql_select.cc
Bug#45266: Uninitialized variable lead to an empty result.
The open_table function now initializes the TABLE::reginfo.impossible_range
variable.
@ sql/structs.h
Bug#45266: Uninitialized variable lead to an empty result.
A comment is added.
modified:
mysql-test/r/select.result
mysql-test/r/select_jcl6.result
mysql-test/t/select.test
sql/sql_base.cc
sql/sql_select.cc
sql/structs.h
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result 2009-04-28 00:30:41 +0000
+++ b/mysql-test/r/select.result 2009-06-25 16:53:51 +0000
@@ -4458,4 +4458,52 @@ id select_type table type possible_keys
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
DROP TABLE t1;
+#
+# Bug#45266: Uninitialized variable lead to an empty result.
+#
+CREATE TABLE `A` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`date_key` date NOT NULL,
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`time_nokey` time NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `date_key` (`date_key`),
+KEY `time_key` (`time_key`)
+);
+CREATE TABLE `AA` (
+`int_nokey` int(11) NOT NULL,
+`time_key` time NOT NULL,
+KEY `time_key` (`time_key`)
+);
+CREATE TABLE `B` (
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`datetime_nokey` datetime NOT NULL,
+KEY `time_key` (`time_key`)
+);
+INSERT INTO `B` VALUES ('2003-07-28','15:13:38','0000-00-00 00:00:00'),('0000-00-00','00:05:48','2004-07-02 14:34:13');
+CREATE TABLE `BB` (
+`int_nokey` int(11) NOT NULL,
+`date_nokey` date NOT NULL
+);
+INSERT INTO `BB` VALUES (8,'0000-00-00'),(0,'2005-08-18');
+SELECT A.time_nokey FROM
+(AA AS table1 CROSS JOIN
+(AA AS table2 STRAIGHT_JOIN
+(B STRAIGHT_JOIN A
+ON ( A.date_key = B.time_key ))
+ON ( A.pk = B.date_nokey ))
+ON ( A.time_key = B.datetime_nokey ))
+WHERE ( A.time_key < table1.time_key AND
+table1.int_nokey != 'f')
+GROUP BY 1;
+time_nokey
+SELECT B.`time_key` FROM B LEFT JOIN BB JOIN A ON A.`date_nokey` ON A.`int_nokey` GROUP BY 1;
+time_key
+00:05:48
+15:13:38
+drop table A,AA,B,BB;
+# end of test#45266
End of 6.0 tests
=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result 2009-04-13 09:51:24 +0000
+++ b/mysql-test/r/select_jcl6.result 2009-06-25 16:53:51 +0000
@@ -4462,6 +4462,54 @@ id select_type table type possible_keys
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
DROP TABLE t1;
+#
+# Bug#45266: Uninitialized variable lead to an empty result.
+#
+CREATE TABLE `A` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`date_key` date NOT NULL,
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`time_nokey` time NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `date_key` (`date_key`),
+KEY `time_key` (`time_key`)
+);
+CREATE TABLE `AA` (
+`int_nokey` int(11) NOT NULL,
+`time_key` time NOT NULL,
+KEY `time_key` (`time_key`)
+);
+CREATE TABLE `B` (
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`datetime_nokey` datetime NOT NULL,
+KEY `time_key` (`time_key`)
+);
+INSERT INTO `B` VALUES ('2003-07-28','15:13:38','0000-00-00 00:00:00'),('0000-00-00','00:05:48','2004-07-02 14:34:13');
+CREATE TABLE `BB` (
+`int_nokey` int(11) NOT NULL,
+`date_nokey` date NOT NULL
+);
+INSERT INTO `BB` VALUES (8,'0000-00-00'),(0,'2005-08-18');
+SELECT A.time_nokey FROM
+(AA AS table1 CROSS JOIN
+(AA AS table2 STRAIGHT_JOIN
+(B STRAIGHT_JOIN A
+ON ( A.date_key = B.time_key ))
+ON ( A.pk = B.date_nokey ))
+ON ( A.time_key = B.datetime_nokey ))
+WHERE ( A.time_key < table1.time_key AND
+table1.int_nokey != 'f')
+GROUP BY 1;
+time_nokey
+SELECT B.`time_key` FROM B LEFT JOIN BB JOIN A ON A.`date_nokey` ON A.`int_nokey` GROUP BY 1;
+time_key
+00:05:48
+15:13:38
+drop table A,AA,B,BB;
+# end of test#45266
End of 6.0 tests
set join_cache_level=default;
show variables like 'join_cache_level';
=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test 2009-04-28 00:30:41 +0000
+++ b/mysql-test/t/select.test 2009-06-25 16:53:51 +0000
@@ -3799,4 +3799,57 @@ EXPLAIN EXTENDED SELECT a, b FROM t1 WHE
DROP TABLE t1;
+--echo #
+--echo # Bug#45266: Uninitialized variable lead to an empty result.
+--echo #
+CREATE TABLE `A` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) NOT NULL,
+ `date_key` date NOT NULL,
+ `date_nokey` date NOT NULL,
+ `time_key` time NOT NULL,
+ `time_nokey` time NOT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `date_key` (`date_key`),
+ KEY `time_key` (`time_key`)
+);
+
+CREATE TABLE `AA` (
+ `int_nokey` int(11) NOT NULL,
+ `time_key` time NOT NULL,
+ KEY `time_key` (`time_key`)
+);
+
+CREATE TABLE `B` (
+ `date_nokey` date NOT NULL,
+ `time_key` time NOT NULL,
+ `datetime_nokey` datetime NOT NULL,
+ KEY `time_key` (`time_key`)
+);
+
+INSERT INTO `B` VALUES ('2003-07-28','15:13:38','0000-00-00 00:00:00'),('0000-00-00','00:05:48','2004-07-02 14:34:13');
+
+CREATE TABLE `BB` (
+ `int_nokey` int(11) NOT NULL,
+ `date_nokey` date NOT NULL
+);
+
+INSERT INTO `BB` VALUES (8,'0000-00-00'),(0,'2005-08-18');
+
+SELECT A.time_nokey FROM
+ (AA AS table1 CROSS JOIN
+ (AA AS table2 STRAIGHT_JOIN
+ (B STRAIGHT_JOIN A
+ ON ( A.date_key = B.time_key ))
+ ON ( A.pk = B.date_nokey ))
+ ON ( A.time_key = B.datetime_nokey ))
+ WHERE ( A.time_key < table1.time_key AND
+ table1.int_nokey != 'f')
+ GROUP BY 1;
+
+SELECT B.`time_key` FROM B LEFT JOIN BB JOIN A ON A.`date_nokey` ON A.`int_nokey` GROUP BY 1;
+
+drop table A,AA,B,BB;
+--echo # end of test#45266
+
--echo End of 6.0 tests
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2009-05-12 16:41:11 +0000
+++ b/sql/sql_base.cc 2009-06-25 16:53:51 +0000
@@ -2831,6 +2831,7 @@ bool open_table(THD *thd, TABLE_LIST *ta
table->insert_values= 0;
table->fulltext_searched= 0;
table->file->ft_handler= 0;
+ table->reginfo.impossible_range= 0;
/* Catch wrong handling of the auto_increment_field_not_null. */
DBUG_ASSERT(!table->auto_increment_field_not_null);
table->auto_increment_field_not_null= FALSE;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-05-14 21:49:53 +0000
+++ b/sql/sql_select.cc 2009-06-25 16:53:51 +0000
@@ -3905,7 +3905,6 @@ static ha_rows get_quick_record_count(TH
if (select)
{
select->head=table;
- table->reginfo.impossible_range=0;
if ((error= select->test_quick_select(thd, *(key_map *)keys,(table_map) 0,
limit, 0, FALSE)) == 1)
DBUG_RETURN(select->quick->records);
=== modified file 'sql/structs.h'
--- a/sql/structs.h 2009-02-02 12:28:30 +0000
+++ b/sql/structs.h 2009-06-25 16:53:51 +0000
@@ -112,6 +112,10 @@ typedef struct st_reginfo { /* Extra in
struct st_join_table *join_tab; /* Used by SELECT() */
enum thr_lock_type lock_type; /* How database is used */
bool not_exists_optimize;
+ /*
+ TRUE <=> range optimizer found that there is no rows satisfying
+ table conditions.
+ */
bool impossible_range;
} REGINFO;
Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20090625165351-vrhd1hmc4cgd3v0g.bundle
| Thread |
|---|
| • bzr commit into mysql-5.4 branch (epotemkin:2796) Bug#45266 | Evgeny Potemkin | 25 Jun |
