- commits - lists.mariadb.org

[Commits] 148df3abde1: InnoDB adjustments
by jan 15 Aug '18

15 Aug '18

revision-id: 148df3abde105671b5b33e87269298dec74a2dda (mariadb-10.1.35-12-g148df3abde1) parent(s): 678bc584a123746e7db4355132d3cf7b6c56fc70 author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-15 14:16:21 +0300 message: InnoDB adjustments - Apply patch to xtradb - Fix coding standard - Fix test case result to match 10.1 --- .../galera/r/galera_binlog_stmt_autoinc.result | 14 ----- storage/innobase/handler/ha_innodb.cc | 67 ++++++++++------------ storage/xtradb/handler/ha_innodb.cc | 57 ++++++++++++++---- 3 files changed, 76 insertions(+), 62 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result b/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result index 78b40228eb0..542bd156816 100644 --- a/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result +++ b/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result @@ -1,8 +1,4 @@ -connection node_1; -connection node_2; -connection node_2; SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; -connection node_1; SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; CREATE TABLE t1 ( i int(11) NOT NULL AUTO_INCREMENT, @@ -20,7 +16,6 @@ i c 3 dummy_text 5 dummy_text 7 dummy_text -connection node_2; select * from t1; i c 1 dummy_text @@ -28,7 +23,6 @@ i c 5 dummy_text 7 dummy_text SET GLOBAL wsrep_forced_binlog_format='none'; -connection node_1; SET GLOBAL wsrep_forced_binlog_format='none'; drop table t1; SET SESSION binlog_format='STATEMENT'; @@ -54,14 +48,12 @@ i c 4 dummy_text 7 dummy_text 10 dummy_text -connection node_2; select * from t1; i c 1 dummy_text 4 dummy_text 7 dummy_text 10 dummy_text -connection node_1; SET GLOBAL wsrep_auto_increment_control='ON'; SET SESSION binlog_format='ROW'; show variables like 'binlog_format'; @@ -80,9 +72,7 @@ auto_increment_offset 1 wsrep_auto_increment_control OFF SET GLOBAL wsrep_auto_increment_control='ON'; drop table t1; -connection node_2; SET GLOBAL wsrep_forced_binlog_format='ROW'; -connection node_1; SET GLOBAL wsrep_forced_binlog_format='ROW'; CREATE TABLE t1 ( i int(11) NOT NULL AUTO_INCREMENT, @@ -100,7 +90,6 @@ i c 3 dummy_text 5 dummy_text 7 dummy_text -connection node_2; select * from t1; i c 1 dummy_text @@ -108,7 +97,6 @@ i c 5 dummy_text 7 dummy_text SET GLOBAL wsrep_forced_binlog_format='none'; -connection node_1; SET GLOBAL wsrep_forced_binlog_format='none'; drop table t1; SET SESSION binlog_format='ROW'; @@ -134,14 +122,12 @@ i c 4 dummy_text 7 dummy_text 10 dummy_text -connection node_2; select * from t1; i c 1 dummy_text 4 dummy_text 7 dummy_text 10 dummy_text -connection node_1; SET GLOBAL wsrep_auto_increment_control='ON'; show variables like 'binlog_format'; Variable_name Value diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index bee3c2ee9c0..3d569a2d1d7 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -8401,26 +8401,25 @@ ha_innobase::write_row( dberr_t err; #ifdef WITH_WSREP - /* Applier threads which are processing - ROW events and don't go through server - level autoinc processing, therefore - m_prebuilt autoinc values don't get - properly assigned. Fetch values from - server side. */ - if (wsrep_on(current_thd) && - wsrep_thd_exec_mode(current_thd) == REPL_RECV) - { - wsrep_thd_auto_increment_variables(current_thd, &offset, &increment); - } - else - { + /* Applier threads which are + processing ROW events and don't go + through server level autoinc + processing, therefore m_prebuilt + autoinc values don't get + properly assigned. Fetch values from + server side. */ + if (wsrep_on(current_thd) && + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { + wsrep_thd_auto_increment_variables(current_thd, &offset, &increment); + } else { #endif /* WITH_WSREP */ - ut_a(prebuilt->autoinc_increment > 0); - offset = prebuilt->autoinc_offset; - increment = prebuilt->autoinc_increment; + ut_a(prebuilt->autoinc_increment > 0); + offset = prebuilt->autoinc_offset; + increment = prebuilt->autoinc_increment; #ifdef WITH_WSREP - } + } #endif /* WITH_WSREP */ + auto_inc = innobase_next_autoinc( auto_inc, 1, increment, offset, @@ -8943,25 +8942,22 @@ ha_innobase::update_row( ulonglong increment; #ifdef WITH_WSREP - /* Applier threads which are processing - ROW events and don't go through server - level autoinc processing, therefore - m_prebuilt autoinc values don't get - properly assigned. Fetch values from - server side. */ - if (wsrep_on(current_thd) && - wsrep_thd_exec_mode(current_thd) == REPL_RECV) - { - wsrep_thd_auto_increment_variables( - current_thd, &offset, &increment); - } - else - { + /* Applier threads which are processing + ROW events and don't go through server + level autoinc processing, therefore + m_prebuilt autoinc values don't get + properly assigned. Fetch values from + server side. */ + if (wsrep_on(current_thd) && + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { + wsrep_thd_auto_increment_variables( + current_thd, &offset, &increment); + } else { #endif /* WITH_WSREP */ - offset = prebuilt->autoinc_offset; - increment = prebuilt->autoinc_increment; + offset = prebuilt->autoinc_offset; + increment = prebuilt->autoinc_increment; #ifdef WITH_WSREP - } + } #endif /* WITH_WSREP */ auto_inc = innobase_next_autoinc( @@ -16074,8 +16070,7 @@ ha_innobase::get_auto_increment( thd_get_thread_id(ha_thd()), current, autoinc); - if (!wsrep_on(ha_thd())) - { + if (!wsrep_on(ha_thd())) { current = autoinc - prebuilt->autoinc_increment; current = innobase_next_autoinc( diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index 3cd7cb6977b..857fc7e66f1 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -8988,8 +8988,25 @@ ha_innobase::write_row( ulonglong increment; dberr_t err; - offset = prebuilt->autoinc_offset; - increment = prebuilt->autoinc_increment; +#ifdef WITH_WSREP + /* Applier threads which are + processing ROW events and don't go + through server level autoinc + processing, therefore m_prebuilt + autoinc values don't get + properly assigned. Fetch values from + server side. */ + if (wsrep_on(current_thd) && + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { + wsrep_thd_auto_increment_variables(current_thd, &offset, &increment); + } else { +#endif /* WITH_WSREP */ + ut_a(prebuilt->autoinc_increment > 0); + offset = prebuilt->autoinc_offset; + increment = prebuilt->autoinc_increment; +#ifdef WITH_WSREP + } +#endif /* WITH_WSREP */ auto_inc = innobase_next_autoinc( auto_inc, @@ -9502,16 +9519,32 @@ ha_innobase::update_row( /* We need the upper limit of the col type to check for whether we update the table autoinc counter or not. */ - col_max_value = innobase_get_int_col_max_value( - table->next_number_field); + col_max_value = + table->next_number_field->get_max_int_value(); if (auto_inc <= col_max_value && auto_inc != 0) { ulonglong offset; ulonglong increment; - offset = prebuilt->autoinc_offset; - increment = prebuilt->autoinc_increment; +#ifdef WITH_WSREP + /* Applier threads which are processing + ROW events and don't go through server + level autoinc processing, therefore + m_prebuilt autoinc values don't get + properly assigned. Fetch values from + server side. */ + if (wsrep_on(current_thd) && + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { + wsrep_thd_auto_increment_variables( + current_thd, &offset, &increment); + } else { +#endif /* WITH_WSREP */ + offset = prebuilt->autoinc_offset; + increment = prebuilt->autoinc_increment; +#ifdef WITH_WSREP + } +#endif /* WITH_WSREP */ auto_inc = innobase_next_autoinc( auto_inc, 1, increment, offset, col_max_value); @@ -16742,13 +16775,13 @@ ha_innobase::get_auto_increment( increment, thd_get_thread_id(ha_thd()), current, autoinc); - if (!wsrep_on(ha_thd())) - { - current = autoinc - prebuilt->autoinc_increment; - } - current = innobase_next_autoinc( - current, 1, increment, offset, col_max_value); + if (!wsrep_on(ha_thd())) { + current = autoinc - prebuilt->autoinc_increment; + + current = innobase_next_autoinc( + current, 1, increment, offset, col_max_value); + } dict_table_autoinc_initialize(prebuilt->table, current);

1 0

[Commits] ef25cbea8ec: MDEV-16921: Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity
by Varun 14 Aug '18

14 Aug '18

revision-id: ef25cbea8ec8cbd23f281890ad60136707db9608 (mariadb-10.1.34-44-gef25cbea8ec) parent(s): b9f0112248ee085199b2918f2c9d74ec7099a21e author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-14 19:02:57 +0530 message: MDEV-16921: Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 In this issue we try to read values from the statistic table for a column even when we have the table mysql.column_stats doen not have any such statistics for this column. This leads to differnt query plans. So the solution here would be not to read values from stats table if we have no such statistics for a column. --- mysql-test/r/derived.result | 22 ++++++++++++++++++++++ mysql-test/t/derived.test | 18 ++++++++++++++++++ sql/sql_statistics.cc | 2 +- sql/sql_statistics.h | 6 ++++++ 4 files changed, 47 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 6f786e34a9a..2ae5fd24fdd 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1154,5 +1154,27 @@ a 5 DROP TABLE t1; # +# MDEV-16921: Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 +# +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (1), (1); +CREATE TABLE t2 (b int) ; +INSERT INTO t2 VALUES (9), (NULL), (7); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +analyze SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 2.00 100.00 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 3.00 100.00 33.33 Using where; Using join buffer (flat, BNL join) +SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +a b +1 NULL +1 NULL +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; +# # End of 10.1 tests # diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 778d141b80f..68209def125 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -989,6 +989,24 @@ SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1; SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1; DROP TABLE t1; +--echo # +--echo # MDEV-16921: Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 +--echo # + +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (1), (1); +CREATE TABLE t2 (b int) ; +INSERT INTO t2 VALUES (9), (NULL), (7); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +analyze SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index a1c21421c59..8a59415d051 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3800,7 +3800,7 @@ double get_column_range_cardinality(Field *field, Column_statistics *col_stats= table->field[field->field_index]->read_stats; double tab_records= table->stat_records(); - if (!col_stats) + if (!col_stats || col_stats->is_all_nulls()) return tab_records; /* Use statistics for a table only when we have actually read diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 0611c021e88..d9f65664871 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -398,6 +398,12 @@ class Column_statistics return !is_null(COLUMN_STAT_MIN_VALUE) && !is_null(COLUMN_STAT_MIN_VALUE); } + bool is_all_nulls() + { + return column_stat_nulls == + ((1 << (COLUMN_STAT_HISTOGRAM-COLUMN_STAT_COLUMN_NAME))-1) << + (COLUMN_STAT_COLUMN_NAME+1); + } };

1 0

[Commits] 562dd53: MDEV-16575: rocksdb.bulk_load_errors fails in buildbot with wrong result
by psergey＠askmonty.org 13 Aug '18

13 Aug '18

revision-id: 562dd53c290e0ef361976c679bc569a64d098625 parent(s): ba10ffe0f4c94c0fd2b1e42615a0c8d1a7f88ab9 committer: Sergei Petrunia branch nick: 10.2-r11 timestamp: 2018-08-13 18:56:52 +0300 message: MDEV-16575: rocksdb.bulk_load_errors fails in buildbot with wrong result Fix a race condition in the test. --- storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test | 7 +++++++ 1 file changed, 7 insertions(+) diff --git a/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test b/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test index 1e349d0..3f08526 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test @@ -60,6 +60,13 @@ SELECT * FROM t1; --source include/wait_until_count_sessions.inc +# Note: in MariaDB, session count will be decremented *before* +# myrocks::rocksdb_close_connection is called. This causes a race condition: +# we may grep the error log before bulk load is finalized. +# To prevent that, do a soft restart of the server (I wasnt able to find +# any other reliable way) +--source include/restart_mysqld_with_option.inc + --let SEARCH_FILE=$LOG2 --let SEARCH_PATTERN=RocksDB: Error [0-9]+ finalizing last SST file while disconnecting --source include/search_pattern_in_file.inc

1 0

[Commits] 2b4479b: MDEV-16930 Crash when VALUES in derived table contains expressions
by IgorBabaev 13 Aug '18

13 Aug '18

revision-id: 2b4479bca822827c6c8cc85f45c11b36ae9aff4a (mariadb-10.3.7-109-g2b4479b) parent(s): d453374fc480112266996a1026b97654cc174c09 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-12 23:11:14 -0700 message: MDEV-16930 Crash when VALUES in derived table contains expressions This patch provides columns of the temporary table used for materialization of a table value constructor with comprehensive 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. --- mysql-test/main/table_value_constr.result | 17 ++++++++++++++++ mysql-test/main/table_value_constr.test | 11 ++++++++++ sql/sql_tvc.cc | 34 ++++++++++++++++++++++++++++--- 3 files changed, 59 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a096..6eacc57 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2097,3 +2097,20 @@ 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-16930: expression in the first row of TVC specifying derived table +# +VALUES(1+1,2); +exp_1 2 +2 2 +SELECT * FROM (VALUES(1+1,2)) t; +exp_1 2 +2 2 +PREPARE stmt FROM "SELECT * FROM (VALUES(1+1,2)) t"; +EXECUTE stmt; +exp_1 2 +2 2 +EXECUTE stmt; +exp_1 2 +2 2 +DEALLOCATE PREPARE stmt; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59..12bae99 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,14 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-16930: expression in the first row of TVC specifying derived table +--echo # + +VALUES(1+1,2); +SELECT * FROM (VALUES(1+1,2)) t; +PREPARE stmt FROM "SELECT * FROM (VALUES(1+1,2)) t"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 188ba8c..a184487 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -237,10 +237,27 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl, get_type_attributes_for_tvc(thd, li, holders, lists_of_values.elements, cnt)) DBUG_RETURN(true); - + List_iterator_fast<Item> it(*first_elem); Item *item; - + + /* + Temporarily set comprehensive names for those items in the first_elem list + that have no name. Each item from the first_elem list must have a name as + this name will be used as the corresponding column name of the temporary table + that is created for the table value constructor. + */ + size_t name_len; + char buff[NAME_LEN]; + for (uint column_no= 1; (item= it++); column_no++) + { + if ((item->orig_name= item->name.str)) + continue; + name_len= my_snprintf(buff, NAME_LEN, "exp_%u", column_no); + item->set_name(thd, buff, name_len, system_charset_info); + } + + it.rewind(); sl->item_list.empty(); for (uint pos= 0; (item= it++); pos++) { @@ -254,7 +271,18 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl, new_holder->fix_fields(thd, 0); sl->item_list.push_back(new_holder); } - + + /* + Restore the original names of the items in the first_elem list. + This is needed for execution of prepared statements. + */ + it.rewind(); + while((item= it++)) + { + if (!item->orig_name) + item->name= null_clex_str; + } + if (unlikely(thd->is_fatal_error)) DBUG_RETURN(true); // out of memory

1 0

[Commits] ba10ffe: MDEV-16203: autoinc_debug of rocksdb test suite fails
by psergey＠askmonty.org 12 Aug '18

12 Aug '18

revision-id: ba10ffe0f4c94c0fd2b1e42615a0c8d1a7f88ab9 parent(s): 9dd3e5ea3c7392562b75a40c7fb90b6750308b3a committer: Sergei Petrunia branch nick: 10.2-r11 timestamp: 2018-08-12 22:10:32 +0300 message: MDEV-16203: autoinc_debug of rocksdb test suite fails The test causes simulated server crashes with DBUG_SUICIDE();. It also relies on transactions that were committed right before the crash to be visible after the crash (that is, it requires durability). Run the test with transaction durability enabled: set rocksdb-flush-log-at-trx-commit=1. --- storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt index 83ed852..0691718 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt +++ b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug-master.opt @@ -1 +1 @@ ---binlog-format=row +--binlog-format=row --rocksdb-flush-log-at-trx-commit=1

1 0

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

11 Aug '18

revision-id: 226138774a1befa622e5e1dcaf9749b5cda43db4 (mariadb-10.3.7-109-g2261387) parent(s): d453374fc480112266996a1026b97654cc174c09 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-11 16:21:08 -0700 message: MDEV-16930 Crash when VALUES in derived table contains expressions This patch provides columns of the temporary table used for materialization of a table value constructors with comprehensive names. Before this patch these names were borrowed from the items of the first row of the table value constructor. It caused different problems when a TVC was used as the specification of a derived table: some columns of the derived table has the same name, some columns did not get any name and this triggered a crash. --- mysql-test/main/opt_tvc.result | 46 +++---- mysql-test/main/sp-bugs.result | 2 +- mysql-test/main/table_value_constr.result | 216 ++++++++++++++++-------------- mysql-test/main/table_value_constr.test | 8 ++ sql/sql_tvc.cc | 33 ++++- 5 files changed, 175 insertions(+), 130 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 0ecae5b..37c76d1 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -51,7 +51,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from t1 where a in ( @@ -64,7 +64,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` # AND-condition with IN-predicates in WHERE-part select * from t1 where a in (1,2) and @@ -98,7 +98,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 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` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`col_1` explain extended select * from t1 where a in ( @@ -119,7 +119,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 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` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`col_1` # subquery with IN-predicate select * from t1 where a in @@ -154,7 +154,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) 4 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` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`col_1` explain extended select * from t1 where a in ( @@ -172,7 +172,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) 4 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` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`col_1` # derived table with IN-predicate select * from ( @@ -211,7 +211,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from ( select * @@ -229,7 +229,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` # non-recursive CTE with IN-predicate with tvc_0 as ( @@ -270,7 +270,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from ( select * @@ -288,7 +288,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` # VIEW with IN-predicate create view v1 as select * @@ -321,7 +321,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from v2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 @@ -329,7 +329,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` drop view v1,v2; # subselect defined by derived table with IN-predicate select * from t1 @@ -386,7 +386,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) 5 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` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from t1 where a in ( @@ -411,7 +411,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) 5 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` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`col_1` # derived table with IN-predicate and group by select * from ( @@ -509,11 +509,11 @@ a b explain extended select * from t3 where a in (1,4,10); 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 +1 PRIMARY t3 ref idx idx 5 tvc_0.col_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),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`col_1` # use vectors in IN predeicate set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); @@ -526,7 +526,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` and `test`.`t1`.`b` = `tvc_0`.`2` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` and `test`.`t1`.`b` = `tvc_0`.`col_2` set @@in_predicate_conversion_threshold= 2; # trasformation works for the one IN predicate and doesn't work for the other set @@in_predicate_conversion_threshold= 5; @@ -545,7 +545,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`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))) +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`.`col_1` and `test`.`t2`.`c` = `tvc_0`.`col_2` and (`tvc_0`.`col_1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) set @@in_predicate_conversion_threshold= 2; # # mdev-14281: conversion of NOT IN predicate into subquery predicate @@ -573,7 +573,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`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`)))) +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`.`col_1`,`tvc_0`.`col_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>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`)))) 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 @@ -581,7 +581,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`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`)))) +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`.`col_1`,`tvc_0`.`col_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>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`)))) select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); a b @@ -595,7 +595,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`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`)))) +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`.`col_1`,`tvc_0`.`col_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>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`)))) select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); a b c @@ -611,7 +611,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`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`)))) +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`.`col_1`,`tvc_0`.`col_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>`.`col_1` and `test`.`t2`.`c` = `<subquery2>`.`col_2`)))) drop table t1, t2, t3; set @@in_predicate_conversion_threshold= default; # @@ -635,7 +635,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 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` +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`.`col_1` SET in_predicate_conversion_threshold= default; DROP TABLE t1; # diff --git a/mysql-test/main/sp-bugs.result b/mysql-test/main/sp-bugs.result index a699cd1..aaaf4f6 100644 --- a/mysql-test/main/sp-bugs.result +++ b/mysql-test/main/sp-bugs.result @@ -322,7 +322,7 @@ DROP PROCEDURE p1; CREATE PROCEDURE p1() VALUES (1); $$ CALL p1; -1 +col_1 1 SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1'; body diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a096..4023c27 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -3,15 +3,15 @@ insert into t1 values (1,2),(4,6),(9,7), (1,1),(2,5),(7,8); # just VALUES values (1,2); -1 2 +col_1 col_2 1 2 values (1,2), (3,4), (5.6,0); -1 2 +col_1 col_2 1.0 2 3.0 4 5.6 0 values ("abc", "def"); -abc def +col_1 col_2 abc def # UNION that uses VALUES structure(s) select 1,2 @@ -22,7 +22,7 @@ values (1,2); values (1,2) union select 1,2; -1 2 +col_1 col_2 1 2 select 1,2 union @@ -70,14 +70,14 @@ values (1,2),(3,6); values (1,2.4),(3,6) union select 2.8,9; -1 2.4 +col_1 col_2 1.0 2.4 3.0 6.0 2.8 9.0 values (1,2),(3,4),(5,6),(7,8) union select 5,6; -1 2 +col_1 col_2 1 2 3 4 5 6 @@ -92,18 +92,18 @@ we q values ("ab", "cdf") union select "ab","cdf"; -ab cdf +col_1 col_2 ab cdf values (1,2) union values (1,2),(5,6); -1 2 +col_1 col_2 1 2 5 6 values (1,2) union values (3,4),(5,6); -1 2 +col_1 col_2 1 2 3 4 5 6 @@ -111,21 +111,21 @@ values (1,2) union values (1,2) union values (4,5); -1 2 +col_1 col_2 1 2 4 5 # UNION ALL that uses VALUES structure values (1,2),(3,4) union all select 5,6; -1 2 +col_1 col_2 1 2 3 4 5 6 values (1,2),(3,4) union all select 1,2; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -146,14 +146,14 @@ values (1,2),(3,4); values (1,2) union all values (1,2),(5,6); -1 2 +col_1 col_2 1 2 1 2 5 6 values (1,2) union all values (3,4),(5,6); -1 2 +col_1 col_2 1 2 3 4 5 6 @@ -162,7 +162,7 @@ union all values (1,2) union all values (4,5); -1 2 +col_1 col_2 1 2 1 2 4 5 @@ -170,14 +170,14 @@ values (1,2) union all values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 values (1,2) union values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 # EXCEPT that uses VALUES structure(s) @@ -193,24 +193,24 @@ values (1,2),(3,4); values (1,2),(3,4) except select 5,6; -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) except select 1,2; -1 2 +col_1 col_2 3 4 values (1,2),(3,4) except values (5,6); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) except values (1,2); -1 2 +col_1 col_2 3 4 # INTERSECT that uses VALUES structure(s) select 1,2 @@ -225,27 +225,27 @@ values (1,2),(3,4); values (1,2),(3,4) intersect select 5,6; -1 2 +col_1 col_2 values (1,2),(3,4) intersect select 1,2; -1 2 +col_1 col_2 1 2 values (1,2),(3,4) intersect values (5,6); -1 2 +col_1 col_2 values (1,2),(3,4) intersect values (1,2); -1 2 +col_1 col_2 1 2 # combination of different structures that uses VALUES structures : UNION + EXCEPT values (1,2),(3,4) except select 1,2 union values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -253,7 +253,7 @@ except values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -261,14 +261,14 @@ except values (1,2) union values (3,4); -1 2 +col_1 col_2 3 4 values (1,2),(3,4) union values (1,2) except values (1,2); -1 2 +col_1 col_2 3 4 # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT values (1,2),(3,4) @@ -276,7 +276,7 @@ except select 1,2 union all values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -284,7 +284,7 @@ except values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -292,7 +292,7 @@ except values (1,2) union all values (3,4); -1 2 +col_1 col_2 3 4 3 4 values (1,2),(3,4) @@ -300,7 +300,7 @@ union all values (1,2) except values (1,2); -1 2 +col_1 col_2 3 4 # combination of different structures that uses VALUES structures : UNION + INTERSECT values (1,2),(3,4) @@ -308,21 +308,21 @@ intersect select 1,2 union values (1,2); -1 2 +col_1 col_2 1 2 values (1,2),(3,4) intersect values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 values (1,2),(3,4) intersect values (1,2) union values (3,4); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -330,7 +330,7 @@ union values (1,2) intersect values (1,2); -1 2 +col_1 col_2 1 2 3 4 # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT @@ -339,7 +339,7 @@ intersect select 1,2 union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 values (1,2),(3,4) @@ -347,7 +347,7 @@ intersect values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 values (1,2),(3,4) @@ -355,7 +355,7 @@ intersect values (1,2) union all values (3,4); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -363,7 +363,7 @@ union all values (1,2) intersect values (1,2); -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -373,7 +373,7 @@ union all select 1,2 union values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -381,7 +381,7 @@ union all values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -389,7 +389,7 @@ union all values (1,2) union values (3,4); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -397,7 +397,7 @@ union values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -406,7 +406,7 @@ union values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 # CTE that uses VALUES structure(s) : non-recursive CTE @@ -415,7 +415,7 @@ with t2 as values (1,2),(3,4) ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 with t2 as @@ -444,7 +444,7 @@ union select 1,2 ) select * from t2; -1 2 +col_1 col_2 1 2 with t2 as ( @@ -453,7 +453,7 @@ union select 1,2 ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 with t2 as @@ -463,7 +463,7 @@ union values (1,2),(3,4) ) select * from t2; -5 6 +col_1 col_2 5 6 1 2 3 4 @@ -474,7 +474,7 @@ union values (1,2),(3,4) ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 with t2 as @@ -495,7 +495,7 @@ union all select 1,2 ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -506,7 +506,7 @@ union all values (1,2),(3,4) ) select * from t2; -1 2 +col_1 col_2 1 2 1 2 3 4 @@ -594,7 +594,7 @@ n f 10 362880 # Derived table that uses VALUES structure(s) : singe VALUES structure select * from (values (1,2),(3,4)) as t2; -1 2 +col_1 col_2 1 2 3 4 # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) @@ -606,19 +606,19 @@ select * from (select 1,2 union values (1,2),(3,4)) as t2; 1 2 3 4 select * from (values (1,2) union select 1,2) as t2; -1 2 +col_1 col_2 1 2 select * from (values (1,2),(3,4) union select 1,2) as t2; -1 2 +col_1 col_2 1 2 3 4 select * from (values (5,6) union values (1,2),(3,4)) as t2; -5 6 +col_1 col_2 5 6 1 2 3 4 select * from (values (1,2) union values (1,2),(3,4)) as t2; -1 2 +col_1 col_2 1 2 3 4 # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) @@ -628,19 +628,19 @@ select * from (select 1,2 union all values (1,2),(3,4)) as t2; 1 2 3 4 select * from (values (1,2),(3,4) union all select 1,2) as t2; -1 2 +col_1 col_2 1 2 3 4 1 2 select * from (values (1,2) union all values (1,2),(3,4)) as t2; -1 2 +col_1 col_2 1 2 1 2 3 4 # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure create view v1 as values (1,2),(3,4); select * from v1; -1 2 +col_1 col_2 1 2 3 4 drop view v1; @@ -667,7 +667,7 @@ values (1,2) union select 1,2; select * from v1; -1 2 +col_1 col_2 1 2 drop view v1; create view v1 as @@ -675,7 +675,7 @@ values (1,2),(3,4) union select 1,2; select * from v1; -1 2 +col_1 col_2 1 2 3 4 drop view v1; @@ -684,7 +684,7 @@ values (5,6) union values (1,2),(3,4); select * from v1; -5 6 +col_1 col_2 5 6 1 2 3 4 @@ -695,7 +695,7 @@ values (1,2) union values (1,2),(3,4); select * from v1; -1 2 +col_1 col_2 1 2 3 4 drop view v1; @@ -714,7 +714,7 @@ values (1,2),(3,4) union all select 1,2; select * from v1; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -724,7 +724,7 @@ values (1,2) union all values (1,2),(3,4); select * from v1; -1 2 +col_1 col_2 1 2 1 2 3 4 @@ -748,7 +748,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -757,7 +757,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); @@ -781,7 +781,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) explain extended select * from t1 where a in (select * from (values (1)) as tvc_0 union select 2); @@ -792,7 +792,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a in (select 2 union values (1)); @@ -816,7 +816,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) explain extended select * from t1 where a in (select 2 union select * from (values (1)) tvc_0); @@ -827,7 +827,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) # IN-subquery with VALUES structure(s) : UNION ALL select * from t1 where a in (values (1) union all select b from t1); @@ -852,7 +852,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) explain extended select * from t1 where a in (select * from (values (1)) as tvc_0 union all select b from t1); @@ -862,7 +862,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) # NOT IN subquery with VALUES structure(s) : simple case select * from t1 where a not in (values (1),(2)); @@ -883,7 +883,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 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`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`col_1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`col_1`)))) explain extended select * from t1 where a not in (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -891,7 +891,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1`)))) # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a not in (values (1) union select 2); @@ -915,7 +915,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#3 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`) union /* select#3 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) explain extended select * from t1 where a not in (select * from (values (1)) as tvc_0 union select 2); @@ -926,7 +926,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#4 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`) union /* select#4 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a not in (select 2 union values (1)); @@ -950,7 +950,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))) explain extended select * from t1 where a not in (select 2 union select * from (values (1)) as tvc_0); @@ -961,7 +961,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))) # ANY-subquery with VALUES structure(s) : simple case select * from t1 where a = any (values (1),(2)); @@ -983,7 +983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -992,7 +992,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); @@ -1016,7 +1016,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) explain extended select * from t1 where a = any (select * from (values (1)) as tvc_0 union select 2); @@ -1027,7 +1027,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a = any (select 2 union values (1)); @@ -1051,7 +1051,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) explain extended select * from t1 where a = any (select 2 union select * from (values (1)) as tvc_0); @@ -1062,7 +1062,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) # ALL-subquery with VALUES structure(s) : simple case select * from t1 where a = all (values (1)); @@ -1081,7 +1081,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 2 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 <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`))))) explain extended select * from t1 where a = all (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -1089,7 +1089,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where 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 <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`))))) # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = all (values (1) union select 1); @@ -1111,7 +1111,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#3 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`) union /* select#3 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) explain extended select * from t1 where a = all (select * from (values (1)) as tvc_0 union select 1); @@ -1122,7 +1122,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#4 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`) union /* select#4 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a = any (select 1 union values (1)); @@ -1144,7 +1144,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) explain extended select * from t1 where a = any (select 1 union select * from (values (1)) as tvc_0); @@ -1155,16 +1155,16 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) # prepare statement that uses VALUES structure(s): single VALUES structure prepare stmt1 from " values (1,2); "; execute stmt1; -1 2 +col_1 col_2 1 2 execute stmt1; -1 2 +col_1 col_2 1 2 deallocate prepare stmt1; # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) @@ -1188,11 +1188,11 @@ prepare stmt1 from " select 1,2; "; execute stmt1; -1 2 +col_1 col_2 1 2 3 4 execute stmt1; -1 2 +col_1 col_2 1 2 3 4 deallocate prepare stmt1; @@ -1218,12 +1218,12 @@ prepare stmt1 from " values (1,2),(3,4); "; execute stmt1; -5 6 +col_1 col_2 5 6 1 2 3 4 execute stmt1; -5 6 +col_1 col_2 5 6 1 2 3 4 @@ -1249,12 +1249,12 @@ prepare stmt1 from " select 1,2; "; execute stmt1; -1 2 +col_1 col_2 1 2 3 4 1 2 execute stmt1; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -1283,12 +1283,12 @@ prepare stmt1 from " values (1,2),(3,4); "; execute stmt1; -1 2 +col_1 col_2 1 2 1 2 3 4 execute stmt1; -1 2 +col_1 col_2 1 2 1 2 3 4 @@ -2097,3 +2097,13 @@ 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-16930: expression in the first row of TVC specifying derived table +# the same constant/expressions in the first row +# +SELECT * FROM (VALUES(1+1,2)) t; +col_1 col_2 +2 2 +SELECT * FROM (VALUES(2,2)) t; +col_1 col_2 +2 2 diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59..73d0195 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,11 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-16930: expression in the first row of TVC specifying derived table +--echo # the same constant/expressions in the first row +--echo # + +SELECT * FROM (VALUES(1+1,2)) t; +SELECT * FROM (VALUES(2,2)) t; diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 188ba8c..5c81228 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -237,10 +237,25 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl, get_type_attributes_for_tvc(thd, li, holders, lists_of_values.elements, cnt)) DBUG_RETURN(true); - + List_iterator_fast<Item> it(*first_elem); Item *item; - + + /* + Temporarily set comprehensive names for the items in the first_elem list + These names will be used as the column names of the temporary table + that is created for the table value constructor. + */ + size_t name_len; + char buff[NAME_LEN]; + for (uint column_no= 1; (item= it++); column_no++) + { + name_len= my_snprintf(buff, NAME_LEN, "col_%u", column_no); + item->orig_name= item->name.str; + item->set_name(thd, buff, name_len, system_charset_info); + } + + it.rewind(); sl->item_list.empty(); for (uint pos= 0; (item= it++); pos++) { @@ -254,7 +269,19 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl, new_holder->fix_fields(thd, 0); sl->item_list.push_back(new_holder); } - + + /* + Restore the original names of the items in the first_elem list. This is + needed for the proper print of the table value constructor. + */ + it.rewind(); + while((item= it++)) + { + item->set_name(thd, item->orig_name, + item->orig_name ? strlen(item->orig_name) : 0, + system_charset_info); + } + if (unlikely(thd->is_fatal_error)) DBUG_RETURN(true); // out of memory

1 0

[Commits] 0d99049dbcd: MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions
by Varun 10 Aug '18

10 Aug '18

revision-id: 0d99049dbcd7cc5e3dd309dd7e3df627863fcb81 (mariadb-10.3.7-115-g0d99049dbcd) parent(s): 340c8a2a32dcbe0bb9bc88bd0a6bda5d5e76ed02 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-11 02:31:28 +0530 message: MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions Aggregate function has: - initialization code - code that is run for every row - code that generates a result For an empty table we don't run the middle part but for custom aggregate we are running the middle part also. Fixed this by only allowing the generation of result. As soon as we encounter the first FETCH GROUP NEXT ROW instruction with empty tables, we should exit and tell the handler that there is nothing to fetch and return the result. --- mysql-test/main/custom_aggregate_functions.result | 44 ++++++++++++++++++++--- mysql-test/main/custom_aggregate_functions.test | 43 ++++++++++++++++++++++ sql/sp_head.cc | 41 ++++++++++++++++----- 3 files changed, 115 insertions(+), 13 deletions(-) diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result index 4060d6665f6..e85145c040c 100644 --- a/mysql-test/main/custom_aggregate_functions.result +++ b/mysql-test/main/custom_aggregate_functions.result @@ -84,8 +84,7 @@ return (select count(*) + f2( i - 1) from t1 where id = i); end if; end| select f2(1)| -f2(1) -3 +ERROR 02000: No data - zero rows fetched, selected, or processed select f2(2)| ERROR HY000: Recursive stored functions and triggers are not allowed select f2(3)| @@ -109,7 +108,7 @@ f1(sal) 6000 select f1(sal) from t1 where 1=0; f1(sal) -NULL +0 drop function f1; create aggregate function f1(x int) returns int begin @@ -464,7 +463,7 @@ f1(sal) set @param= 5; execute test using @param; f1(sal) -NULL +0 deallocate prepare test; drop function f2; prepare test from "select f1(sal) from t1 where id>= ?"; @@ -1153,3 +1152,40 @@ i sum(i) NULL 8 drop function agg_sum; drop table t1; +# +# MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions +# +CREATE AGGREGATE FUNCTION test1(p_value TEXT) +RETURNS BOOL +BEGIN +DECLARE CONTINUE HANDLER +FOR NOT FOUND +BEGIN +RETURN FALSE; +END; +FETCH GROUP NEXT ROW; +RETURN TRUE; +END| +CREATE OR REPLACE TABLE t1 (n TEXT); +SELECT test1(n) FROM t1; +test1(n) +0 +CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT) +RETURNS BOOL +BEGIN +DECLARE CONTINUE HANDLER +FOR NOT FOUND +BEGIN +RETURN FALSE; +END; +FETCH GROUP NEXT ROW; +FETCH GROUP NEXT ROW; +FETCH GROUP NEXT ROW; +RETURN TRUE; +END| +SELECT test2(n) FROM t1; +test2(n) +0 +drop function test1; +drop function test2; +drop table t1; diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test index ab799b48bdb..2c981ac518d 100644 --- a/mysql-test/main/custom_aggregate_functions.test +++ b/mysql-test/main/custom_aggregate_functions.test @@ -69,6 +69,7 @@ begin return (select count(*) + f2( i - 1) from t1 where id = i); end if; end| +--error 1329 select f2(1)| # Since currently recursive functions are disallowed ER_SP_NO_RECURSION # error will be returned, once we will allow them error about @@ -965,3 +966,45 @@ select i, sum(i) from t1 group by i with rollup; # Cleanup drop function agg_sum; drop table t1; + +--echo # +--echo # MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions +--echo # + +delimiter |; +CREATE AGGREGATE FUNCTION test1(p_value TEXT) + RETURNS BOOL +BEGIN + DECLARE CONTINUE HANDLER + FOR NOT FOUND + BEGIN + RETURN FALSE; + END; + FETCH GROUP NEXT ROW; + RETURN TRUE; +END| + +delimiter ;| +CREATE OR REPLACE TABLE t1 (n TEXT); +SELECT test1(n) FROM t1; + +delimiter |; +CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT) + RETURNS BOOL + BEGIN + DECLARE CONTINUE HANDLER + FOR NOT FOUND + BEGIN + RETURN FALSE; + END; + FETCH GROUP NEXT ROW; + FETCH GROUP NEXT ROW; + FETCH GROUP NEXT ROW; + RETURN TRUE; + END| + +delimiter ;| +SELECT test2(n) FROM t1; +drop function test1; +drop function test2; +drop table t1; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index c1c938dd9e7..3828cb5237b 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -4406,6 +4406,37 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp) { DBUG_ENTER("sp_instr_agg_cfetch::execute"); int res= 0; + if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT) + { + if (!thd->spcont->quit_func) + { + my_message(ER_SP_FETCH_NO_DATA, + ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); + thd->spcont->quit_func= TRUE; + thd->spcont->pause_state= FALSE; + } + else + { + /* + required when we don't come across the return statement. + An example would be + create aggregate function f1(x int) returns int + begin + declare mini int default 0; + declare continue handler for not found set mini=-1; + LOOP + FETCH GROUP NEXT ROW; + set mini = mini + x; + END LOOP; + return 0; + end| + So here we would never execute the RETURN statement, so here + we force to quit the function execution. + */ + thd->spcont->pause_state= TRUE; + } + DBUG_RETURN(res); + } if (!thd->spcont->instr_ptr) { *nextp= m_ip+1; @@ -4416,15 +4447,7 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp) else { thd->spcont->pause_state= FALSE; - if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT) - { - my_message(ER_SP_FETCH_NO_DATA, - ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); - res= -1; - thd->spcont->quit_func= TRUE; - } - else - *nextp= m_ip + 1; + *nextp= m_ip + 1; } DBUG_RETURN(res); }

1 0

[Commits] 470b99d4cbc: MDEV-16722: Assertion `type() != NULL_ITEM' failed
by Varun 10 Aug '18

10 Aug '18

revision-id: 470b99d4cbc93daad8fed568bea6b6eed1920d58 (mariadb-10.3.6-84-g470b99d4cbc) parent(s): 522cd3c7aa9b466d5e0a4d010d4acb13c76a014c author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-10 14:48:51 +0530 message: MDEV-16722: Assertion `type() != NULL_ITEM' failed We hit this assert during the create of a temporary table field because the current code does not handle the case when the value of the NAME_CONST function is NULL. Fixed this by allowing creation of temporary table fields even for the case when NAME_CONST returns NULL value. Introduced tmp_table_field_from_field_type_maybe_null() function in Item class so both Item_basic_value and Item_name_const can use it. Introduced a virtual method get_func_item() in the Item class. --- mysql-test/main/win.result | 11 +++++++++++ mysql-test/main/win.test | 9 +++++++++ sql/item.cc | 22 ++++++---------------- sql/item.h | 24 +++++++++++++++++++++--- sql/item_cmpfunc.h | 14 ++++---------- sql/item_func.h | 1 + sql/sql_select.cc | 41 ++++++++++++++++------------------------- 7 files changed, 68 insertions(+), 54 deletions(-) diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result index 7607cebc3a5..fd0fbefdcc5 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -3334,3 +3334,14 @@ d x 00:00:01 00:00:02 00:00:02 NULL DROP TABLE t1; +# +# MDEV-16722: Assertion `type() != NULL_ITEM' failed +# +create table t1 (a int); +insert into t1 values (1),(2),(3); +SELECT row_number() OVER (order by a) FROM t1 order by NAME_CONST('myname',NULL); +row_number() OVER (order by a) +1 +2 +3 +drop table t1; diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index 4b73f70d737..cc16595fcd4 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -2100,3 +2100,12 @@ CREATE TABLE t1 (d time); INSERT INTO t1 VALUES ('00:00:01'),('00:00:02'); SELECT *, LEAD(d) OVER (ORDER BY d) AS x FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-16722: Assertion `type() != NULL_ITEM' failed +--echo # + +create table t1 (a int); +insert into t1 values (1),(2),(3); +SELECT row_number() OVER (order by a) FROM t1 order by NAME_CONST('myname',NULL); +drop table t1; diff --git a/sql/item.cc b/sql/item.cc index 8b962d1706d..d9c66d3cfc5 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -2004,7 +2004,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; - valid_args= true; if (!name_item->basic_const_item()) goto err; @@ -2023,7 +2022,6 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val): } err: - valid_args= false; my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST"); } @@ -2031,24 +2029,16 @@ Item_name_const::Item_name_const(THD *thd, Item *name_arg, Item *val): Item::Type Item_name_const::type() const { /* - As - 1. one can try to create the Item_name_const passing non-constant - arguments, although it's incorrect and - 2. the type() method can be called before the fix_fields() to get - type information for a further type cast, e.g. - if (item->type() == FIELD_ITEM) - ((Item_field *) item)->... - we return NULL_ITEM in the case to avoid wrong casting. - - valid_args guarantees value_item->basic_const_item(); if type is - FUNC_ITEM, then we have a fudged item_func_neg() on our hands - and return the underlying type. + + We are guarenteed that value_item->basic_const_item(), if not + an error is thrown that WRONG ARGUMENTS are supplied to + NAME_CONST function. + If type is FUNC_ITEM, then we have a fudged item_func_neg() + on our hands and return the underlying type. For Item_func_set_collation() e.g. NAME_CONST('name', 'value' COLLATE collation) we return its 'value' argument type. */ - if (!valid_args) - return NULL_ITEM; Item::Type value_type= value_item->type(); if (value_type == FUNC_ITEM) { diff --git a/sql/item.h b/sql/item.h index a96406fa0cd..d6ad088e60a 100644 --- a/sql/item.h +++ b/sql/item.h @@ -820,6 +820,10 @@ class Item: public Value_source, return tmp_table_field_from_field_type(table); } Field *create_tmp_field_int(TABLE *table, uint convert_int_length); + Field *tmp_table_field_from_field_type_maybe_null(TABLE *table, + Tmp_field_src *src, + const Tmp_field_param *param, + bool is_explicit_null); void push_note_converted_to_negative_complement(THD *thd); void push_note_converted_to_positive_complement(THD *thd); @@ -876,6 +880,7 @@ class Item: public Value_source, expressions with subqueries in the ORDER/GROUP clauses. */ String *val_str() { return val_str(&str_value); } + virtual Item_func *get_item_func() { return NULL; } const MY_LOCALE *locale_from_val_str(); @@ -2608,7 +2613,20 @@ class Item_basic_value :public Item, Item_basic_value(THD *thd): Item(thd) {} public: Field *create_tmp_field_ex(TABLE *table, Tmp_field_src *src, - const Tmp_field_param *param); + const Tmp_field_param *param) + { + + /* + create_tmp_field_ex() for this type of Items is called for: + - CREATE TABLE ... SELECT + - In ORDER BY: SELECT max(a) FROM t1 GROUP BY a ORDER BY 'const'; + - In CURSORS: + DECLARE c CURSOR FOR SELECT 'test'; + OPEN c; + */ + return tmp_table_field_from_field_type_maybe_null(table, src, param, + type() == Item::NULL_ITEM); + } bool eq(const Item *item, bool binary_cmp) const; const Type_all_attributes *get_type_all_attributes_from_const() const { return this; } @@ -2949,7 +2967,6 @@ class Item_name_const : public Item_fixed_hybrid { Item *value_item; Item *name_item; - bool valid_args; public: Item_name_const(THD *thd, Item *name_arg, Item *val); @@ -2982,7 +2999,8 @@ class Item_name_const : public Item_fixed_hybrid DECLARE c CURSOR FOR SELECT NAME_CONST('x','y') FROM t1; OPEN c; */ - return create_tmp_field_ex_simple(table, src, param); + return tmp_table_field_from_field_type_maybe_null(table, src, param, + type() == Item::NULL_ITEM); } int save_in_field(Field *field, bool no_conversions) { diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 3336885c036..2d917389e32 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -3313,11 +3313,8 @@ class Item_cond_and :public Item_cond inline bool is_cond_and(Item *item) { - if (item->type() != Item::COND_ITEM) - return FALSE; - - Item_cond *cond_item= (Item_cond*) item; - return (cond_item->functype() == Item_func::COND_AND_FUNC); + Item_func *func_item= item->get_item_func(); + return func_item && func_item->type() == Item::COND_ITEM && func_item->functype() == Item_func::COND_AND_FUNC; } class Item_cond_or :public Item_cond @@ -3418,11 +3415,8 @@ class Item_func_cursor_notfound: public Item_func_cursor_bool_attr inline bool is_cond_or(Item *item) { - if (item->type() != Item::COND_ITEM) - return FALSE; - - Item_cond *cond_item= (Item_cond*) item; - return (cond_item->functype() == Item_func::COND_OR_FUNC); + Item_func *func_item= item->get_item_func(); + return func_item && func_item->type() == Item::COND_ITEM && func_item->functype() == Item_func::COND_OR_FUNC; } Item *and_expressions(Item *a, Item *b, Item **org_item); diff --git a/sql/item_func.h b/sql/item_func.h index f44986a9111..48b2ad2afeb 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -393,6 +393,7 @@ class Item_func :public Item_func_or_sum, bool with_sum_func() const { return m_with_sum_func; } With_sum_func_cache* get_with_sum_func_cache() { return this; } + Item_func *get_item_func() { return this; } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 80ca1d7da62..bfd1c7580fc 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -16616,6 +16616,22 @@ Field *Item::create_tmp_field_int(TABLE *table, uint convert_int_length) *this, table); } +Field *Item::tmp_table_field_from_field_type_maybe_null(TABLE *table, + Tmp_field_src *src, + const Tmp_field_param *param, + bool is_explicit_null) +{ + DBUG_ASSERT(!param->make_copy_field()); + DBUG_ASSERT(!is_result_field()); + Field *result; + if ((result= tmp_table_field_from_field_type(table))) + { + if (result && is_explicit_null) + result->is_created_from_null_item= true; + } + return result; +} + Field *Item_sum::create_tmp_field(bool group, TABLE *table) { @@ -16847,31 +16863,6 @@ Field *Item_func_sp::create_tmp_field_ex(TABLE *table, return result; } - -Field *Item_basic_value::create_tmp_field_ex(TABLE *table, - Tmp_field_src *src, - const Tmp_field_param *param) -{ - /* - create_tmp_field_ex() for this type of Items is called for: - - CREATE TABLE ... SELECT - - In ORDER BY: SELECT max(a) FROM t1 GROUP BY a ORDER BY 'const'; - - In CURSORS: - DECLARE c CURSOR FOR SELECT 'test'; - OPEN c; - */ - DBUG_ASSERT(!param->make_copy_field()); - DBUG_ASSERT(!is_result_field()); - Field *result; - if ((result= tmp_table_field_from_field_type(table))) - { - if (type() == Item::NULL_ITEM) // Item_null or Item_param - result->is_created_from_null_item= true; - } - return result; -} - - /** Create field for temporary table.

1 0

[Commits] 017adbeb394: MDEV-15475: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed on EXPLAIN EXTENDED with constant table and view
by Oleksandr Byelkin 08 Aug '18

08 Aug '18

revision-id: 017adbeb3940ac162dcb8dd99478375a37edec02 (mariadb-5.5.61-3-g017adbeb394) parent(s): 68ebfb31f215247d2fa08c8ed97a320191afc179 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-08 19:44:04 +0200 message: MDEV-15475: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed on EXPLAIN EXTENDED with constant table and view Print constant ISNULL fireld independent. Fix of printing of view FRM and CREATE VIEW output --- mysql-test/r/derived_view.result | 6 +++--- mysql-test/r/func_isnull.result | 20 ++++++++++++++++++++ mysql-test/r/subselect_mat.result | 6 +++--- mysql-test/r/subselect_sj_mat.result | 6 +++--- mysql-test/t/func_isnull.test | 16 ++++++++++++++++ sql/item.cc | 2 +- sql/item_cmpfunc.cc | 13 +++++++++++++ sql/item_cmpfunc.h | 1 + sql/sql_lex.cc | 2 +- sql/sql_show.cc | 2 +- 10 files changed, 62 insertions(+), 12 deletions(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index f7062473a3f..12811ebc6b3 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1101,7 +1101,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1115,7 +1115,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1129,7 +1129,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) DROP VIEW v1; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result index 88c5bfd5468..a97d4a67939 100644 --- a/mysql-test/r/func_isnull.result +++ b/mysql-test/r/func_isnull.result @@ -106,5 +106,25 @@ Note 1003 select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.` DROP VIEW v1; DROP TABLE t1,t2; # +# MDEV-15475: Assertion `!table || (!table->read_set || +# bitmap_is_set(table->read_set, field_index))' +# failed on EXPLAIN EXTENDED with constant table and view +# +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select isnull(/*always not null*/ 1) AS `ISNULL(pk)` from dual +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual +DROP VIEW v1; +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index eca3b760b65..efc348a26ce 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -499,7 +499,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -509,7 +509,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1896,7 +1896,7 @@ 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 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 180c182a51a..fd9435e8a39 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -520,7 +520,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -530,7 +530,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1934,7 +1934,7 @@ 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 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test index 4c59fa3cbe8..7d1a7e83a1a 100644 --- a/mysql-test/t/func_isnull.test +++ b/mysql-test/t/func_isnull.test @@ -83,6 +83,22 @@ SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15475: Assertion `!table || (!table->read_set || +--echo # bitmap_is_set(table->read_set, field_index))' +--echo # failed on EXPLAIN EXTENDED with constant table and view +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +# Cleanup +DROP VIEW v1; +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/sql/item.cc b/sql/item.cc index 33c35f8c3e0..0cf4864326f 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6845,7 +6845,7 @@ Item *Item_field::update_value_transformer(uchar *select_arg) void Item_field::print(String *str, enum_query_type query_type) { if (field && field->table->const_table && - !(query_type & QT_NO_DATA_EXPANSION)) + !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL))) { print_value(str); return; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 6fb650b975b..d4a2c767b15 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4850,6 +4850,19 @@ Item *and_expressions(Item *a, Item *b, Item **org_item) } +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 --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 3c8cc71370d..c4e6a53dd6b 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1389,6 +1389,7 @@ class Item_func_isnull :public Item_bool_func const_item_cache= args[0]->const_item(); } } + 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); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 2bf1216ec34..cfbde25314b 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2414,7 +2414,7 @@ void st_select_lex::print_order(String *str, { if (order->counter_used) { - if (query_type != QT_VIEW_INTERNAL) + if (!(query_type & QT_VIEW_INTERNAL)) { char buffer[20]; size_t length= my_snprintf(buffer, 20, "%d", order->counter); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 06d5a6f570a..db33a9de781 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2085,7 +2085,7 @@ view_store_create_info(THD *thd, TABLE_LIST *table, String *buff) 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, QT_ORDINARY); + table->view->unit.print(buff, QT_VIEW_INTERNAL); if (table->with_check != VIEW_CHECK_NONE) {

1 0

[Commits] d964b91272c: Other fix (opst review)
by Oleksandr Byelkin 08 Aug '18

08 Aug '18

revision-id: d964b91272caaf799eb53dfd075a961376e66e66 (mariadb-5.5.61-4-gd964b91272c) parent(s): d1c90870ed114da62de8b12a71e2fba62724baa9 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-08 19:43:30 +0200 message: Other fix (opst review) --- mysql-test/r/derived_view.result | 6 +++--- mysql-test/r/func_isnull.result | 7 ++++++- mysql-test/r/subselect_mat.result | 6 +++--- mysql-test/r/subselect_sj_mat.result | 6 +++--- mysql-test/t/func_isnull.test | 1 + sql/item.cc | 5 +---- sql/item_cmpfunc.cc | 13 +++++++++++++ sql/item_cmpfunc.h | 1 + sql/sql_lex.cc | 2 +- sql/sql_show.cc | 2 +- 10 files changed, 33 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index f7062473a3f..12811ebc6b3 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1101,7 +1101,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1115,7 +1115,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1129,7 +1129,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) DROP VIEW v1; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result index fd55b7be26c..a97d4a67939 100644 --- a/mysql-test/r/func_isnull.result +++ b/mysql-test/r/func_isnull.result @@ -117,7 +117,12 @@ EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select isnull(`test`.`t1`.`pk`) AS `ISNULL(pk)` from dual +Note 1003 select isnull(/*always not null*/ 1) AS `ISNULL(pk)` from dual +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual DROP VIEW v1; DROP TABLE t1; # diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index eca3b760b65..efc348a26ce 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -499,7 +499,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -509,7 +509,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1896,7 +1896,7 @@ 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 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 180c182a51a..fd9435e8a39 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -520,7 +520,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -530,7 +530,7 @@ 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 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1934,7 +1934,7 @@ 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 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test index 2111b8f16bc..7d1a7e83a1a 100644 --- a/mysql-test/t/func_isnull.test +++ b/mysql-test/t/func_isnull.test @@ -94,6 +94,7 @@ CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; CREATE VIEW v1 AS SELECT * FROM t1; INSERT INTO t1 VALUES (1); EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; # Cleanup DROP VIEW v1; DROP TABLE t1; diff --git a/sql/item.cc b/sql/item.cc index 77be702bac5..0cf4864326f 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6845,10 +6845,7 @@ Item *Item_field::update_value_transformer(uchar *select_arg) void Item_field::print(String *str, enum_query_type query_type) { if (field && field->table->const_table && - !(query_type & QT_NO_DATA_EXPANSION) && - // and we was gping to read field value (it is not optimised out) - field->table->read_set && - bitmap_is_set(field->table->read_set, field->field_index)) + !(query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL))) { print_value(str); return; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 6fb650b975b..d4a2c767b15 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4850,6 +4850,19 @@ Item *and_expressions(Item *a, Item *b, Item **org_item) } +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 --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 3c8cc71370d..c4e6a53dd6b 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1389,6 +1389,7 @@ class Item_func_isnull :public Item_bool_func const_item_cache= args[0]->const_item(); } } + 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); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 2bf1216ec34..cfbde25314b 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2414,7 +2414,7 @@ void st_select_lex::print_order(String *str, { if (order->counter_used) { - if (query_type != QT_VIEW_INTERNAL) + if (!(query_type & QT_VIEW_INTERNAL)) { char buffer[20]; size_t length= my_snprintf(buffer, 20, "%d", order->counter); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 06d5a6f570a..db33a9de781 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2085,7 +2085,7 @@ view_store_create_info(THD *thd, TABLE_LIST *table, String *buff) 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, QT_ORDINARY); + table->view->unit.print(buff, QT_VIEW_INTERNAL); if (table->with_check != VIEW_CHECK_NONE) {

1 0