- commits - lists.mariadb.org

[Commits] 65c2610: MDEV-17017 Explain for query using derived table specified with
by IgorBabaev 26 Aug '18

26 Aug '18

revision-id: 65c26107395c7cbd9b5a4e5c97f48d64382a799c (mariadb-10.3.7-145-g65c2610) parent(s): 6c6ca907ee84e3f129bc33be8202c0fee2f29196 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-26 11:24:00 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows This is another attempt to fix this bug. The previous patch did not take into account that a transformation for ALL/ANY subqueries could be applied to the materialized table that wrapped the table value constructor used as a specification of the subselect used an ALL/ANY subquery. In this case the result of the derived table used a sink of the class select_subselect rather than of the class select_unit. Thus the previous fix could cause memory overwrites when running EXPLAIN for queries with table value constructors in ALL/ANY subselects. --- mysql-test/main/table_value_constr.test | 2 -- sql/sql_class.h | 5 ++--- sql/sql_lex.cc | 6 +++--- sql/table.cc | 2 +- 4 files changed, 6 insertions(+), 9 deletions(-) diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 5df40d10..0dd0a7a 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1123,5 +1123,3 @@ PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; - - diff --git a/sql/sql_class.h b/sql/sql_class.h index faae443..026ef4d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4956,7 +4956,8 @@ class select_result :public select_result_sink SELECT_LEX_UNIT *unit; /* Something used only by the parser: */ public: - select_result(THD *thd_arg): select_result_sink(thd_arg) {} + ha_rows est_records; /* estimated number of records in the result */ + select_result(THD *thd_arg): select_result_sink(thd_arg), est_records(0) {} void set_unit(SELECT_LEX_UNIT *unit_arg) { unit= unit_arg; } virtual ~select_result() {}; /** @@ -5528,7 +5529,6 @@ class select_unit :public select_result_interceptor TMP_TABLE_PARAM tmp_table_param; int write_err; /* Error code from the last send_data->ha_write_row call. */ TABLE *table; - ha_rows records; select_unit(THD *thd_arg): select_result_interceptor(thd_arg), @@ -5566,7 +5566,6 @@ class select_unit :public select_result_interceptor curr_sel= UINT_MAX; step= UNION_TYPE; write_err= 0; - records= 0; } void change_select(); }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7d88a6c..1b1031d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4678,18 +4678,18 @@ void SELECT_LEX::increase_derived_records(ha_rows records) return; } - select_unit *result= (select_unit*)unit->result; + select_result *result= unit->result; switch (linkage) { case INTERSECT_TYPE: // result of intersect can't be more then one of components - set_if_smaller(result->records, records); + set_if_smaller(result->est_records, records); case EXCEPT_TYPE: // in worse case none of record will be removed break; default: // usual UNION - result->records+= records; + result->est_records+= records; break; } } diff --git a/sql/table.cc b/sql/table.cc index 627f615..4aef9aa 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8334,7 +8334,7 @@ int TABLE_LIST::fetch_number_of_rows() } if (is_materialized_derived() && !fill_me) { - table->file->stats.records= ((select_unit*)(get_unit()->result))->records; + table->file->stats.records= get_unit()->result->est_records; set_if_bigger(table->file->stats.records, 2); table->used_stat_records= table->file->stats.records; }

1 0

[Commits] d3e0dbb: MDEV-17017 Explain for query using derived table specified with
by IgorBabaev 26 Aug '18

26 Aug '18

revision-id: d3e0dbbd37131cd5da553244980ecb2490bbee63 (mariadb-10.3.7-145-gd3e0dbb) parent(s): 6c6ca907ee84e3f129bc33be8202c0fee2f29196 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-26 10:49:31 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows This is another attempt to fix this bug. The previous patch did not take into account that a transformation for ALL/ANY subqueries could be applied to the materialized table that wrapped the table value constructor used as a specification of the subselect used an ALL/ANY subquery. In this case the result of the derived table used a sink of the class select_subselect rather than of the class select_unit. Thus the previous fix could cause memory overwrites when running EXPLAIN for queries with table value constructors in ALL/ANY subselects. --- sql/sql_class.h | 5 ++--- sql/sql_lex.cc | 6 +++--- sql/table.cc | 2 +- 3 files changed, 6 insertions(+), 7 deletions(-) diff --git a/sql/sql_class.h b/sql/sql_class.h index faae443..32520b6 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4956,7 +4956,8 @@ class select_result :public select_result_sink SELECT_LEX_UNIT *unit; /* Something used only by the parser: */ public: - select_result(THD *thd_arg): select_result_sink(thd_arg) {} + ha_rows est_records; + select_result(THD *thd_arg): select_result_sink(thd_arg), est_records(0) {} void set_unit(SELECT_LEX_UNIT *unit_arg) { unit= unit_arg; } virtual ~select_result() {}; /** @@ -5528,7 +5529,6 @@ class select_unit :public select_result_interceptor TMP_TABLE_PARAM tmp_table_param; int write_err; /* Error code from the last send_data->ha_write_row call. */ TABLE *table; - ha_rows records; select_unit(THD *thd_arg): select_result_interceptor(thd_arg), @@ -5566,7 +5566,6 @@ class select_unit :public select_result_interceptor curr_sel= UINT_MAX; step= UNION_TYPE; write_err= 0; - records= 0; } void change_select(); }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7d88a6c..1b1031d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4678,18 +4678,18 @@ void SELECT_LEX::increase_derived_records(ha_rows records) return; } - select_unit *result= (select_unit*)unit->result; + select_result *result= unit->result; switch (linkage) { case INTERSECT_TYPE: // result of intersect can't be more then one of components - set_if_smaller(result->records, records); + set_if_smaller(result->est_records, records); case EXCEPT_TYPE: // in worse case none of record will be removed break; default: // usual UNION - result->records+= records; + result->est_records+= records; break; } } diff --git a/sql/table.cc b/sql/table.cc index 627f615..4aef9aa 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8334,7 +8334,7 @@ int TABLE_LIST::fetch_number_of_rows() } if (is_materialized_derived() && !fill_me) { - table->file->stats.records= ((select_unit*)(get_unit()->result))->records; + table->file->stats.records= get_unit()->result->est_records; set_if_bigger(table->file->stats.records, 2); table->used_stat_records= table->file->stats.records; }

1 0

[Commits] 3e7f9495082: MDEV-16695: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with
by Varun 26 Aug '18

26 Aug '18

revision-id: 3e7f949508210b5cc308468d5775b5e2c7063ded (mariadb-10.0.36-10-g3e7f9495082) parent(s): 0cafc1316410d118c0b6298daf4d923b3258c502 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-24 13:50:16 +0530 message: MDEV-16695: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with optimizer_use_condition_selectivity>=3 Selectivity analysis should be disabled for Geometrical columns. --- mysql-test/r/gis.result | 16 ++++++++++++++++ mysql-test/t/gis.test | 15 +++++++++++++++ sql/opt_range.cc | 9 ++++++++- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index f77cd4d14bf..40d3117de38 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1659,5 +1659,21 @@ c2 DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; # +# MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +# optimizer_use_condition_selectivity>=3 +# +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +COUNT(*) +0 +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; +# # End 10.0 tests # diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 4192a3284b7..e3b264b8194 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1516,6 +1516,21 @@ SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FR DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +--echo # optimizer_use_condition_selectivity>=3 +--echo # + +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; --echo # --echo # End 10.0 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 734adfbc9d1..303f92f9bb6 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3326,13 +3326,17 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, for (field_ptr= table->field; *field_ptr; field_ptr++) { - if (bitmap_is_set(used_fields, (*field_ptr)->field_index)) + if (bitmap_is_set(used_fields, (*field_ptr)->field_index) && + !((*field_ptr)->type() == MYSQL_TYPE_GEOMETRY)) parts++; } KEY_PART *key_part; uint keys= 0; + if (!parts) + return TRUE; + if (!(key_part= (KEY_PART *) alloc_root(param->mem_root, sizeof(KEY_PART) * parts))) return TRUE; @@ -3344,6 +3348,9 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, if (bitmap_is_set(used_fields, (*field_ptr)->field_index)) { Field *field= *field_ptr; + if (field->type() == MYSQL_TYPE_GEOMETRY) + continue; + uint16 store_length; uint16 max_key_part_length= (uint16) table->file->max_key_part_length(); key_part->key= keys;

2 1

[Commits] 2b76f6f: MDEV-16703: Update AUTO_INCREMENT in the UPDATE statement
by psergey＠askmonty.org 26 Aug '18

26 Aug '18

revision-id: 2b76f6f61dba93fd920456a0dd9efd366a6dc6b1 parent(s): 2a361ebe1b9c52eadfe811a219a89df6a0d6e3ea committer: Sergei Petrunia branch nick: 10.2-r14 timestamp: 2018-08-26 15:10:32 +0300 message: MDEV-16703: Update AUTO_INCREMENT in the UPDATE statement Currently RocksDB engine doesn't update AUTO_INCREMENT in the UPDATE statement. For example, CREATE TABLE t1 (pk INT AUTO_INCREMENT, a INT, PRIMARY KEY(pk)) ENGINE=RocksDB; INSERT INTO t1 (a) VALUES (1); UPDATE t1 SET pk = 3; ==> AUTO_INCREMENT should be updated to 4. Without this fix, it hits the Assertion `dd_val >= last_val' failed in myrocks::ha_rocksdb::load_auto_incr_value_from_index. (cherry picked from commit f7154242b8807fdaa74ed0b89c3de8c35ee35edb) --- storage/rocksdb/ha_rocksdb.cc | 2 +- storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result | 8 ++++++++ storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test | 10 ++++++++++ 3 files changed, 19 insertions(+), 1 deletion(-) diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index c1c3191..adef71e 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -9553,7 +9553,7 @@ int ha_rocksdb::update_pk(const Rdb_key_def &kd, } } - if (table->next_number_field) { + if (table->found_next_number_field) { update_auto_incr_val_from_field(); } diff --git a/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result b/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result index 8cc9b07..0c49622 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result @@ -150,3 +150,11 @@ CREATE TABLE t0(c0 BLOB) ENGINE=ROCKSDB; INSERT INTO t0 VALUES(0); ALTER TABLE t0 AUTO_INCREMENT=0; DROP TABLE t0; +#--------------------------------------------------------------- +# MDEV-16703 Assertion failed in load_auto_incr_value_from_index +#--------------------------------------------------------------- +CREATE TABLE t1 (pk INT AUTO_INCREMENT, a INT, PRIMARY KEY(pk)) ENGINE=RocksDB; +INSERT INTO t1 (a) VALUES (1); +UPDATE t1 SET pk = 3; +ALTER TABLE t1 AUTO_INCREMENT 2; +DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test index 9d7f036..86a0a7f 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test @@ -116,3 +116,13 @@ CREATE TABLE t0(c0 BLOB) ENGINE=ROCKSDB; INSERT INTO t0 VALUES(0); ALTER TABLE t0 AUTO_INCREMENT=0; DROP TABLE t0; + +--echo #--------------------------------------------------------------- +--echo # MDEV-16703 Assertion failed in load_auto_incr_value_from_index +--echo #--------------------------------------------------------------- + +CREATE TABLE t1 (pk INT AUTO_INCREMENT, a INT, PRIMARY KEY(pk)) ENGINE=RocksDB; +INSERT INTO t1 (a) VALUES (1); +UPDATE t1 SET pk = 3; +ALTER TABLE t1 AUTO_INCREMENT 2; +DROP TABLE t1;

1 0

[Commits] e25114a: Added a new parameter for the function eq_ranges_exceeds_limit()
by IgorBabaev 25 Aug '18

25 Aug '18

revision-id: e25114a2270876cb8ad182162add8bc2038333d1 (mariadb-10.3.7-150-ge25114a) parent(s): 65a56d96a799441abea93fbd201c4c808953576d author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-24 21:15:08 -0700 message: Added a new parameter for the function eq_ranges_exceeds_limit() introduced in the patch fo MDEV-16934. --- sql/multi_range_read.cc | 8 +++++--- sql/opt_range.cc | 12 ++---------- sql/opt_range.h | 3 ++- 3 files changed, 9 insertions(+), 14 deletions(-) diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index be2cb8b..d6952e7 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -65,13 +65,15 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, ha_rows rows, total_rows= 0; uint n_ranges=0; THD *thd= table->in_use; + uint limit= thd->variables.eq_range_index_dive_limit; + bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, + seq_init_param, + limit); + /* Default MRR implementation doesn't need buffer */ *bufsz= 0; - bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, - seq_init_param); - seq_it= seq->init(seq_init_param, n_ranges, *flags); while (!seq->next(seq_it, &range)) { diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 2d941ee..0b29fcd 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -14647,20 +14647,12 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, /* Check whether the number for equality ranges exceeds the set threshold */ -bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param) +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param, + uint limit) { KEY_MULTI_RANGE range; range_seq_t seq_it; uint count = 0; - PARAM *param= ((SEL_ARG_RANGE_SEQ*) seq_init_param)->param; -#if 0 - uint limit= param->thd->variables.eq_range_index_dive_limit; -#else - /* FIXME: multi_range_read_info_const() is not being called with - seq_init_param as the 3rd parameter, for example in - ha_partition::multi_range_read_info_const() */ - uint limit= 0; -#endif if (limit == 0) { diff --git a/sql/opt_range.h b/sql/opt_range.h index 1628c79..d541698 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1724,7 +1724,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); -bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param, + uint limit); #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond);

1 0

[Commits] c826b6b: Added a new parameter for the function eq_ranges_exceeds_limit()
by IgorBabaev 25 Aug '18

25 Aug '18

revision-id: c826b6b8da3c9ee89ab457155bae96310213e5a1 (mariadb-10.2.16-104-gc826b6b) parent(s): 8b949d961ce46479eb10b6ddb5d91eb7d8bc0f73 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-24 20:53:00 -0700 message: Added a new parameter for the function eq_ranges_exceeds_limit() introduced in the patch fo MDEV-16934. --- sql/multi_range_read.cc | 8 +++++--- sql/opt_range.cc | 5 ++--- sql/opt_range.h | 3 ++- 3 files changed, 9 insertions(+), 7 deletions(-) diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index 94a96c2..9b6d0e8 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -64,13 +64,15 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, ha_rows rows, total_rows= 0; uint n_ranges=0; THD *thd= table->in_use; + uint limit= thd->variables.eq_range_index_dive_limit; + bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, + seq_init_param, + limit); + /* Default MRR implementation doesn't need buffer */ *bufsz= 0; - bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, - seq_init_param); - seq_it= seq->init(seq_init_param, n_ranges, *flags); while (!seq->next(seq_it, &range)) { diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 47e0701..2c015c1 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -14619,13 +14619,12 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, /* Check whether the number for equality ranges exceeds the set threshold */ -bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param) +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param, + uint limit) { KEY_MULTI_RANGE range; range_seq_t seq_it; uint count = 0; - PARAM *param= ((SEL_ARG_RANGE_SEQ*) seq_init_param)->param; - uint limit= param->thd->variables.eq_range_index_dive_limit; if (limit == 0) { diff --git a/sql/opt_range.h b/sql/opt_range.h index 6698c98..9e0bd3a 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1664,7 +1664,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); -bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param, + uint limit); #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond);

