- commits - lists.mariadb.org

[Commits] 511eda6d520: MDEV-17073: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4
by Varun 22 Aug '18

22 Aug '18

revision-id: 511eda6d520fb06c59edd24ef15438932c7c088b (mariadb-10.0.36-8-g511eda6d520) parent(s): bcc677bb7264db08b22284998706b44c377ed8ec author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-22 12:06:06 +0530 message: MDEV-17073: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4 No need to read statistics for tables that are not USER tables. We allocate memory for structures to collect statistics only for USER TABLES. --- mysql-test/r/stat_tables.result | 13 +++++++++++++ mysql-test/r/stat_tables_innodb.result | 13 +++++++++++++ mysql-test/t/stat_tables.test | 12 ++++++++++++ sql/sql_statistics.cc | 3 +++ 4 files changed, 41 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index c1457d5e91a..cd78d44462e 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -577,3 +577,16 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4 +# +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables= PREFERABLY; +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL +1 SIMPLE user ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 2ac868e9341..02a07fa8bbb 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -604,5 +604,18 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4 +# +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables= PREFERABLY; +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL +1 SIMPLE user ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index d69b00618ea..a0b2a22b946 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -356,3 +356,15 @@ SELECT * FROM mysql.column_stats; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4 +--echo # + +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables= PREFERABLY; +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 537ede91710..cb75a5c2176 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3129,6 +3129,9 @@ int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables) if (!tl->is_view_or_derived() && !is_temporary_table(tl) && tl->table) { TABLE_SHARE *table_share= tl->table->s; + if (table_share && !(table_share->table_category == TABLE_CATEGORY_USER)) + continue; + if (table_share && table_share->stats_cb.stats_can_be_read && !table_share->stats_cb.stats_is_read)

1 0

[Commits] 0ff33b4: MDEV-16930 Crash when VALUES in derived table contains expressions
by IgorBabaev 21 Aug '18

21 Aug '18

revision-id: 0ff33b4df25a6f687cc1e56eb68c329eb310afdd (mariadb-10.3.7-139-g0ff33b4) parent(s): dbc7c3562d523e194734c206c0f0e1cb78164cf0 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-21 12:01:25 -0700 message: MDEV-16930 Crash when VALUES in derived table contains expressions This patch always provides columns of the temporary table used for materialization of a table value constructor with some names. Before this patch these names were always borrowed from the items of the first row of the table value constructor. When this row contained expressions and expressions were not named then it could cause different kinds of problems. In particular if the TVC is used as the specification of a derived table this could cause a crash. The names given to the expressions used in a TVC are the same as those given to the columns of the result set from the corresponding SELECT. --- mysql-test/main/table_value_constr.result | 35 ++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 21 ++++++++++++++++ sql/sql_yacc.yy | 42 ++++++++++++++++++++++++++++++- sql/sql_yacc_ora.yy | 42 ++++++++++++++++++++++++++++++- 4 files changed, 138 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index b0b0fa8..1d485af 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2154,3 +2154,38 @@ id select_type table type possible_keys key key_len ref rows Extra 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 +PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +DEALLOCATE PREPARE stmt; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb30f00..5df40d10 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1104,3 +1104,24 @@ 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; +PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + + diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index f915895..1ec7317 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2044,6 +2044,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); preload_list preload_list_or_parts preload_keys preload_keys_parts select_item_list select_item values_list no_braces opt_limit_clause delete_limit_clause fields opt_values values + no_braces_with_names opt_values_with_names values_with_names procedure_list procedure_list2 procedure_item field_def handler opt_generated_always opt_ignore opt_column opt_restrict @@ -13247,7 +13248,7 @@ insert_values: values_list: values_list ',' no_braces - | no_braces + | no_braces_with_names ; ident_eq_list: @@ -13300,11 +13301,31 @@ no_braces: } ; +no_braces_with_names: + '(' + { + if (unlikely(!(Lex->insert_list= new (thd->mem_root) List_item))) + MYSQL_YYABORT; + } + opt_values_with_names ')' + { + LEX *lex=Lex; + if (unlikely(lex->many_values.push_back(lex->insert_list, + thd->mem_root))) + MYSQL_YYABORT; + } + ; + opt_values: /* empty */ {} | values ; +opt_values_with_names: + /* empty */ {} + | values_with_names + ; + values: values ',' expr_or_default { @@ -13318,6 +13339,25 @@ values: } ; +values_with_names: + values_with_names ',' remember_name expr_or_default remember_end + { + 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) + $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); + } + | remember_name expr_or_default remember_end + { + 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) + $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); + } + ; + expr_or_default: expr { $$= $1;} | DEFAULT diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index d95e87c..a644618 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -1450,6 +1450,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); assign_to_keycache_parts preload_list preload_list_or_parts preload_keys preload_keys_parts select_item_list select_item values_list no_braces + no_braces_with_names opt_values_with_names values_with_names opt_limit_clause delete_limit_clause fields opt_values values procedure_list procedure_list2 procedure_item field_def handler opt_generated_always @@ -13402,7 +13403,7 @@ insert_values: values_list: values_list ',' no_braces - | no_braces + | no_braces_with_names ; ident_eq_list: @@ -13455,11 +13456,31 @@ no_braces: } ; +no_braces_with_names: + '(' + { + if (unlikely(!(Lex->insert_list= new (thd->mem_root) List_item))) + MYSQL_YYABORT; + } + opt_values_with_names ')' + { + LEX *lex=Lex; + if (unlikely(lex->many_values.push_back(lex->insert_list, + thd->mem_root))) + MYSQL_YYABORT; + } + ; + opt_values: /* empty */ {} | values ; +opt_values_with_names: + /* empty */ {} + | values_with_names + ; + values: values ',' expr_or_default { @@ -13473,6 +13494,25 @@ values: } ; +values_with_names: + values_with_names ',' remember_name expr_or_default remember_end + { + 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) + $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); + } + | remember_name expr_or_default remember_end + { + 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) + $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); + } + ; + expr_or_default: expr { $$= $1;} | DEFAULT

1 0

[Commits] d1ccc60: MDEV-14005 Remove need for Partition Key to be part of Primary Key.
by holyfoot＠askmonty.org 21 Aug '18

21 Aug '18

