- commits - lists.mariadb.org

[Commits] 9c74849dd6a: MDEV-14472 Assertion `is_current_stmt_binlog_format_row()' failed in...
by sachin 05 May '18

05 May '18

revision-id: 9c74849dd6abd77c7c1891fa90ccd0a03a46d5b3 (mariadb-10.2.14-75-g9c74849dd6a) parent(s): da3c5c3c9ad53eda04293e5f5ee6795197c70652 author: Sachin Setiya committer: Sachin Setiya timestamp: 2018-05-05 13:03:15 +0530 message: MDEV-14472 Assertion `is_current_stmt_binlog_format_row()' failed in... THD::binlog_write_table_map Commit Message it is really very long , will be added after review --- mysql-test/suite/binlog/r/mdev_14472.result | 12 ++++++++++++ mysql-test/suite/binlog/t/mdev_14472.test | 22 ++++++++++++++++++++++ sql/handler.cc | 4 +++- 3 files changed, 37 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/binlog/r/mdev_14472.result b/mysql-test/suite/binlog/r/mdev_14472.result new file mode 100644 index 00000000000..0a2483678f1 --- /dev/null +++ b/mysql-test/suite/binlog/r/mdev_14472.result @@ -0,0 +1,12 @@ +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +CREATE TABLE t3 (f3 INT); +CREATE TABLE t4 (pk INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (); +CREATE TRIGGER tr2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (); +CREATE TRIGGER tr3 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t4 VALUES (); +LOCK TABLE t1 WRITE; +INSERT INTO t2 () VALUES (); +INSERT INTO t3 VALUES (); +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4; diff --git a/mysql-test/suite/binlog/t/mdev_14472.test b/mysql-test/suite/binlog/t/mdev_14472.test new file mode 100644 index 00000000000..fd892a44f5c --- /dev/null +++ b/mysql-test/suite/binlog/t/mdev_14472.test @@ -0,0 +1,22 @@ +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed.inc + +# Reproducible with MyISAM, InnoDB, Aria + +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +CREATE TABLE t3 (f3 INT); +CREATE TABLE t4 (pk INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (); +CREATE TRIGGER tr2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (); +CREATE TRIGGER tr3 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t4 VALUES (); + +LOCK TABLE t1 WRITE; +INSERT INTO t2 () VALUES (); +INSERT INTO t3 VALUES (); + +# Cleanup + +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4; diff --git a/sql/handler.cc b/sql/handler.cc index 06c407b572a..859963f8fa5 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -5650,7 +5650,9 @@ inline bool handler::check_table_binlog_row_based(bool binlog_row) { if (unlikely((table->in_use->variables.sql_log_bin_off))) return 0; /* Called by partitioning engine */ - if (unlikely((!check_table_binlog_row_based_done))) + /* Dont use cached result when current binlog format is stmt */ + if (unlikely(!table->in_use->is_current_stmt_binlog_format_row() || + (!check_table_binlog_row_based_done))) { check_table_binlog_row_based_done= 1; check_table_binlog_row_based_result=

1 0

[Commits] db779516bb4: MDEV-9266 Creating index on temporaray table breaks replication
by sachin 04 May '18

04 May '18

revision-id: db779516bb48c45ba9e70ec45af4c0b7cc078e4e (mariadb-10.0.34-56-gdb779516bb4) parent(s): 42fac3241368ad72f8cfef2b8521269e6c173558 author: Sachin Setiya committer: Sachin Setiya timestamp: 2018-05-04 17:48:58 +0530 message: MDEV-9266 Creating index on temporaray table breaks replication Problem:- Create index was logged into binlog. Goal:- Operation on temporary table should not be binlog when binlog format is row. Solution:- 1st- We should add CF_FORCE_ORIGINAL_BINLOG_FORMAT when there is ddl on temp table. 2nd- For optimize, analyze and repair we dont check if binlog format is row and this is tmp table, we dont need to log that. --- .../suite/binlog/r/binlog_tmp_table_row.result | 22 ++++++ .../suite/binlog/t/binlog_tmp_table_row.test | 81 ++++++++++++++++++++++ sql/sql_admin.cc | 12 +++- sql/sql_parse.cc | 33 ++++++--- 4 files changed, 136 insertions(+), 12 deletions(-) diff --git a/mysql-test/suite/binlog/r/binlog_tmp_table_row.result b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result new file mode 100644 index 00000000000..49b2103f28f --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result @@ -0,0 +1,22 @@ +RESET MASTER; +create temporary table t1(a int, b int); +#Analyze test +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +#Optimize test +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status Table is already up to date +#Repair test +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +#Check test +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +#Checksum test +checksum table t1; +Table Checksum +test.t1 0 diff --git a/mysql-test/suite/binlog/t/binlog_tmp_table_row.test b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test new file mode 100644 index 00000000000..5a2984a53f2 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test @@ -0,0 +1,81 @@ +# ==== Purpose ==== +# +# Test if statements used temporary tables are not binlogged in the case of +# binlog_format=row +# +# ==== Method ==== +# +# We will see if binlog file size is increased or not, It should be constant for the +# entire period of test. +# +# ==== Related bugs ==== +# +# Mdev-9266 +# +source include/have_log_bin.inc; +source include/have_binlog_format_row.inc; + +RESET MASTER; + +--let $binlog_size= query_get_value(show binary logs, File_size, 1) +create temporary table t1(a int, b int); +--disable_parsing + +--echo #Add index test +create index index_a on t1(a); +alter table t1 add index index_b(b); +insert into t1 values(1,2); + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #drop index test +drop index index_a on t1; +drop index index_b on t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--enable_parsing +--echo #Analyze test +analyze table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #Optimize test +optimize table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #Repair test +repair table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #Check test +check table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} +--echo #Checksum test +checksum table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 06a453e1bb7..3b2ea349f53 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -1197,7 +1197,9 @@ bool Sql_cmd_analyze_table::execute(THD *thd) "analyze", lock_type, 1, 0, 0, 0, &handler::ha_analyze, 0); /* ! we write after unlocking the table */ - if (!res && !m_lex->no_write_to_binlog) + if (!res && !m_lex->no_write_to_binlog && + !(thd->is_current_stmt_binlog_format_row() && + first_table->table->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, ANALYZE and binlog writing doesn't require synchronization @@ -1254,7 +1256,9 @@ bool Sql_cmd_optimize_table::execute(THD *thd) "optimize", TL_WRITE, 1, 0, 0, 0, &handler::ha_optimize, 0); /* ! we write after unlocking the table */ - if (!res && !m_lex->no_write_to_binlog) + if (!res && !m_lex->no_write_to_binlog && + !(thd->is_current_stmt_binlog_format_row() && + first_table->table->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, OPTIMIZE and binlog writing doesn't require synchronization @@ -1287,7 +1291,9 @@ bool Sql_cmd_repair_table::execute(THD *thd) &handler::ha_repair, &view_repair); /* ! we write after unlocking the table */ - if (!res && !m_lex->no_write_to_binlog) + if (!res && !m_lex->no_write_to_binlog && + !(thd->is_current_stmt_binlog_format_row() && + first_table->table->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, REPAIR and binlog writing doesn't require synchronization diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 4c0be4ebc8b..75f52ed9b87 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -447,6 +447,16 @@ void init_update_queries(void) CF_OPTIMIZER_TRACE; // (1) sql_command_flags[SQLCOM_EXECUTE]= CF_CAN_GENERATE_ROW_EVENTS; + /* + The following admin table operations are allowed + on log tables. + */ + sql_command_flags[SQLCOM_REPAIR]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_OPTIMIZE]|= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_ANALYZE]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_CHECK]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_CHECKSUM]= CF_REPORT_PROGRESS; + /* We don't want to change to statement based replication for these commands */ @@ -457,18 +467,23 @@ void init_update_queries(void) sql_command_flags[SQLCOM_TRUNCATE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; /* We don't want to replicate DROP for temp tables in row format */ sql_command_flags[SQLCOM_DROP_TABLE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate CREATE/DROP INDEX for temp tables in row format */ + sql_command_flags[SQLCOM_CREATE_INDEX]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + sql_command_flags[SQLCOM_DROP_INDEX]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate OPTIMIZE TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_OPTIMIZE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate ANALYZE TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_ANALYZE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate REPAIR TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_REPAIR]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate CHECK TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_CHECK]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate CHECKSUM TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_CHECKSUM]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* One can change replication mode with SET */ sql_command_flags[SQLCOM_SET_OPTION]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; - /* - The following admin table operations are allowed - on log tables. - */ - sql_command_flags[SQLCOM_REPAIR]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_OPTIMIZE]|= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_ANALYZE]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_CHECK]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_CHECKSUM]= CF_REPORT_PROGRESS; sql_command_flags[SQLCOM_CREATE_USER]|= CF_AUTO_COMMIT_TRANS; sql_command_flags[SQLCOM_DROP_USER]|= CF_AUTO_COMMIT_TRANS;

1 0

[Commits] aaa54f56428: MDEV-14472 Assertion `is_current_stmt_binlog_format_row()' failed in...
by sachin 03 May '18

03 May '18

revision-id: aaa54f56428169a9a4398fd7b0ab28fe9406349e (mariadb-10.2.14-75-gaaa54f56428) parent(s): da3c5c3c9ad53eda04293e5f5ee6795197c70652 author: Sachin Setiya committer: Sachin Setiya timestamp: 2018-05-03 18:14:19 +0530 message: MDEV-14472 Assertion `is_current_stmt_binlog_format_row()' failed in... THD::binlog_write_table_map Initial patch , rpl test are failing because of this it really needs some changnes , but later patch will follow the same idea --- mysql-test/suite/binlog/r/mdev_14472.result | 12 ++++++++++++ mysql-test/suite/binlog/t/mdev_14472.test | 22 ++++++++++++++++++++++ sql/handler.cc | 4 +++- 3 files changed, 37 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/binlog/r/mdev_14472.result b/mysql-test/suite/binlog/r/mdev_14472.result new file mode 100644 index 00000000000..0a2483678f1 --- /dev/null +++ b/mysql-test/suite/binlog/r/mdev_14472.result @@ -0,0 +1,12 @@ +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +CREATE TABLE t3 (f3 INT); +CREATE TABLE t4 (pk INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (); +CREATE TRIGGER tr2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (); +CREATE TRIGGER tr3 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t4 VALUES (); +LOCK TABLE t1 WRITE; +INSERT INTO t2 () VALUES (); +INSERT INTO t3 VALUES (); +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4; diff --git a/mysql-test/suite/binlog/t/mdev_14472.test b/mysql-test/suite/binlog/t/mdev_14472.test new file mode 100644 index 00000000000..fd892a44f5c --- /dev/null +++ b/mysql-test/suite/binlog/t/mdev_14472.test @@ -0,0 +1,22 @@ +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed.inc + +# Reproducible with MyISAM, InnoDB, Aria + +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +CREATE TABLE t3 (f3 INT); +CREATE TABLE t4 (pk INT AUTO_INCREMENT PRIMARY KEY); + +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (); +CREATE TRIGGER tr2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (); +CREATE TRIGGER tr3 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t4 VALUES (); + +LOCK TABLE t1 WRITE; +INSERT INTO t2 () VALUES (); +INSERT INTO t3 VALUES (); + +# Cleanup + +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4; diff --git a/sql/handler.cc b/sql/handler.cc index 06c407b572a..b5cdc72bc3c 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -5650,7 +5650,9 @@ inline bool handler::check_table_binlog_row_based(bool binlog_row) { if (unlikely((table->in_use->variables.sql_log_bin_off))) return 0; /* Called by partitioning engine */ - if (unlikely((!check_table_binlog_row_based_done))) + /* Dont use cached result when current binlog format is stmt */ + if (unlikely(!table->in_use->is_current_stmt_binlog_format_row() && + (!check_table_binlog_row_based_done))) { check_table_binlog_row_based_done= 1; check_table_binlog_row_based_result=

1 0

[Commits] 5651e72: MDEV-15465 Server crash or ASAN heap-use-after-free in Item_func_match::cleanup upon using FT search with partitioning.
by holyfoot＠askmonty.org 03 May '18

03 May '18