1 0

[Commits] 80a169e2e63: MDEV-16803: Pushdown Item_func_in item that uses vectors in several SELECTs
by Galina 24 Aug '18

24 Aug '18

revision-id: 80a169e2e63ea709e5232ff2461582cbf02cd29d (mariadb-10.2.16-104-g80a169e2e63) parent(s): 8b949d961ce46479eb10b6ddb5d91eb7d8bc0f73 author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-08-24 15:51:05 +0200 message: MDEV-16803: Pushdown Item_func_in item that uses vectors in several SELECTs The bug appears because of the Item_func_in::build_clone() method. The 'array' field for the Item_func_in item that can be pushed into the materialized view/derived table was built in the wrong way. It becomes lame after the pushdown of the condition into the first SELECT that defines that view/derived table. The server crashes in the pushdown into the next SELECT while trying to use already lame 'array' field. To fix it Item_func_in::build_clone() was changed. --- mysql-test/r/derived_cond_pushdown.result | 21 +++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 20 ++++++++++++++++++++ sql/item_cmpfunc.cc | 3 +-- 3 files changed, 42 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index fd930d13547..9ef3488e06d 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10220,3 +10220,24 @@ EXPLAIN } } DROP TABLE t1; +# +# MDEV-16803: pushdown condition with IN predicate in the derived table +# defined with several SELECT statements +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,2),(1,1); +SELECT * FROM +( +SELECT a,b,1 as c +FROM t1 +UNION ALL +SELECT a,b,2 as c +FROM t1 +) AS tab +WHERE ((a,b) IN ((1,2),(3,2))); +a b c +1 2 1 +3 2 1 +1 2 2 +3 2 2 +DROP TABLE t1; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index b8e74a2d5aa..23109662c6d 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2022,3 +2022,23 @@ EVAL $query; EVAL EXPLAIN FORMAT=JSON $query; DROP TABLE t1; + +--echo # +--echo # MDEV-16803: pushdown condition with IN predicate in the derived table +--echo # defined with several SELECT statements +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,2),(1,1); + +SELECT * FROM +( + SELECT a,b,1 as c + FROM t1 + UNION ALL + SELECT a,b,2 as c + FROM t1 +) AS tab +WHERE ((a,b) IN ((1,2),(3,2))); + +DROP TABLE t1; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 577a98df0d2..69ebeb2b95c 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4436,9 +4436,8 @@ Item *Item_func_in::build_clone(THD *thd, MEM_ROOT *mem_root) Item_func_in *clone= (Item_func_in *) Item_func::build_clone(thd, mem_root); if (clone) { - if (array && clone->create_array(thd)) - return NULL; bzero(&clone->cmp_items, sizeof(cmp_items)); + clone->fix_length_and_dec(); } return clone; }

