At file:///home/bell/maria/bzr/work-maria-5.3-lb609043/
------------------------------------------------------------
revno: 2807
revision-id: [email protected]
parent: [email protected]
committer: [email protected]
branch nick: work-maria-5.3-lb609043
timestamp: Thu 2010-07-29 19:44:49 +0300
message:
Fix for luanchpad bug#609043
Removed indirect reference in equalities for cache index lookup.
We should use direct reference because optiomization of the query can
optimize out condition and if the outer reference is the only element of
condition the indirect reference become NULL.
We can resolve correctly indirect reference in
Expression_cache_tmptable::make_equalities because it called before
optimisation of the cached subquery.
=== modified file 'mysql-test/r/subquery_cache.result'
--- a/mysql-test/r/subquery_cache.result 2010-07-10 10:37:30 +0000
+++ b/mysql-test/r/subquery_cache.result 2010-07-29 16:44:49 +0000
@@ -1838,3 +1838,107 @@
Handler_read_rnd_next 27
drop table t0,t1,t2;
set optimizer_switch='default';
+# launchpad BUG#609043
+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_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO `t1` VALUES
(19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19
00:19:53','2007-04-19 00:19:53','f','f');
+INSERT INTO `t1` VALUES
(20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01
00:00:00','1900-01-01 00:00:00','d','d');
+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_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`)
+) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
+CREATE TABLE `t3` (
+`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`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
+CREATE TABLE `t4` (
+`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`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+INSERT INTO `t4` VALUES
(100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25
05:08:49','2007-04-25 05:08:49','f','f');
+SET @@optimizer_switch = 'subquery_cache=off';
+/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
+SELECT SUBQUERY2_t1 .`col_int_key`
+FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
+WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
+FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON
table3 .`col_varchar_key` = table2 .`col_varchar_key`
+GROUP BY field10 ;
+COUNT( DISTINCT table2 .`col_int_key` ) (
+SELECT SUBQUERY2_t1 .`col_int_key`
+FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
+WHERE table1 .`col_varchar_key` ) field10
+1 NULL d
+1 NULL f
+SET @@optimizer_switch = 'subquery_cache=on';
+/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
+SELECT SUBQUERY2_t1 .`col_int_key`
+FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
+WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
+FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON
table3 .`col_varchar_key` = table2 .`col_varchar_key`
+GROUP BY field10 ;
+COUNT( DISTINCT table2 .`col_int_key` ) (
+SELECT SUBQUERY2_t1 .`col_int_key`
+FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
+WHERE table1 .`col_varchar_key` ) field10
+1 NULL d
+1 NULL f
+drop table t1,t2,t3,t4;
+set @@optimizer_switch= default;
=== modified file 'mysql-test/t/subquery_cache.test'
--- a/mysql-test/t/subquery_cache.test 2010-07-10 10:37:30 +0000
+++ b/mysql-test/t/subquery_cache.test 2010-07-29 16:44:49 +0000
@@ -507,3 +507,107 @@
drop table t0,t1,t2;
set optimizer_switch='default';
+
+#
+--echo # launchpad BUG#609043
+#
+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_varchar_key` (`col_varchar_key`,`col_int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO `t1` VALUES
(19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19
00:19:53','2007-04-19 00:19:53','f','f');
+INSERT INTO `t1` VALUES
(20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01
00:00:00','1900-01-01 00:00:00','d','d');
+
+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_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`)
+) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
+
+CREATE TABLE `t3` (
+ `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`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
+
+CREATE TABLE `t4` (
+ `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`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+INSERT INTO `t4` VALUES
(100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25
05:08:49','2007-04-25 05:08:49','f','f');
+
+SET @@optimizer_switch = 'subquery_cache=off';
+
+/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
+SELECT SUBQUERY2_t1 .`col_int_key`
+FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
+WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
+FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON
table3 .`col_varchar_key` = table2 .`col_varchar_key`
+GROUP BY field10 ;
+
+SET @@optimizer_switch = 'subquery_cache=on';
+
+/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
+SELECT SUBQUERY2_t1 .`col_int_key`
+FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
+WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
+FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON
table3 .`col_varchar_key` = table2 .`col_varchar_key`
+GROUP BY field10 ;
+
+drop table t1,t2,t3,t4;
+set @@optimizer_switch= default;
+
=== modified file 'sql/sql_expression_cache.cc'
--- a/sql/sql_expression_cache.cc 2010-07-10 10:37:30 +0000
+++ b/sql/sql_expression_cache.cc 2010-07-29 16:44:49 +0000
@@ -41,7 +41,6 @@
List<Item> args;
List_iterator_fast<Item*> li(*list);
Item **ref;
- Name_resolution_context *cn= NULL;
DBUG_ENTER("Expression_cache_tmptable::make_equalities");
for (uint i= 1 /* skip result filed */; (ref= li++); i++)
@@ -58,13 +57,7 @@
fld->type() == MYSQL_TYPE_NEWDECIMAL ||
fld->type() == MYSQL_TYPE_DECIMAL)
{
- if (!cn)
- {
- // dummy resolution context
- cn= new Name_resolution_context();
- cn->init();
- }
- args.push_front(new Item_func_eq(new Item_ref(cn, ref, "", "", FALSE),
+ args.push_front(new Item_func_eq(*ref,
new Item_field(fld)));
}
}
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