revision-id: 5651e72b8dd20e29a4963723773ec6bcfac457ba (mariadb-10.3.6-102-g5651e72) parent(s): 73a10cbcc5178ae5378abb821428d35d3276b4da committer: Alexey Botchkov timestamp: 2018-05-03 12:42:56 +0400 message: MDEV-15465 Server crash or ASAN heap-use-after-free in Item_func_match::cleanup upon using FT search with partitioning. The Item_func_match::cleanup() uses table's internals so it's not safe to call it after the close_thread_tables(). Let's call it in st_select_lex::cleanup(). --- mysql-test/main/partition_sync.result | 25 ++++++++++++++++++++++++ mysql-test/main/partition_sync.test | 36 +++++++++++++++++++++++++++++++++++ sql/sql_base.cc | 13 +++++++++++++ sql/sql_base.h | 1 + sql/sql_union.cc | 1 + 5 files changed, 76 insertions(+) diff --git a/mysql-test/main/partition_sync.result b/mysql-test/main/partition_sync.result index 18cc506..badcb10 100644 --- a/mysql-test/main/partition_sync.result +++ b/mysql-test/main/partition_sync.result @@ -61,3 +61,28 @@ connection default; disconnect con2; disconnect con3; DROP TABLE tbl_with_partitions; +# +# MDEV-15465 Server crash or ASAN heap-use-after-free in Item_func_match::cleanup +# upon using FT search with partitioning. +# +connect con1,localhost,root,,test; +CREATE OR REPLACE TABLE t1 (c CHAR(8)) ENGINE=MyISAM PARTITION BY KEY(c); +connection default; +set debug_sync= 'execute_command_after_close_tables SIGNAL opened WAIT_FOR go'; +DELETE FROM t1 WHERE MATCH(c) AGAINST ('foo' IN BOOLEAN MODE); +connection con1; +set debug_sync= 'now WAIT_FOR opened'; +FLUSH TABLES; +set debug_sync= 'now SIGNAL go'; +connection default; +set debug_sync= 'execute_command_after_close_tables SIGNAL opened WAIT_FOR go'; +SELECT * FROM t1 WHERE MATCH(c) AGAINST ('foo' IN BOOLEAN MODE); +connection con1; +set debug_sync= 'now WAIT_FOR opened'; +FLUSH TABLES; +set debug_sync= 'now SIGNAL go'; +disconnect con1; +connection default; +c +DROP TABLE t1; +set debug_sync= 'RESET'; diff --git a/mysql-test/main/partition_sync.test b/mysql-test/main/partition_sync.test index fd704f3..22ca7df 100644 --- a/mysql-test/main/partition_sync.test +++ b/mysql-test/main/partition_sync.test @@ -77,6 +77,42 @@ disconnect con2; disconnect con3; DROP TABLE tbl_with_partitions; +--echo # +--echo # MDEV-15465 Server crash or ASAN heap-use-after-free in Item_func_match::cleanup +--echo # upon using FT search with partitioning. +--echo # + +--connect (con1,localhost,root,,test) +CREATE OR REPLACE TABLE t1 (c CHAR(8)) ENGINE=MyISAM PARTITION BY KEY(c); + +--connection default +set debug_sync= 'execute_command_after_close_tables SIGNAL opened WAIT_FOR go'; +--send +DELETE FROM t1 WHERE MATCH(c) AGAINST ('foo' IN BOOLEAN MODE); + +--connection con1 +set debug_sync= 'now WAIT_FOR opened'; +FLUSH TABLES; +set debug_sync= 'now SIGNAL go'; + +--connection default +--reap +set debug_sync= 'execute_command_after_close_tables SIGNAL opened WAIT_FOR go'; +--send +SELECT * FROM t1 WHERE MATCH(c) AGAINST ('foo' IN BOOLEAN MODE); + +--connection con1 +set debug_sync= 'now WAIT_FOR opened'; +FLUSH TABLES; +set debug_sync= 'now SIGNAL go'; + +# Cleanup +--disconnect con1 +--connection default +--reap +DROP TABLE t1; +set debug_sync= 'RESET'; + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0081365..673473e 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -8639,6 +8639,19 @@ int setup_ftfuncs(SELECT_LEX *select_lex) } +void cleanup_ftfuncs(SELECT_LEX *select_lex) +{ + List_iterator<Item_func_match> li(*(select_lex->ftfunc_list)), + lj(*(select_lex->ftfunc_list)); + Item_func_match *ftf; + + while ((ftf=li++)) + { + ftf->cleanup(); + } +} + + int init_ftfuncs(THD *thd, SELECT_LEX *select_lex, bool no_order) { if (select_lex->ftfunc_list->elements) diff --git a/sql/sql_base.h b/sql/sql_base.h index 8f6d406..645a4a8 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -216,6 +216,7 @@ int setup_conds(THD *thd, TABLE_LIST *tables, List<TABLE_LIST> &leaves, COND **conds); void wrap_ident(THD *thd, Item **conds); int setup_ftfuncs(SELECT_LEX* select); +void cleanup_ftfuncs(SELECT_LEX *select_lex); int init_ftfuncs(THD *thd, SELECT_LEX* select, bool no_order); bool lock_table_names(THD *thd, const DDL_options_st &options, TABLE_LIST *table_list, diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 432ad85..0bd654c 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1909,6 +1909,7 @@ bool st_select_lex::cleanup() cleanup_order(order_list.first); cleanup_order(group_list.first); + cleanup_ftfuncs(this); if (join) {

1 0

[Commits] e733fed325b: MDEV-9266 Creating index on temporary table breaks replication
by sachin 03 May '18

03 May '18

revision-id: e733fed325b53889a2e0bc15279cfd085296a433 (mariadb-10.0.34-56-ge733fed325b) parent(s): 42fac3241368ad72f8cfef2b8521269e6c173558 author: Sachin Setiya committer: Sachin Setiya timestamp: 2018-05-03 11:25:39 +0530 message: MDEV-9266 Creating index on temporary table breaks replication We should not write the create index on binlog. Since Create Index is a different way of writing alter table. It should also have CF_FORCE_ORIGINAL_BINLOG_FORMAT flag --- mysql-test/suite/rpl/r/rpl_temp_table_row.result | 12 ++++++++++++ mysql-test/suite/rpl/t/rpl_temp_table_row.test | 19 +++++++++++++++++++ sql/sql_parse.cc | 2 ++ 3 files changed, 33 insertions(+) diff --git a/mysql-test/suite/rpl/r/rpl_temp_table_row.result b/mysql-test/suite/rpl/r/rpl_temp_table_row.result new file mode 100644 index 00000000000..608ba2404a1 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_temp_table_row.result @@ -0,0 +1,12 @@ +include/master-slave.inc +[connection master] +##testing index creation on temp table with +##binlog format row #mdev-9266 +create temporary table t1(a int, b int); +create index index_t1 on t1(a); +alter table t1 add index indx(b); +insert into t1 values(1,2); +select * from t1; +ERROR 42S02: Table 'test.t1' doesn't exist +drop table t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_temp_table_row.test b/mysql-test/suite/rpl/t/rpl_temp_table_row.test new file mode 100644 index 00000000000..030ce441012 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_temp_table_row.test @@ -0,0 +1,19 @@ +-- source include/have_binlog_format_row.inc +-- source include/master-slave.inc + +--echo ##testing index creation on temp table with +--echo ##binlog format row #mdev-9266 +--connection master +create temporary table t1(a int, b int); +create index index_t1 on t1(a); +alter table t1 add index indx(b); +insert into t1 values(1,2); + +--sync_slave_with_master +--error ER_NO_SUCH_TABLE +select * from t1; + +--connection master +drop table t1; + +--source include/rpl_end.inc diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 4c0be4ebc8b..d9477233035 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -453,6 +453,8 @@ void init_update_queries(void) sql_command_flags[SQLCOM_ROLLBACK]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; /* We don't want to replicate ALTER TABLE for temp tables in row format */ sql_command_flags[SQLCOM_ALTER_TABLE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate CREATE INDEX for temp tables in row format */ + sql_command_flags[SQLCOM_CREATE_INDEX]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; /* We don't want to replicate TRUNCATE for temp tables in row format */ sql_command_flags[SQLCOM_TRUNCATE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; /* We don't want to replicate DROP for temp tables in row format */

1 0

[Commits] f3dda8e: MDEV-15357 Wrong result with join_cache_level=4, BNLH join
by IgorBabaev 01 May '18

01 May '18