1 0

[Commits] 700232cdd5f: Renamed the objects created for the system variable 'in_predicate_conversion_threshold'
by Galina 24 Aug '18

24 Aug '18

revision-id: 700232cdd5f7c7d2254644b33b93fa0c15f1e2f7 (mariadb-10.3.7-147-g700232cdd5f) parent(s): 9c5a0380019ba16016708b4eb9031818830f8fb9 author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-08-24 15:41:02 +0200 message: Renamed the objects created for the system variable 'in_predicate_conversion_threshold' --- sql/mysqld.cc | 2 +- sql/sql_class.h | 2 +- sql/sql_priv.h | 2 +- sql/sql_tvc.cc | 2 +- sql/sys_vars.cc | 6 +++--- 5 files changed, 7 insertions(+), 7 deletions(-) diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 1a3ff53be6f..9e36b919c62 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -4751,7 +4751,7 @@ static int init_common_variables() return 1; } - global_system_variables.in_subquery_conversion_threshold= IN_SUBQUERY_CONVERSION_THRESHOLD; + global_system_variables.in_predicate_conversion_threshold= IN_PREDICATE_CONVERSION_THRESHOLD; return 0; } diff --git a/sql/sql_class.h b/sql/sql_class.h index faae443e7a0..52115124cd9 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -725,7 +725,7 @@ typedef struct system_variables uint idle_write_transaction_timeout; uint column_compression_threshold; uint column_compression_zlib_level; - uint in_subquery_conversion_threshold; + uint in_predicate_conversion_threshold; vers_asof_timestamp_t vers_asof_timestamp; ulong vers_alter_history; diff --git a/sql/sql_priv.h b/sql/sql_priv.h index e48b6195bb7..ba7a68e57ec 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -327,7 +327,7 @@ /* Used to check GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */ #define UNDEF_POS (-1) -#define IN_SUBQUERY_CONVERSION_THRESHOLD 1000 +#define IN_PREDICATE_CONVERSION_THRESHOLD 1000 #endif /* !MYSQL_CLIENT */ diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 188ba8c4629..6ec9e7e9238 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -791,7 +791,7 @@ bool Item_func_in::to_be_transformed_into_in_subq(THD *thd) if (args[1]->type() == Item::ROW_ITEM) values_count*= ((Item_row *)(args[1]))->cols(); - if (values_count < thd->variables.in_subquery_conversion_threshold) + if (values_count < thd->variables.in_predicate_conversion_threshold) return false; return true; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 8858259c863..37848f27b90 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -6071,12 +6071,12 @@ static Sys_var_mybool Sys_session_track_state_change( #endif //EMBEDDED_LIBRARY #ifndef DBUG_OFF -static Sys_var_uint Sys_in_subquery_conversion_threshold( +static Sys_var_uint Sys_in_predicate_conversion_threshold( "in_predicate_conversion_threshold", "The minimum number of scalar elements in the value list of " "IN predicate that triggers its conversion to IN subquery", - SESSION_VAR(in_subquery_conversion_threshold), CMD_LINE(OPT_ARG), - VALID_RANGE(0, UINT_MAX), DEFAULT(IN_SUBQUERY_CONVERSION_THRESHOLD), BLOCK_SIZE(1)); + SESSION_VAR(in_predicate_conversion_threshold), CMD_LINE(OPT_ARG), + VALID_RANGE(0, UINT_MAX), DEFAULT(IN_PREDICATE_CONVERSION_THRESHOLD), BLOCK_SIZE(1)); #endif static Sys_var_enum Sys_secure_timestamp(