revision-id: d1ccc60360c79cd456abbebafc1c80c8043b7dce (mariadb-10.3.6-106-gd1ccc60) parent(s): ead9a34a3e934f607c2ea7a6c68f7f6d9d29b5bd committer: Alexey Botchkov timestamp: 2018-08-21 14:47:45 +0400 message: MDEV-14005 Remove need for Partition Key to be part of Primary Key. The limitation was removed, so now we check all the partition for unique key duplicates in these cases. --- mysql-test/main/partition_unique.result | 51 ++++++++ mysql-test/main/partition_unique.test | 58 ++++++++ sql/ha_partition.cc | 225 ++++++++++++++++++++++++++++++-- sql/ha_partition.h | 9 +- sql/partition_info.h | 10 +- sql/sql_partition.cc | 122 +++++++++-------- sql/sql_partition.h | 1 + sql/sql_update.cc | 3 +- 8 files changed, 407 insertions(+), 72 deletions(-) diff --git a/mysql-test/main/partition_unique.result b/mysql-test/main/partition_unique.result new file mode 100644 index 0000000..17a9550 --- /dev/null +++ b/mysql-test/main/partition_unique.result @@ -0,0 +1,51 @@ +CREATE TABLE t1 ( +id int(10) NOT NULL, +status varchar(1) DEFAULT NULL, +PRIMARY KEY (`id`) +) +PARTITION BY LIST COLUMNS(`status`) +( +PARTITION `a` VALUES IN ('A'), +PARTITION `b` VALUES IN ('B'), +PARTITION `c` VALUES IN ('C'), +PARTITION `d` DEFAULT); +INSERT INTO t1 VALUES (4, 'A'); +INSERT INTO t1 VALUES (6, 'A'); +INSERT INTO t1 VALUES (4, 'C'); +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +INSERT INTO t1 VALUES (5, 'C'); +UPDATE t1 SET id=4 WHERE id=5; +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +UPDATE t1 SET id=4 WHERE id=5 AND status='C'; +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +UPDATE t1 SET id=6 WHERE id=4 AND status='A'; +ERROR 23000: Duplicate entry '6' for key 'PRIMARY' +select * from t1; +id status +4 A +6 A +5 C +connect con1,localhost,root,,test; +connect con2,localhost,root,,test; +connection con1; +SET DEBUG_SYNC= 'berfore_part_unique_check SIGNAL bpu_hit WAIT_FOR bpu_flushed'; +INSERT INTO t1 VALUES(7, 'A'); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR bpu_hit'; +INSERT INTO t1 VALUES(7, 'C'); +connection default; +SET DEBUG_SYNC= 'now SIGNAL bpu_flushed'; +connection con1; +connection con2; +ERROR 23000: Duplicate entry '7' for key 'PRIMARY' +disconnect con1; +disconnect con2; +connection default; +select * from t1; +id status +4 A +6 A +7 A +5 C +DROP TABLE t1; +set debug_sync= "RESET"; diff --git a/mysql-test/main/partition_unique.test b/mysql-test/main/partition_unique.test new file mode 100644 index 0000000..f292359 --- /dev/null +++ b/mysql-test/main/partition_unique.test @@ -0,0 +1,58 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +CREATE TABLE t1 ( + id int(10) NOT NULL, + status varchar(1) DEFAULT NULL, + PRIMARY KEY (`id`) + ) + PARTITION BY LIST COLUMNS(`status`) + ( + PARTITION `a` VALUES IN ('A'), + PARTITION `b` VALUES IN ('B'), + PARTITION `c` VALUES IN ('C'), + PARTITION `d` DEFAULT); + +INSERT INTO t1 VALUES (4, 'A'); +INSERT INTO t1 VALUES (6, 'A'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (4, 'C'); +INSERT INTO t1 VALUES (5, 'C'); +--error ER_DUP_ENTRY +UPDATE t1 SET id=4 WHERE id=5; +--error ER_DUP_ENTRY +UPDATE t1 SET id=4 WHERE id=5 AND status='C'; +--error ER_DUP_ENTRY +UPDATE t1 SET id=6 WHERE id=4 AND status='A'; +select * from t1; + +connect (con1,localhost,root,,test); +connect (con2,localhost,root,,test); +connection con1; +SET DEBUG_SYNC= 'berfore_part_unique_check SIGNAL bpu_hit WAIT_FOR bpu_flushed'; +send INSERT INTO t1 VALUES(7, 'A'); + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR bpu_hit'; +send INSERT INTO t1 VALUES(7, 'C'); + +connection default; +SET DEBUG_SYNC= 'now SIGNAL bpu_flushed'; + +connection con1; +--reap +connection con2; +--error ER_DUP_ENTRY +--reap + +disconnect con1; +disconnect con2; + +connection default; + +select * from t1; + +DROP TABLE t1; + +set debug_sync= "RESET"; + diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 262e791..da07075 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -368,6 +368,7 @@ void ha_partition::init_handler_variables() part_share= NULL; m_new_partitions_share_refs.empty(); m_part_ids_sorted_by_num_of_records= NULL; + m_cu_errkey= (uint) -1; m_partitions_to_open= NULL; m_range_info= NULL; @@ -4198,6 +4199,95 @@ void ha_partition::try_semi_consistent_read(bool yes) } +int ha_partition::check_files_for_key(uchar *key, int n_key, + int part_begin, int part_end, + int part_to_skip, + int *res) +{ + DBUG_ASSERT(inited == NONE || + (inited == RND && !m_scan_value)); + + for (int n=part_begin; n < part_end; n++) + { + handler *f= m_file[n]; + init_stat sav_inited; + + if ((int) n == part_to_skip) + continue; + + if ((sav_inited= f->inited) == RND) + f->ha_rnd_end(); + + *res= f->index_read_idx_map(m_part_info->table->record[0], + n_key, key, HA_WHOLE_KEY, HA_READ_KEY_EXACT); + + f->ha_end_keyread(); + + if (sav_inited == RND) + { + int ires= f->ha_rnd_init(FALSE); + if (ires) + *res= ires; + } + + if (*res == HA_ERR_KEY_NOT_FOUND) + continue; + + if (*res == 0) + *res= HA_ERR_FOUND_DUPP_KEY; + + m_last_part= n; + m_cu_errkey= n_key; + return 1; + } + + *res= 0; + return 0; +} + + +int ha_partition::check_uniques_insert(uchar *buf, + int part_begin, int part_end, + KEY **dup_key, + int *res) +{ + uint n_key; + + for (n_key=0; n_key < m_part_info->n_uniques_to_check; n_key++) + { + uchar *cbuf= m_part_info->unique_key_buf[0]; + KEY *ckey= m_part_info->uniques_to_check[n_key]; + uint n; + + for (n=0; n < ckey->user_defined_key_parts; n++) + { + const KEY_PART_INFO *cpart= ckey->key_part + n; + uint maybe_null= MY_TEST(cpart->null_bit); + Field *f= cpart->field; + my_ptrdiff_t ofs= buf-table->record[0]; + + f->move_field_offset(ofs); + if (maybe_null) + cbuf[0]= f->is_null(); + f->get_key_image(cbuf+maybe_null, cpart->length, Field::itRAW); + cbuf+= cpart->store_length; + f->move_field_offset(-ofs); + } + + if (check_files_for_key(m_part_info->unique_key_buf[0], + table->key_info - ckey, + part_begin, part_end, -1, res)) + { + *dup_key= ckey; + return 1; + } + } + + *res= 0; + return 0; +} + + /**************************************************************************** MODULE change record ****************************************************************************/ @@ -4249,6 +4339,7 @@ int ha_partition::write_row(uchar * buf) THD *thd= ha_thd(); sql_mode_t saved_sql_mode= thd->variables.sql_mode; bool saved_auto_inc_field_not_null= table->auto_increment_field_not_null; + KEY *dup_key; DBUG_ENTER("ha_partition::write_row"); DBUG_PRINT("enter", ("partition this: %p", this)); @@ -4307,7 +4398,41 @@ int ha_partition::write_row(uchar * buf) start_part_bulk_insert(thd, part_id); tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */ + + /* + Check unique keys (if there is any) for duplications in + partitions 0 .. inserted partition, then + do the write_row then check the unique in + partitions inserted partition +1 .. m_tot_parts. + We do so to keep the order of locking always same to + avoid deadlocks. + */ + if (table->part_info->n_uniques_to_check && + check_uniques_insert(buf, 0, part_id, &dup_key, &error)) + { + goto exit; + } + error= m_file[part_id]->ha_write_row(buf); + + DEBUG_SYNC(thd, "berfore_part_unique_check"); + + if (!error && table->part_info->n_uniques_to_check && + check_uniques_insert(buf, part_id+1, m_tot_parts, &dup_key, &error)) + { + /* + Errors here are ignored, as the error already found. + and i don't have a good idea what to do if things go + wrong here. + */ + if (!(m_file[part_id]->ha_index_read_idx_map(table->record[1], + dup_key - table->key_info, table->part_info->unique_key_buf[0], + HA_WHOLE_KEY, HA_READ_KEY_EXACT))) + { + (void) m_file[part_id]->ha_delete_row(buf); + } + } + if (have_auto_increment && !table->s->next_number_keypart) set_auto_increment_if_higher(table->next_number_field); reenable_binlog(thd); @@ -4317,6 +4442,59 @@ int ha_partition::write_row(uchar * buf) table->auto_increment_field_not_null= saved_auto_inc_field_not_null; DBUG_RETURN(error); } + + +int ha_partition::check_uniques_update(const uchar *old_data, + const uchar *new_data, + int new_part_id, int *res) +{ + uint n_key; + + for (n_key=0; n_key < m_part_info->n_uniques_to_check; n_key++) + { + uchar *buf0= m_part_info->unique_key_buf[0]; + uchar *buf1= m_part_info->unique_key_buf[1]; + KEY *ckey= m_part_info->uniques_to_check[n_key]; + uint n; + + for (n=0; n < ckey->user_defined_key_parts; n++) + { + const KEY_PART_INFO *cpart= ckey->key_part + n; + uint maybe_null= MY_TEST(cpart->null_bit); + Field *f= cpart->field; + my_ptrdiff_t dif; + + dif= old_data - table->record[0]; + f->move_field_offset(dif); + if (maybe_null) + buf0[0]= f->is_null(); + f->get_key_image(buf0+maybe_null, cpart->length, Field::itRAW); + buf0+= cpart->store_length; + f->move_field_offset(-dif); + + dif= new_data - table->record[0]; + f->move_field_offset(dif); + if (maybe_null) + buf1[0]= f->is_null(); + f->get_key_image(buf1+maybe_null, cpart->length, Field::itRAW); + buf1+= cpart->store_length; + f->move_field_offset(-dif); + } + + if (memcmp(m_part_info->unique_key_buf[0], m_part_info->unique_key_buf[1], + buf0 - m_part_info->unique_key_buf[0]) == 0) + { + /* Key did not change. */ + continue; + } + + if (check_files_for_key(m_part_info->unique_key_buf[1], + table->key_info - ckey, 0, m_tot_parts, new_part_id, res)) + return 1; + } + + return 0; +} /* @@ -4387,6 +4565,9 @@ int ha_partition::update_row(const uchar *old_data, const uchar *new_data) goto exit; } + if (table->part_info->n_uniques_to_check && + check_uniques_update(old_data, new_data, new_part_id, &error)) + goto exit; m_last_part= new_part_id; start_part_bulk_insert(thd, new_part_id); @@ -5754,11 +5935,14 @@ int ha_partition::index_read_idx_map(uchar *buf, uint index, get_partition_set(table, buf, index, &m_start_key, &m_part_spec); /* - We have either found exactly 1 partition + If there is no 'unbound' unique keys where not all keyparts + are partition definition fields, + we have either found exactly 1 partition (in which case start_part == end_part) - or no matching partitions (start_part > end_part) + or no matching partitions (start_part > end_part), */ - DBUG_ASSERT(m_part_spec.start_part >= m_part_spec.end_part); + DBUG_ASSERT(m_part_spec.start_part >= m_part_spec.end_part || + m_part_info->n_uniques_to_check); /* The start part is must be marked as used. */ DBUG_ASSERT(m_part_spec.start_part > m_part_spec.end_part || bitmap_is_set(&(m_part_info->read_partitions), @@ -8315,15 +8499,20 @@ int ha_partition::info(uint flag) { handler *file= m_file[m_last_part]; DBUG_PRINT("info", ("info: HA_STATUS_ERRKEY")); - /* - This flag is used to get index number of the unique index that - reported duplicate key - We will report the errkey on the last handler used and ignore the rest - Note: all engines does not support HA_STATUS_ERRKEY, so set errkey. - */ - file->errkey= errkey; - file->info(HA_STATUS_ERRKEY | no_lock_flag); - errkey= file->errkey; + if ((int) m_cu_errkey >= 0) + errkey= m_cu_errkey; + else + { + /* + This flag is used to get index number of the unique index that + reported duplicate key + We will report the errkey on the last handler used and ignore the rest + Note: all engines does not support HA_STATUS_ERRKEY, so set errkey. + */ + file->errkey= errkey; + file->info(HA_STATUS_ERRKEY | no_lock_flag); + errkey= file->errkey; + } } if (flag & HA_STATUS_TIME) { @@ -9711,6 +9900,18 @@ void ha_partition::print_error(int error, myf errflag) m_part_info->print_no_partition_found(table, errflag); DBUG_VOID_RETURN; } + else if (error == HA_ERR_FOUND_DUPP_KEY) + { + if ((int) m_cu_errkey >=0) + { + print_keydup_error(table, + m_cu_errkey == MAX_KEY ? NULL : + &table->key_info[m_cu_errkey], errflag); + m_cu_errkey= -1; + DBUG_VOID_RETURN; + } + /* fall through */ + } else if (error == HA_ERR_ROW_IN_WRONG_PARTITION) { /* Should only happen on DELETE or UPDATE! */ diff --git a/sql/ha_partition.h b/sql/ha_partition.h index 8a25101..54267e3 100644 --- a/sql/ha_partition.h +++ b/sql/ha_partition.h @@ -385,6 +385,7 @@ class ha_partition :public handler /** partitions that returned HA_ERR_KEY_NOT_FOUND. */ MY_BITMAP m_key_not_found_partitions; bool m_key_not_found; + uint m_cu_errkey; /* Last dup key */ List<String> *m_partitions_to_open; MY_BITMAP m_opened_partitions; /** This is one of the m_file-s that it guaranteed to be opened. */ @@ -523,7 +524,13 @@ class ha_partition :public handler void fix_data_dir(char* path); bool init_partition_bitmaps(); void free_partition_bitmaps(); - + int check_files_for_key(uchar *key, int n_key, + int part_begin, int part_end, + int partition_to_skip, int *res); + int check_uniques_insert(uchar *buf, int part_begin, int part_end, + KEY** dup_key, int *res); + int check_uniques_update(const uchar *old_data, const uchar *new_data, + int new_part_id, int *res); public: /* diff --git a/sql/partition_info.h b/sql/partition_info.h index e00a2c4..eeac0d9 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -283,6 +283,14 @@ class partition_info : public Sql_alloc bool is_auto_partitioned; bool has_null_value; bool column_list; // COLUMNS PARTITIONING, 5.5+ + /* + Unique keys that don't have all the partitioning fields in them + need to be checked when INSERT/UPDATE. + So they are collected here. + */ + KEY **uniques_to_check; + uint n_uniques_to_check; + uchar *unique_key_buf[2]; partition_info() : get_partition_id(NULL), get_part_partition_id(NULL), @@ -314,7 +322,7 @@ class partition_info : public Sql_alloc list_of_part_fields(FALSE), list_of_subpart_fields(FALSE), linear_hash_ind(FALSE), fixed(FALSE), is_auto_partitioned(FALSE), - has_null_value(FALSE), column_list(FALSE) + has_null_value(FALSE), column_list(FALSE), n_uniques_to_check(0) { all_fields_in_PF.clear_all(); all_fields_in_PPF.clear_all(); diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index 3133b94..6cd46dd 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -910,53 +910,10 @@ static bool fix_fields_part_func(THD *thd, Item* func_expr, TABLE *table, /* - Check that the primary key contains all partition fields if defined - - SYNOPSIS - check_primary_key() - table TABLE object for which partition fields are set-up - - RETURN VALUES - TRUE Not all fields in partitioning function was part - of primary key - FALSE Ok, all fields of partitioning function were part - of primary key - - DESCRIPTION - This function verifies that if there is a primary key that it contains - all the fields of the partition function. - This is a temporary limitation that will hopefully be removed after a - while. -*/ - -static bool check_primary_key(TABLE *table) -{ - uint primary_key= table->s->primary_key; - bool all_fields, some_fields; - bool result= FALSE; - DBUG_ENTER("check_primary_key"); - - if (primary_key < MAX_KEY) - { - set_indicator_in_key_fields(table->key_info+primary_key); - check_fields_in_PF(table->part_info->full_part_field_array, - &all_fields, &some_fields); - clear_indicator_in_key_fields(table->key_info+primary_key); - if (unlikely(!all_fields)) - { - my_error(ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF,MYF(0),"PRIMARY KEY"); - result= TRUE; - } - } - DBUG_RETURN(result); -} - - -/* Check that unique keys contains all partition fields SYNOPSIS - check_unique_keys() + find_uniques_to_check() table TABLE object for which partition fields are set-up RETURN VALUES @@ -972,12 +929,12 @@ static bool check_primary_key(TABLE *table) while. */ -static bool check_unique_keys(TABLE *table) +static uint find_uniques_to_check(TABLE *table) { bool all_fields, some_fields; - bool result= FALSE; uint keys= table->s->keys; uint i; + uint keys_found= 0; DBUG_ENTER("check_unique_keys"); for (i= 0; i < keys; i++) @@ -988,15 +945,15 @@ static bool check_unique_keys(TABLE *table) check_fields_in_PF(table->part_info->full_part_field_array, &all_fields, &some_fields); clear_indicator_in_key_fields(table->key_info+i); - if (unlikely(!all_fields)) + if (!all_fields) { - my_error(ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF,MYF(0),"UNIQUE INDEX"); - result= TRUE; - break; + ++keys_found; + if (table->part_info->n_uniques_to_check >= keys_found) + table->part_info->uniques_to_check[keys_found-1]= table->key_info+i; } } } - DBUG_RETURN(result); + DBUG_RETURN(keys_found); } @@ -2043,12 +2000,22 @@ bool fix_partition_func(THD *thd, TABLE *table, bool is_create_table_ind) } if (unlikely(create_full_part_field_array(thd, table, part_info))) goto end; - if (unlikely(check_primary_key(table))) - goto end; - if (unlikely((!(table->s->db_type()->partition_flags && - (table->s->db_type()->partition_flags() & HA_CAN_PARTITION_UNIQUE))) && - check_unique_keys(table))) - goto end; + if ((table->part_info->n_uniques_to_check= find_uniques_to_check(table))) + { + table->part_info->uniques_to_check= + (KEY **) alloc_root(&table->mem_root, + sizeof(KEY *) * table->part_info->n_uniques_to_check); + table->part_info->unique_key_buf[0]= + (uchar *) alloc_root(&table->mem_root, MAX_KEY_LENGTH); + table->part_info->unique_key_buf[1]= + (uchar *) alloc_root(&table->mem_root, MAX_KEY_LENGTH); + if (unlikely(!table->part_info->uniques_to_check) || + !table->part_info->unique_key_buf[0] || + !table->part_info->unique_key_buf[1]) + goto end; + + (void) find_uniques_to_check(table); + } if (unlikely(set_up_partition_bitmaps(thd, part_info))) goto end; if (unlikely(part_info->set_up_charset_field_preps(thd))) @@ -2798,6 +2765,47 @@ bool partition_key_modified(TABLE *table, const MY_BITMAP *fields) } + /* + Check if there are unique keys that not entirely 'inside' the partition + key and if any fields of such keys are modified. + If it's so, it usually means we have to use tamporary storage for records + handling the UPDATE command. + + SYNOPSIS + partition_unique_modified + table TABLE object for which partition fields are set-up + fields Bitmap representing fields to be modified + + RETURN VALUES + TRUE Need special handling of UPDATE + FALSE Normal UPDATE handling is ok +*/ + +bool partition_unique_modified(TABLE *table, const MY_BITMAP *fields) +{ + partition_info *part_info= table->part_info; + DBUG_ENTER("partition_unique_modified"); + + if (!part_info) + DBUG_RETURN(FALSE); + + if (part_info->uniques_to_check == 0) + DBUG_RETURN(FALSE); + + for (uint n_key=0; n_key < part_info->n_uniques_to_check; n_key++) + { + KEY *ckey= part_info->uniques_to_check[n_key]; + for (uint n_part=0; n_part < ckey->user_defined_key_parts; n_part++) + { + if (bitmap_is_set(fields, ckey->key_part[n_part].field->field_index)) + DBUG_RETURN(TRUE); + } + } + + DBUG_RETURN(FALSE); +} + + /* A function to handle correct handling of NULL values in partition functions. diff --git a/sql/sql_partition.h b/sql/sql_partition.h index 170ae8c..0df0269 100644 --- a/sql/sql_partition.h +++ b/sql/sql_partition.h @@ -281,6 +281,7 @@ bool verify_data_with_partition(TABLE *table, TABLE *part_table, bool compare_partition_options(HA_CREATE_INFO *table_create_info, partition_element *part_elem); bool partition_key_modified(TABLE *table, const MY_BITMAP *fields); +bool partition_unique_modified(TABLE *table, const MY_BITMAP *fields); #else #define partition_key_modified(X,Y) 0 #endif diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 6994ffa..b75cc31 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -562,7 +562,8 @@ int mysql_update(THD *thd, query_plan.possible_keys= select? select->possible_keys: key_map(0); if (used_key_is_modified || order || - partition_key_modified(table, table->write_set)) + partition_key_modified(table, table->write_set) || + partition_unique_modified(table, table->write_set)) { if (order && need_sort) query_plan.using_filesort= true;

1 0

[Commits] b4210f36400: Merge branch '10.0' into 10.1
by Oleksandr Byelkin 21 Aug '18

21 Aug '18

revision-id: b4210f364003fbea3ccd778b5f5f5dbfc2bfa2f8 (mariadb-10.1.35-10-gb4210f36400) parent(s): 75dfd4acb995789ca5f86ccbd361fff9d2797e79 bcc677bb7264db08b22284998706b44c377ed8ec author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-21 10:07:26 +0200 message: Merge branch '10.0' into 10.1 mysql-test/r/func_isnull.result | 20 ++++++++++++++++ mysql-test/r/func_time.result | 5 ++++ mysql-test/r/subselect_extra_no_semijoin.result | 19 +++++++++++++++ mysql-test/r/subselect_mat.result | 6 ++--- mysql-test/r/subselect_sj_mat.result | 6 ++--- mysql-test/suite/rpl/r/rpl_row_spatial.result | 14 +++++++++++ mysql-test/suite/rpl/t/rpl_row_spatial.test | 17 ++++++++++++++ mysql-test/t/func_isnull.test | 16 +++++++++++++ mysql-test/t/func_time.test | 4 ++++ mysql-test/t/subselect_extra_no_semijoin.test | 31 ++++++++++++++++++++++++- sql/item.cc | 2 +- sql/item_cmpfunc.cc | 13 +++++++++++ sql/item_cmpfunc.h | 1 + sql/key.cc | 3 ++- sql/mysqld.cc | 22 +++++++++--------- sql/protocol.cc | 8 +++---- sql/sql_list.h | 5 +++- sql/sql_show.cc | 2 +- sql/sql_time.cc | 2 +- 19 files changed, 169 insertions(+), 27 deletions(-) diff --cc mysql-test/r/func_time.result index 2180ec2b83c,6f81ffbd410..74911287a6a --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@@ -2796,6 -2770,9 +2796,11 @@@ SEC_TO_TIME(MAKEDATE(0,RAND(~0)) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '20000101' + SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); + PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')) + 24257 ++Warnings: ++Warning 1292 Truncated incorrect INTEGER value: '-3S\xFA\xDE?\x00\x00\xCA\xB3\xEEE\xA4\xD1\xC1\xA8' # # End of 5.5 tests # diff --cc sql/item_cmpfunc.cc index 6ffd582c133,49bbee9edd2..efd00839e4b --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@@ -5113,13 -4927,19 +5113,26 @@@ Item *and_expressions(THD *thd, Item *a } +bool Item_func_null_predicate::count_sargable_conds(uchar *arg) +{ + ((SELECT_LEX*) arg)->cond_count++; + return 0; +} + + + void Item_func_isnull::print(String *str, enum_query_type query_type) + { + str->append(func_name()); + str->append('('); + if (const_item() && !args[0]->maybe_null && + !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL))) + str->append("/*always not null*/ 1"); + else + args[0]->print(str, query_type); + str->append(')'); + } + + longlong Item_func_isnull::val_int() { DBUG_ASSERT(fixed == 1); diff --cc sql/item_cmpfunc.h index 03f234ad1e4,fdefcc86c64..8d85bc8afca --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@@ -1718,10 -1422,11 +1718,11 @@@ public const_item_cache= args[0]->const_item(); } } + COND *remove_eq_conds(THD *thd, Item::cond_result *cond_value, + bool top_level); + virtual void print(String *str, enum_query_type query_type); table_map not_null_tables() const { return 0; } - optimize_type select_optimize() const { return OPTIMIZE_NULL; } Item *neg_transformer(THD *thd); - CHARSET_INFO *compare_collation() { return args[0]->collation.collation; } }; /* Functions used by HAVING for rewriting IN subquery */ diff --cc sql/sql_show.cc index bbf2cb30b97,cdcd6fe47e3..c029b607815 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@@ -2389,8 -2219,7 +2389,8 @@@ static int show_create_view(THD *thd, T We can't just use table->query, because our SQL_MODE may trigger a different syntax, like when ANSI_QUOTES is defined. */ - table->view->unit.print(buff, enum_query_type(QT_ORDINARY | - table->view->unit.print(buff, QT_VIEW_INTERNAL); ++ table->view->unit.print(buff, enum_query_type(QT_VIEW_INTERNAL | + QT_ITEM_ORIGINAL_FUNC_NULLIF)); if (table->with_check != VIEW_CHECK_NONE) {

1 0

[Commits] 166a0efbf7e: Fixed ASAN failure for the test main.func_misc
by Varun 21 Aug '18

21 Aug '18

revision-id: 166a0efbf7e84d8b2994df1932bf4215399e3f03 (mariadb-10.3.6-112-g166a0efbf7e) parent(s): 5abc79dd7ab2fccb4b05ca38a512ec816d2f8e52 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-21 11:43:23 +0530 message: Fixed ASAN failure for the test main.func_misc Moved the checks for arguments validation of Item_name_const from the constructor to Create_func_name_const::create_2_arg Also reverted the fix bf1c53e9be84437ada32393bb7b4a8ff06dbf369 --- sql/item.cc | 19 ------------------- sql/item.h | 2 -- sql/item_create.cc | 21 ++++++++++++++++++++- sql/sql_yacc.yy | 5 ++--- 4 files changed, 22 insertions(+), 25 deletions(-) diff --git a/sql/item.cc b/sql/item.cc index 77e751d7789..7171a95c21d 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1960,25 +1960,6 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val): Item_fixed_hybrid(thd), value_item(val), name_item(name_arg) { Item::maybe_null= TRUE; - if (!name_item->basic_const_item()) - goto err; - - if (value_item->basic_const_item()) - return; // ok - - if (value_item->type() == FUNC_ITEM) - { - Item_func *value_func= (Item_func *) value_item; - if (value_func->functype() != Item_func::COLLATE_FUNC && - value_func->functype() != Item_func::NEG_FUNC) - goto err; - - if (value_func->key_item()->basic_const_item()) - return; // ok - } - -err: - my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST"); } diff --git a/sql/item.h b/sql/item.h index 6f1f70c3cc7..c013781f30f 100644 --- a/sql/item.h +++ b/sql/item.h @@ -881,7 +881,6 @@ class Item: public Value_source, */ String *val_str() { return val_str(&str_value); } virtual Item_func *get_item_func() { return NULL; } - virtual Item_field *get_item_field() {return NULL;} const MY_LOCALE *locale_from_val_str(); @@ -3262,7 +3261,6 @@ class Item_field :public Item_ident, longlong val_int_endpoint(bool left_endp, bool *incl_endp); bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); bool get_date_result(MYSQL_TIME *ltime,ulonglong fuzzydate); - Item_field* get_item_field() {return this;} bool is_null() { return field->is_null(); } void update_null_value(); void update_table_bitmaps() diff --git a/sql/item_create.cc b/sql/item_create.cc index d9b007d4728..87bf69f3c96 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -6103,7 +6103,26 @@ Create_func_name_const Create_func_name_const::s_singleton; Item* Create_func_name_const::create_2_arg(THD *thd, Item *arg1, Item *arg2) { - return new (thd->mem_root) Item_name_const(thd, arg1, arg2); + if (!arg1->basic_const_item()) + goto err; + + if (arg2->basic_const_item()) + return new (thd->mem_root) Item_name_const(thd, arg1, arg2); + + if (arg2->type() == Item::FUNC_ITEM) + { + Item_func *value_func= (Item_func *) arg2; + if (value_func->functype() != Item_func::COLLATE_FUNC && + value_func->functype() != Item_func::NEG_FUNC) + goto err; + + if (!value_func->key_item()->basic_const_item()) + goto err; + return new (thd->mem_root) Item_name_const(thd, arg1, arg2); + } +err: + my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST"); + return NULL; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index bb47988e3c0..cf894325ba5 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -292,14 +292,13 @@ LEX::set_system_variable(enum enum_var_type var_type, Item *val) { set_var *setvar; - Item_field *item_field; /* No AUTOCOMMIT from a stored function or trigger. */ if (spcont && sysvar == Sys_autocommit_ptr) sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT; - if (val && (item_field= val->get_item_field()) && - item_field->table_name) + if (val && val->type() == Item::FIELD_ITEM && + ((Item_field*)val)->table_name) { my_error(ER_WRONG_TYPE_FOR_VAR, MYF(0), sysvar->name.str); return TRUE;

1 0

[Commits] 066a826: MDEV-16930 Crash when VALUES in derived table contains expressions
by IgorBabaev 21 Aug '18

21 Aug '18

revision-id: 066a826bbebd6b058a070dfeda2b43a3ed9292f8 (mariadb-10.3.7-139-g066a826) parent(s): dbc7c3562d523e194734c206c0f0e1cb78164cf0 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-20 11:02:01 -0700 message: MDEV-16930 Crash when VALUES in derived table contains expressions This patch always provides columns of the temporary table used for materialization of a table value constructor with some names. Before this patch these names were always borrowed from the items of the first row of the table value constructor. When this row contained expressions and expressions were not named then it could cause different kinds of problems. In particular if the TVC is used as the specification of a derived table this could cause a crash. The names given to the expressions used in a TVC are the same as those given to the columns of the result set from the corresponding SELECT. --- mysql-test/main/table_value_constr.result | 35 ++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 21 ++++++++++++++++ sql/sql_yacc.yy | 42 ++++++++++++++++++++++++++++++- 3 files changed, 97 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index b0b0fa8..1d485af 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2154,3 +2154,38 @@ id select_type table type possible_keys key key_len ref rows Extra 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 +PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +DEALLOCATE PREPARE stmt; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb30f00..5df40d10 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1104,3 +1104,24 @@ 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; +PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + + diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index f915895..1ec7317 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2044,6 +2044,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); preload_list preload_list_or_parts preload_keys preload_keys_parts select_item_list select_item values_list no_braces opt_limit_clause delete_limit_clause fields opt_values values + no_braces_with_names opt_values_with_names values_with_names procedure_list procedure_list2 procedure_item field_def handler opt_generated_always opt_ignore opt_column opt_restrict @@ -13247,7 +13248,7 @@ insert_values: values_list: values_list ',' no_braces - | no_braces + | no_braces_with_names ; ident_eq_list: @@ -13300,11 +13301,31 @@ no_braces: } ; +no_braces_with_names: + '(' + { + if (unlikely(!(Lex->insert_list= new (thd->mem_root) List_item))) + MYSQL_YYABORT; + } + opt_values_with_names ')' + { + LEX *lex=Lex; + if (unlikely(lex->many_values.push_back(lex->insert_list, + thd->mem_root))) + MYSQL_YYABORT; + } + ; + opt_values: /* empty */ {} | values ; +opt_values_with_names: + /* empty */ {} + | values_with_names + ; + values: values ',' expr_or_default { @@ -13318,6 +13339,25 @@ values: } ; +values_with_names: + values_with_names ',' remember_name expr_or_default remember_end + { + 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) + $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); + } + | remember_name expr_or_default remember_end + { + 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) + $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); + } + ; + expr_or_default: expr { $$= $1;} | DEFAULT