revision-id: f3dda8e2002dc2ad630c07bda0d8c00be7269907 (mariadb-10.2.14-72-gf3dda8e) parent(s): b4c5e4a717e3ce2c2d434106cc74417fe9a1d3dc author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-30 21:26:38 -0700 message: MDEV-15357 Wrong result with join_cache_level=4, BNLH join This bug was introduced by the architectural changes of the patch for MDEV-11640. The patch moved initialization of join caches after the call of make_join_readinfo(). As any failure to initialize a join cache caused denial of its usage the execution code for the query had to be revised. This revision required rolling back many actions taken by make_join_readinfo(). It was not done in the patch. As a result if a denial of join cache happened some join conditions could be lost. This was exactly the cause of wrong results in the bug's reported test case. Thus the introduced architectural change is not valid and it would be better to roll it back. At the same time two new methods adjust_read_set_for_vcol_keyread() and get_covering_index_for_scan() were added to the class JOIN_TAB to resolve the problems of MDEV-11640. --- mysql-test/r/analyze_format_json.result | 1 - mysql-test/r/join_cache.result | 62 +++++++++++++ mysql-test/r/limit_rows_examined.result | 24 +++-- mysql-test/t/join_cache.test | 47 ++++++++++ mysql-test/t/limit_rows_examined.test | 1 + sql/sql_join_cache.cc | 12 ++- sql/sql_select.cc | 150 ++++++++++++++++++-------------- sql/sql_select.h | 4 +- 8 files changed, 218 insertions(+), 83 deletions(-) diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result index 307fb73..c8bb615 100644 --- a/mysql-test/r/analyze_format_json.result +++ b/mysql-test/r/analyze_format_json.result @@ -626,7 +626,6 @@ ANALYZE "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))", "r_filtered": null }, "subqueries": [ diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index eea3974..4f57266 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6016,4 +6016,66 @@ select f2 from t2,t1 where f2 = 0; f2 drop table t1, t2; set join_buffer_size = default; +# +# MDEV-15357: join cache denied: optimize_join_buffer_size=off and +# join_buffer_size > join_buffer_space_limit +# +CREATE TABLE t1 ( +id varchar(30) NOT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL6040'),('ZSBILL6050'),('ZSBILL7000'); +CREATE TABLE t2 ( +id varchar(30) NOT NULL, +a char(1) NOT NULL DEFAULT '3', +PRIMARY KEY (id,a) +) ENGINE=MyISAM; +INSERT INTO t2(id) VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL2030'),('ZSBILL3000'); +SET join_buffer_size=30000000; +SET join_cache_level=4; +SELECT @@join_buffer_space_limit; +@@join_buffer_space_limit +2097152 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=on'; +EXPLAIN EXTENDED SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index PRIMARY PRIMARY 33 NULL 9 100.00 Using index +1 SIMPLE t1 hash_index PRIMARY #hash#PRIMARY:PRIMARY 32:32 test.t2.id 10 10.00 Using index; Using join buffer (flat, BNLH join) +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`id` = `test`.`t2`.`id` +SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id id +ZSBILL1000 ZSBILL1000 +ZSBILL1010 ZSBILL1010 +ZSBILL1020 ZSBILL1020 +ZSBILL1030 ZSBILL1030 +ZSBILL2000 ZSBILL2000 +ZSBILL2010 ZSBILL2010 +ZSBILL2020 ZSBILL2020 +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=off'; +EXPLAIN EXTENDED SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index PRIMARY PRIMARY 33 NULL 9 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 32 test.t2.id 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`id` = `test`.`t2`.`id` +SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; +id id +ZSBILL1000 ZSBILL1000 +ZSBILL1010 ZSBILL1010 +ZSBILL1020 ZSBILL1020 +ZSBILL1030 ZSBILL1030 +ZSBILL2000 ZSBILL2000 +ZSBILL2010 ZSBILL2010 +ZSBILL2020 ZSBILL2020 +SET join_buffer_size=default; +SET join_cache_level=default; +set optimizer_switch= @tmp_optimizer_switch; +DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index 2b5bca2..dd1c971 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -750,24 +750,22 @@ FROM t1, t2 AS alias2, t2 AS alias3 WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 120; -field1 field2 field3 field4 field5 -Warnings: -Warning 1931 Query execution was interrupted. The query examined at least 121 rows, which exceeds LIMIT ROWS EXAMINED (120). The query result may be incomplete +ERROR HY000: Sort aborted: SHOW STATUS LIKE 'Handler_read%'; Variable_name Value Handler_read_first 1 -Handler_read_key 4 +Handler_read_key 5 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 7 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 -Handler_read_rnd_next 46 +Handler_read_rnd_next 38 SHOW STATUS LIKE 'Handler_tmp%'; Variable_name Value Handler_tmp_update 0 -Handler_tmp_write 66 +Handler_tmp_write 70 FLUSH STATUS; SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 FROM t1, t2 AS alias2, t2 AS alias3 @@ -775,20 +773,20 @@ WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 124; field1 field2 field3 field4 field5 +00:21:38 06:07:10 a 2007-06-08 04:35:26 2007-05-28 00:00:00 Warnings: Warning 1931 Query execution was interrupted. The query examined at least 125 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete -Warning 1931 Query execution was interrupted. The query examined at least 127 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete SHOW STATUS LIKE 'Handler_read%'; Variable_name Value Handler_read_first 1 -Handler_read_key 4 +Handler_read_key 5 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 7 Handler_read_prev 0 Handler_read_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 48 +Handler_read_rnd 2 +Handler_read_rnd_deleted 1 +Handler_read_rnd_next 39 SHOW STATUS LIKE 'Handler_tmp%'; Variable_name Value Handler_tmp_update 0 diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index b775725..5356341 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3957,6 +3957,53 @@ select f2 from t2,t1 where f2 = 0; drop table t1, t2; set join_buffer_size = default; +--echo # +--echo # MDEV-15357: join cache denied: optimize_join_buffer_size=off and +--echo # join_buffer_size > join_buffer_space_limit +--echo # + +CREATE TABLE t1 ( + id varchar(30) NOT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL6040'),('ZSBILL6050'),('ZSBILL7000'); + +CREATE TABLE t2 ( + id varchar(30) NOT NULL, + a char(1) NOT NULL DEFAULT '3', + PRIMARY KEY (id,a) +) ENGINE=MyISAM; +INSERT INTO t2(id) VALUES +('ZSBILL1000'),('ZSBILL1010'),('ZSBILL1020'),('ZSBILL1030'),('ZSBILL2000'), +('ZSBILL2010'),('ZSBILL2020'),('ZSBILL2030'),('ZSBILL3000'); + +SET join_buffer_size=30000000; +SET join_cache_level=4; + +SELECT @@join_buffer_space_limit; + +let $q= +SELECT t1.id, t2.id FROM t1,t2 WHERE t1.id = t2.id; + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=on'; +eval EXPLAIN EXTENDED $q; +eval $q; + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=off'; +eval EXPLAIN EXTENDED $q; +eval $q; + +SET join_buffer_size=default; +SET join_cache_level=default; + +set optimizer_switch= @tmp_optimizer_switch; + +DROP TABLE t1,t2; + # The following command must be the last one the file # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/limit_rows_examined.test b/mysql-test/t/limit_rows_examined.test index 815394a..29b3b41 100644 --- a/mysql-test/t/limit_rows_examined.test +++ b/mysql-test/t/limit_rows_examined.test @@ -489,6 +489,7 @@ GROUP BY field1, field2, field3, field4, field5 LIMIT ROWS EXAMINED 120; FLUSH STATUS; +--error 1028 SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 FROM t1, t2 AS alias2, t2 AS alias3 WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 3612cb6..242cb91 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -226,6 +226,16 @@ void JOIN_CACHE::calc_record_fields() flag_fields+= MY_TEST(tab->table->maybe_null); fields+= tab->used_fields; blobs+= tab->used_blobs; + if (tab->type == JT_ALL || tab->type == JT_HASH) + { + uint idx= tab->get_covering_index_for_scan(); + if (idx != MAX_KEY) + { + fields-= bitmap_bits_set(tab->table->read_set); + tab->adjust_read_set_for_vcol_keyread(idx); + fields+= bitmap_bits_set(tab->table->read_set); + } + } } if ((with_match_flag= join_tab->use_match_flag())) flag_fields++; @@ -596,7 +606,7 @@ void JOIN_CACHE::create_remaining_fields() bitmap_invert(&table->tmp_set); bitmap_intersect(&table->tmp_set, table->read_set); rem_field_set= &table->tmp_set; - } + } length+= add_table_data_fields_to_join_cache(tab, rem_field_set, &data_field_count, &copy, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 30ef26f..e5b75ec 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1143,36 +1143,6 @@ int JOIN::optimize() } -int JOIN::init_join_caches() -{ - JOIN_TAB *tab; - - for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); - tab; - tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) - { - TABLE *table= tab->table; - if (table->file->keyread_enabled()) - { - if (!(table->file->index_flags(table->file->keyread, 0, 1) & HA_CLUSTERED_INDEX)) - table->mark_columns_used_by_index(table->file->keyread, table->read_set); - } - else if ((tab->read_first_record == join_read_first || - tab->read_first_record == join_read_last) && - !tab->filesort && table->covering_keys.is_set(tab->index) && - !table->no_keyread) - { - table->prepare_for_keyread(tab->index, table->read_set); - } - if (tab->cache && tab->cache->init(select_options & SELECT_DESCRIBE)) - revise_cache_usage(tab); - else - tab->remove_redundant_bnl_scan_conds(); - } - return 0; -} - - /** global select optimisation. @@ -2189,9 +2159,6 @@ JOIN::optimize_inner() if (make_aggr_tables_info()) DBUG_RETURN(1); - if (init_join_caches()) - DBUG_RETURN(1); - error= 0; if (select_options & SELECT_DESCRIBE) @@ -11142,7 +11109,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, case JT_ALL: if (cache_level == 1) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BNL(join, tab, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BNL(join, tab, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->icp_other_tables_ok= FALSE; return (2 - MY_TEST(!prev_cache)); @@ -11176,7 +11144,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, goto no_join_cache; if (cache_level == 3) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BNLH(join, tab, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BNLH(join, tab, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->icp_other_tables_ok= FALSE; return (4 - MY_TEST(!prev_cache)); @@ -11196,7 +11165,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, { if (cache_level == 5) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BKA(join, tab, flags, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BKA(join, tab, flags, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) return (6 - MY_TEST(!prev_cache)); goto no_join_cache; } @@ -11204,7 +11174,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, { if (cache_level == 7) prev_cache= 0; - if ((tab->cache= new (root) JOIN_CACHE_BKAH(join, tab, flags, prev_cache))) + if ((tab->cache= new (root) JOIN_CACHE_BKAH(join, tab, flags, prev_cache)) && + !tab->cache->init(options & SELECT_DESCRIBE)) { tab->idx_cond_fact_out= FALSE; return (8 - MY_TEST(!prev_cache)); @@ -11404,6 +11375,61 @@ void JOIN_TAB::remove_redundant_bnl_scan_conds() } +uint JOIN_TAB::get_covering_index_for_scan() +{ + int idx= MAX_KEY; + if (table->no_keyread) + return idx; + if (select && select->quick && + select->quick->index != MAX_KEY && //not index_merge + table->covering_keys.is_set(select->quick->index)) + idx= select->quick->index; + else if (!table->covering_keys.is_clear_all() && + !(select && select->quick)) + { // Only read index tree + if (loosescan_match_tab) + idx= loosescan_key; + else + { +#ifdef BAD_OPTIMIZATION + /* + It has turned out that the below change, while speeding things + up for disk-bound loads, slows them down for cases when the data + is in disk cache (see BUG#35850): + See bug #26447: "Using the clustered index for a table scan + is always faster than using a secondary index". + */ + if (table->s->primary_key != MAX_KEY && + table->file->primary_key_is_clustered()) + idx= table->s->primary_key; + else +#endif + idx= find_shortest_key(table, & table->covering_keys); + } + } + return idx; +} + + +void JOIN_TAB::adjust_read_set_for_vcol_keyread(uint keyread_idx) +{ + if (!table->vcol_set || bitmap_is_clear_all(table->vcol_set)) + return; + + MY_BITMAP *keyread_set= &table->cond_set; // is free for use at call + bitmap_clear_all(keyread_set); + table->mark_columns_used_by_index(keyread_idx, keyread_set); + bitmap_intersect(keyread_set, table->read_set); + bitmap_intersect(keyread_set, table->vcol_set); + if (!bitmap_is_clear_all(keyread_set)) + { + bitmap_clear_all(keyread_set); + table->mark_columns_used_by_index(keyread_idx, keyread_set); + bitmap_intersect(table->read_set, keyread_set); + } +} + + /* Plan refinement stage: do various setup things for the executor @@ -11526,7 +11552,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system : join_read_const; if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) + { + tab->adjust_read_set_for_vcol_keyread(tab->ref.key); table->file->ha_start_keyread(tab->ref.key); + } else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; @@ -11534,7 +11563,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) tab->read_record.unlock_row= join_read_key_unlock_row; /* fall through */ if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) + { + tab->adjust_read_set_for_vcol_keyread(tab->ref.key); table->file->ha_start_keyread(tab->ref.key); + } else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; @@ -11548,7 +11580,10 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) delete tab->quick; tab->quick=0; if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) + { + tab->adjust_read_set_for_vcol_keyread(tab->ref.key); table->file->ha_start_keyread(tab->ref.key); + } else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; @@ -11606,35 +11641,19 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) } } } - if (!table->no_keyread) + uint idx= tab->get_covering_index_for_scan(); + if (idx != MAX_KEY) { if (tab->select && tab->select->quick && tab->select->quick->index != MAX_KEY && //not index_merge table->covering_keys.is_set(tab->select->quick->index)) - table->file->ha_start_keyread(tab->select->quick->index); + table->file->ha_start_keyread(idx); else if (!table->covering_keys.is_clear_all() && !(tab->select && tab->select->quick)) - { // Only read index tree - if (tab->loosescan_match_tab) - tab->index= tab->loosescan_key; - else - { -#ifdef BAD_OPTIMIZATION - /* - It has turned out that the below change, while speeding things - up for disk-bound loads, slows them down for cases when the data - is in disk cache (see BUG#35850): - See bug #26447: "Using the clustered index for a table scan - is always faster than using a secondary index". - */ - if (table->s->primary_key != MAX_KEY && - table->file->primary_key_is_clustered()) - tab->index= table->s->primary_key; - else -#endif - tab->index=find_shortest_key(table, & table->covering_keys); - } + { // Only read index tree + tab->index= idx; tab->read_first_record= join_read_first; + table->file->ha_start_keyread(tab->index); /* Read with index_first / index_next */ tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT; } @@ -11657,6 +11676,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) /* purecov: end */ } + tab->remove_redundant_bnl_scan_conds(); DBUG_EXECUTE("where", char buff[256]; String str(buff,sizeof(buff),system_charset_info); @@ -19663,9 +19683,8 @@ join_read_first(JOIN_TAB *tab) TABLE *table=tab->table; DBUG_ENTER("join_read_first"); - DBUG_ASSERT(table->no_keyread || - !table->covering_keys.is_set(tab->index) || - table->file->keyread == tab->index); + if (table->covering_keys.is_set(tab->index) && !table->no_keyread) + table->file->ha_start_keyread(tab->index); tab->table->status=0; tab->read_record.read_record=join_read_next; tab->read_record.table=table; @@ -19701,11 +19720,10 @@ join_read_last(JOIN_TAB *tab) { TABLE *table=tab->table; int error= 0; - DBUG_ENTER("join_read_first"); + DBUG_ENTER("join_read_last"); - DBUG_ASSERT(table->no_keyread || - !table->covering_keys.is_set(tab->index) || - table->file->keyread == tab->index); + if (table->covering_keys.is_set(tab->index) && !table->no_keyread) + table->file->ha_start_keyread(tab->index); tab->table->status=0; tab->read_record.read_record=join_read_prev; tab->read_record.table=table; diff --git a/sql/sql_select.h b/sql/sql_select.h index 2003cc1..b87d97e 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -614,7 +614,8 @@ typedef struct st_join_table { bool use_order() const; ///< Use ordering provided by chosen index? bool sort_table(); bool remove_duplicates(); - + uint get_covering_index_for_scan(); + void adjust_read_set_for_vcol_keyread(uint keyread_idx); } JOIN_TAB; @@ -1522,7 +1523,6 @@ class JOIN :public Sql_alloc bool flatten_subqueries(); bool optimize_unflattened_subqueries(); bool optimize_constant_subqueries(); - int init_join_caches(); bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields, bool before_group_by, bool recompute= FALSE);

1 0

[Commits] fabdfa991d6: MDEV-16057: Using optimization Splitting with Group BY we see an unnecessary attached condition
by varunraiko1803＠gmail.com 30 Apr '18

30 Apr '18