1 0

[Commits] 6c6ca90: Correction for MDEV-16930.
by IgorBabaev 24 Aug '18

24 Aug '18

revision-id: 6c6ca907ee84e3f129bc33be8202c0fee2f29196 (mariadb-10.3.7-144-g6c6ca90) parent(s): 2c76653849c1729bedc213b233089bf361c82e12 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-24 00:51:47 -0700 message: Correction for MDEV-16930. --- sql/sql_yacc.yy | 4 ++-- sql/sql_yacc_ora.yy | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 1ec7317..7ed4498 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13345,7 +13345,7 @@ values_with_names: if (unlikely(Lex->insert_list->push_back($4, thd->mem_root))) MYSQL_YYABORT; // give some name in case of using in table value constuctor (TVC) - if (!$4->name.str) + if (!$4->name.str || $4->name.str == item_empty_name) $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); } | remember_name expr_or_default remember_end @@ -13353,7 +13353,7 @@ values_with_names: if (unlikely(Lex->insert_list->push_back($2, thd->mem_root))) MYSQL_YYABORT; // give some name in case of using in table value constuctor (TVC) - if (!$2->name.str) + if (!$2->name.str || $2->name.str == item_empty_name) $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); } ; diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index a644618..8f98cfa 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -13500,7 +13500,7 @@ values_with_names: if (unlikely(Lex->insert_list->push_back($4, thd->mem_root))) MYSQL_YYABORT; // give some name in case of using in table value constuctor (TVC) - if (!$4->name.str) + if (!$4->name.str || $4->name.str == item_empty_name) $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); } | remember_name expr_or_default remember_end @@ -13508,7 +13508,7 @@ values_with_names: if (unlikely(Lex->insert_list->push_back($2, thd->mem_root))) MYSQL_YYABORT; // give some name in case of using in table value constuctor (TVC) - if (!$2->name.str) + if (!$2->name.str || $2->name.str == item_empty_name) $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); } ;