2 1

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

19 Aug '18

revision-id: dbc7c3562d523e194734c206c0f0e1cb78164cf0 (mariadb-10.3.7-138-gdbc7c35) parent(s): 34c7222c088ded8f1192142db935c000b2ba6b8d author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-18 22:57:20 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows If the specification of a derived table contained a table value constructor then the optimizer incorrectly estimated the number of rows in the derived table. This happened because the optimizer did not take into account the number of rows in the constructor. The wrong estimate could lead to choosing inefficient execution plans. --- mysql-test/main/opt_tvc.result | 30 ++++++++-------- mysql-test/main/opt_tvc.test | 2 +- mysql-test/main/range.result | 6 ++-- mysql-test/main/range.test | 4 +++ mysql-test/main/range_mrr_icp.result | 6 ++-- mysql-test/main/table_value_constr.result | 57 +++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 29 ++++++++++++++++ sql/sql_tvc.h | 2 ++ sql/sql_union.cc | 2 ++ 9 files changed, 115 insertions(+), 23 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 0ecae5b..fdbd932 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -486,34 +486,32 @@ a b deallocate prepare stmt; # use inside out access from tvc rows set @@in_predicate_conversion_threshold= default; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 range idx idx 5 NULL 5 100.00 Using index condition +1 SIMPLE t3 range idx idx 5 NULL 4 100.00 Using index condition Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10) +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4) set @@in_predicate_conversion_threshold= 2; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 1 PRIMARY t3 ref idx idx 5 tvc_0.1 3 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` # use vectors in IN predeicate set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); @@ -540,9 +538,9 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) @@ -570,7 +568,7 @@ explain extended select * from t1 where (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -578,7 +576,7 @@ explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -592,7 +590,7 @@ explain extended select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -608,7 +606,7 @@ explain extended select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`)))) @@ -632,7 +630,7 @@ i EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL` diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index d5c9a5c..2d06a0a 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -255,7 +255,7 @@ deallocate prepare stmt; --echo # use inside out access from tvc rows -let $query= select * from t3 where a in (1,4,10); +let $query= select * from t3 where a in (1,4); set @@in_predicate_conversion_threshold= default; eval $query; eval explain extended $query; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index e2996b9..cbf9d5b 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1052,6 +1052,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1062,15 +1063,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 36e0e32..43b5b18 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -870,6 +870,8 @@ insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; + set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; set @a=concat(@a, ')'); @@ -884,6 +886,8 @@ execute stmt1; prepare stmt1 from @a; execute stmt1; +set in_predicate_conversion_threshold= default; + drop table t1, t2; # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 629d183..483957f 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1054,6 +1054,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1064,15 +1065,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a096..b0b0fa8 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2097,3 +2097,60 @@ 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; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59..eb30f00 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,32 @@ 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; diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h index 420311c..128cc88 100644 --- a/sql/sql_tvc.h +++ b/sql/sql_tvc.h @@ -50,6 +50,8 @@ class table_value_constr : public Sql_alloc have_query_plan(QEP_NOT_PRESENT_YET), explain(0), select_options(select_options_arg) { }; + + ha_rows get_records() { return lists_of_values.elements; } bool prepare(THD *thd_arg, SELECT_LEX *sl, select_result *tmp_result, diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 1203532..c8bf9bd 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1321,6 +1321,8 @@ bool st_select_lex_unit::optimize() thd->lex->current_select= lex_select_save; DBUG_RETURN(TRUE); } + if (derived) + sl->increase_derived_records(sl->tvc->get_records()); continue; } thd->lex->current_select= sl;

