At file:///home/psergey/dev/maria-5.3-subqueries-r7-rel/
------------------------------------------------------------
revno: 2774
revision-id: psergey@askmonty.org-20100314175412-umtxuabkn4txl1yd
parent: psergey@askmonty.org-20100307154145-ksby2b1l0sqm1xne
committer: Sergey Petrunya
branch nick: maria-5.3-subqueries-r7-rel
timestamp: Sun 2010-03-14 20:54:12 +0300
message:
BUG#43768: Prepared query with nested subqueries core dumps on second execution
Fix two problems:
1. Let optimize_semijoin_nests() reset sj_nest->sjmat_info irrespectively
of value of optimizer_flag. We need this in case somebody has turned optimization
off between reexecutions of the same statement.
2. Do not pull out constant tables out of semi-join nests. The problem is that pullout
operation is not undoable, and if a table is constant because it is 1/0-row table it
may cease to be constant on the next execution. Note that tables that are constant
because of possible eq_ref(const) access will still be pulled out as they are
considered functionally-dependent.
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2010-02-24 11:33:42 +0000
+++ b/mysql-test/r/subselect_sj.result 2010-03-14 17:54:12 +0000
@@ -1,4 +1,4 @@
-drop table if exists t0, t1, t2, t10, t11, t12;
+drop table if exists t0, t1, t2, t3, t4, t10, t11, t12;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
@@ -871,3 +871,54 @@
DROP TABLE t1, t2, t3;
DROP VIEW v2, v3;
# End of Bug#49198
+#
+# BUG#43768: Prepared query with nested subqueries core dumps on second execution
+#
+create table t1 (
+id int(11) unsigned not null primary key auto_increment,
+partner_id varchar(35) not null,
+t1_status_id int(10) unsigned
+);
+insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
+("3", "partner3", "10"), ("4", "partner4", "10");
+create table t2 (
+id int(11) unsigned not null default '0',
+t1_line_id int(11) unsigned not null default '0',
+article_id varchar(20),
+sequence int(11) not null default '0',
+primary key (id,t1_line_id)
+);
+insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
+("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
+("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
+("4", "1", "sup", "0");
+create table t3 (
+id int(11) not null default '0',
+preceeding_id int(11) not null default '0',
+primary key (id,preceeding_id)
+);
+create table t4 (
+user_id varchar(50) not null,
+article_id varchar(20) not null,
+primary key (user_id,article_id)
+);
+insert into t4 values("nicke", "imp");
+prepare stmt from
+'select t1.partner_id
+from t1
+where
+ t1.id in (
+ select pl_inner.id
+ from t2 as pl_inner
+ where pl_inner.article_id in (
+ select t4.article_id from t4
+ where t4.user_id = \'nicke\'
+ )
+ )';
+execute stmt;
+partner_id
+partner2
+execute stmt;
+partner_id
+partner2
+drop table t1,t2,t3,t4;
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-07 15:41:45 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-14 17:54:12 +0000
@@ -2,7 +2,7 @@
show variables like 'join_cache_level';
Variable_name Value
join_cache_level 6
-drop table if exists t0, t1, t2, t10, t11, t12;
+drop table if exists t0, t1, t2, t3, t4, t10, t11, t12;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
@@ -876,6 +876,57 @@
DROP VIEW v2, v3;
# End of Bug#49198
#
+# BUG#43768: Prepared query with nested subqueries core dumps on second execution
+#
+create table t1 (
+id int(11) unsigned not null primary key auto_increment,
+partner_id varchar(35) not null,
+t1_status_id int(10) unsigned
+);
+insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
+("3", "partner3", "10"), ("4", "partner4", "10");
+create table t2 (
+id int(11) unsigned not null default '0',
+t1_line_id int(11) unsigned not null default '0',
+article_id varchar(20),
+sequence int(11) not null default '0',
+primary key (id,t1_line_id)
+);
+insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
+("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
+("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
+("4", "1", "sup", "0");
+create table t3 (
+id int(11) not null default '0',
+preceeding_id int(11) not null default '0',
+primary key (id,preceeding_id)
+);
+create table t4 (
+user_id varchar(50) not null,
+article_id varchar(20) not null,
+primary key (user_id,article_id)
+);
+insert into t4 values("nicke", "imp");
+prepare stmt from
+'select t1.partner_id
+from t1
+where
+ t1.id in (
+ select pl_inner.id
+ from t2 as pl_inner
+ where pl_inner.article_id in (
+ select t4.article_id from t4
+ where t4.user_id = \'nicke\'
+ )
+ )';
+execute stmt;
+partner_id
+partner2
+execute stmt;
+partner_id
+partner2
+drop table t1,t2,t3,t4;
+#
# BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
#
CREATE TABLE t0 (a INT);
=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test 2010-02-24 11:33:42 +0000
+++ b/mysql-test/t/subselect_sj.test 2010-03-14 17:54:12 +0000
@@ -2,7 +2,7 @@
# Nested Loops semi-join subquery evaluation tests
#
--disable_warnings
-drop table if exists t0, t1, t2, t10, t11, t12;
+drop table if exists t0, t1, t2, t3, t4, t10, t11, t12;
--enable_warnings
#
@@ -770,3 +770,60 @@
DROP VIEW v2, v3;
--echo # End of Bug#49198
+
+--echo #
+--echo # BUG#43768: Prepared query with nested subqueries core dumps on second execution
+--echo #
+create table t1 (
+ id int(11) unsigned not null primary key auto_increment,
+ partner_id varchar(35) not null,
+ t1_status_id int(10) unsigned
+);
+
+insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
+ ("3", "partner3", "10"), ("4", "partner4", "10");
+
+create table t2 (
+ id int(11) unsigned not null default '0',
+ t1_line_id int(11) unsigned not null default '0',
+ article_id varchar(20),
+ sequence int(11) not null default '0',
+ primary key (id,t1_line_id)
+);
+
+insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
+ ("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
+ ("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
+ ("4", "1", "sup", "0");
+create table t3 (
+ id int(11) not null default '0',
+ preceeding_id int(11) not null default '0',
+ primary key (id,preceeding_id)
+);
+
+create table t4 (
+ user_id varchar(50) not null,
+ article_id varchar(20) not null,
+ primary key (user_id,article_id)
+);
+
+insert into t4 values("nicke", "imp");
+prepare stmt from
+'select t1.partner_id
+from t1
+where
+ t1.id in (
+ select pl_inner.id
+ from t2 as pl_inner
+ where pl_inner.article_id in (
+ select t4.article_id from t4
+ where t4.user_id = \'nicke\'
+ )
+ )';
+
+execute stmt;
+execute stmt;
+drop table t1,t2,t3,t4;
+
+
+
=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc 2010-03-07 15:41:45 +0000
+++ b/sql/opt_subselect.cc 2010-03-14 17:54:12 +0000
@@ -963,7 +963,6 @@
{
/* Action #1: Mark the constant tables to be pulled out */
table_map pulled_tables= 0;
-
List_iterator child_li(sj_nest->nested_join->join_list);
TABLE_LIST *tbl;
while ((tbl= child_li++))
@@ -971,12 +970,34 @@
if (tbl->table)
{
tbl->table->reginfo.join_tab->emb_sj_nest= sj_nest;
+#if 0
+ /*
+ Do not pull out tables because they are constant. This operation has
+ a problem:
+ - Some constant tables may become/cease to be constant across PS
+ re-executions
+ - Contrary to our initial assumption, it turned out that table pullout
+ operation is not easily undoable.
+
+ The solution is to leave constant tables where they are. This will
+ affect only constant tables that are 1-row or empty, tables that are
+ constant because they are accessed via eq_ref(const) access will
+ still be pulled out as functionally-dependent.
+
+ This will cause us to miss the chance to flatten some of the
+ subqueries, but since const tables do not generate many duplicates,
+ it really doesn't matter that much whether they were pulled out or
+ not.
+
+ All of this was done as fix for BUG#43768.
+ */
if (tbl->table->map & join->const_table_map)
{
pulled_tables |= tbl->table->map;
DBUG_PRINT("info", ("Table %s pulled out (reason: constant)",
tbl->table->alias));
}
+#endif
}
}
@@ -1048,6 +1069,7 @@
pointers.
*/
child_li.remove();
+ sj_nest->nested_join->used_tables &= ~tbl->table->map;
upper_join_list->push_back(tbl);
tbl->join_list= upper_join_list;
tbl->embedding= sj_nest->embedding;
@@ -1104,20 +1126,20 @@
DBUG_ENTER("optimize_semijoin_nests");
List_iterator sj_list_it(join->select_lex->sj_nests);
TABLE_LIST *sj_nest;
- /*
- The statement may have been executed with 'semijoin=on' earlier.
- We need to verify that 'semijoin=on' still holds.
- */
- if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_SEMIJOIN) &&
- optimizer_flag(join->thd, OPTIMIZER_SWITCH_MATERIALIZATION))
+ while ((sj_nest= sj_list_it++))
{
- while ((sj_nest= sj_list_it++))
+ /* semi-join nests with only constant tables are not valid */
+ /// DBUG_ASSERT(sj_nest->sj_inner_tables & ~join->const_table_map);
+
+ sj_nest->sj_mat_info= NULL;
+ /*
+ The statement may have been executed with 'semijoin=on' earlier.
+ We need to verify that 'semijoin=on' still holds.
+ */
+ if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_SEMIJOIN) &&
+ optimizer_flag(join->thd, OPTIMIZER_SWITCH_MATERIALIZATION))
{
- /* semi-join nests with only constant tables are not valid */
- DBUG_ASSERT(sj_nest->sj_inner_tables & ~join->const_table_map);
-
- sj_nest->sj_mat_info= NULL;
- if (sj_nest->sj_inner_tables && /* not everything was pulled out */
+ if ((sj_nest->sj_inner_tables & ~join->const_table_map) && /* not everything was pulled out */
!sj_nest->sj_subq_pred->is_correlated &&
sj_nest->sj_subq_pred->types_allow_materialization)
{
@@ -1128,7 +1150,7 @@
The best plan to run the subquery is now in join->best_positions,
save it.
*/
- uint n_tables= my_count_bits(sj_nest->sj_inner_tables);
+ uint n_tables= my_count_bits(sj_nest->sj_inner_tables & ~join->const_table_map);
SJ_MATERIALIZATION_INFO* sjm;
if (!(sjm= new SJ_MATERIALIZATION_INFO) ||
!(sjm->positions= (POSITION*)join->thd->alloc(sizeof(POSITION)*
@@ -1443,7 +1465,7 @@
new_join_tab->emb_sj_nest->nested_join->sj_corr_tables |
new_join_tab->emb_sj_nest->nested_join->sj_depends_on;
const table_map sj_inner_tables=
- new_join_tab->emb_sj_nest->sj_inner_tables;
+ new_join_tab->emb_sj_nest->sj_inner_tables & ~join->const_table_map;
/*
Enter condition:
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-03-07 15:41:45 +0000
+++ b/sql/sql_select.cc 2010-03-14 17:54:12 +0000
@@ -5127,7 +5127,9 @@
/* number of tables that remain to be optimized */
n_tables= size_remain= my_count_bits(remaining_tables &
(join->emb_sjm_nest?
- join->emb_sjm_nest->sj_inner_tables :
+ (join->emb_sjm_nest->sj_inner_tables &
+ ~join->const_table_map)
+ :
~(table_map)0));
do {
@@ -5387,7 +5389,7 @@
table_map allowed_tables= ~(table_map)0;
if (join->emb_sjm_nest)
- allowed_tables= join->emb_sjm_nest->sj_inner_tables;
+ allowed_tables= join->emb_sjm_nest->sj_inner_tables & ~join->const_table_map;
for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
{