1 0

[Commits] 2c76653: Added test cases for MDEV-17017 and MDEV-16930 into compat/oracle
by IgorBabaev 24 Aug '18

24 Aug '18

revision-id: 2c76653849c1729bedc213b233089bf361c82e12 (mariadb-10.3.7-143-g2c76653) parent(s): b4cf8557e3821ff3220a5ec62ff937a1f96793e9 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-23 17:43:54 -0700 message: Added test cases for MDEV-17017 and MDEV-16930 into compat/oracle --- .../compat/oracle/r/table_value_constr.result | 84 ++++++++++++++++++++++ .../suite/compat/oracle/t/table_value_constr.test | 44 ++++++++++++ 2 files changed, 128 insertions(+) diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index 31dcecf..18fce08 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -2099,3 +2099,87 @@ v # with t as (values (),()) select 1 from t; ERROR HY000: Row with no elements is not allowed in table value constructor in this context +# +# MDEV-17017: TVC in derived table +# +create table t1 (a int); +insert into t1 values (9), (3), (2); +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +8 +1 +explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +1 11 +1 11 +7 77 +3 31 +4 42 +explain select * from (values (1,11), (7,77), (3,31), (4,42)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +select * from (values (7), (5), (8), (1) union select * from t1) t; +7 +7 +5 +8 +1 +9 +3 +2 +explain select * from (values (7), (5), (8), (1) union select * from t1) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION t1 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +drop table t1; +# +# MDEV-16930: expression in the first row of TVC specifying derived table +# +SELECT 1 + 1, 2, 'abc'; +1 + 1 2 abc +2 2 abc +SELECT * FROM (SELECT 1 + 1, 2, 'abc') t; +1 + 1 2 abc +2 2 abc +WITH cte AS (SELECT 1 + 1, 2, 'abc') SELECT * FROM cte; +1 + 1 2 abc +2 2 abc +SELECT 1 + 1, 2, 'abc' UNION SELECT 3+4, 3, 'abc'; +1 + 1 2 abc +2 2 abc +7 3 abc +CREATE VIEW v1 AS SELECT 1 + 1, 2, 'abc'; +SELECT * FROM v1; +1 + 1 2 abc +2 2 abc +DROP VIEW v1; +VALUES(1 + 1,2,'abc'); +1 + 1 2 abc +2 2 abc +SELECT * FROM (VALUES(1 + 1,2,'abc')) t; +1 + 1 2 abc +2 2 abc diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test index 66519e9..37d2521 100644 --- a/mysql-test/suite/compat/oracle/t/table_value_constr.test +++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test @@ -1081,3 +1081,47 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-17017: TVC in derived table +--echo # + +create table t1 (a int); +insert into t1 values (9), (3), (2); + +let $q1= +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +eval $q1; +eval explain $q1; + +let $q2= +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +eval $q2; +eval explain $q2; + +let $q3= +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +eval $q3; +eval explain $q3; + +let $q4= +select * from (values (7), (5), (8), (1) union select * from t1) t; +eval $q4; +eval explain $q4; + +drop table t1; + +--echo # +--echo # MDEV-16930: expression in the first row of TVC specifying derived table +--echo # + +SELECT 1 + 1, 2, 'abc'; +SELECT * FROM (SELECT 1 + 1, 2, 'abc') t; +WITH cte AS (SELECT 1 + 1, 2, 'abc') SELECT * FROM cte; +SELECT 1 + 1, 2, 'abc' UNION SELECT 3+4, 3, 'abc'; +CREATE VIEW v1 AS SELECT 1 + 1, 2, 'abc'; +SELECT * FROM v1; +DROP VIEW v1; + +VALUES(1 + 1,2,'abc'); +SELECT * FROM (VALUES(1 + 1,2,'abc')) t;

1 0