1 0

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

19 Aug '18

revision-id: f26449872153d4b80ad0d99ee2f79a4eca701fd7 (mariadb-10.3.7-138-gf264498) parent(s): 34c7222c088ded8f1192142db935c000b2ba6b8d author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-18 22:40:58 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows If the specification of a derived table contained a table value constructor then the cardinality of the constructor was not passed to the derived table and as a result the optimizer incorrectly estimated the number of rows in the derived table. This could lead to choosing inefficient execution plans. --- mysql-test/main/opt_tvc.result | 30 ++++++++-------- mysql-test/main/opt_tvc.test | 2 +- mysql-test/main/range.result | 6 ++-- mysql-test/main/range.test | 4 +++ mysql-test/main/range_mrr_icp.result | 6 ++-- mysql-test/main/table_value_constr.result | 57 +++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 29 ++++++++++++++++ sql/sql_tvc.h | 2 ++ sql/sql_union.cc | 2 ++ 9 files changed, 115 insertions(+), 23 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 0ecae5b..fdbd932 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -486,34 +486,32 @@ a b deallocate prepare stmt; # use inside out access from tvc rows set @@in_predicate_conversion_threshold= default; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 range idx idx 5 NULL 5 100.00 Using index condition +1 SIMPLE t3 range idx idx 5 NULL 4 100.00 Using index condition Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10) +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4) set @@in_predicate_conversion_threshold= 2; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 1 PRIMARY t3 ref idx idx 5 tvc_0.1 3 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` # use vectors in IN predeicate set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); @@ -540,9 +538,9 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) @@ -570,7 +568,7 @@ explain extended select * from t1 where (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -578,7 +576,7 @@ explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -592,7 +590,7 @@ explain extended select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -608,7 +606,7 @@ explain extended select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`)))) @@ -632,7 +630,7 @@ i EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL` diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index d5c9a5c..2d06a0a 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -255,7 +255,7 @@ deallocate prepare stmt; --echo # use inside out access from tvc rows -let $query= select * from t3 where a in (1,4,10); +let $query= select * from t3 where a in (1,4); set @@in_predicate_conversion_threshold= default; eval $query; eval explain extended $query; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index e2996b9..cbf9d5b 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1052,6 +1052,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1062,15 +1063,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 36e0e32..43b5b18 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -870,6 +870,8 @@ insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; + set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; set @a=concat(@a, ')'); @@ -884,6 +886,8 @@ execute stmt1; prepare stmt1 from @a; execute stmt1; +set in_predicate_conversion_threshold= default; + drop table t1, t2; # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 629d183..483957f 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1054,6 +1054,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1064,15 +1065,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a096..b0b0fa8 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2097,3 +2097,60 @@ 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; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59..eb30f00 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,32 @@ 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; diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h index 420311c..128cc88 100644 --- a/sql/sql_tvc.h +++ b/sql/sql_tvc.h @@ -50,6 +50,8 @@ class table_value_constr : public Sql_alloc have_query_plan(QEP_NOT_PRESENT_YET), explain(0), select_options(select_options_arg) { }; + + ha_rows get_records() { return lists_of_values.elements; } bool prepare(THD *thd_arg, SELECT_LEX *sl, select_result *tmp_result, diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 1203532..c8bf9bd 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1321,6 +1321,8 @@ bool st_select_lex_unit::optimize() thd->lex->current_select= lex_select_save; DBUG_RETURN(TRUE); } + if (derived) + sl->increase_derived_records(sl->tvc->get_records()); continue; } thd->lex->current_select= sl;

