diff options
| -rw-r--r-- | mysql-test/r/sp-innodb.result | 34 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 26 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/sp-innodb.test | 42 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 30 | ||||
| -rw-r--r-- | sql-common/client.c | 2 | ||||
| -rw-r--r-- | sql/item.cc | 7 | ||||
| -rw-r--r-- | sql/item.h | 1 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 42 | ||||
| -rw-r--r-- | sql/item_func.cc | 1 | ||||
| -rw-r--r-- | sql/item_func.h | 7 | ||||
| -rw-r--r-- | sql/item_row.cc | 1 | ||||
| -rw-r--r-- | sql/item_sum.cc | 3 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 24 | ||||
| -rw-r--r-- | sql/sql_select.cc | 6 |
15 files changed, 228 insertions, 24 deletions
diff --git a/mysql-test/r/sp-innodb.result b/mysql-test/r/sp-innodb.result index b3405705698..8dee74040b3 100644 --- a/mysql-test/r/sp-innodb.result +++ b/mysql-test/r/sp-innodb.result @@ -130,3 +130,37 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; # # BUG 16041903: End of test case # +# +# MDEV-15035: SP using query with outer join and a parameter +# in ON expression +# +CREATE TABLE t1 ( +id int NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 ( +id int NOT NULL, +id_foo int NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1, 1); +DROP PROCEDURE IF EXISTS test_proc; +CREATE PROCEDURE test_proc(IN param int) +LANGUAGE SQL +READS SQL DATA +BEGIN +SELECT DISTINCT f.id +FROM t1 f +LEFT OUTER JOIN t2 b ON b.id_foo = f.id +WHERE (param <> 0 OR b.id IS NOT NULL); +END| +CALL test_proc(0); +id +1 +CALL test_proc(1); +id +1 +2 +DROP PROCEDURE IF EXISTS test_proc; +DROP TABLE t1, t2; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 79a98b3efa1..fd9a66d8ef1 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -3155,4 +3155,30 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1 DROP TABLE t1,t2,t3,t4; +# +# MDEV-13699: Assertion `!new_field->field_name.str || +# strlen(new_field->field_name.str) == new_field->field_name.length' +# failed in create_tmp_table on 2nd execution of PS with semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c INT); +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (5),(6); +PREPARE stmt FROM +"SELECT * FROM t1 + WHERE EXISTS ( + SELECT * FROM t2 WHERE t1.a IN ( SELECT c AS fld FROM v3 ) + )"; +EXECUTE stmt; +a +EXECUTE stmt; +a +EXECUTE stmt; +a +drop view v3; +drop table t1,t2,t3; +# End of 5.5 test set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 2bc72acd478..71493df594f 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3169,6 +3169,32 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1 DROP TABLE t1,t2,t3,t4; +# +# MDEV-13699: Assertion `!new_field->field_name.str || +# strlen(new_field->field_name.str) == new_field->field_name.length' +# failed in create_tmp_table on 2nd execution of PS with semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c INT); +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (5),(6); +PREPARE stmt FROM +"SELECT * FROM t1 + WHERE EXISTS ( + SELECT * FROM t2 WHERE t1.a IN ( SELECT c AS fld FROM v3 ) + )"; +EXECUTE stmt; +a +EXECUTE stmt; +a +EXECUTE stmt; +a +drop view v3; +drop table t1,t2,t3; +# End of 5.5 test set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/t/sp-innodb.test b/mysql-test/t/sp-innodb.test index 23715166a02..e44a853e713 100644 --- a/mysql-test/t/sp-innodb.test +++ b/mysql-test/t/sp-innodb.test @@ -158,5 +158,47 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; --echo # BUG 16041903: End of test case --echo # +--echo # +--echo # MDEV-15035: SP using query with outer join and a parameter +--echo # in ON expression +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1), (2); + +CREATE TABLE t2 ( + id int NOT NULL, + id_foo int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (1, 1); + +--disable_warnings +DROP PROCEDURE IF EXISTS test_proc; +--enable_warnings + +DELIMITER |; +CREATE PROCEDURE test_proc(IN param int) +LANGUAGE SQL +READS SQL DATA +BEGIN + SELECT DISTINCT f.id + FROM t1 f + LEFT OUTER JOIN t2 b ON b.id_foo = f.id + WHERE (param <> 0 OR b.id IS NOT NULL); +END| +DELIMITER ;| + +CALL test_proc(0); +CALL test_proc(1); + +DROP PROCEDURE IF EXISTS test_proc; +DROP TABLE t1, t2; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 16df4127b49..f98f5ac236f 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2845,5 +2845,35 @@ eval EXPLAIN EXTENDED $q2; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # MDEV-13699: Assertion `!new_field->field_name.str || +--echo # strlen(new_field->field_name.str) == new_field->field_name.length' +--echo # failed in create_tmp_table on 2nd execution of PS with semijoin +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (c INT); +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (5),(6); + +PREPARE stmt FROM + "SELECT * FROM t1 + WHERE EXISTS ( + SELECT * FROM t2 WHERE t1.a IN ( SELECT c AS fld FROM v3 ) + )"; +EXECUTE stmt; +EXECUTE stmt; +EXECUTE stmt; + +drop view v3; +drop table t1,t2,t3; + +--echo # End of 5.5 test + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/sql-common/client.c b/sql-common/client.c index 5574ac332aa..d583f54bcba 100644 --- a/sql-common/client.c +++ b/sql-common/client.c @@ -1503,7 +1503,7 @@ MYSQL_DATA *cli_read_rows(MYSQL *mysql,MYSQL_FIELD *mysql_fields, else { cur->data[field] = to; - if (len > (ulong) (end_to - to)) + if (to + len > end_to) { free_rows(result); set_mysql_error(mysql, CR_MALFORMED_PACKET, unknown_sqlstate); diff --git a/sql/item.cc b/sql/item.cc index 5f8cf6c80f7..5d413c38dd0 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -548,6 +548,7 @@ Item::Item(): in_rollup= 0; decimals= 0; max_length= 0; with_subselect= 0; + with_param= 0; cmp_context= IMPOSSIBLE_RESULT; /* Initially this item is not attached to any JOIN_TAB. */ join_tab_idx= MAX_TABLES; @@ -594,6 +595,7 @@ Item::Item(THD *thd, Item *item): null_value(item->null_value), unsigned_flag(item->unsigned_flag), with_sum_func(item->with_sum_func), + with_param(item->with_param), with_field(item->with_field), fixed(item->fixed), is_autogenerated_name(item->is_autogenerated_name), @@ -1475,6 +1477,9 @@ bool Item_sp_variable::fix_fields(THD *thd, Item **) max_length= it->max_length; decimals= it->decimals; unsigned_flag= it->unsigned_flag; + with_param= 1; + if (thd->lex->current_select->master_unit()->item) + thd->lex->current_select->master_unit()->item->with_param= 1; fixed= 1; collation.set(it->collation.collation, it->collation.derivation); @@ -7220,6 +7225,7 @@ void Item_ref::set_properties() split_sum_func() doesn't try to change the reference. */ with_sum_func= (*ref)->with_sum_func; + with_param= (*ref)->with_param; with_field= (*ref)->with_field; unsigned_flag= (*ref)->unsigned_flag; fixed= 1; @@ -7667,6 +7673,7 @@ Item_cache_wrapper::Item_cache_wrapper(Item *item_arg) decimals= orig_item->decimals; collation.set(orig_item->collation); with_sum_func= orig_item->with_sum_func; + with_param= orig_item->with_param; with_field= orig_item->with_field; unsigned_flag= orig_item->unsigned_flag; name= item_arg->name; diff --git a/sql/item.h b/sql/item.h index 7856328a33b..b111d74057c 100644 --- a/sql/item.h +++ b/sql/item.h @@ -669,6 +669,7 @@ public: bool null_value; /* if item is null */ bool unsigned_flag; bool with_sum_func; /* True if item contains a sum func */ + bool with_param; /* True if contains an SP parameter */ /** True if any item except Item_sum_func contains a field. Set during parsing. */ diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index d1c62859d20..410d58bdc29 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1571,6 +1571,7 @@ bool Item_in_optimizer::fix_left(THD *thd) } eval_not_null_tables(NULL); with_sum_func= args[0]->with_sum_func; + with_param= args[0]->with_param || args[1]->with_param; with_field= args[0]->with_field; if ((const_item_cache= args[0]->const_item())) { @@ -1620,6 +1621,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) with_subselect= 1; with_sum_func= with_sum_func || args[1]->with_sum_func; with_field= with_field || args[1]->with_field; + with_param= args[0]->with_param || args[1]->with_param; used_tables_cache|= args[1]->used_tables(); const_item_cache&= args[1]->const_item(); fixed= 1; @@ -2171,6 +2173,7 @@ void Item_func_interval::fix_length_and_dec() used_tables_cache|= row->used_tables(); not_null_tables_cache= row->not_null_tables(); with_sum_func= with_sum_func || row->with_sum_func; + with_param= with_param || row->with_param; with_field= with_field || row->with_field; const_item_cache&= row->const_item(); } @@ -4401,6 +4404,7 @@ Item_cond::fix_fields(THD *thd, Item **ref) List_iterator<Item> li(list); Item *item; uchar buff[sizeof(char*)]; // Max local vars in function + bool is_and_cond= functype() == Item_func::COND_AND_FUNC; not_null_tables_cache= used_tables_cache= 0; const_item_cache= 1; @@ -4462,26 +4466,33 @@ Item_cond::fix_fields(THD *thd, Item **ref) (item= *li.ref())->check_cols(1)) return TRUE; /* purecov: inspected */ used_tables_cache|= item->used_tables(); - if (item->const_item()) + if (item->const_item() && !item->with_param && + !item->is_expensive() && !cond_has_datetime_is_null(item)) { - if (!item->is_expensive() && !cond_has_datetime_is_null(item) && - item->val_int() == 0) + if (item->val_int() == is_and_cond && top_level()) { /* - This is "... OR false_cond OR ..." + a. This is "... AND true_cond AND ..." + In this case, true_cond has no effect on cond_and->not_null_tables() + b. This is "... OR false_cond/null cond OR ..." In this case, false_cond has no effect on cond_or->not_null_tables() */ } else { /* - This is "... OR const_cond OR ..." + a. This is "... AND false_cond/null_cond AND ..." + The whole condition is FALSE/UNKNOWN. + b. This is "... OR const_cond OR ..." In this case, cond_or->not_null_tables()=0, because the condition const_cond might evaluate to true (regardless of whether some tables were NULL-complemented). */ + not_null_tables_cache= (table_map) 0; and_tables_cache= (table_map) 0; } + if (thd->is_error()) + return TRUE; } else { @@ -4493,6 +4504,7 @@ Item_cond::fix_fields(THD *thd, Item **ref) } with_sum_func= with_sum_func || item->with_sum_func; + with_param= with_param || item->with_param; with_field= with_field || item->with_field; with_subselect|= item->has_subquery(); if (item->maybe_null) @@ -4509,30 +4521,36 @@ bool Item_cond::eval_not_null_tables(uchar *opt_arg) { Item *item; + bool is_and_cond= functype() == Item_func::COND_AND_FUNC; List_iterator<Item> li(list); not_null_tables_cache= (table_map) 0; and_tables_cache= ~(table_map) 0; while ((item=li++)) { table_map tmp_table_map; - if (item->const_item()) + if (item->const_item() && !item->with_param && + !item->is_expensive() && !cond_has_datetime_is_null(item)) { - if (!item->is_expensive() && !cond_has_datetime_is_null(item) && - item->val_int() == 0) + if (item->val_int() == is_and_cond && top_level()) { /* - This is "... OR false_cond OR ..." + a. This is "... AND true_cond AND ..." + In this case, true_cond has no effect on cond_and->not_null_tables() + b. This is "... OR false_cond/null cond OR ..." In this case, false_cond has no effect on cond_or->not_null_tables() */ } else { /* - This is "... OR const_cond OR ..." + a. This is "... AND false_cond/null_cond AND ..." + The whole condition is FALSE/UNKNOWN. + b. This is "... OR const_cond OR ..." In this case, cond_or->not_null_tables()=0, because the condition - some_cond_or might be true regardless of what tables are - NULL-complemented. + const_cond might evaluate to true (regardless of whether some tables + were NULL-complemented). */ + not_null_tables_cache= (table_map) 0; and_tables_cache= (table_map) 0; } } diff --git a/sql/item_func.cc b/sql/item_func.cc index 002eef67667..dac45cc0928 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -220,6 +220,7 @@ Item_func::fix_fields(THD *thd, Item **ref) maybe_null=1; with_sum_func= with_sum_func || item->with_sum_func; + with_param= with_param || item->with_param; with_field= with_field || item->with_field; used_tables_cache|= item->used_tables(); const_item_cache&= item->const_item(); diff --git a/sql/item_func.h b/sql/item_func.h index b0ba87b4bd0..813bcf0d023 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -90,6 +90,7 @@ public: args= tmp_arg; args[0]= a; with_sum_func= a->with_sum_func; + with_param= a->with_param; with_field= a->with_field; } Item_func(Item *a,Item *b): @@ -98,6 +99,7 @@ public: args= tmp_arg; args[0]= a; args[1]= b; with_sum_func= a->with_sum_func || b->with_sum_func; + with_param= a->with_param || b->with_param; with_field= a->with_field || b->with_field; } Item_func(Item *a,Item *b,Item *c): @@ -109,6 +111,7 @@ public: arg_count= 3; args[0]= a; args[1]= b; args[2]= c; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func; + with_param= a->with_param || b->with_param || c->with_param; with_field= a->with_field || b->with_field || c->with_field; } } @@ -122,6 +125,8 @@ public: args[0]= a; args[1]= b; args[2]= c; args[3]= d; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func || d->with_sum_func; + with_param= a->with_param || b->with_param || + c->with_param || d->with_param; with_field= a->with_field || b->with_field || c->with_field || d->with_field; } @@ -135,6 +140,8 @@ public: args[0]= a; args[1]= b; args[2]= c; args[3]= d; args[4]= e; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func || d->with_sum_func || e->with_sum_func ; + with_param= a->with_param || b->with_param || + c->with_param || d->with_param || e->with_param; with_field= a->with_field || b->with_field || c->with_field || d->with_field || e->with_field; } diff --git a/sql/item_row.cc b/sql/item_row.cc index c07ea612850..30afedb6cc9 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -126,6 +126,7 @@ bool Item_row::fix_fields(THD *thd, Item **ref) with_sum_func= with_sum_func || item->with_sum_func; with_field= with_field || item->with_field; with_subselect|= item->with_subselect; + with_param|= item->with_param; } fixed= 1; return FALSE; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 7b017cd810a..fae3f2344df 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -1181,6 +1181,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref) return TRUE; set_if_bigger(decimals, args[i]->decimals); with_subselect|= args[i]->with_subselect; + with_param|= args[i]->with_param; } result_field=0; max_length=float_length(decimals); @@ -1212,6 +1213,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref) return TRUE; decimals=item->decimals; with_subselect= args[0]->with_subselect; + with_param= args[0]->with_param; switch (hybrid_type= item->result_type()) { case INT_RESULT: @@ -3443,6 +3445,7 @@ Item_func_group_concat::fix_fields(THD *thd, Item **ref) args[i]->check_cols(1)) return TRUE; with_subselect|= args[i]->with_subselect; + with_param|= args[i]->with_param; } /* skip charset aggregation for order columns */ diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index a9e21e67f13..d8ecdc9a2ee 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3730,21 +3730,29 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab) SJ_MATERIALIZATION_INFO *sjm= emb_sj_nest->sj_mat_info; THD *thd= tab->join->thd; /* First the calls come to the materialization function */ - //List<Item> &item_list= emb_sj_nest->sj_subq_pred->unit->first_select()->item_list; - + DBUG_ASSERT(sjm->is_used); /* Set up the table to write to, do as select_union::create_result_table does */ sjm->sjm_table_param.init(); sjm->sjm_table_param.bit_fields_as_long= TRUE; - //List_iterator<Item> it(item_list); SELECT_LEX *subq_select= emb_sj_nest->sj_subq_pred->unit->first_select(); - Item **p_item= subq_select->ref_pointer_array; - Item **p_end= p_item + subq_select->item_list.elements; - //while((right_expr= it++)) - for(;p_item != p_end; p_item++) - sjm->sjm_table_cols.push_back(*p_item); + List_iterator<Item> it(subq_select->item_list); + Item *item; + while((item= it++)) + { + /* + This semi-join replaced the subquery (subq_select) and so on + re-executing it will not be prepared. To use the Items from its + select list we have to prepare (fix_fields) them + */ + if (!item->fixed && item->fix_fields(thd, it.ref())) + DBUG_RETURN(TRUE); + item= *(it.ref()); // it can be changed by fix_fields + DBUG_ASSERT(!item->name_length || item->name_length == strlen(item->name)); + sjm->sjm_table_cols.push_back(item, thd->mem_root); + } sjm->sjm_table_param.field_count= subq_select->item_list.elements; sjm->sjm_table_param.force_not_null_cols= TRUE; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 37d68c730dd..1f15d5bcec0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1143,9 +1143,6 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S eval_select_list_used_tables(); - if (optimize_constant_subqueries()) - DBUG_RETURN(1); - table_count= select_lex->leaf_tables.elements; if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */ @@ -1207,6 +1204,9 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S thd->restore_active_arena(arena, &backup); } + if (optimize_constant_subqueries()) + DBUG_RETURN(1); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1); |