3005 Evgeny Potemkin 2011-02-23
Bug#11791649 - ASSERT: FIXED == 0, IN ITEM.CC ON EXPLAIN WITH VIEW IN SUBQUERY
The mysql_derived_cleanup function was mistakenly called for EXPLAIN causing
materializable view to be prepared for the second time and thus throwing the
reported assertion.
Fixed by skipping immediate cleanup in case of EXPLAIN.
@ mysql-test/r/derived.result
Added a test case for the bug#11791649.
@ mysql-test/t/derived.test
Added a test case for the bug#11791649.
@ sql/item_subselect.cc
Bug#11791649 - ASSERT: FIXED == 0, IN ITEM.CC ON EXPLAIN WITH VIEW IN SUBQUERY
subselect_uniquesubquery_engine::exec and subselect_indexsubquery_engine::exec
functions now don't call cleanup function immediately for EXPLAIN queries.
@ sql/sql_select.cc
Bug#11791649 - ASSERT: FIXED == 0, IN ITEM.CC ON EXPLAIN WITH VIEW IN SUBQUERY
join_materialize_table function now doesn't call cleanup for EXPLAIN queries.
modified:
mysql-test/r/derived.result
mysql-test/t/derived.test
sql/item_subselect.cc
sql/sql_select.cc
3004 Evgeny Potemkin 2011-02-21
Bug#11791677: ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN SQL_SELECT.CC ON
NESTED SUBQUERY
Derived tables materialization mechanism substitutes table's read_first_record
with the join_materialize_table function to materialize table on the first read,
after this is done the original read_first_record is restored and called.
Subqueries in WHERE clause could reinitialize join and restore
join_materialize_table as the read_first_record function. Assertion is thrown
on attempt to materialize a derived table second time.
Now original read_first_record function is restored in saved copy also to
prevent second materialization attempt.
@ mysql-test/r/derived.result
Added a test case for the bug#11791677.
@ mysql-test/t/derived.test
Added a test case for the bug#11791677.
modified:
mysql-test/r/derived.result
mysql-test/t/derived.test
sql/sql_select.cc
3003 Evgeny Potemkin 2011-02-21
Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
!TAB->SAVE_READ_FIRST_RECORD
The JOIN_TAB::save_read_first_record field is used for disabling an optimization
for IN subqueries and for materializing derived tables. Those two cases
wasn't expected to happen in the same query, thus caused the assertion to
fail.
As the JOIN_TAB::save_read_first_record can't store two pointers at the same
time it was substituted for the list of pointers to the read_first_record
functions. The list is used as a stack.
@ mysql-test/r/derived.result
Added a test case for the bug#11791705.
@ mysql-test/t/derived.test
Added a test case for the bug#11791705.
@ sql/item_subselect.cc
Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
!TAB->SAVE_READ_FIRST_RECORD
To allow storing more that one pointer JOIN_TAB::save_read_first_record
was substituted for the list of pointers, which is used as a stack.
@ sql/sql_select.cc
Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
!TAB->SAVE_READ_FIRST_RECORD
To allow storing more that one pointer JOIN_TAB::save_read_first_record
was substituted for the list of pointers, which is used as a stack.
@ sql/sql_select.h
Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
!TAB->SAVE_READ_FIRST_RECORD
The JOIN_TAB::save_read_first_record field was substituted for the
same named list.
modified:
mysql-test/r/derived.result
mysql-test/t/derived.test
sql/item_subselect.cc
sql/sql_select.cc
sql/sql_select.h
3002 Evgeny Potemkin 2011-02-18
Bug#11783262 - CRASH IN ITEM_FIELD::ITEM_FIELD IN ITEM.CC ON SUBQUERY IN FROM WITH WL5274
Incorrectly set field number in key part description caused server crash.
@ mysql-test/r/derived.result
Added a test case for the bug#11783262.
@ mysql-test/t/derived.test
Added a test case for the bug#11783262.
@ sql/table.cc
Bug#11783262 - CRASH IN ITEM_FIELD::ITEM_FIELD IN ITEM.CC ON SUBQUERY IN FROM WITH WL5274
Incorrectly set field number in key part description caused server crash.
modified:
mysql-test/r/derived.result
mysql-test/t/derived.test
sql/table.cc
=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result 2011-02-18 14:42:19 +0000
+++ b/mysql-test/r/derived.result 2011-02-23 14:37:35 +0000
@@ -1388,3 +1388,173 @@ GROUP BY field2 ;
field1 field2
1 2
DROP TABLE t1,t2;
+#
+# Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
+# !TAB->SAVE_READ_FIRST_RECORD
+#
+CREATE TABLE t1 (
+pk INT NOT NULL AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,7,8),
+(11,1,9),
+(12,5,9),
+(13,3,186),
+(14,6,NULL)
+;
+CREATE TABLE t2 (
+pk INT NOT NULL AUTO_INCREMENT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10,8);
+SELECT alias2.col_int_nokey
+FROM t2 AS alias1
+RIGHT JOIN t1 AS alias2
+JOIN t1 AS alias3
+ON alias3.pk ON alias2.pk
+WHERE
+( alias3.col_int_key , alias1.col_int_key )
+NOT IN
+(
+SELECT *
+FROM (SELECT 8 AS `8`, 4 AS `4` UNION SELECT 2 AS `2`, 3 AS `3`) tt
+)
+;
+col_int_nokey
+7
+1
+5
+3
+6
+7
+1
+5
+3
+6
+7
+1
+5
+3
+6
+7
+1
+5
+3
+6
+7
+1
+5
+3
+6
+DROP TABLE t1;
+DROP TABLE t2;
+#
+#
+# Bug#11791677 - ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN
+# SQL_SELECT.CC ON NESTED SUBQUERY
+#
+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_time_key time DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,7,8,'01:27:35','v'),
+(28,5,6,'21:44:25','m'),
+(29,NULL,4,'22:43:58','c')
+;
+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_time_key time DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(8,8,8,'11:32:06','u'),
+(10,5,53,'15:19:25','o'),
+(16,1,1,'04:56:48','c'),
+(17,0,9,'19:56:05','m'),
+(18,9,5,'19:35:19','y'),
+(20,4,2,'18:38:59','d')
+;
+CREATE TABLE `t3` (
+`field1` varchar(1) DEFAULT NULL,
+`field2` int(11) DEFAULT NULL
+);
+INSERT INTO t3 VALUES ('m', 6),('c',4);
+SELECT *
+FROM t3
+WHERE (field1, field2) IN (
+SELECT alias1.col_varchar_key AS field1,
+alias1.col_int_key AS field2
+FROM ( t1 AS alias1
+INNER JOIN (
+SELECT SQ1_alias1.*
+FROM t2 AS SQ1_alias1
+WHERE SQ1_alias1.col_int_nokey < SQ1_alias1.pk ) AS alias2
+ON (alias2.col_varchar_key = alias1.col_varchar_key )))
+GROUP BY field1, field2
+;
+field1 field2
+c 4
+m 6
+DROP TABLE t1,t2,t3;
+#
+#
+# Bug#11791649 - ASSERT: FIXED == 0, IN ITEM.CC ON EXPLAIN WITH VIEW
+# IN SUBQUERY
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
+(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
+;
+CREATE TABLE t2 (
+pk INT NOT NULL AUTO_INCREMENT,
+col_int_key INT,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (2, 9, 'm', 'm');
+CREATE VIEW view1 AS
+SELECT t2.col_varchar_key AS col_varchar_key,
+COUNT(col_varchar_nokey) AS `COUNT( col_varchar_nokey )`
+ FROM t2
+WHERE (2,4) IN (SELECT t2.col_int_key, t2.pk)
+;
+EXPLAIN SELECT pk
+FROM t1
+WHERE ( 'd' , 'f' ) IN
+( SELECT *
+FROM view1
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY <derived3> index_subquery auto_key0 auto_key0 12 const,const 0 Using index; Using where
+3 DERIVED t2 ALL NULL NULL NULL NULL 1 Using where
+4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+DROP VIEW view1;
+DROP TABLE t1,t2;
+#
=== modified file 'mysql-test/t/derived.test'
--- a/mysql-test/t/derived.test 2011-02-18 14:42:19 +0000
+++ b/mysql-test/t/derived.test 2011-02-23 14:37:35 +0000
@@ -758,3 +758,161 @@ GROUP BY field2 ;
DROP TABLE t1,t2;
+--echo #
+--echo # Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
+--echo # !TAB->SAVE_READ_FIRST_RECORD
+--echo #
+
+CREATE TABLE t1 (
+ pk INT NOT NULL AUTO_INCREMENT,
+ col_int_nokey INT,
+ col_int_key INT,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ (10,7,8),
+ (11,1,9),
+ (12,5,9),
+ (13,3,186),
+ (14,6,NULL)
+;
+
+CREATE TABLE t2 (
+ pk INT NOT NULL AUTO_INCREMENT,
+ col_int_key INT,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (10,8);
+
+SELECT alias2.col_int_nokey
+FROM t2 AS alias1
+ RIGHT JOIN t1 AS alias2
+ JOIN t1 AS alias3
+ ON alias3.pk ON alias2.pk
+WHERE
+ ( alias3.col_int_key , alias1.col_int_key )
+ NOT IN
+ (
+ SELECT *
+ FROM (SELECT 8 AS `8`, 4 AS `4` UNION SELECT 2 AS `2`, 3 AS `3`) tt
+ )
+;
+
+DROP TABLE t1;
+DROP TABLE t2;
+--echo #
+
+--echo #
+--echo # Bug#11791677 - ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN
+--echo # SQL_SELECT.CC ON NESTED SUBQUERY
+--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_time_key time DEFAULT NULL,
+ col_varchar_key varchar(1) DEFAULT NULL,
+ PRIMARY KEY (pk),
+ KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ (10,7,8,'01:27:35','v'),
+ (28,5,6,'21:44:25','m'),
+ (29,NULL,4,'22:43:58','c')
+;
+
+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_time_key time DEFAULT NULL,
+ col_varchar_key varchar(1) DEFAULT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key),
+ KEY col_time_key (col_time_key),
+ KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+ (8,8,8,'11:32:06','u'),
+ (10,5,53,'15:19:25','o'),
+ (16,1,1,'04:56:48','c'),
+ (17,0,9,'19:56:05','m'),
+ (18,9,5,'19:35:19','y'),
+ (20,4,2,'18:38:59','d')
+;
+
+CREATE TABLE `t3` (
+ `field1` varchar(1) DEFAULT NULL,
+ `field2` int(11) DEFAULT NULL
+);
+
+INSERT INTO t3 VALUES ('m', 6),('c',4);
+
+SELECT *
+FROM t3
+WHERE (field1, field2) IN (
+ SELECT alias1.col_varchar_key AS field1,
+ alias1.col_int_key AS field2
+ FROM ( t1 AS alias1
+ INNER JOIN (
+ SELECT SQ1_alias1.*
+ FROM t2 AS SQ1_alias1
+ WHERE SQ1_alias1.col_int_nokey < SQ1_alias1.pk ) AS alias2
+ ON (alias2.col_varchar_key = alias1.col_varchar_key )))
+ GROUP BY field1, field2
+;
+
+DROP TABLE t1,t2,t3;
+--echo #
+
+--echo #
+--echo # Bug#11791649 - ASSERT: FIXED == 0, IN ITEM.CC ON EXPLAIN WITH VIEW
+--echo # IN SUBQUERY
+--echo #
+CREATE TABLE t1 (
+ pk int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
+ (11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
+;
+
+CREATE TABLE t2 (
+ pk INT NOT NULL AUTO_INCREMENT,
+ col_int_key INT,
+ col_varchar_key varchar(1),
+ col_varchar_nokey varchar(1),
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key),
+ KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (2, 9, 'm', 'm');
+
+CREATE VIEW view1 AS
+ SELECT t2.col_varchar_key AS col_varchar_key,
+ COUNT(col_varchar_nokey) AS `COUNT( col_varchar_nokey )`
+ FROM t2
+ WHERE (2,4) IN (SELECT t2.col_int_key, t2.pk)
+;
+
+EXPLAIN SELECT pk
+FROM t1
+WHERE ( 'd' , 'f' ) IN
+ ( SELECT *
+ FROM view1
+ )
+;
+
+DROP VIEW view1;
+DROP TABLE t1,t2;
+--echo #
+
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2011-02-18 10:46:59 +0000
+++ b/sql/item_subselect.cc 2011-02-23 14:37:35 +0000
@@ -2370,12 +2370,27 @@ int subselect_single_select_engine::exec
Make sure that save_read_first_record usage doesn't
intersect with join_materialize_table()'s.
*/
- DBUG_ASSERT(!tab->save_read_first_record);
/* Change the access method to full table scan */
- tab->save_read_first_record= tab->read_first_record;
+ if (tab->save_read_first_record.elements)
+ {
+ TABLE_LIST *tl= tab->table->pos_in_table_list;
+ /*
+ This is a materializable derived table and it's not
+ materialized yet.
+ */
+ DBUG_ASSERT(tl->uses_materialization() && !tl->materialized);
+ // Preserve join_materialize_table to be the first handler
+ tab->save_read_first_record.push_front(
+ (READ_RECORD::Setup_func*)read_first_record_seq);
+ }
+ else
+ {
+ tab->save_read_first_record.push_front(
+ (READ_RECORD::Setup_func*)tab->read_first_record);
+ tab->read_first_record= read_first_record_seq;
+ }
tab->save_read_record= tab->read_record.read_record;
tab->read_record.read_record= rr_sequential;
- tab->read_first_record= read_first_record_seq;
tab->read_record.record= tab->table->record[0];
tab->read_record.thd= join->thd;
tab->read_record.ref_length= tab->table->file->ref_length;
@@ -2396,9 +2411,10 @@ int subselect_single_select_engine::exec
JOIN_TAB *tab= *ptab;
tab->read_record.record= 0;
tab->read_record.ref_length= 0;
- tab->read_first_record= tab->save_read_first_record;
+ DBUG_ASSERT(tab->save_read_first_record.elements);
+ tab->read_first_record=
+ (READ_RECORD::Setup_func)tab->save_read_first_record.pop();
tab->read_record.read_record= tab->save_read_record;
- tab->save_read_first_record= NULL;
}
executed= true;
@@ -2618,9 +2634,10 @@ int subselect_uniquesubquery_engine::exe
bool err= mysql_handle_single_derived(table->in_use->lex, tl,
mysql_derived_create) ||
mysql_handle_single_derived(table->in_use->lex, tl,
- mysql_derived_materialize) ||
- mysql_handle_single_derived(table->in_use->lex, tl,
- mysql_derived_cleanup);
+ mysql_derived_materialize);
+ if (!tab->table->in_use->lex->describe)
+ err|= mysql_handle_single_derived(table->in_use->lex, tl,
+ mysql_derived_cleanup);
if (err)
DBUG_RETURN(1);
}
@@ -2738,9 +2755,10 @@ int subselect_indexsubquery_engine::exec
bool err= mysql_handle_single_derived(table->in_use->lex, tl,
mysql_derived_create) ||
mysql_handle_single_derived(table->in_use->lex, tl,
- mysql_derived_materialize) ||
- mysql_handle_single_derived(table->in_use->lex, tl,
- mysql_derived_cleanup);
+ mysql_derived_materialize);
+ if (!tab->table->in_use->lex->describe)
+ err|= mysql_handle_single_derived(table->in_use->lex, tl,
+ mysql_derived_cleanup);
if (err)
DBUG_RETURN(1);
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-02-17 12:14:26 +0000
+++ b/sql/sql_select.cc 2011-02-23 14:37:35 +0000
@@ -11415,7 +11415,8 @@ make_join_readinfo(JOIN *join, ulonglong
if (tab->table->pos_in_table_list->uses_materialization() &&
!tab->table->pos_in_table_list->materialized)
{
- tab->save_read_first_record= tab->read_first_record;
+ tab->save_read_first_record.push_front(
+ (READ_RECORD::Setup_func*)tab->read_first_record);
tab->read_first_record= join_materialize_table;
}
}
@@ -18513,12 +18514,20 @@ join_materialize_table(JOIN_TAB *tab)
!derived->materialized);
bool res= mysql_handle_single_derived(tab->table->in_use->lex,
derived, &mysql_derived_materialize);
- mysql_handle_single_derived(tab->table->in_use->lex,
- derived, &mysql_derived_cleanup);
+ if (!tab->table->in_use->lex->describe)
+ mysql_handle_single_derived(tab->table->in_use->lex,
+ derived, &mysql_derived_cleanup);
if (res)
return -1;
- tab->read_first_record= tab->save_read_first_record;
- tab->save_read_first_record= NULL;
+ tab->read_first_record=
+ (READ_RECORD::Setup_func)tab->save_read_first_record.pop();
+ // Adjust read_first_record in saved tab too
+ if (tab->join->join_tab_save)
+ {
+ JOIN *join= tab->join;
+ uint idx= tab - join->join_tab;
+ join->join_tab_save[idx].read_first_record= tab->read_first_record;
+ }
return (*tab->read_first_record)(tab);
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2011-02-18 10:46:59 +0000
+++ b/sql/sql_select.h 2011-02-21 14:17:57 +0000
@@ -273,7 +273,8 @@ typedef struct st_join_table : public Sq
the subquery predicate is evaluated to NULL.
save_read_first_record is also used during derived tables materialization.
*/
- READ_RECORD::Setup_func save_read_first_record;/* to save read_first_record */
+ /* to save read_first_record */
+ List<READ_RECORD::Setup_func> save_read_first_record;
READ_RECORD::Read_func save_read_record;/* to save read_record.read_record */
double worst_seeks;
key_map const_keys; /**< Keys with constant part */
@@ -456,7 +457,6 @@ st_join_table::st_join_table()
read_first_record(NULL),
next_select(NULL),
read_record(),
- save_read_first_record(NULL),
save_read_record(NULL),
worst_seeks(0.0),
const_keys(),
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (epotemkin:3002 to 3005) Bug#11791649 | Evgeny Potemkin | 24 Feb |