1 0

862a977: MDEV-17011 “condition_pushdown_for_derived” optimization not used when
by IgorBabaev 18 Aug '18

18 Aug '18

revision-id: 862a97749d5a36218ba4d55c26eef30cd7b2e3cb (mariadb-10.2.16-93-g862a977) parent(s): 4eac5df3fcebb1adf52e33d9d88dc05bc1e339ce author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-17 19:29:01 -0700 message: MDEV-17011 “condition_pushdown_for_derived” optimization not used when using INSERT INTO This patch allows condition pushdown into a materialized derived / view when this table is used in INSERT SELECT, multi-table UPDATE and multi-table DELETE. --- mysql-test/r/derived_cond_pushdown.result | 143 ++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 44 +++++++++ sql/sql_select.cc | 3 +- 3 files changed, 188 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 22b81ef..839ae58 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9874,3 +9874,146 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT a aa DROP FUNCTION f1; +# +# MDEV-17011: condition pushdown into materialized derived used +# in INSERT SELECT, multi-table UPDATE and DELETE +# +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES +(3), (7), (1), (4), (1); +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; +EXPLAIN FORMAT=JSON INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a <= 2", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a <= 2" + } + } + } + } + } + } + } +} +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +SELECT * FROM t3; +a b +1 2 +2 2 +EXPLAIN FORMAT=JSON UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.a is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "8", + "used_key_parts": ["c"], + "ref": ["test.t2.a"], + "rows": 2, + "filtered": 100, + "attached_condition": "t2.a = t.c and t.a >= 3", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a >= 3" + } + } + } + } + } + } + } +} +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +SELECT * FROM t2; +a +3 +7 +11 +4 +11 +EXPLAIN FORMAT=JSON DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a = 2 and t2.a = t.c + 9", + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + } + } + } +} +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +SELECT * FROM t2; +a +3 +7 +4 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index d9d767f..f85b301 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1920,3 +1920,47 @@ END;$$ DELIMITER ;$$ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); DROP FUNCTION f1; + +--echo # +--echo # MDEV-17011: condition pushdown into materialized derived used +--echo # in INSERT SELECT, multi-table UPDATE and DELETE +--echo # + +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); + +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES + (3), (7), (1), (4), (1); + +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; + +let $q1= +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; + +eval EXPLAIN FORMAT=JSON $q1; +eval $q1; + +SELECT * FROM t3; + +let $q2= +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 + WHERE t2.a= t.c and t.a>=3; + +eval EXPLAIN FORMAT=JSON $q2; +eval $q2; + +SELECT * FROM t2; + +let $q3= +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t + WHERE t2.a= t.c+9 and t.a=2; + +eval EXPLAIN FORMAT=JSON $q3; +eval $q3; + +SELECT * FROM t2; + +DROP TABLE t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 643ddfe..b94ca49 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1375,8 +1375,7 @@ JOIN::optimize_inner() DBUG_RETURN(1); } - if (thd->lex->sql_command == SQLCOM_SELECT && - optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) + if (optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) { TABLE_LIST *tbl; List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables);