revision-id: fabdfa991d6d36a97bd7f7f6606dad4a4dbe1372 (mariadb-10.3.0-860-gfabdfa991d6) parent(s): 9b1313e8493909cdb7953607cfe1e85d3276937f author: Varun Gupta committer: Varun Gupta timestamp: 2018-04-30 13:05:01 +0530 message: MDEV-16057: Using optimization Splitting with Group BY we see an unnecessary attached condition t1.pk IS NOT NULL where pk is a PRIMARY KEY For equalites in the WHERE clause we create a keyuse array that contains the set of all equalities. For each KEYUSE inside the keyuse array we have a field "null_rejecting" which tells that the equality will not hold if either the left or right hand side of the equality is NULL. If the equality is NULL rejecting then we accordingly add a NOT NULL condition for the field present in the item val(present in the KEYUSE struct) when we are doing ref access. For the optimization of splitting with GROUP BY we always set the null_rejecting to TRUE and we are doing ref access on the GROUP by field. This does create a problem when the equality is NOT NULL rejecting. This happens in this case as in the equality we have the right hand side as t1.pk where pk is a PRIMARY KEY , hence it is NOT NULLABLE. So we should have null rejecting set to FALSE for such a case. --- mysql-test/main/derived_cond_pushdown.result | 1 - sql/opt_split.cc | 9 ++++++++- 2 files changed, 8 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 82f621c73a2..5a496d2bd4e 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15071,7 +15071,6 @@ EXPLAIN "materialized": { "query_block": { "select_id": 2, - "outer_ref_condition": "t1.pk1 is not null", "table": { "table_name": "t2", "access_type": "eq_ref", diff --git a/sql/opt_split.cc b/sql/opt_split.cc index b81556e56a2..37853bdbbe9 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -544,7 +544,14 @@ void TABLE::add_splitting_info_for_key_field(KEY_FIELD *key_field) added_key_field->level= 0; added_key_field->optimize= KEY_OPTIMIZE_EQ; added_key_field->eq_func= true; - added_key_field->null_rejecting= true; + + Item *real= key_field->val->real_item(); + if ((real->type() == Item::FIELD_ITEM) && + ((Item_field*)real)->field->maybe_null()) + added_key_field->null_rejecting= true; + else + added_key_field->null_rejecting= false; + added_key_field->cond_guard= NULL; added_key_field->sj_pred_no= UINT_MAX; return;

1 0

[Commits] f7d3c995037: Merge branch 'MDEV-15803' of https://github.com/codership/mariadb-server into codership-MDEV-15803
by jan 30 Apr '18

30 Apr '18

revision-id: f7d3c9950377092b260e09599ec1e5b5b34fa250 (mariadb-10.1.32-74-gf7d3c995037) parent(s): ed1d9c802dc3568487f0154df46c71f1bb31782c 74f22939dcb8f3a4d574f84f1296ac77f28a6735 author: Jan Lindström committer: Jan Lindström timestamp: 2018-04-30 07:37:34 +0300 message: Merge branch 'MDEV-15803' of https://github.com/codership/mariadb-server into codership-MDEV-15803 mysql-test/suite/galera/disabled.def | 1 - .../galera/r/galera_var_auto_inc_control_on.result | 18 ++------------ .../galera/t/galera_var_auto_inc_control_on.test | 29 ++++++++++++---------- 3 files changed, 18 insertions(+), 30 deletions(-) diff --cc mysql-test/suite/galera/disabled.def index 738e1236b89,294efa9c444..513f9ac2c88 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@@ -26,5 -26,7 +26,4 @@@ galera_gcache_recover_manytrx : MDEV-13 galera_ssl_upgrade : MDEV-13549 Galera test failures galera.MW-329 : wsrep_local_replays not stable galera.MW-328A : have_deadlocks test not stable - galera_var_auto_inc_control_on : MDEV-15803 Test failure on galera.galera_var_auto_inc_control_on -galera_var_retry_autocommit : MDEV-15794 Test failure on galera.galera_var_retry_autocommit query_cache : MDEV-15805 Test failure on galera.query_cache -

1 0

[Commits] f9cbc58: MDEV-12387 Push conditions into materialized subqueries
by galina.shalygina＠mariadb.com 29 Apr '18

29 Apr '18

revision-id: f9cbc5873429543fc489907938a378f4c91470b2 (mariadb-10.3.4-56-gf9cbc58) parent(s): 1bfad1ffbd716b78e338f3c6b2d21c8b5e9a591f author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-04-29 20:55:51 +0200 message: MDEV-12387 Push conditions into materialized subqueries Post review changes: comments changed, tests changed --- mysql-test/r/in_subq_cond_pushdown.result | 1963 +++++++++++++++++++++-------- mysql-test/t/in_subq_cond_pushdown.test | 611 +++++---- sql/item.cc | 127 +- sql/item.h | 35 +- sql/item_cmpfunc.h | 2 + sql/item_subselect.h | 2 +- sql/opt_subselect.cc | 376 +++--- sql/sql_derived.cc | 207 ++- sql/sql_lex.cc | 144 ++- sql/sql_lex.h | 5 + sql/sql_select.cc | 2 +- 11 files changed, 2340 insertions(+), 1134 deletions(-) diff --git a/mysql-test/r/in_subq_cond_pushdown.result b/mysql-test/r/in_subq_cond_pushdown.result index 6676d3b..ebb6265 100644 --- a/mysql-test/r/in_subq_cond_pushdown.result +++ b/mysql-test/r/in_subq_cond_pushdown.result @@ -1,44 +1,44 @@ -create table t1 (t1_a int, t1_b int, t1_c int, t1_d int); -create table t2 (t2_e int, t2_f int, t2_g int); -create table t3 (t3_x int, t3_y int); -insert into t1 values +CREATE TABLE t1 (t1_a INt, t1_b INt, t1_c INt, t1_d INt); +CREATE TABLE t2 (t2_e INt, t2_f INt, t2_g INt); +CREATE TABLE t3 (t3_x INt, t3_y INt); +INSERT INTO t1 VALUES (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4), (4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2), (5,5,65,1), (2,3,70,3), (1,4,35,3), (2,3,25,3); -insert into t2 values +INSERT INTO t2 VALUES (1,2,38), (2,3,15), (1,3,40), (1,4,35), (2,2,70), (3,4,23), (5,5,12), (5,4,17), (3,3,17), (4,2,24), (2,5,25), (5,1,65); -insert into t3 values +INSERT INTO t3 VALUES (1,25), (1,18), (2,15), (4,24), (1,35), (3,23), (3,17), (2,15); -create view v1 as +CREATE VIEW v1 AS ( -select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3 +SELECT t3_x AS v1_x, t3_y AS v1_y FROM t3 WHERE t3_x<=3 ); # conjunctive subformula : pushing into HAVING -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_c<25 and -(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e); +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_c<25 AND +(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e); t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 -select * from t1 -where t1_c<25 and -(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e); +SELECT * FROM t1 +WHERE t1_c<25 AND +(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e); t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 -explain select * from t1 -where t1_c<25 and -(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e); +EXPLAIN SELECT * FROM t1 +WHERE t1_c<25 AND +(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where t1_c<25 and -(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e); +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_c<25 AND +(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e); EXPLAIN { "query_block": { @@ -56,7 +56,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -64,7 +64,7 @@ EXPLAIN "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(t2_g)` < 25", + "having_condition": "`MAX(t2_g)` < 25", "temporary_table": { "table": { "table_name": "t2", @@ -79,53 +79,53 @@ EXPLAIN } } } -# extracted and formula : pushing into HAVING -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_c>55 and t1_b<4 and -(t1_a,t1_b,t1_c) in +# extracted AND formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_c>55 AND t1_b<4 AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 2 3 70 3 -select * from t1 -where t1_c>55 and t1_b<4 and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE t1_c>55 AND t1_b<4 AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 2 3 70 3 -explain select * from t1 -where t1_c>55 and t1_b<4 and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE t1_c>55 AND t1_b<4 AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where t1_c>55 and t1_b<4 and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_c>55 AND t1_b<4 AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -145,7 +145,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -153,7 +153,7 @@ EXPLAIN "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(t2_g)` > 55 and t2.t2_f < 4", + "having_condition": "`MAX(t2_g)` > 55 and t2.t2_f < 4", "temporary_table": { "table": { "table_name": "t2", @@ -168,55 +168,55 @@ EXPLAIN } } } -# extracted or formula : pushing into HAVING -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where (t1_c>60 or t1_c<25) and -(t1_a,t1_b,t1_c) in +# extracted OR formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_c>60 OR t1_c<25) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 2 3 70 3 -select * from t1 -where (t1_c>60 or t1_c<25) and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE (t1_c>60 OR t1_c<25) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 2 3 70 3 -explain select * from t1 -where (t1_c>60 or t1_c<25) and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE (t1_c>60 OR t1_c<25) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where (t1_c>60 or t1_c<25) and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_c>60 OR t1_c<25) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -236,7 +236,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -244,7 +244,7 @@ EXPLAIN "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(t2_g)` > 60 or `max(t2_g)` < 25", + "having_condition": "`MAX(t2_g)` > 60 or `MAX(t2_g)` < 25", "temporary_table": { "table": { "table_name": "t2", @@ -259,53 +259,53 @@ EXPLAIN } } } -# extracted and-or formula : pushing into HAVING -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((t1_c>60 or t1_c<25) and t1_b>2) and -(t1_a,t1_b,t1_c) in +# extracted AND-or formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 2 3 70 3 -select * from t1 -where ((t1_c>60 or t1_c<25) and t1_b>2) and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 2 3 70 3 -explain select * from t1 -where ((t1_c>60 or t1_c<25) and t1_b>2) and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where ((t1_c>60 or t1_c<25) and t1_b>2) and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -325,7 +325,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -333,7 +333,7 @@ EXPLAIN "unique": 1, "query_block": { "select_id": 2, - "having_condition": "(`max(t2_g)` > 60 or `max(t2_g)` < 25) and t2.t2_f > 2", + "having_condition": "(`MAX(t2_g)` > 60 or `MAX(t2_g)` < 25) and t2.t2_f > 2", "temporary_table": { "table": { "table_name": "t2", @@ -349,54 +349,54 @@ EXPLAIN } } # conjunctive subformula : pushing into HAVING -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((t1_a<2 or t1_d>3) and t1_b>1) and -(t1_a,t1_b,t1_c) in +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 1 2 40 2 -select * from t1 -where ((t1_a<2 or t1_d>3) and t1_b>1) and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 1 2 40 2 -explain select * from t1 -where ((t1_a<2 or t1_d>3) and t1_b>1) and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where ((t1_a<2 or t1_d>3) and t1_b>1) and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -416,7 +416,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -439,53 +439,53 @@ EXPLAIN } } } -# using view in subquery definition : pushing into HAVING -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_c>20 and -(t1_a,t1_c) in +# using view IN subquery defINition : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_c>20 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; t1_a t1_b t1_c t1_d 3 2 23 1 -select * from t1 -where t1_c>20 and -(t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_c>20 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; t1_a t1_b t1_c t1_d 3 2 23 1 -explain select * from t1 -where t1_c>20 and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE t1_c>20 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary -explain format=json select * from t1 -where t1_c>20 and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_c>20 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; EXPLAIN @@ -505,7 +505,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["v1_x", "max(v1_y)"], + "used_key_parts": ["v1_x", "MAX(v1_y)"], "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -513,7 +513,7 @@ EXPLAIN "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(v1_y)` > 20", + "having_condition": "`MAX(v1_y)` > 20", "temporary_table": { "table": { "table_name": "t3", @@ -529,38 +529,38 @@ EXPLAIN } } # using equality : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1 -where t1_c>20 and t1_c=v1_y and -(t1_a,t1_c) in +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1,v1 +WHERE t1_c>20 AND t1_c=v1_y AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d v1_x v1_y 3 2 23 1 3 23 -select * from t1,v1 -where t1_c>20 and t1_c=v1_y and -(t1_a,t1_c) in +SELECT * FROM t1,v1 +WHERE t1_c>20 AND t1_c=v1_y AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d v1_x v1_y 3 2 23 1 3 23 -explain select * from t1,v1 -where t1_c>20 and t1_c=v1_y and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1,v1 +WHERE t1_c>20 AND t1_c=v1_y AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra @@ -568,14 +568,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t3.t3_y 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1,v1 -where t1_c>20 and t1_c=v1_y and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1,v1 +WHERE t1_c>20 AND t1_c=v1_y AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -607,7 +607,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t3.t3_y"], "rows": 1, "filtered": 100, @@ -615,7 +615,7 @@ EXPLAIN "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(t2_g)` > 20", + "having_condition": "`MAX(t2_g)` > 20", "temporary_table": { "table": { "table_name": "t2", @@ -631,54 +631,54 @@ EXPLAIN } } # conjunctive subformula : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_a<2 and -(t1_a,t1_c) in +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_a<2 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 3 40 1 1 2 40 2 -select * from t1 -where t1_a<2 and -(t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_a<2 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 3 40 1 1 2 40 2 -explain select * from t1 -where t1_a<2 and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE t1_a<2 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where t1_a<2 and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_a<2 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -698,7 +698,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -720,55 +720,55 @@ EXPLAIN } } } -# extracted and formula : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_a>2 and t1_a<5 and -(t1_a,t1_c) in +# extracted AND formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_a>2 AND t1_a<5 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 -select * from t1 -where t1_a>2 and t1_a<5 and -(t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_a>2 AND t1_a<5 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 -explain select * from t1 -where t1_a>2 and t1_a<5 and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE t1_a>2 AND t1_a<5 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where t1_a>2 and t1_a<5 and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_a>2 AND t1_a<5 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -788,7 +788,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -810,57 +810,57 @@ EXPLAIN } } } -# extracted or formula : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where (t1_a<2 or t1_a>=4) and -(t1_a,t1_c) in +# extracted OR formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_a<2 OR t1_a>=4) AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 3 40 1 4 2 24 4 1 2 40 2 -select * from t1 -where (t1_a<2 or t1_a>=4) and -(t1_a,t1_c) in +SELECT * FROM t1 +WHERE (t1_a<2 OR t1_a>=4) AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 3 40 1 4 2 24 4 1 2 40 2 -explain select * from t1 -where (t1_a<2 or t1_a>=4) and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE (t1_a<2 OR t1_a>=4) AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where (t1_a<2 or t1_a>=4) and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_a<2 OR t1_a>=4) AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -880,7 +880,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -902,53 +902,53 @@ EXPLAIN } } } -# extracted and-or formula : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +# extracted AND-or formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; t1_a t1_b t1_c t1_d 1 4 35 3 -select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; t1_a t1_b t1_c t1_d 1 4 35 3 -explain select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; EXPLAIN @@ -968,7 +968,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -990,53 +990,53 @@ EXPLAIN } } } -# extracted and-or formula : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +# extracted AND-or formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; t1_a t1_b t1_c t1_d 1 4 35 3 -select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; t1_a t1_b t1_c t1_d 1 4 35 3 -explain select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e,t2_f +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f ) ; EXPLAIN @@ -1056,7 +1056,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -1079,52 +1079,52 @@ EXPLAIN } } # conjunctive subformula : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((t1_b<3 or t1_d>2) and t1_a<2) and -(t1_a,t1_b,t1_c) in +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 2 40 2 -select * from t1 -where ((t1_b<3 or t1_d>2) and t1_a<2) and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 2 40 2 -explain select * from t1 -where ((t1_b<3 or t1_d>2) and t1_a<2) and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where ((t1_b<3 or t1_d>2) and t1_a<2) and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -1144,7 +1144,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -1167,52 +1167,52 @@ EXPLAIN } } # using equalities : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_d=1 and t1_a=t1_d and -(t1_a,t1_c) in +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_d=1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 3 40 1 -select * from t1 -where t1_d=1 and t1_a=t1_d and -(t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_d=1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 3 40 1 -explain select * from t1 -where t1_d=1 and t1_a=t1_d and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE t1_d=1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 const,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where -explain format=json select * from t1 -where t1_d=1 and t1_a=t1_d and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_d=1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -1232,7 +1232,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["const", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -1253,52 +1253,52 @@ EXPLAIN } } # using equality : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_d>1 and t1_a=t1_d and -(t1_a,t1_c) in +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_d>1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 -select * from t1 -where t1_d>1 and t1_a=t1_d and -(t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_d>1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 4 2 24 4 -explain select * from t1 -where t1_d>1 and t1_a=t1_d and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE t1_d>1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where t1_d>1 and t1_a=t1_d and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_d>1 AND t1_a=t1_d AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -1318,7 +1318,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -1340,53 +1340,53 @@ EXPLAIN } } } -# using view in subquery definition : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where t1_a<3 and -(t1_a,t1_c) in +# using view IN subquery defINition : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1_a<3 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; t1_a t1_b t1_c t1_d 2 1 15 4 -select * from t1 -where t1_a<3 and -(t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_a<3 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; t1_a t1_b t1_c t1_d 2 1 15 4 -explain select * from t1 -where t1_a<3 and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE t1_a<3 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary -explain format=json select * from t1 -where t1_a<3 and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1_a<3 AND +(t1_a,t1_c) IN ( -select v1_x,max(v1_y) -from v1 -where v1_x>1 -group by v1_x +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x ) ; EXPLAIN @@ -1406,7 +1406,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["v1_x", "max(v1_y)"], + "used_key_parts": ["v1_x", "MAX(v1_y)"], "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -1429,40 +1429,40 @@ EXPLAIN } } # using equality : pushing into WHERE -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1 -where t1_a=v1_x and v1_x<2 and v1_y>30 and -(t1_a,t1_c) in +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1,v1 +WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d v1_x v1_y 1 3 40 1 1 35 1 2 40 2 1 35 -select * from t1,v1 -where t1_a=v1_x and v1_x<2 and v1_y>30 and -(t1_a,t1_c) in +SELECT * FROM t1,v1 +WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d v1_x v1_y 1 3 40 1 1 35 1 2 40 2 1 35 -explain select * from t1,v1 -where t1_a=v1_x and v1_x<2 and v1_y>30 and -(t1_a,t1_c) in +EXPLAIN SELECT * FROM t1,v1 +WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra @@ -1470,14 +1470,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t3.t3_x,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1,v1 -where t1_a=v1_x and v1_x<2 and v1_y>30 and -(t1_a,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1,v1 +WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1_a,t1_c) IN ( -select t2_e,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -1509,7 +1509,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "max(t2_g)"], + "used_key_parts": ["t2_e", "MAX(t2_g)"], "ref": ["test.t3.t3_x", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -1532,53 +1532,53 @@ EXPLAIN } } # conjunctive subformula : pushing into WHERE -# extracted or formula : pushing into HAVING -set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((t1_b<3 or t1_b=4) and t1_a<3) and -(t1_a,t1_b,t1_c) in +# extracted OR formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 2 40 2 -select * from t1 -where ((t1_b<3 or t1_b=4) and t1_a<3) and -(t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; t1_a t1_b t1_c t1_d 1 2 40 2 -explain select * from t1 -where ((t1_b<3 or t1_b=4) and t1_a<3) and -(t1_a,t1_b,t1_c) in +EXPLAIN SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary -explain format=json select * from t1 -where ((t1_b<3 or t1_b=4) and t1_a<3) and -(t1_a,t1_b,t1_c) in +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND +(t1_a,t1_b,t1_c) IN ( -select t2_e,t2_f,max(t2_g) -from t2 -where t2_e<5 -group by t2_e +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e ) ; EXPLAIN @@ -1598,7 +1598,7 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, @@ -1621,5 +1621,904 @@ EXPLAIN } } } -drop table t1,t2,t3; -drop view v1; +# conjunctive subformula using addition : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_a+t1_c>41) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +2 3 70 3 +SELECT * FROM t1 +WHERE (t1_a+t1_c>41) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1_a+t1_c>41) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_a+t1_c>41) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_a + t1.t1_c > 41 and t1.t1_a is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["t2_e", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "t2.t2_e + `MAX(t2_g)` > 41", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using substitution : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_c-t1_a<35) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +3 2 23 1 +SELECT * FROM t1 +WHERE (t1_c-t1_a<35) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +3 2 23 1 +EXPLAIN SELECT * FROM t1 +WHERE (t1_c-t1_a<35) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_c-t1_a<35) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_c - t1.t1_a < 35 and t1.t1_a is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["t2_e", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2_g)` - t2.t2_e < 35", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using multiplication : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_c*t1_a>100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +2 3 70 3 +SELECT * FROM t1 +WHERE (t1_c*t1_a>100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1_c*t1_a>100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_c*t1_a>100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_c * t1.t1_a > 100 and t1.t1_a is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["t2_e", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2_g)` * t2.t2_e > 100", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using division : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_c/t1_a>30) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +1 3 40 1 +1 2 40 2 +2 3 70 3 +SELECT * FROM t1 +WHERE (t1_c/t1_a>30) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +1 3 40 1 +1 2 40 2 +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1_c/t1_a>30) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_c/t1_a>30) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_c / t1.t1_a > 30 and t1.t1_a is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["t2_e", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2_g)` / t2.t2_e > 30", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using BETWEEN : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_c BETWEEN 50 AND 100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +2 3 70 3 +SELECT * FROM t1 +WHERE (t1_c BETWEEN 50 AND 100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1_c BETWEEN 50 AND 100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_c BETWEEN 50 AND 100) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_c between 50 and 100 and t1.t1_a is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["t2_e", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2_g)` between 50 and 100", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using addition : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_a+t1_b > 5) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +SELECT * FROM t1 +WHERE (t1_a+t1_b > 5) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1_a+t1_b > 5) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_a+t1_b > 5) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_a + t1.t1_b > 5 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5 and t2.t2_e + t2.t2_f > 5" + } + } + } + } + } + } +} +# conjunctive subformula using substitution : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_a-t1_b > 0) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +SELECT * FROM t1 +WHERE (t1_a-t1_b > 0) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1_a-t1_b > 0) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_a-t1_b > 0) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_a - t1.t1_b > 0 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5 and t2.t2_e - t2.t2_f > 0" + } + } + } + } + } + } +} +# conjunctive subformula using multiplication : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_a*t1_b > 6) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +SELECT * FROM t1 +WHERE (t1_a*t1_b > 6) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +4 2 24 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1_a*t1_b > 6) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_a*t1_b > 6) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_a * t1.t1_b > 6 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5 and t2.t2_e * t2.t2_f > 6" + } + } + } + } + } + } +} +# conjunctive subformula using division : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_b/t1_a > 2) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +1 3 40 1 +1 4 35 3 +SELECT * FROM t1 +WHERE (t1_b/t1_a > 2) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +t1_a t1_b t1_c t1_d +1 3 40 1 +1 4 35 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1_b/t1_a > 2) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_b/t1_a > 2) AND +(t1_a,t1_b,t1_c) IN +( +SELECT t2_e,t2_f,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e,t2_f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_b / t1.t1_a > 2 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5 and t2.t2_f / t2.t2_e > 2" + } + } + } + } + } + } +} +# conjunctive subformula using BETWEEN : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1_a BETWEEN 1 AND 3) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +1 3 40 1 +3 2 23 1 +1 2 40 2 +2 3 70 3 +SELECT * FROM t1 +WHERE (t1_a BETWEEN 1 AND 3) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +t1_a t1_b t1_c t1_d +1 3 40 1 +3 2 23 1 +1 2 40 2 +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1_a BETWEEN 1 AND 3) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1_a BETWEEN 1 AND 3) AND +(t1_a,t1_c) IN +( +SELECT t2_e,MAX(t2_g) +FROM t2 +WHERE t2_e<5 +GROUP BY t2_e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t1.t1_a between 1 and 3 and t1.t1_a is not null and t1.t1_c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["t2_e", "MAX(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.t2_e < 5 and t2.t2_e between 1 and 3" + } + } + } + } + } + } +} +DROP TABLE t1,t2,t3; +DROP VIEW v1; diff --git a/mysql-test/t/in_subq_cond_pushdown.test b/mysql-test/t/in_subq_cond_pushdown.test index a40ba1a..1aae56b 100644 --- a/mysql-test/t/in_subq_cond_pushdown.test +++ b/mysql-test/t/in_subq_cond_pushdown.test @@ -1,345 +1,526 @@ -let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_subquery=off' for; +LET $no_pushdown= + SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR; -create table t1 (t1_a int, t1_b int, t1_c int, t1_d int); -create table t2 (t2_e int, t2_f int, t2_g int); -create table t3 (t3_x int, t3_y int); +CREATE TABLE t1 (t1_a INt, t1_b INt, t1_c INt, t1_d INt); +CREATE TABLE t2 (t2_e INt, t2_f INt, t2_g INt); +CREATE TABLE t3 (t3_x INt, t3_y INt); -insert into t1 values +INSERT INTO t1 VALUES (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4), (4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2), (5,5,65,1), (2,3,70,3), (1,4,35,3), (2,3,25,3); -insert into t2 values +INSERT INTO t2 VALUES (1,2,38), (2,3,15), (1,3,40), (1,4,35), (2,2,70), (3,4,23), (5,5,12), (5,4,17), (3,3,17), (4,2,24), (2,5,25), (5,1,65); -insert into t3 values +INSERT INTO t3 VALUES (1,25), (1,18), (2,15), (4,24), (1,35), (3,23), (3,17), (2,15); -create view v1 as +CREATE VIEW v1 AS ( - select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3 + SELECT t3_x AS v1_x, t3_y AS v1_y FROM t3 WHERE t3_x<=3 ); --echo # conjunctive subformula : pushing into HAVING let $query= -select * from t1 -where t1_c<25 and - (t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e); +SELECT * FROM t1 +WHERE t1_c<25 AND + (t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted and formula : pushing into HAVING +--echo # extracted AND formula : pushing into HAVING let $query= -select * from t1 -where t1_c>55 and t1_b<4 and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE t1_c>55 AND t1_b<4 AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted or formula : pushing into HAVING +--echo # extracted OR formula : pushing into HAVING let $query= -select * from t1 -where (t1_c>60 or t1_c<25) and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE (t1_c>60 OR t1_c<25) AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted and-or formula : pushing into HAVING +--echo # extracted AND-or formula : pushing into HAVING let $query= -select * from t1 -where ((t1_c>60 or t1_c<25) and t1_b>2) and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into HAVING let $query= -select * from t1 -where ((t1_a<2 or t1_d>3) and t1_b>1) and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # using view in subquery definition : pushing into HAVING +--echo # using view IN subquery defINition : pushing into HAVING let $query= -select * from t1 -where t1_c>20 and - (t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_c>20 AND + (t1_a,t1_c) IN ( - select v1_x,max(v1_y) - from v1 - where v1_x>1 - group by v1_x + SELECT v1_x,MAX(v1_y) + FROM v1 + WHERE v1_x>1 + GROUP BY v1_x ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # using equality : pushing into WHERE let $query= -select * from t1,v1 -where t1_c>20 and t1_c=v1_y and - (t1_a,t1_c) in +SELECT * FROM t1,v1 +WHERE t1_c>20 AND t1_c=v1_y AND + (t1_a,t1_c) IN ( - select t2_e,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into WHERE let $query= -select * from t1 -where t1_a<2 and - (t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_a<2 AND + (t1_a,t1_c) IN ( - select t2_e,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted and formula : pushing into WHERE +--echo # extracted AND formula : pushing into WHERE let $query= -select * from t1 -where t1_a>2 and t1_a<5 and - (t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_a>2 AND t1_a<5 AND + (t1_a,t1_c) IN ( - select t2_e,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted or formula : pushing into WHERE +--echo # extracted OR formula : pushing into WHERE let $query= -select * from t1 -where (t1_a<2 or t1_a>=4) and - (t1_a,t1_c) in +SELECT * FROM t1 +WHERE (t1_a<2 OR t1_a>=4) AND + (t1_a,t1_c) IN ( - select t2_e,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted and-or formula : pushing into WHERE +--echo # extracted AND-or formula : pushing into WHERE let $query= -select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e,t2_f + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e,t2_f ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted and-or formula : pushing into WHERE +--echo # extracted AND-or formula : pushing into WHERE let $query= -select * from t1 -where ((t1_a<2 or t1_a=5) and t1_b>3) and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e,t2_f + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e,t2_f ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into WHERE let $query= -select * from t1 -where ((t1_b<3 or t1_d>2) and t1_a<2) and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # using equalities : pushing into WHERE let $query= -select * from t1 -where t1_d=1 and t1_a=t1_d and - (t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_d=1 AND t1_a=t1_d AND + (t1_a,t1_c) IN ( - select t2_e,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # using equality : pushing into WHERE let $query= -select * from t1 -where t1_d>1 and t1_a=t1_d and - (t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_d>1 AND t1_a=t1_d AND + (t1_a,t1_c) IN ( - select t2_e,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; ---echo # using view in subquery definition : pushing into WHERE +--echo # using view IN subquery defINition : pushing into WHERE let $query= -select * from t1 -where t1_a<3 and - (t1_a,t1_c) in +SELECT * FROM t1 +WHERE t1_a<3 AND + (t1_a,t1_c) IN ( - select v1_x,max(v1_y) - from v1 - where v1_x>1 - group by v1_x + SELECT v1_x,MAX(v1_y) + FROM v1 + WHERE v1_x>1 + GROUP BY v1_x ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # using equality : pushing into WHERE let $query= -select * from t1,v1 -where t1_a=v1_x and v1_x<2 and v1_y>30 and - (t1_a,t1_c) in +SELECT * FROM t1,v1 +WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND + (t1_a,t1_c) IN ( - select t2_e,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into WHERE ---echo # extracted or formula : pushing into HAVING +--echo # extracted OR formula : pushing into HAVING let $query= -select * from t1 -where ((t1_b<3 or t1_b=4) and t1_a<3) and - (t1_a,t1_b,t1_c) in +SELECT * FROM t1 +WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND + (t1_a,t1_b,t1_c) IN ( - select t2_e,t2_f,max(t2_g) - from t2 - where t2_e<5 - group by t2_e + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e ) ; -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; -drop table t1,t2,t3; -drop view v1; +--echo # conjunctive subformula using addition : pushing into HAVING +let $query= +SELECT * FROM t1 +WHERE (t1_a+t1_c>41) AND + (t1_a,t1_c) IN + ( + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using substitution : pushing into HAVING +let $query= +SELECT * FROM t1 +WHERE (t1_c-t1_a<35) AND + (t1_a,t1_c) IN + ( + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using multiplication : pushing into HAVING +let $query= +SELECT * FROM t1 +WHERE (t1_c*t1_a>100) AND + (t1_a,t1_c) IN + ( + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using division : pushing into HAVING +let $query= +SELECT * FROM t1 +WHERE (t1_c/t1_a>30) AND + (t1_a,t1_c) IN + ( + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using BETWEEN : pushing into HAVING +let $query= +SELECT * FROM t1 +WHERE (t1_c BETWEEN 50 AND 100) AND + (t1_a,t1_c) IN + ( + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using addition : pushing into WHERE +let $query= +SELECT * FROM t1 +WHERE (t1_a+t1_b > 5) AND + (t1_a,t1_b,t1_c) IN + ( + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e,t2_f + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using substitution : pushing into WHERE +let $query= +SELECT * FROM t1 +WHERE (t1_a-t1_b > 0) AND + (t1_a,t1_b,t1_c) IN + ( + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e,t2_f + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using multiplication : pushing into WHERE +let $query= +SELECT * FROM t1 +WHERE (t1_a*t1_b > 6) AND + (t1_a,t1_b,t1_c) IN + ( + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e,t2_f + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using division : pushing into WHERE +let $query= +SELECT * FROM t1 +WHERE (t1_b/t1_a > 2) AND + (t1_a,t1_b,t1_c) IN + ( + SELECT t2_e,t2_f,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e,t2_f + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula using BETWEEN : pushing into WHERE +let $query= +SELECT * FROM t1 +WHERE (t1_a BETWEEN 1 AND 3) AND + (t1_a,t1_c) IN + ( + SELECT t2_e,MAX(t2_g) + FROM t2 + WHERE t2_e<5 + GROUP BY t2_e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1,t2,t3; +DROP VIEW v1; diff --git a/sql/item.cc b/sql/item.cc index 7a6879e..113a56e 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7532,34 +7532,34 @@ Item *Item_field::update_value_transformer(THD *thd, uchar *select_arg) /** @brief - Mark subformulas of a condition unusable for the pushed condition + Prepare AND/OR formula for extraction of a pushable condition - @param tab_map bitmap of tables used by derived table - @param subq_pred subquery + @param checker the checker callback function to be applied to the nodes + of the tree of the object + @param arg parameter to be passed to the checker @details - - This method is called for both condition pushdown optimizations: - pushdown into the materialized views/derived tables (1) and - pushdown into the IN subqueries (2). - (1) : - When the method is called for the pushdown into the materialized views - or derived tables (object) the subq_pred parameter is set to 0. - - (2) : - When the method is called for the pushdown into the IN subqueries (object) the - tab_map parameter is set to 0. - - This method recursively traverses the AND-OR condition cond and for each subformula - of the condition it checks whether it can be usable for the extraction of a condition - that can be pushed into the object. The subformulas that are not usable are - marked with the flag NO_EXTRACTION_FL. + This method recursively traverses this AND/OR condition and for each + subformula of the condition it checks whether it can be usable for the + extraction of a pushable condition. The criteria of pushability of + a subformula is checked by the callback function 'checker' with one + parameter arg. The subformulas that are not usable are marked with + the flag NO_EXTRACTION_FL. @note This method is called before any call of build_pushable_cond. - The flag NO_EXTRACTION_FL set in a subformula allows to avoid building clone - for the subformula when extracting the pushable condition. -**/ -void Item::check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred) + The flag NO_EXTRACTION_FL set in a subformula allows to avoid building + clones for the subformulas that are not used in the pushable condition. + @note + This method is called for pushdown conditions into materialized + derived tables/views optimization. + Item::pushable_cond_checker_for_derived() is passed as the actual callback + function. + Also it is called for pushdown conditions in materialized IN subqueries. + Item::pushable_cond_checker_for_subquery is passed as the actual + callback function. +*/ + +void Item::check_pushable_cond(Pushdown_checker checker, uchar *arg) { clear_extraction_flag(); if (type() == Item::COND_ITEM) @@ -7570,7 +7570,7 @@ void Item::check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred) Item *item; while ((item=li++)) { - item->check_pushable_cond(tab_map, subq_pred); + item->check_pushable_cond(checker, arg); if (item->get_extraction_flag() != NO_EXTRACTION_FL) count++; else if (!and_cond) @@ -7585,56 +7585,54 @@ void Item::check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred) item->clear_extraction_flag(); } } - else if ((tab_map && !excl_dep_on_table(tab_map)) || - (subq_pred && !excl_dep_on_in_subq_left_part(subq_pred))) + else if (!((this->*checker) (arg))) set_extraction_flag(NO_EXTRACTION_FL); } /** @brief - Build condition extractable from the given one for the pushdown + Build condition extractable from this condition for pushdown - @param thd the thread handle - @param tab_map bitmap of tables used by derived table - @param subq_pred subquery + @param thd the thread handle + @param checker the checker callback function to be applied to the + equal items of multiple equality items + @param arg parameter to be passed to the checker @details - This method is called for both condition pushdown optimizations: - pushdown into the materialized views/derived tables (1) and - pushdown into the IN subqueries (2). - (1) : - When the method is called for the pushdown into the materialized views - or derived tables the subq_pred parameter is set to 0. The condition c1 - is the check of whether the item depends only on the tab_map of the - derived table. - - (2) : - When the method is called for the pushdown into the IN subqueries the - tab_map parameter is set to 0. The condition c1 is the check of whether - the item depends only on the fields from the left part of the IN subquery. - - For the given condition cond this method finds out what condition for which - c1 is satisfied can be extracted from cond. If such condition C exists - the method builds the item for it. - The method uses the flag NO_EXTRACTION_FL set by the preliminary call of - the method check_pushable_cond to figure out whether c1 is satisfied for the - subformula or not. + This method finds out what condition that can be pushed down can be + extracted from this condition. If such condition C exists the + method builds the item for it. The method uses the flag NO_EXTRACTION_FL + set by the preliminary call of the method check_pushable_cond() to figure + out whether a subformula is pushable or not. + In the case when this item is a multiple equality a checker method is + called to find the equal fields to build a new equality that can be + pushed down. @note The built condition C is always implied by the condition cond (cond => C). The method tries to build the most restrictive such condition (i.e. for any other condition C' such that cond => C' we have C => C'). - @note + @note The build item is not ready for usage: substitution for the field items has to be done and it has to be re-fixed. + @note + This method is called for pushdown conditions into materialized + derived tables/views optimization. + Item::pushable_equality_checker_for_derived() is passed as the actual + callback function. + Also it is called for pushdown conditions into materialized IN subqueries. + Item::pushable_equality_checker_for_subquery() is passed as the actual + callback function. @retval - the built condition pushable into this table if such a condition exists + the built condition pushable into if such a condition exists NULL if there is no such a condition -**/ -Item *Item::build_pushable_cond(THD *thd, table_map tab_map, - Item_in_subselect *subq_pred) +*/ + +Item *Item::build_pushable_cond(THD *thd, + Pushdown_checker checker, + uchar *arg) { bool is_multiple_equality= type() == Item::FUNC_ITEM && ((Item_func*) this)->functype() == Item_func::MULT_EQUAL_FUNC; @@ -7649,7 +7647,7 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map, if (((Item_cond*) this)->functype() == Item_func::COND_AND_FUNC) { cond_and= true; - new_cond=new (thd->mem_root) Item_cond_and(thd); + new_cond= new (thd->mem_root) Item_cond_and(thd); } else new_cond= new (thd->mem_root) Item_cond_or(thd); @@ -7666,12 +7664,13 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map, return 0; continue; } - Item *fix= item->build_pushable_cond(thd, tab_map, subq_pred); + Item *fix= item->build_pushable_cond(thd, checker, arg); if (!fix && !cond_and) return 0; if (!fix) continue; - new_cond->argument_list()->push_back(fix, thd->mem_root); + if (new_cond->argument_list()->push_back(fix, thd->mem_root)) + return 0; } switch (new_cond->argument_list()->elements) @@ -7686,8 +7685,6 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map, } else if (is_multiple_equality) { - if (tab_map && !(used_tables() & tab_map)) - return 0; Item *new_cond= NULL; int i= 0; Item_equal *item_equal= (Item_equal *) this; @@ -7699,8 +7696,7 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map, { while ((item=it++)) { - left_item= ((tab_map && (item->used_tables() == tab_map)) || - (subq_pred && item->find_field(subq_pred))) ? item : NULL; + left_item= ((item->*checker) (arg)) ? item : NULL; if (left_item) break; } @@ -7709,8 +7705,7 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map, return 0; while ((item=it++)) { - right_item= ((tab_map && (item->used_tables() == tab_map)) || - (subq_pred && item->find_field(subq_pred))) ? item : NULL; + right_item= ((item->*checker) (arg)) ? item : NULL; if (!right_item) continue; Item_func_eq *eq= 0; @@ -7741,8 +7736,8 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map, } } } - if (new_cond) - new_cond->fix_fields(thd, &new_cond); + if (new_cond && new_cond->fix_fields(thd, &new_cond)) + return 0; return new_cond; } else if (get_extraction_flag() != NO_EXTRACTION_FL) diff --git a/sql/item.h b/sql/item.h index faeb4ae..056e592 100644 --- a/sql/item.h +++ b/sql/item.h @@ -531,6 +531,7 @@ typedef bool (Item::*Item_processor) (void *arg); typedef bool (Item::*Item_analyzer) (uchar **argp); typedef Item* (Item::*Item_transformer) (THD *thd, uchar *arg); typedef void (*Cond_traverser) (const Item *item, void *arg); +typedef bool (Item::*Pushdown_checker) (uchar *arg); struct st_cond_statistic; @@ -1787,6 +1788,8 @@ class Item: public Value_source, { return this; } virtual Item *grouping_field_transformer_for_where(THD *thd, uchar *arg) { return this; } + virtual Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg) + { return this; } virtual Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg) { return this; } virtual Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg) @@ -1970,10 +1973,30 @@ class Item: public Value_source, { marker &= ~EXTRACTION_MASK; } - bool find_field(Item_in_subselect *subq_pred); - Item *build_pushable_cond(THD *thd, table_map tab_map, - Item_in_subselect *subq_pred); - void check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred); + void check_pushable_cond(Pushdown_checker excl_dep_func, uchar *arg); + bool pushable_cond_checker_for_derived(uchar *arg) + { + return excl_dep_on_table(*((table_map *)arg)); + } + bool pushable_cond_checker_for_subquery(uchar *arg) + { + return excl_dep_on_in_subq_left_part((Item_in_subselect *)arg); + } + Item *get_corresponding_field_in_insubq(Item_in_subselect *subq_pred); + Item *build_pushable_cond(THD *thd, + Pushdown_checker checker, + uchar *arg); + /* + Checks if this item depends only on the tables used in arg + */ + bool pushable_equality_checker_for_derived(uchar *arg) + { + return (used_tables() == *((table_map *)arg)); + } + bool pushable_equality_checker_for_subquery(uchar *arg) + { + return get_corresponding_field_in_insubq((Item_in_subselect *)arg); + } }; MEM_ROOT *get_thd_memroot(THD *thd); @@ -2959,6 +2982,7 @@ class Item_field :public Item_ident Item *derived_field_transformer_for_having(THD *thd, uchar *arg); Item *derived_field_transformer_for_where(THD *thd, uchar *arg); Item *grouping_field_transformer_for_where(THD *thd, uchar *arg); + Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg); Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg); virtual void print(String *str, enum_query_type query_type); bool excl_dep_on_table(table_map tab_map); @@ -5091,6 +5115,7 @@ class Item_direct_view_ref :public Item_direct_ref Item *derived_field_transformer_for_having(THD *thd, uchar *arg); Item *derived_field_transformer_for_where(THD *thd, uchar *arg); Item *grouping_field_transformer_for_where(THD *thd, uchar *arg); + Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg); Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg); void save_val(Field *to) @@ -5999,6 +6024,8 @@ class Item_cache: public Item_basic_constant, { return convert_to_basic_const_item(thd); } Item *grouping_field_transformer_for_where(THD *thd, uchar *arg) { return convert_to_basic_const_item(thd); } + Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg) + { return convert_to_basic_const_item(thd); } Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg) { return convert_to_basic_const_item(thd); } }; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index f565f3a..38f1698 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1262,6 +1262,8 @@ class Item_func_nullif :public Item_func_case_expression { reset_first_arg_if_needed(); return this; } Item *grouping_field_transformer_for_where(THD *thd, uchar *arg) { reset_first_arg_if_needed(); return this; } + Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg) + { reset_first_arg_if_needed(); return this; } Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg) { reset_first_arg_if_needed(); return this; } }; diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 49dc582..301f11c 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -585,7 +585,7 @@ class Item_in_subselect :public Item_exists_subselect */ bool is_registered_semijoin; - List<In_subq_field> comparable_fields; + List<In_subq_field> corresponding_fields; /* Used to determine how this subselect item is represented in the item tree, diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 49747e4..e56801b 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5560,8 +5560,9 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, bool is_mult_eq= (cond && cond->type() == Item::FUNC_ITEM && ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC); - if (cond && !is_mult_eq) - new_conds_list.push_back(cond, thd->mem_root); + if (cond && !is_mult_eq && + new_conds_list.push_back(cond, thd->mem_root)) + return NULL; if (new_conds.elements > 0) { li.rewind(); @@ -5578,7 +5579,8 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, { li.rewind(); item= li++; - new_conds_list.push_back(item, thd->mem_root); + if (new_conds_list.push_back(item, thd->mem_root)) + return NULL; } } @@ -5720,10 +5722,8 @@ bool execute_degenerate_jtbm_semi_join(THD *thd, new (thd->mem_root) Item_func_eq(thd, subq_pred->left_expr->element_index(i), new_sink->row[i]); - if (!eq_cond) + if (!eq_cond || eq_list.push_back(eq_cond, thd->mem_root)) DBUG_RETURN(TRUE); - - eq_list.push_back(eq_cond, thd->mem_root); } } else @@ -5805,7 +5805,9 @@ setup_degenerate_jtbm_semi_joins(JOIN *join, } if ((nested_join= table->nested_join)) { - if (setup_degenerate_jtbm_semi_joins(join, &nested_join->join_list, eq_list)) + if (setup_degenerate_jtbm_semi_joins(join, + &nested_join->join_list, + eq_list)) DBUG_RETURN(TRUE); } } @@ -5905,7 +5907,10 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, List_iterator<Item> li(*hash_sj_engine->semi_join_conds->argument_list()); Item *item; while ((item=li++)) - eq_list.push_back(item, thd->mem_root); + { + if (eq_list.push_back(item, thd->mem_root)) + DBUG_RETURN(TRUE); + } } table->table->maybe_null= MY_TEST(join->mixed_implicit_grouping); } @@ -6307,38 +6312,33 @@ bool JOIN::choose_tableless_subquery_plan() } -/** - @brief - Check if item exists in the list of fields from the left part of subquery - - @param item the search field - @param fields list of In_subq_fields - - @retval TRUE if the item was found in the list - @retval FALSE otherwise +/* + Check if the item exists in the fields list of the left part of + the IN subquery predicate subq_pred and returns its corresponding + item from the select of the right part of subq_pred. */ -bool Item::find_field(Item_in_subselect *subq_pred) +Item *Item::get_corresponding_field_in_insubq(Item_in_subselect *subq_pred) { DBUG_ASSERT(type() == Item::FIELD_ITEM || (type() == Item::REF_ITEM && ((Item_ref *) this)->ref_type() == Item_ref::VIEW_REF)); - List_iterator<In_subq_field> it(subq_pred->comparable_fields); + List_iterator<In_subq_field> it(subq_pred->corresponding_fields); In_subq_field *ret; Item_field *field_item= (Item_field *) (real_item()); while ((ret= it++)) { if (field_item->field == ((Item_field *) (ret->left_it->real_item()))->field) - return true; + return ret->right_it; } - return false; + return NULL; } bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) { - if (((Item *)this)->find_field(subq_pred)) + if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred)) return true; if (item_equal) { @@ -6347,9 +6347,9 @@ bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) while ((equal_item= it++)) { if (equal_item->const_item()) - continue; - if (equal_item->find_field(subq_pred)) - return true; + continue; + if (equal_item->get_corresponding_field_in_insubq(subq_pred)) + return true; } } return false; @@ -6361,7 +6361,8 @@ bool Item_direct_view_ref::excl_dep_on_in_subq_left_part(Item_in_subselect *subq if (item_equal) { DBUG_ASSERT(real_item()->type() == Item::FIELD_ITEM); - return ((Item *)this)->find_field(subq_pred); + if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred)) + return true; } return (*ref)->excl_dep_on_in_subq_left_part(subq_pred); } @@ -6396,37 +6397,35 @@ bool Item_equal::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) /** @brief - Transforms item so it can be pushed into the IN subquery HAVING clause + Get corresponding item from the select of the right part of IN subquery @param thd the thread handle - @param in_item the item for which pushable item should be created + @param item the item from the left part of subq_pred for which + corresponding item should be find @param subq_pred the IN subquery predicate @details - This method traverses the fields of the select of the IN subquery predicate - subq_pred trying to find the corresponding item 'new_item' for in_item. - If in_item has equal items it traverses the fields of the select of - subq_pred for each equal item trying to find corresponding item 'new_item'. - If 'new_item' is found, a shell for this item is created. This shell can be - pushed into the HAVING part of subq_pred select. - - @retval <item*> reference to the created corresonding item shell for in_item - @retval NULL if mistake occurs + This method looks through the fields of the select of the right part of + the IN subquery predicate subq_pred trying to find the corresponding + item 'new_item' for item. If item has equal items it looks through + the fields of the select of the right part of subq_pred for each equal + item trying to find the corresponding item. + The method assumes that the given item is either a field item or + a reference to a field item. + + @retval <item*> reference to the corresonding item + @retval NULL if item was not found */ - static -Item *get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item, - Item_in_subselect *subq_pred) +Item *get_corresponding_item(THD *thd, Item *item, + Item_in_subselect *subq_pred) { - DBUG_ASSERT(in_item->type() == Item::FIELD_ITEM || - (in_item->type() == Item::REF_ITEM && - ((Item_ref *) in_item)->ref_type() == Item_ref::VIEW_REF)); + DBUG_ASSERT(item->type() == Item::FIELD_ITEM || + (item->type() == Item::REF_ITEM && + ((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF)); - In_subq_field *fi; - Item *new_item; - List_iterator<In_subq_field> li(subq_pred->comparable_fields); - Item_field *field_item= (Item_field *) (in_item->real_item()); - Item_equal *item_equal= in_item->get_item_equal(); + Item *corresonding_item; + Item_equal *item_equal= item->get_item_equal(); if (item_equal) { @@ -6434,32 +6433,64 @@ Item *get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item, Item *equal_item; while ((equal_item= it++)) { - field_item= (Item_field *) (equal_item->real_item()); - li.rewind(); - while ((fi= li++)) - { - if (field_item->field == - ((Item_field *) (fi->left_it->real_item()))->field) - { - new_item= fi->right_it; - break; - } - } + corresonding_item= + equal_item->get_corresponding_field_in_insubq(subq_pred); + if (corresonding_item) + return corresonding_item; } + return NULL; } else + return item->get_corresponding_field_in_insubq(subq_pred); +} + + +Item *Item_field::in_subq_field_transformer_for_where(THD *thd, uchar *arg) +{ + Item_in_subselect *subq_pred= (Item_in_subselect *)arg; + Item *producing_item= get_corresponding_item(thd, this, subq_pred); + if (producing_item) + return producing_item->build_clone(thd); + return this; +} + +Item *Item_direct_view_ref::in_subq_field_transformer_for_where(THD *thd, + uchar *arg) +{ + if (item_equal) { - li.rewind(); - while ((fi= li++)) - { - if (field_item->field == - ((Item_field *) (fi->left_it->real_item()))->field) - { - new_item= fi->right_it; - break; - } - } + Item_in_subselect *subq_pred= (Item_in_subselect *)arg; + Item *producing_item= get_corresponding_item(thd, this, subq_pred); + DBUG_ASSERT (producing_item != NULL); + return producing_item->build_clone(thd); } + return this; +} + + +/** + @brief + Transforms item so it can be pushed into the IN subquery HAVING clause + + @param thd the thread handle + @param in_item the item for which pushable item should be created + @param subq_pred the IN subquery predicate + + @details + This method finds for in_item that is a field from the left part of the + IN subquery predicate subq_pred its corresponding item from the right part + of subq_pred. + If corresponding item is found, a shell for this item is created. + This shell can be pushed into the HAVING part of subq_pred select. + + @retval <item*> reference to the created corresonding item shell for in_item + @retval NULL if mistake occurs +*/ +static Item* +get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item, + Item_in_subselect *subq_pred) +{ + Item *new_item= get_corresponding_item(thd, in_item, subq_pred); if (new_item) { @@ -6471,7 +6502,6 @@ Item *get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item, return ref; } DBUG_ASSERT(0); - return NULL; } @@ -6503,11 +6533,12 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd, sel and saves them with their corresponding items from fields. */ -void grouping_fields_in_the_in_subq_left_part(THD *thd, +bool grouping_fields_in_the_in_subq_left_part(THD *thd, st_select_lex *sel, List<In_subq_field> *fields, ORDER *grouping_list) { + DBUG_ENTER("grouping_fields_in_the_in_subq_left_part"); sel->grouping_tmp_fields.empty(); List_iterator<In_subq_field> it(*fields); In_subq_field *item; @@ -6521,54 +6552,62 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd, ((Item_field *)(item->left_it->real_item()))->field; Grouping_tmp_field *grouping_tmp_field= new Grouping_tmp_field(field, item->right_it); - sel->grouping_tmp_fields.push_back(grouping_tmp_field); + if (sel->grouping_tmp_fields.push_back(grouping_tmp_field, + thd->mem_root)) + DBUG_RETURN(TRUE); } } } + DBUG_RETURN(FALSE); } /** @brief - Extract the condition that depends only on the fields from the - left part of the IN subquery and push it into the select of - the right part of the IN subquery + Extract condition that can be pushed into select of this IN subquery @param thd the thread handle @param cond current condition @details - How the pushdown is made on the example: - - select * from t1 - where a>3 and b>10 and - (a,b) in (select x,max(y) from t2 group by x); - - --> - - select * from t1 - where a>3 and b>10 and - (a,b) in (select x,max(y) - from t2 - where x>3 - group by x - having max(y)>10); - - The implementation scheme: - - 1. It extracts the condition extr from condition cond that depends only on - the fields of the left part of the IN subquery (left_part). - 2. Finds fields F_group in the right part of the IN subquery (right_part) - that are used in the GROUP BY. - 3. Extracts from the extr condition cond_where that depends only on the - those fields from the left_part that stay at the same places in the left_part - (have the same indexes) as the F_group fields in the projection of the - right_part. - 4. Transforms cond_where so it can be pushed into the WHERE clause of the - right_part and delete cond_where from the extr. - 5. Transforms extr so it can be pushed into the HAVING clause of the right_part + This functiom builds the least restrictive condition depending only on + the list of fields of the left part of this IN subquery predicate that + can be extracted from the given condition cond and pushes it into + this IN subquery. + + Example of the transformation: + + SELECT * FROM t1 + WHERE a>3 AND b>10 AND + (a,b) IN (SELECT x,MAX(y) FROM t2 GROUP BY x); + + => + + SELECT * FROM t1 + WHERE a>3 AND b>10 AND + (a,b) IN (SELECT x,max(y) + FROM t2 + WHERE x>3 + GROUP BY x + HAVING MAX(y)>10); + + + In details: + 1. Check what pushable formula can be extracted from cond + 2. Build a clone PC of the formula that can be extracted + 3. If there is no HAVING clause prepare PC to be conjuncted with + WHERE clause of this subquery. Otherwise do 4-7. + 4. Check what formula PC_where can be extracted from PC to be pushed + into the WHERE clause of the subquery + 5. Build PC_where and if PC_where is a conjunct of PC remove it from PC + getting PC_having + 6. Prepare PC_where to be conjuncted with the WHERE clause of + the IN subquery + 7. Prepare PC_having to be conjuncted with the HAVING clause of + the IN subquery + @note - This method is similar with pushdown_cond_for_derived() + This method is similar to pushdown_cond_for_derived() @retval TRUE if an error occurs @retval FALSE otherwise @@ -6577,6 +6616,8 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd, bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) { DBUG_ENTER("Item_in_subselect::pushdown_cond_for_in_subquery"); + Item *remaining_cond= NULL; + if (!cond) DBUG_RETURN(FALSE); @@ -6589,13 +6630,14 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) DBUG_RETURN(FALSE); /* - Create the list of In_subq_field items for this IN subquery: - it consists of the pairs of fields from the left part of the IN subquery - 'left_part' and the respective fields from the select of the right part of - the IN subquery 'right_part' (fields that stay on the same places as fields - of left_part in the projection list of right_part). + Create a list of In_subq_field items for this IN subquery. + It consists of the pairs of fields from the left part of this IN subquery + predicate 'left_part' and the respective fields from the select of the + right part of the IN subquery 'sel' (the field from left_part with the + corresponding field from the sel projection list). + Attach this list to the IN subquery. */ - comparable_fields.empty(); + corresponding_fields.empty(); List_iterator_fast<Item> it(sel->join->fields_list); Item *item; for (uint i= 0; i < left_expr->cols(); i++) @@ -6606,104 +6648,66 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) if (elem->real_item()->type() != Item::FIELD_ITEM) continue; - comparable_fields.push_back(new In_subq_field(elem, item)); + if (corresponding_fields.push_back(new In_subq_field(elem, item))) + DBUG_RETURN(TRUE); } - /* - Build the new condition from cond that can be pushed into sel - */ + /* 1. Check what pushable formula can be extracted from cond */ Item *extracted_cond; - cond->check_pushable_cond(0, this); - extracted_cond= cond->build_pushable_cond(thd, 0, this); - /* - Nothing to push - */ + cond->check_pushable_cond(&Item::pushable_cond_checker_for_subquery, + (uchar *)this); + /* 2. Build a clone PC of the formula that can be extracted */ + extracted_cond= + cond->build_pushable_cond(thd, + &Item::pushable_equality_checker_for_subquery, + (uchar *)this); + /* Nothing to push */ if (!extracted_cond) { DBUG_RETURN(FALSE); } + /* Collect fields that are used in the GROUP BY of sel */ st_select_lex *save_curr_select= thd->lex->current_select; - thd->lex->current_select= sel; - if (sel->have_window_funcs()) { if (sel->group_list.first || sel->join->implicit_grouping) goto exit; ORDER *common_partition_fields= - sel->find_common_window_func_partition_fields(thd); + sel->find_common_window_func_partition_fields(thd); if (!common_partition_fields) goto exit; - Item *cond_over_partition_fields; - grouping_fields_in_the_in_subq_left_part(thd, sel, &comparable_fields, - common_partition_fields); - - sel->check_cond_extraction_for_grouping_fields(extracted_cond); - cond_over_partition_fields= - sel->build_cond_for_grouping_fields(thd, extracted_cond, true); - - if (cond_over_partition_fields) - cond_over_partition_fields= cond_over_partition_fields->transform(thd, - &Item::grouping_field_transformer_for_where, (uchar*) sel); - - if (cond_over_partition_fields) - { - cond_over_partition_fields->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - sel->cond_pushed_into_where= cond_over_partition_fields; - } - goto exit; + if (grouping_fields_in_the_in_subq_left_part(thd, sel, &corresponding_fields, + common_partition_fields)) + DBUG_RETURN(TRUE); } + else if (grouping_fields_in_the_in_subq_left_part(thd, sel, + &corresponding_fields, + sel->group_list.first)) + DBUG_RETURN(TRUE); + /* Do 4-6. */ + sel->pushdown_cond_into_where_clause(thd, extracted_cond, + &remaining_cond, + &Item::in_subq_field_transformer_for_where, + (uchar *) this); + if (!remaining_cond) + goto exit; /* - Checks what can be pushed into the WHERE clause of sel from the - extracted condition - */ - Item *cond_over_grouping_fields; - grouping_fields_in_the_in_subq_left_part(thd, sel, &comparable_fields, - sel->group_list.first); - sel->check_cond_extraction_for_grouping_fields(extracted_cond); - cond_over_grouping_fields= - sel->build_cond_for_grouping_fields(thd, extracted_cond, true); - - /* - Transforms the references to the left_part fields so they can be pushed - into sel of the WHERE clause. - */ - if (cond_over_grouping_fields) - cond_over_grouping_fields= - cond_over_grouping_fields->transform(thd, - &Item::grouping_field_transformer_for_where, - (uchar*) sel); - - if (cond_over_grouping_fields) - { - /* - Removes from extracted_cond all parts that can be pushed into the - WHERE clause - */ - extracted_cond= remove_pushed_top_conjuncts(thd, extracted_cond); - - cond_over_grouping_fields->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - sel->cond_pushed_into_where= cond_over_grouping_fields; - - if (!extracted_cond) - goto exit; - } - /* - Transforms the references to the left_part fields so the transformed - condition can be pushed into sel of the HAVING clause + 7. Prepare PC_having to be conjuncted with the HAVING clause of + the IN subquery */ - extracted_cond= extracted_cond->transform(thd, - &Item::in_subq_field_transformer_for_having, - (uchar*) this); - if (!extracted_cond) + remaining_cond= + remaining_cond->transform(thd, + &Item::in_subq_field_transformer_for_having, + (uchar *)this); + if (!remaining_cond) goto exit; - extracted_cond->walk(&Item::cleanup_excluding_const_fields_processor, 0, 0); - sel->cond_pushed_into_having= extracted_cond; + remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor, + 0, 0); + sel->cond_pushed_into_having= remaining_cond; exit: thd->lex->current_select= save_curr_select; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 1c62a38..dd7f93e 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1208,25 +1208,59 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) /** @brief - Extract the condition depended on derived table/view and pushed it there + Extract condition that can be pushed into a derived table/view - @param thd The thread handle - @param cond The condition from which to extract the pushed condition - @param derived The reference to the derived table/view + @param thd the thread handle + @param cond current condition + @param derived the reference to the derived table/view @details - This functiom builds the most restrictive condition depending only on - the derived table/view that can be extracted from the condition cond. - The built condition is pushed into the having clauses of the - selects contained in the query specifying the derived table/view. - The function also checks for each select whether any condition depending - only on grouping fields can be extracted from the pushed condition. - If so, it pushes the condition over grouping fields into the where - clause of the select. - - @retval - true if an error is reported - false otherwise + This functiom builds the least restrictive condition depending only on + the derived table/view that can be extracted from the given condition + cond and pushes it into the derived table/view. + + Example of the transformation: + + SELECT * + FROM t1, + ( + SELECT x,MAX(y) AS max_y + FROM t2 + GROUP BY x + ) AS d_tab + WHERE d_tab.x>1 AND d_tab.max_y<30; + + => + + SELECT * + FROM t1, + ( + SELECT x,z,MAX(y) AS max_y + FROM t2 + WHERE x>1 + HAVING max_y<30 + GROUP BY x + ) AS d_tab + WHERE d_tab.x>1 AND d_tab.max_y<30; + + In details: + 1. Check what pushable formula can be extracted from cond + 2. Build a clone PC of the formula that can be extracted + + Do for every select specifying derived table/view: + 3. If there is no HAVING clause prepare PC to be conjuncted with + WHERE clause of the select. Otherwise do 4-7. + 4. Check what formula PC_where can be extracted from PC to be pushed + into the WHERE clause of the select + 5. Build PC_where and if PC_where is a conjunct of PC remove it from PC + getting PC_having + 6. Prepare PC_where to be conjuncted with the WHERE clause of the select + 7. Prepare PC_having to be conjuncted with the HAVING clause of the select + @note + This method is similar to pushdown_cond_for_in_subquery() + + @retval TRUE if an error occurs + @retval FALSE otherwise */ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) @@ -1266,62 +1300,25 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!some_select_allows_cond_pushdown) DBUG_RETURN(false); - /* - Build the most restrictive condition extractable from 'cond' - that can be pushed into the derived table 'derived'. - All subexpressions of this condition are cloned from the - subexpressions of 'cond'. - This condition has to be fixed yet. - */ + /* 1. Check what pushable formula can be extracted from cond */ Item *extracted_cond; - cond->check_pushable_cond(derived->table->map, 0); - extracted_cond= cond->build_pushable_cond(thd, derived->table->map, 0); + cond->check_pushable_cond(&Item::pushable_cond_checker_for_derived, + (uchar *)(&derived->table->map)); + /* 2. Build a clone PC of the formula that can be extracted */ + extracted_cond= + cond->build_pushable_cond(thd, + &Item::pushable_equality_checker_for_derived, + ((uchar *)&derived->table->map)); if (!extracted_cond) { /* Nothing can be pushed into the derived table */ DBUG_RETURN(false); } - /* Push extracted_cond into every select of the unit specifying 'derived' */ + st_select_lex *save_curr_select= thd->lex->current_select; for (; sl; sl= sl->next_select()) { Item *extracted_cond_copy; - if (!sl->cond_pushdown_is_allowed()) - continue; - thd->lex->current_select= sl; - if (sl->have_window_funcs()) - { - if (sl->join->group_list || sl->join->implicit_grouping) - continue; - ORDER *common_partition_fields= - sl->find_common_window_func_partition_fields(thd); - if (!common_partition_fields) - continue; - extracted_cond_copy= !sl->next_select() ? - extracted_cond : - extracted_cond->build_clone(thd); - if (!extracted_cond_copy) - continue; - - Item *cond_over_partition_fields;; - sl->collect_grouping_fields(thd, common_partition_fields); - sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy); - cond_over_partition_fields= - sl->build_cond_for_grouping_fields(thd, extracted_cond_copy, true); - if (cond_over_partition_fields) - cond_over_partition_fields= cond_over_partition_fields->transform(thd, - &Item::grouping_field_transformer_for_where, - (uchar*) sl); - if (cond_over_partition_fields) - { - cond_over_partition_fields->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - sl->cond_pushed_into_where= cond_over_partition_fields; - } - - continue; - } - /* For each select of the unit except the last one create a clone of extracted_cond @@ -1332,72 +1329,44 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!extracted_cond_copy) continue; - if (!sl->join->group_list && !sl->with_sum_func) - { - /* extracted_cond_copy is pushed into where of sl */ - extracted_cond_copy= extracted_cond_copy->transform(thd, - &Item::derived_field_transformer_for_where, - (uchar*) sl); - if (extracted_cond_copy) - { - extracted_cond_copy->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - sl->cond_pushed_into_where= extracted_cond_copy; - } - - continue; - } - - /* - Figure out what can be extracted from the pushed condition - that could be pushed into the where clause of sl - */ - Item *cond_over_grouping_fields; - sl->collect_grouping_fields(thd, sl->join->group_list); - sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy); - cond_over_grouping_fields= - sl->build_cond_for_grouping_fields(thd, extracted_cond_copy, true); - - /* - Transform the references to the 'derived' columns from the condition - pushed into the where clause of sl to make them usable in the new context - */ - if (cond_over_grouping_fields) - cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, - &Item::grouping_field_transformer_for_where, - (uchar*) sl); - - if (cond_over_grouping_fields) + /* Collect fields that are used in the GROUP BY of sl */ + if (sl->have_window_funcs()) { - /* - In extracted_cond_copy remove top conjuncts that - has been pushed into the where clause of sl - */ - extracted_cond_copy= remove_pushed_top_conjuncts(thd, extracted_cond_copy); - - cond_over_grouping_fields->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - sl->cond_pushed_into_where= cond_over_grouping_fields; - - if (!extracted_cond_copy) + if (sl->join->group_list || sl->join->implicit_grouping) + continue; + ORDER *common_partition_fields= + sl->find_common_window_func_partition_fields(thd); + if (!common_partition_fields) continue; + sl->collect_grouping_fields(thd, common_partition_fields); } + else + sl->collect_grouping_fields(thd, sl->join->group_list); + + Item *remaining_cond= NULL; + /* Do 4-6 */ + sl->pushdown_cond_into_where_clause(thd, extracted_cond_copy, + &remaining_cond, + &Item::derived_field_transformer_for_where, + (uchar *) sl); + if (!remaining_cond) + continue; /* - Transform the references to the 'derived' columns from the condition - pushed into the having clause of sl to make them usable in the new context + 7. Prepare PC_having to be conjuncted with the HAVING clause of + the select */ - extracted_cond_copy= extracted_cond_copy->transform(thd, - &Item::derived_field_transformer_for_having, - (uchar*) sl); - if (!extracted_cond_copy) + remaining_cond= + remaining_cond->transform(thd, + &Item::derived_field_transformer_for_having, + (uchar *) sl); + if (!remaining_cond) continue; - extracted_cond_copy->walk(&Item::cleanup_excluding_const_fields_processor, - 0, 0); - sl->cond_pushed_into_having= extracted_cond_copy; + remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor, + 0, 0); + sl->cond_pushed_into_having= remaining_cond; } thd->lex->current_select= save_curr_select; DBUG_RETURN(false); } - diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index ddb0aa5..4e01447 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -7224,7 +7224,7 @@ st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond) to figure out whether a subformula depends only on these fields or not. @note The built condition C is always implied by the condition cond - (cond => C). The method tries to build the most restictive such + (cond => C). The method tries to build the least restictive such condition (i.e. for any other condition C' such that cond => C' we have C => C'). @note @@ -7237,7 +7237,7 @@ st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond) */ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond, - bool no_top_clones) + bool no_top_clones) { if (cond->get_extraction_flag() == FULL_EXTRACTION_FL) { @@ -7265,17 +7265,17 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond, { if (item->get_extraction_flag() == NO_EXTRACTION_FL) { - DBUG_ASSERT(cond_and); - item->clear_extraction_flag(); - continue; + DBUG_ASSERT(cond_and); + item->clear_extraction_flag(); + continue; } Item *fix= build_cond_for_grouping_fields(thd, item, - no_top_clones & cond_and); + no_top_clones & cond_and); if (!fix) { - if (cond_and) - continue; - break; + if (cond_and) + continue; + break; } new_cond->argument_list()->push_back(fix, thd->mem_root); } @@ -7283,7 +7283,7 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond, if (!cond_and && item) { while((item= li++)) - item->clear_extraction_flag(); + item->clear_extraction_flag(); return 0; } switch (new_cond->argument_list()->elements) @@ -7496,3 +7496,127 @@ bool SELECT_LEX::vers_push_field(THD *thd, TABLE_LIST *table, const LEX_CSTRING return false; } + +/** + @brief + Extract from given item a condition pushable into WHERE clause + + @param thd the thread handle + @param cond the item to extract a condition to be pushed + into WHERE + @param remaining_cond the condition that will remain of cond after + the pushdown of its parts into the WHERE clause + @param transformer the transformer callback function to be + applied to the condition so it can be pushed + down into the WHERE clause of this select + @param arg parameter to be passed to the transformer + + @details + This method checks if cond entirely or its parts can be + pushed into the WHERE clause of this select and prepares it for pushing. + + First it checks wherever this select doesn't have any aggregation function + in its projection and GROUP BY clause. If so cond can be entirely + pushed into the WHERE clause of this select but before its fields should + be transformed with transformer_for_where to make it pushable. + + Otherwise the method checks wherever any condition depending only on + grouping fields can be extracted from cond. If there is any it prepares it + for pushing using grouping_field_transformer_for_where and if it happens to + be a conjunct of cond it removes it from cond. It saves the result of + removal in remaining_cond. + The extracted condition is saved in cond_pushed_into_where of this select. + + @note + When looking for pushable condition the method considers only the grouping + fields from the list grouping_tmp_fields whose elements are of the type + Grouping_tmp_field. This list must be prepared before the call of the + function. + + @note + This method is called for pushdown conditions into materialized + derived tables/views optimization. + Item::derived_field_transformer_for_where is passed as the actual + callback function. + Also it is called for pushdown conditions into materialized IN subqueries. + Item::in_subq_field_transformer_for_where is passed as the actual + callback function. +*/ + +void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, + Item **remaining_cond, + Item_transformer transformer, + uchar *arg) +{ + if (!cond_pushdown_is_allowed()) + return; + thd->lex->current_select= this; + if (have_window_funcs()) + { + Item *cond_over_partition_fields; + check_cond_extraction_for_grouping_fields(cond); + cond_over_partition_fields= + build_cond_for_grouping_fields(thd, cond, true); + if (cond_over_partition_fields) + cond_over_partition_fields= cond_over_partition_fields->transform(thd, + &Item::grouping_field_transformer_for_where, + (uchar*) this); + if (cond_over_partition_fields) + { + cond_over_partition_fields->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond_over_partition_fields; + } + + return; + } + + if (!join->group_list && !with_sum_func) + { + cond= + cond->transform(thd, transformer, arg); + if (cond) + { + cond->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond; + } + + return; + } + + /* + Figure out what can be extracted from cond + that could be pushed into the WHERE clause of this select + */ + Item *cond_over_grouping_fields; + check_cond_extraction_for_grouping_fields(cond); + cond_over_grouping_fields= + build_cond_for_grouping_fields(thd, cond, true); + + /* + Transform the references to the columns from the cond + pushed into the WHERE clause of this select to make them usable in + the new context + */ + if (cond_over_grouping_fields) + cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, + &Item::grouping_field_transformer_for_where, + (uchar*) this); + + if (cond_over_grouping_fields) + { + + /* + In cond remove top conjuncts that has been pushed into the WHERE + clause of this select + */ + cond= remove_pushed_top_conjuncts(thd, cond); + + cond_over_grouping_fields->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond_over_grouping_fields; + } + + *remaining_cond= cond; +} diff --git a/sql/sql_lex.h b/sql/sql_lex.h index e44453a..57f7972 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1276,6 +1276,11 @@ class st_select_lex: public st_select_lex_node bool cond_pushdown_is_allowed() const { return !olap && !explicit_limit && !tvc; } + void pushdown_cond_into_where_clause(THD *thd, Item *extracted_cond, + Item **remaining_cond, + Item_transformer transformer, + uchar *arg); + private: bool m_non_agg_field_used; bool m_agg_func_used; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 58f7a0e..5b7940a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1781,7 +1781,7 @@ JOIN::optimize_inner() while ((tbl= li++)) if (tbl->jtbm_subselect) { - if (tbl->jtbm_subselect->pushdown_cond_for_in_subquery(thd, conds)) + if (tbl->jtbm_subselect->pushdown_cond_for_in_subquery(thd, conds)) DBUG_RETURN(1); } }

1 0

[Commits] 4968049: MDEV-11084 Select statement with partition selection against MyISAM
by holyfoot＠askmonty.org 28 Apr '18

28 Apr '18

revision-id: 4968049799193394d442f26b4e3a8d95b185be72 (mariadb-10.3.6-70-g4968049) parent(s): 9df0eab32792adb436a3fda82b175b82f4d6b393 committer: Alexey Botchkov timestamp: 2018-04-28 15:16:45 +0400 message: MDEV-11084 Select statement with partition selection against MyISAM table opens all partitions. Not-used partitions are not closed now. --- sql/ha_partition.cc | 23 +++++++++++++++-------- 1 file changed, 15 insertions(+), 8 deletions(-) diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 30d4b33..92357ae 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -8351,7 +8351,7 @@ int ha_partition::open_read_partitions(char *name_buff, size_t name_buff_size) { handler **file; char *name_buffer_ptr; - int error; + int error= 0; name_buffer_ptr= m_name_buffer_ptr; file= m_file; @@ -8362,13 +8362,20 @@ int ha_partition::open_read_partitions(char *name_buff, size_t name_buff_size) int is_open= bitmap_is_set(&m_opened_partitions, n_file); int should_be_open= bitmap_is_set(&m_part_info->read_partitions, n_file); - if (is_open && !should_be_open) - { - if ((error= (*file)->ha_close())) - goto err_handler; - bitmap_clear_bit(&m_opened_partitions, n_file); - } - else if (!is_open && should_be_open) + /* + TODO: we can close some opened partitions if they're not + used in the query. It probably should be syncronized with the + table_open_cache value. + + if (is_open && !should_be_open) + { + if ((error= (*file)->ha_close())) + goto err_handler; + bitmap_clear_bit(&m_opened_partitions, n_file); + } + else + */ + if (!is_open && should_be_open) { LEX_CSTRING save_connect_string= table->s->connect_string; if ((error= create_partition_name(name_buff, name_buff_size,

1 0