#At file:///home/rl136806/mysql/repo/mysql-6.0-linux/ based on revid:holyfoot@stripped
3699 Roy Lyseng 2009-11-05
BUG#45863: Assertion failed: (fixed == 0)...
The problem in JOIN::prepare() is that fix_fields() is called prematurely
from subquery_types_allow_materialization().
The call to fix_fields() observed the unknown column name and set error
status accordingly.
But JOIN::prepare() failed to notice the error and attempted to call
fix_fields() again, now with a half-way resolved expression, and we have
an assert().
The fix is mostly taken from the committed but unpushed work in WL#4389,
which is about applying semijoin transformation to EXISTS queries,
but also consolidates some of the semantic checking on subqueries.
The call to fix_fields() inside JOIN::prepare() is moved so that it is
always executed before subquery_types_allow_materialization().
Hence, the latter no longer needs to call fix_fields() and we can remove
the thd argument (and also let both return values become output arguments).
I also removed one of the "psergey-todo" comments, because the subselect
cardinality check is now always performed in the early stages of
JOIN::prepare(). But eliminating all the comments would be beyond the scope
of this bugfix.
Noticed another problem during development as well: One query was optimized
differently in regular and prepared mode, and this happened because the
is_correlated flag was set differently in the two modes. Had to make sure
that this flag is set consistently, by calling fix_fields() from
JOIN::prepare() also for the first execution of a prepared statement.
Otherwise, is_correlated would be set inconsistently by the fix_fields()
call in convert_subq_to_sj().
mysql-test/r/subselect4.result
Added test results for bug
mysql-test/t/subselect4.test
Added tests for bug
sql/sql_select.cc
Modified JOIN::prepare() and subquery_types_allow_materialization
according to above description.
modified:
mysql-test/r/subselect4.result
mysql-test/t/subselect4.test
sql/sql_select.cc
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result 2009-10-25 13:41:27 +0000
+++ b/mysql-test/r/subselect4.result 2009-11-05 10:13:23 +0000
@@ -131,3 +131,299 @@ set @@session.optimizer_switch
@@session.optimizer_use_mrr = @old_optimizer_use_mrr,
@@session.engine_condition_pushdown = @old_engine_condition_pushdown;
DROP TABLE t1;
+#
+# BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(),
+# file item.cc, line 4448"
+#
+DROP TABLE IF EXISTS C, BB;
+CREATE TABLE `C` (
+`varchar_key` varchar(1) NOT NULL,
+`varchar_nokey` varchar(1) NOT NULL,
+KEY `varchar_key` (`varchar_key`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO `C` VALUES
+('k','k'),('a','a'),('',''),('u','u'),('e','e'),('v','v'),('i','i'),
+('t','t'),('u','u'),('f','f'),('u','u'),('m','m'),('j','j'),('f','f'),
+('v','v'),('j','j'),('g','g'),('e','e'),('h','h'),('z','z');
+CREATE TABLE `BB` (
+`varchar_key` varchar(1) NOT NULL,
+`varchar_nokey` varchar(1) NOT NULL,
+KEY `varchar_key` (`varchar_key`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO `BB` VALUES ('i','i'),('t','t');
+SELECT `varchar_nokey` FROM C
+WHERE (`varchar_nokey`, OUTR) IN (SELECT `varchar_key`
+ FROM BB);
+ERROR 42S22: Unknown column 'OUTR' in 'IN/ALL/ANY subquery'
+DROP TABLE C,BB;
+#
+# During work with BUG#45863 I had problems with a query that was
+# optimized differently in regular and prepared mode.
+# Because there was a bug in one of the selected strategies, I became
+# aware of the problem. Adding an EXPLAIN query to catch this.
+# (we obviously need to run with and without --ps-protocol to catch it)
+DROP TABLE IF EXISTS t1, t2, t3;
+CREATE TABLE t1
+(EMPNUM CHAR(3) NOT NULL,
+EMPNAME CHAR(20),
+GRADE DECIMAL(4),
+CITY CHAR(15));
+CREATE TABLE t2
+(PNUM CHAR(3) NOT NULL,
+PNAME CHAR(20),
+PTYPE CHAR(6),
+BUDGET DECIMAL(9),
+CITY CHAR(15));
+CREATE TABLE t3
+(EMPNUM CHAR(3) NOT NULL,
+PNUM CHAR(3) NOT NULL,
+HOURS DECIMAL(5));
+INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
+INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
+INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
+INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
+INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
+INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
+INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
+INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
+INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
+INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
+INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
+INSERT INTO t3 VALUES ('E1','P1',40);
+INSERT INTO t3 VALUES ('E1','P2',20);
+INSERT INTO t3 VALUES ('E1','P3',80);
+INSERT INTO t3 VALUES ('E1','P4',20);
+INSERT INTO t3 VALUES ('E1','P5',12);
+INSERT INTO t3 VALUES ('E1','P6',12);
+INSERT INTO t3 VALUES ('E2','P1',40);
+INSERT INTO t3 VALUES ('E2','P2',80);
+INSERT INTO t3 VALUES ('E3','P2',20);
+INSERT INTO t3 VALUES ('E4','P2',20);
+INSERT INTO t3 VALUES ('E4','P4',40);
+INSERT INTO t3 VALUES ('E4','P5',80);
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET @old_join_cache_level = @@session.join_cache_level;
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+SET SESSION join_cache_level = 1;
+CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
+EXPLAIN SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+(SELECT EMPNUM
+FROM t3
+WHERE PNUM IN
+(SELECT PNUM
+FROM t2
+WHERE PTYPE = 'Design'));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer
+DROP INDEX t1_IDX ON t1;
+CREATE INDEX t1_IDX ON t1(EMPNUM);
+EXPLAIN SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+(SELECT EMPNUM
+FROM t3
+WHERE PNUM IN
+(SELECT PNUM
+FROM t2
+WHERE PTYPE = 'Design'));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer
+DROP INDEX t1_IDX ON t1;
+EXPLAIN SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+(SELECT EMPNUM
+FROM t3
+WHERE PNUM IN
+(SELECT PNUM
+FROM t2
+WHERE PTYPE = 'Design'));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer
+SET SESSION optimizer_switch = @old_optimizer_switch;
+SET SESSION join_cache_level = @old_join_cache_level;
+DROP TABLE t1, t2, t3;
+#
+# BUG#46631 "Crash with unknown column name in an IN subquery with
+# loosescan=on"
+# (Duplicate of BUG#45863)
+#
+DROP TABLE /*! IF EXISTS */ CC;
+DROP TABLE /*! IF EXISTS */ C;
+DROP TABLE /*! IF EXISTS */ D;
+DROP TABLE /*! IF EXISTS */ BB;
+CREATE TABLE `CC` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
+CREATE TABLE `C` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+CREATE TABLE `D` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+CREATE TABLE `BB` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+SELECT MAX(table1.`varchar_key`) field1
+FROM BB table1
+STRAIGHT_JOIN
+(D table2 JOIN
+CC table3
+ON table2.`varchar_key`)
+ON table2 .`datetime_key`
+WHERE ('q', 'a') IN (SELECT `varchar_key`, `varchar_nokey`
+ FROM C)
+AND ( (SELECT SUBQUERY2_t1.`varchar_nokey`
+ FROM CC SUBQUERY2_t1
+STRAIGHT_JOIN
+(C SUBQUERY2_t2
+JOIN BB SUBQUERY2_t3
+ON SUBQUERY2_t3 .`int_nokey` = SUBQUERY2_t2.`int_key`)
+ON SUBQUERY2_t3.`int_key` = SUBQUERY2_t2.`pk`)
+OR (table3.`pk`, table3) IN (SELECT SUBQUERY3_t1.`int_nokey`,
+SUBQUERY3_t1.`int_key`
+ FROM (D AS SUBQUERY3_t1)
+)
+)
+HAVING field1 != 3
+ORDER BY table1.`varchar_key`,
+CONCAT(table1.`varchar_nokey`, table1.`varchar_nokey`),
+CONCAT(table3.`varchar_nokey`, table3.`varchar_nokey`);
+ERROR 42S22: Unknown column 'table3' in 'IN/ALL/ANY subquery'
+SET SESSION optimizer_switch = @old_optimizer_switch;
+DROP TABLE CC;
+DROP TABLE C;
+DROP TABLE D;
+DROP TABLE BB;
+DROP TABLE /*! IF EXISTS */ CC;
+DROP TABLE /*! IF EXISTS */ C;
+DROP TABLE /*! IF EXISTS */ D;
+DROP TABLE /*! IF EXISTS */ BB;
+CREATE TABLE `CC` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
+CREATE TABLE `C` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+CREATE TABLE `D` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+CREATE TABLE `BB` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) DEFAULT NULL,
+`int_key` int(11) DEFAULT NULL,
+`datetime_key` datetime DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+SELECT MAX(table1 .`varchar_key`) field1
+FROM BB table1
+STRAIGHT_JOIN
+(D table2
+JOIN CC table3
+ON table2.`varchar_key`)
+ON table2.`datetime_key`
+WHERE ('q' , 'a') IN (SELECT `varchar_key`, `varchar_nokey`
+FROM C)
+AND ( (SELECT SUBQUERY2_t1.`varchar_nokey`
+ FROM CC SUBQUERY2_t1
+STRAIGHT_JOIN
+(C SUBQUERY2_t2
+JOIN
+BB SUBQUERY2_t3
+ON SUBQUERY2_t3.`int_nokey` = SUBQUERY2_t2.`int_key`)
+ON SUBQUERY2_t3.`int_key` = SUBQUERY2_t2 .`pk`)
+OR (table3 .`pk`, table3) IN (SELECT SUBQUERY3_t1.`int_nokey`,
+SUBQUERY3_t1 .`int_key`
+FROM (D AS SUBQUERY3_t1)
+)
+)
+HAVING field1 != 3
+ORDER BY table1.`varchar_key` ,
+CONCAT(table1.`varchar_nokey`, table1.`varchar_nokey`),
+CONCAT(table3.`varchar_nokey`, table3.`varchar_nokey`);
+ERROR 42S22: Unknown column 'table3' in 'IN/ALL/ANY subquery'
+SET SESSION optimizer_switch = @old_optimizer_switch;
+DROP TABLE CC;
+DROP TABLE C;
+DROP TABLE D;
+DROP TABLE BB;
=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test 2009-10-25 13:41:27 +0000
+++ b/mysql-test/t/subselect4.test 2009-11-05 10:13:23 +0000
@@ -121,3 +121,325 @@ set @@session.optimizer_switch
@@session.engine_condition_pushdown = @old_engine_condition_pushdown;
DROP TABLE t1;
+
+--echo #
+--echo # BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(),
+--echo # file item.cc, line 4448"
+--echo #
+--disable_warnings
+DROP TABLE IF EXISTS C, BB;
+--enable_warnings
+
+CREATE TABLE `C` (
+ `varchar_key` varchar(1) NOT NULL,
+ `varchar_nokey` varchar(1) NOT NULL,
+ KEY `varchar_key` (`varchar_key`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO `C` VALUES
+ ('k','k'),('a','a'),('',''),('u','u'),('e','e'),('v','v'),('i','i'),
+ ('t','t'),('u','u'),('f','f'),('u','u'),('m','m'),('j','j'),('f','f'),
+ ('v','v'),('j','j'),('g','g'),('e','e'),('h','h'),('z','z');
+CREATE TABLE `BB` (
+ `varchar_key` varchar(1) NOT NULL,
+ `varchar_nokey` varchar(1) NOT NULL,
+ KEY `varchar_key` (`varchar_key`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO `BB` VALUES ('i','i'),('t','t');
+-- error ER_BAD_FIELD_ERROR
+SELECT `varchar_nokey` FROM C
+WHERE (`varchar_nokey`, OUTR) IN (SELECT `varchar_key`
+ FROM BB);
+DROP TABLE C,BB;
+
+--echo #
+--echo # During work with BUG#45863 I had problems with a query that was
+--echo # optimized differently in regular and prepared mode.
+--echo # Because there was a bug in one of the selected strategies, I became
+--echo # aware of the problem. Adding an EXPLAIN query to catch this.
+--echo # (we obviously need to run with and without --ps-protocol to catch it)
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3;
+--enable_warnings
+
+CREATE TABLE t1
+ (EMPNUM CHAR(3) NOT NULL,
+ EMPNAME CHAR(20),
+ GRADE DECIMAL(4),
+ CITY CHAR(15));
+
+CREATE TABLE t2
+ (PNUM CHAR(3) NOT NULL,
+ PNAME CHAR(20),
+ PTYPE CHAR(6),
+ BUDGET DECIMAL(9),
+ CITY CHAR(15));
+
+CREATE TABLE t3
+ (EMPNUM CHAR(3) NOT NULL,
+ PNUM CHAR(3) NOT NULL,
+ HOURS DECIMAL(5));
+
+INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
+INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
+INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
+INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
+INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
+
+INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
+INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
+INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
+INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
+INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
+INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
+
+INSERT INTO t3 VALUES ('E1','P1',40);
+INSERT INTO t3 VALUES ('E1','P2',20);
+INSERT INTO t3 VALUES ('E1','P3',80);
+INSERT INTO t3 VALUES ('E1','P4',20);
+INSERT INTO t3 VALUES ('E1','P5',12);
+INSERT INTO t3 VALUES ('E1','P6',12);
+INSERT INTO t3 VALUES ('E2','P1',40);
+INSERT INTO t3 VALUES ('E2','P2',80);
+INSERT INTO t3 VALUES ('E3','P2',20);
+INSERT INTO t3 VALUES ('E4','P2',20);
+INSERT INTO t3 VALUES ('E4','P4',40);
+INSERT INTO t3 VALUES ('E4','P5',80);
+
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET @old_join_cache_level = @@session.join_cache_level;
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+SET SESSION join_cache_level = 1;
+
+CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
+
+EXPLAIN SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+ (SELECT EMPNUM
+ FROM t3
+ WHERE PNUM IN
+ (SELECT PNUM
+ FROM t2
+ WHERE PTYPE = 'Design'));
+
+DROP INDEX t1_IDX ON t1;
+CREATE INDEX t1_IDX ON t1(EMPNUM);
+
+EXPLAIN SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+ (SELECT EMPNUM
+ FROM t3
+ WHERE PNUM IN
+ (SELECT PNUM
+ FROM t2
+ WHERE PTYPE = 'Design'));
+
+DROP INDEX t1_IDX ON t1;
+
+EXPLAIN SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+ (SELECT EMPNUM
+ FROM t3
+ WHERE PNUM IN
+ (SELECT PNUM
+ FROM t2
+ WHERE PTYPE = 'Design'));
+
+SET SESSION optimizer_switch = @old_optimizer_switch;
+SET SESSION join_cache_level = @old_join_cache_level;
+
+DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # BUG#46631 "Crash with unknown column name in an IN subquery with
+--echo # loosescan=on"
+--echo # (Duplicate of BUG#45863)
+--echo #
+
+--disable_warnings
+DROP TABLE /*! IF EXISTS */ CC;
+DROP TABLE /*! IF EXISTS */ C;
+DROP TABLE /*! IF EXISTS */ D;
+DROP TABLE /*! IF EXISTS */ BB;
+--enable_warnings
+CREATE TABLE `CC` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
+CREATE TABLE `C` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+CREATE TABLE `D` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+CREATE TABLE `BB` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
+
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+
+-- error ER_BAD_FIELD_ERROR
+SELECT MAX(table1.`varchar_key`) field1
+FROM BB table1
+ STRAIGHT_JOIN
+ (D table2 JOIN
+ CC table3
+ ON table2.`varchar_key`)
+ ON table2 .`datetime_key`
+WHERE ('q', 'a') IN (SELECT `varchar_key`, `varchar_nokey`
+ FROM C)
+ AND ( (SELECT SUBQUERY2_t1.`varchar_nokey`
+ FROM CC SUBQUERY2_t1
+ STRAIGHT_JOIN
+ (C SUBQUERY2_t2
+ JOIN BB SUBQUERY2_t3
+ ON SUBQUERY2_t3 .`int_nokey` = SUBQUERY2_t2.`int_key`)
+ ON SUBQUERY2_t3.`int_key` = SUBQUERY2_t2.`pk`)
+ OR (table3.`pk`, table3) IN (SELECT SUBQUERY3_t1.`int_nokey`,
+ SUBQUERY3_t1.`int_key`
+ FROM (D AS SUBQUERY3_t1)
+ )
+ )
+HAVING field1 != 3
+ORDER BY table1.`varchar_key`,
+ CONCAT(table1.`varchar_nokey`, table1.`varchar_nokey`),
+ CONCAT(table3.`varchar_nokey`, table3.`varchar_nokey`);
+
+SET SESSION optimizer_switch = @old_optimizer_switch;
+
+DROP TABLE CC;
+DROP TABLE C;
+DROP TABLE D;
+DROP TABLE BB;
+
+--disable_warnings
+DROP TABLE /*! IF EXISTS */ CC;
+DROP TABLE /*! IF EXISTS */ C;
+DROP TABLE /*! IF EXISTS */ D;
+DROP TABLE /*! IF EXISTS */ BB;
+--enable_warnings
+CREATE TABLE `CC` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
+CREATE TABLE `C` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+CREATE TABLE `D` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
+CREATE TABLE `BB` (
+ `pk` int(11) NOT NULL AUTO_INCREMENT,
+ `int_nokey` int(11) DEFAULT NULL,
+ `int_key` int(11) DEFAULT NULL,
+ `datetime_key` datetime DEFAULT NULL,
+ `varchar_key` varchar(1) DEFAULT NULL,
+ `varchar_nokey` varchar(1) DEFAULT NULL,
+ PRIMARY KEY (`pk`),
+ KEY `int_key` (`int_key`),
+ KEY `datetime_key` (`datetime_key`),
+ KEY `varchar_key` (`varchar_key`,`int_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
+
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+
+-- error ER_BAD_FIELD_ERROR
+SELECT MAX(table1 .`varchar_key`) field1
+FROM BB table1
+ STRAIGHT_JOIN
+ (D table2
+ JOIN CC table3
+ ON table2.`varchar_key`)
+ ON table2.`datetime_key`
+WHERE ('q' , 'a') IN (SELECT `varchar_key`, `varchar_nokey`
+ FROM C)
+ AND ( (SELECT SUBQUERY2_t1.`varchar_nokey`
+ FROM CC SUBQUERY2_t1
+ STRAIGHT_JOIN
+ (C SUBQUERY2_t2
+ JOIN
+ BB SUBQUERY2_t3
+ ON SUBQUERY2_t3.`int_nokey` = SUBQUERY2_t2.`int_key`)
+ ON SUBQUERY2_t3.`int_key` = SUBQUERY2_t2 .`pk`)
+ OR (table3 .`pk`, table3) IN (SELECT SUBQUERY3_t1.`int_nokey`,
+ SUBQUERY3_t1 .`int_key`
+ FROM (D AS SUBQUERY3_t1)
+ )
+ )
+HAVING field1 != 3
+ORDER BY table1.`varchar_key` ,
+ CONCAT(table1.`varchar_nokey`, table1.`varchar_nokey`),
+ CONCAT(table3.`varchar_nokey`, table3.`varchar_nokey`);
+
+SET SESSION optimizer_switch = @old_optimizer_switch;
+
+DROP TABLE CC;
+DROP TABLE C;
+DROP TABLE D;
+DROP TABLE BB;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-11-04 08:53:57 +0000
+++ b/sql/sql_select.cc 2009-11-05 10:13:23 +0000
@@ -257,8 +257,8 @@ static uint make_join_orderinfo(JOIN *jo
static int
join_read_record_no_init(JOIN_TAB *tab);
static
-bool subquery_types_allow_materialization(THD *thd,
- Item_in_subselect *in_subs,
+void subquery_types_allow_materialization(Item_in_subselect *in_subs,
+ bool *mat_allowed,
bool *scan_allowed);
int do_sj_reset(SJ_TMP_TABLE *sj_tbl);
TABLE *create_duplicate_weedout_tmp_table(THD *thd, uint uniq_tuple_length_arg,
@@ -581,6 +581,58 @@ JOIN::prepare(Item ***rref_pointer_array
Item_in_subselect *in_subs= NULL;
if (subselect->substype() == Item_subselect::IN_SUBS)
in_subs= (Item_in_subselect*)subselect;
+
+ /* Resolve expressions and perform semantic analysis for IN query */
+ if (in_subs != NULL)
+ /*
+ TODO: Add the condition below to this if statement when we have proper
+ support for is_correlated handling for materialized semijoins.
+ If we were to add this condition now, the fix_fields() call in
+ convert_subq_to_sj() would force the flag is_correlated to be set
+ erroneously for prepared queries.
+ See decorrelate_subquery_search_cond() in tree mysql-6.0-exists2in
+ for (hopefully) proper correlation handling.
+
+ thd->stmt_arena->state != Query_arena::PREPARED)
+ */
+ {
+ SELECT_LEX *current= thd->lex->current_select;
+ thd->lex->current_select= current->return_after_parsing();
+ char const *save_where= thd->where;
+ thd->where= "IN/ALL/ANY subquery";
+
+ bool failure= !in_subs->left_expr->fixed &&
+ in_subs->left_expr->fix_fields(thd, &in_subs->left_expr);
+ thd->lex->current_select= current;
+ thd->where= save_where;
+ if (failure)
+ DBUG_RETURN(-1); /* purecov: deadcode */
+ /*
+ Check if the left and right expressions have the same # of
+ columns, i.e. we don't have a case like
+ (oe1, oe2) IN (SELECT ie1, ie2, ie3 ...)
+
+ TODO why do we have this duplicated in IN->EXISTS transformers?
+ psergey-todo: fix these: grep for duplicated_subselect_card_check
+ */
+ if (select_lex->item_list.elements != in_subs->left_expr->cols())
+ {
+ my_error(ER_OPERAND_COLUMNS, MYF(0), in_subs->left_expr->cols());
+ DBUG_RETURN(-1);
+ }
+ }
+ /*
+ The below test might seem redundant, but this code block may also be
+ executed for EXISTS queries as soon as WL#4389 has been integrated
+ in the codebase.
+ */
+ if (in_subs != NULL &&
+ thd->stmt_arena->state != Query_arena::PREPARED)
+ {
+ subquery_types_allow_materialization(in_subs,
+ &in_subs->types_allow_materialization,
+ &in_subs->sjm_scan_allowed);
+ }
DBUG_PRINT("info", ("Checking if subq can be converted to semi-join"));
/*
Check if we're in subquery that is a candidate for flattening into a
@@ -613,39 +665,8 @@ JOIN::prepare(Item ***rref_pointer_array
& SELECT_STRAIGHT_JOIN)) // 10
{
DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
- in_subs->types_allow_materialization=
- subquery_types_allow_materialization(thd, in_subs,
- &in_subs->sjm_scan_allowed);
-
- if (thd->stmt_arena->state != Query_arena::PREPARED)
- {
- SELECT_LEX *current= thd->lex->current_select;
- thd->lex->current_select= current->return_after_parsing();
- char const *save_where= thd->where;
- thd->where= "IN/ALL/ANY subquery";
-
- bool failure= !in_subs->left_expr->fixed &&
- in_subs->left_expr->fix_fields(thd,
- &in_subs->left_expr);
- thd->lex->current_select= current;
- thd->where= save_where;
- in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
- if (failure)
- DBUG_RETURN(-1); /* purecov: deadcode */
- /*
- Check if the left and right expressions have the same # of
- columns, i.e. we don't have a case like
- (oe1, oe2) IN (SELECT ie1, ie2, ie3 ...)
- TODO why do we have this duplicated in IN->EXISTS transformers?
- psergey-todo: fix these: grep for duplicated_subselect_card_check
- */
- if (select_lex->item_list.elements != in_subs->left_expr->cols())
- {
- my_error(ER_OPERAND_COLUMNS, MYF(0), in_subs->left_expr->cols());
- DBUG_RETURN(-1);
- }
- }
+ in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
/* Register the subquery for further processing */
select_lex->outer_select()->join->sj_subselects.append(thd->mem_root, in_subs);
@@ -694,9 +715,8 @@ JOIN::prepare(Item ***rref_pointer_array
select_lex->master_unit()->first_select()->leaf_tables && // 3
thd->lex->sql_command == SQLCOM_SELECT && // *
select_lex->outer_select()->leaf_tables && // 3A
- subquery_types_allow_materialization(thd, in_subs, NULL))
+ in_subs->types_allow_materialization)
{
- // psergey-todo: duplicated_subselect_card_check: where it's done?
if (in_subs->is_top_level_item() && // 4
!in_subs->is_correlated && // 5
in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 6
@@ -854,7 +874,8 @@ err:
subquery_types_allow_materialization()
thd Thread handle
in_subs Subquery predicate
- scan_allowed OUT If the return value is TRUE:
+ mat_allowed OUT If TRUE, subquery materialization is allowed
+ scan_allowed OUT If *mat_allowed is TRUE:
indicates whether it is possible to use subquery
materialization and scan the materialized table
Else
@@ -895,39 +916,23 @@ err:
details)
* require that compared columns have exactly the same type. This is
a temporary measure to avoid BUG#36752-type problems.
-
- RETURN
- TRUE Yes, subquery types allow materialization
- FALSE No, or this is an invalid subquery
*/
static
-bool subquery_types_allow_materialization(THD *thd,
- Item_in_subselect *in_subs,
+void subquery_types_allow_materialization(Item_in_subselect *in_subs,
+ bool *mat_allowed,
bool *scan_allowed)
{
DBUG_ENTER("subquery_types_allow_materialization");
- /* Fix the left expression if it is not yet fixed */
- if (!in_subs->left_expr->fixed)
- {
- SELECT_LEX *save_lex= thd->lex->current_select;
- thd->lex->current_select= save_lex->outer_select();
- char const *save_where= thd->where;
- thd->where= "IN/ALL/ANY subquery";
- bool res= in_subs->left_expr->fix_fields(thd, &in_subs->left_expr);
- thd->where= save_where;
- thd->lex->current_select=save_lex;
- if (res)
- DBUG_RETURN(FALSE);
- }
-
List_iterator<Item> it(in_subs->unit->first_select()->item_list);
uint elements= in_subs->unit->first_select()->item_list.elements;
- // psergey: duplicated_subselect_card_check
- if (in_subs->left_expr->cols() != elements)
- DBUG_RETURN(FALSE);
-
+
+ *mat_allowed= FALSE; // Assign default return value
+
+ /* Left expression should be fixed when we reach here */
+ DBUG_ASSERT(in_subs->left_expr->fixed);
+
bool all_are_fields= TRUE;
for (uint i= 0; i < elements; i++)
{
@@ -936,26 +941,27 @@ bool subquery_types_allow_materializatio
all_are_fields &= (outer->real_item()->type() == Item::FIELD_ITEM &&
inner->real_item()->type() == Item::FIELD_ITEM);
if (outer->result_type() != inner->result_type())
- DBUG_RETURN(FALSE);
+ DBUG_VOID_RETURN;
switch (outer->result_type()) {
case STRING_RESULT:
if (outer->is_datetime() != inner->is_datetime())
- DBUG_RETURN(FALSE);
+ DBUG_VOID_RETURN;
if (!(outer->collation.collation == inner->collation.collation
/*&& outer->max_length <= inner->max_length */))
- DBUG_RETURN(FALSE);
+ DBUG_VOID_RETURN;
/*case INT_RESULT:
if (!(outer->unsigned_flag ^ inner->unsigned_flag))
- DBUG_RETURN(FALSE); */
+ DBUG_VOID_RETURN; */
default:
;/* suitable for materialization */
}
}
+ *mat_allowed= TRUE;
if (scan_allowed)
*scan_allowed= all_are_fields;
DBUG_PRINT("info",("subquery_types_allow_materialization: ok, allowed"));
- DBUG_RETURN(TRUE);
+ DBUG_VOID_RETURN;
}
Attachment: [text/bzr-bundle] bzr/roy.lyseng@sun.com-20091105101323-288p72pykuo1h5f0.bundle