1 0

[Commits] ed0b27a: MDEV-16934 Query with very large IN clause lists runs slowly
by IgorBabaev 17 Aug '18

17 Aug '18

revision-id: ed0b27a1577b61fee7031a6c3e33b8d658c0f45b (mariadb-10.2.16-50-ged0b27a) parent(s): 3c141e319ab29afdfe843553da385fe5d981906e author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-17 14:27:42 -0700 message: MDEV-16934 Query with very large IN clause lists runs slowly This patch introduces support for the system variable eq_range_index_dive_limit that existed in MySQL starting from 5.6. The variable sets a limit for index dives into equality ranges. Index dives are performed by optimizer to estimate the number of rows in range scans. Index dives usually provide good estimate but they are pretty expensive. To estimate the number of rows in equality ranges statistical data on indexes can be employed. Its usage gives not so good estimates but it's cheap. So if the number of equality dives required by an index scan exceeds the set limit no dives for equality ranges are performed by the optimizer for this index. As the new system variable is introduced in a stable version the default value for it is set to a special value meaning there is no limit for the number of index dives performed by the optimizer. The patch partially uses the MySQL code for WL 5957 'Statistics-based Range optimization for many ranges'. --- mysql-test/r/mysqld--help.result | 6 +++ mysql-test/r/range.result | 41 ++++++++++++++++ mysql-test/r/range_mrr_icp.result | 41 ++++++++++++++++ .../sys_vars/r/sysvars_server_embedded.result | 14 ++++++ .../sys_vars/r/sysvars_server_notembedded.result | 14 ++++++ mysql-test/t/range.test | 33 +++++++++++++ sql/multi_range_read.cc | 11 +++++ sql/opt_range.cc | 27 +++++++++++ sql/opt_range.h | 4 +- sql/opt_range_mrr.cc | 54 +++++++++++++++------- sql/sql_class.h | 1 + sql/sql_statistics.h | 2 +- sql/sys_vars.cc | 10 ++++ 13 files changed, 239 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 3b7d5e2..51ece33 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -197,6 +197,11 @@ The following specify which files/extra groups are read (specified before remain cache, etc) --enforce-storage-engine=name Force the use of a storage engine for new tables + --eq-range-index-dive-limit=# + The optimizer will use existing index statistics instead + of doing index dives for equality ranges if the number of + equality ranges for the index is larger than or equal to + this number. If set to 0, index dives are always used. --event-scheduler[=name] Enable the event scheduler. Possible values are ON, OFF, and DISABLED (keep the event scheduler completely @@ -1259,6 +1264,7 @@ encrypt-binlog FALSE encrypt-tmp-disk-tables FALSE encrypt-tmp-files FALSE enforce-storage-engine (No default value) +eq-range-index-dive-limit 0 event-scheduler OFF expensive-subquery-limit 100 expire-logs-days 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 3a71d08..0c6be5e 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -3002,5 +3002,46 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 799a299..93f414f 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -3014,6 +3014,47 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index f7a5cf5..9d7fa10 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -765,6 +765,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 034195e..cada139 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -779,6 +779,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index ab95180..ed68cfb 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -2046,6 +2046,39 @@ set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; --echo # +--echo # MDEV-16934: using system variable eq_range_index_dive_limit +--echo # to reduce the number of index dives +--echo # + +create table t1 (a int, b varchar(31), index idx(a)); + +insert into t1 values + (7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), + (4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; + +analyze table t1; + +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; + +let $q= +select * from t1 where a in (8, 15, 31, 1, 9); + +set eq_range_index_dive_limit=0; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=2; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=default; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index 50918d8..94a96c2 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -17,6 +17,7 @@ #include <my_bit.h> #include "sql_select.h" #include "key.h" +#include "sql_statistics.h" /**************************************************************************** * Default MRR implementation (MRR to non-MRR converter) @@ -67,6 +68,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, /* 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)) { @@ -87,8 +91,15 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, min_endp= range.start_key.length? &range.start_key : NULL; max_endp= range.end_key.length? &range.end_key : NULL; } + int keyparts_used= my_count_bits(range.start_key.keypart_map); if ((range.range_flag & UNIQUE_RANGE) && !(range.range_flag & NULL_RANGE)) rows= 1; /* there can be at most one row */ + else if (use_statistics_for_eq_range && + !(range.range_flag & NULL_RANGE) && + (range.range_flag & EQ_RANGE) && + table->key_info[keyno].actual_rec_per_key(keyparts_used - 1) > 0.5) + rows= + (ha_rows) table->key_info[keyno].actual_rec_per_key(keyparts_used - 1); else { if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, diff --git a/sql/opt_range.cc b/sql/opt_range.cc index b011c01..c9c37a2 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -14616,6 +14616,33 @@ 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) +{ + 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) + { + /* 'Statistics instead of index dives' feature is turned off */ + return false; + } + seq_it= seq->init(seq_init_param, 0, 0); + while (!seq->next(seq_it, &range)) + { + if ((range.range_flag & EQ_RANGE) && !(range.range_flag & NULL_RANGE)) + { + if (++count >= limit) + return true; + } + } + return false; +} + #ifndef DBUG_OFF static void print_sel_tree(PARAM *param, SEL_TREE *tree, key_map *tree_map, diff --git a/sql/opt_range.h b/sql/opt_range.h index c1f7079..6698c98 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -242,7 +242,7 @@ class SEL_ARG :public Sql_alloc Number of children of this element in the RB-tree, plus 1 for this element itself. */ - uint16 elements; + uint32 elements; /* Valid only for elements which are RB-tree roots: Number of times this RB-tree is referred to (it is referred by SEL_ARG::next_key_part or by @@ -1664,6 +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); + #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); #endif diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index ace6208..c7e4496 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -72,6 +72,7 @@ typedef struct st_sel_arg_range_seq range_seq_t sel_arg_range_seq_init(void *init_param, uint n_ranges, uint flags) { SEL_ARG_RANGE_SEQ *seq= (SEL_ARG_RANGE_SEQ*)init_param; + seq->param->range_count=0; seq->at_start= TRUE; seq->stack[0].key_tree= NULL; seq->stack[0].min_key= seq->param->min_key; @@ -272,25 +273,44 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) key_info= NULL; else key_info= &seq->param->table->key_info[seq->real_keyno]; - + /* - Conditions below: - (1) - range analysis is used for estimating condition selectivity - (2) - This is a unique key, and we have conditions for all its - user-defined key parts. - (3) - The table uses extended keys, this key covers all components, - and we have conditions for all key parts. + This is an equality range (keypart_0=X and ... and keypart_n=Z) if + (1) - There are no flags indicating open range (e.g., + "keypart_x > y") or GIS. + (2) - The lower bound and the upper bound of the range has the + same value (min_key == max_key). */ - if (!(cur->min_key_flag & ~NULL_RANGE) && !cur->max_key_flag && - (!key_info || // (1) - ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) - key_info->flags & HA_NOSAME) || // (2) - ((key_info->flags & HA_EXT_NOSAME) && // (3) - (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) - ) && - range->start_key.length == range->end_key.length && - !memcmp(seq->param->min_key,seq->param->max_key,range->start_key.length)) - range->range_flag= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); + const uint is_open_range = + (NO_MIN_RANGE | NO_MAX_RANGE | NEAR_MIN | NEAR_MAX | GEOM_FLAG); + const bool is_eq_range_pred = + !(cur->min_key_flag & is_open_range) && // (1) + !(cur->max_key_flag & is_open_range) && // (1) + range->start_key.length == range->end_key.length && // (2) + !memcmp(seq->param->min_key, seq->param->max_key, // (2) + range->start_key.length); + + if (is_eq_range_pred) + { + range->range_flag = EQ_RANGE; + + /* + Conditions below: + (1) - Range analysis is used for estimating condition selectivity + (2) - This is a unique key, and we have conditions for all its + user-defined key parts. + (3) - The table uses extended keys, this key covers all components, + and we have conditions for all key parts. + */ + if ( + !key_info || // (1) + ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) + key_info->flags & HA_NOSAME) || // (2) + ((key_info->flags & HA_EXT_NOSAME) && // (3) + (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) + ) + range->range_flag |= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); + } if (seq->param->is_ror_scan) { diff --git a/sql/sql_class.h b/sql/sql_class.h index 6663d9e..d2f00b2d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -569,6 +569,7 @@ typedef struct system_variables ha_rows max_join_size; ha_rows expensive_subquery_limit; ulong auto_increment_increment, auto_increment_offset; + uint eq_range_index_dive_limit; ulong lock_wait_timeout; ulong join_cache_level; ulong max_allowed_packet; diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index f465838..4ff85cd 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -21,7 +21,7 @@ enum enum_use_stat_tables_mode { NEVER, COMPLEMENTARY, - PEFERABLY, + PREFERABLY, } Use_stat_tables_mode; typedef diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index e6aec47..650b17a 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2578,6 +2578,16 @@ static Sys_var_ulong Sys_div_precincrement( SESSION_VAR(div_precincrement), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, DECIMAL_MAX_SCALE), DEFAULT(4), BLOCK_SIZE(1)); +static Sys_var_uint Sys_eq_range_index_dive_limit( + "eq_range_index_dive_limit", + "The optimizer will use existing index statistics instead of " + "doing index dives for equality ranges if the number of equality " + "ranges for the index is larger than or equal to this number. " + "If set to 0, index dives are always used.", + SESSION_VAR(eq_range_index_dive_limit), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, UINT_MAX32), DEFAULT(0), + BLOCK_SIZE(1)); + static Sys_var_ulong Sys_range_alloc_block_size( "range_alloc_block_size", "Allocation block size for storing ranges during optimization",

1 0