- commits - lists.mariadb.org

[Commits] 40bed2d: MyRocks: Add files lost during the merge
by psergey＠askmonty.org 16 Apr '18

16 Apr '18

revision-id: 40bed2d39e0409d3a1ff468f792ec00ae0c8e7df parent(s): ce4149fc9b5eda464919cefb6ea22d02879a6882 committer: Sergei Petrunia branch nick: 10.2-r10 timestamp: 2018-04-16 11:06:43 +0300 message: MyRocks: Add files lost during the merge Include files that upstream has in mysql-test/include and so they were not picked up by the merge process --- .../rocksdb/include/restart_mysqld_with_invalid_option.inc | 8 ++++++++ .../rocksdb/include/start_mysqld_with_option.inc | 14 ++++++++++++++ 2 files changed, 22 insertions(+) diff --git a/storage/rocksdb/mysql-test/rocksdb/include/restart_mysqld_with_invalid_option.inc b/storage/rocksdb/mysql-test/rocksdb/include/restart_mysqld_with_invalid_option.inc new file mode 100644 index 0000000..8eef7ed --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/include/restart_mysqld_with_invalid_option.inc @@ -0,0 +1,8 @@ +--source include/shutdown_mysqld.inc + +# Expect the server to fail to come up with these options +--error 1 +--exec $MYSQLD_CMD --plugin_load=$HA_ROCKSDB_SO $_mysqld_option + +# Restart the server with the default options +--source include/start_mysqld.inc diff --git a/storage/rocksdb/mysql-test/rocksdb/include/start_mysqld_with_option.inc b/storage/rocksdb/mysql-test/rocksdb/include/start_mysqld_with_option.inc new file mode 100644 index 0000000..73e30b3 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/include/start_mysqld_with_option.inc @@ -0,0 +1,14 @@ +# Include this script only after using shutdown_mysqld.inc +# where $_expect_file_name was initialized. +# Write file to make mysql-test-run.pl start up the server again +--exec echo "restart:$_mysqld_option" > $_expect_file_name + +# Turn on reconnect +--enable_reconnect + +# Call script that will poll the server waiting for it to be back online again +--source include/wait_until_connected_again.inc + +# Turn off reconnect again +--disable_reconnect +

1 0

[Commits] 608d677: Post review changes for MDEV-12387
by galina.shalygina＠mariadb.com 15 Apr '18

15 Apr '18

revision-id: 608d6773bff86b82d9fa35e77065cb0b6ed9e547 (mariadb-10.3.4-58-g608d677) parent(s): 5db956f9df61eb11410cc8331853d43f6fd774d0 author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-04-16 01:24:18 +0200 message: Post review changes for MDEV-12387 Mistake resolved, comments added. --- sql/opt_subselect.cc | 133 +++++++++++++++++++++++---------------------------- 1 file changed, 61 insertions(+), 72 deletions(-) diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 69b96ec..8f6483e 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5527,36 +5527,30 @@ Item *search_for_missing_parts_of_equalities(THD *thd, /** @brief - Attach new equalities to the WHERE-clause condition + Attach the equalities to the WHERE-clause condition - @param join join of the select where equalities should be - attached + @param join join where equalities should be attached @param eq_list the set of the equalities to add @details The method modifies the condition of the join (the condition of the - WHERE clause) by adding new equalities from the eq_list. It adds new + WHERE clause) by adding new equalities from eq_list. It adds new equalities to the remain multiple equalities of the WHERE clause condition - or if it is not possible just attaches them to the WHERE clause condition. - - First multiple equalities are disjoined from the join condition to avoid - repetitions. These multiple equalities are merged with the equalities from - the eq_list. - - Example: - - MULT_EQ(t1.a, t1.b) and (t1.a = 2) will become - MULT_EQ(2, t1.a, t1.b) - - Sometimes it is not possible and in this case the eqialities that can't - be merged are saved to be attached to the condition later. - This situation can appear after the optimize of the IN subquery - predicate it is transformed in the knowlingly false equality. - For example, (3 = 1) equality. - - Than a new condition is created. It consists of the old condition from - which multiple equalities were disjoint, new multiple equalities and - the equalities that weren't attached to the multiple equalities. + and attaches them to the WHERE clause condition. + + First all multiple equalities are disjoined from the WHERE clause condition + to avoid repetitions. + Multiple equalities for the on expression of join are merged with the + equalities from eq_list. For example, MULT_EQ(t1.a, t1.b) and (t1.a = 2) + will become MULT_EQ(2, t1.a, t1.b) + Sometimes merge is not possible and in this case the equalities that can't + be merged are saved to be attached to the condition later. This situation + can appear after the optimize of the IN subquery predicate if it is + transformed in the knowingly false equality. For example, (3 = 1) equality. + + Finally, a new condition is created. It consists of the old condition from + which multiple equalities were disjoint, new multiple equalities and the + equalities from eq_list that weren't merged with the multiple equalities. @retval TRUE if an error occurs @retval FALSE otherwise @@ -5672,16 +5666,16 @@ bool join_equalities_after_optimize_cond(JOIN *join, ((Item_cond_and *)conds)->m_cond_equal.copy(cond_equal); cond_equal.current_level= ((Item_cond_and *)conds)->m_cond_equal.current_level; - if (mult_eq_cnt == 1) - { - it.rewind(); - conds= it++; - } - else + if (and_args) { and_args->append((List<Item> *)&cond_equal.current_level); conds= search_for_missing_parts_of_equalities(thd, 0, conds, &cond_equal); } + else if (mult_eq_cnt == 1) + { + it.rewind(); + conds= it++; + } join->conds= conds; @@ -5792,21 +5786,25 @@ bool execute_degenerate_jtbm_semi_join(THD *thd, /** - @brief Find degenerate jtbm semi joins + @brief + Execute degenerate jtbm semi joins before optimize_cond() for parent @param join the parent join for jtbm semi joins - @param join_list the list of TABLE_LIST objects where jtbm semi join can occur - @param eq_list IN/OUT the list where to add produced equalities + @param join_list the list of tables where jtbm semi joins are processed + @param eq_list IN/OUT the list where to add equalities produced after + materialization of single-row degenerate jtbm semi joins @details The method traverses join_list trying to find any degenerate jtbm semi joins for subqueries of IN predicates. For each degenerate jtbm - semi join execute_degenerate_jtbm_semi_join is called. + semi join execute_degenerate_jtbm_semi_join() is called. As a result + of this call new equalities that substitute for single-row materialized + jtbm semi join are added to eq_list. - In the case when a TABLE_LIST object is nested in another TABLE_LIST - 'nested_join' method is recursively called for the list - of TABLE_LIST objects of the 'nested_join' trying to find in the list - any degenerate jtbm semi joins. + In the case when a table is nested in another table 'nested_join' the + method is recursively called for the join_list of the 'nested_join' trying + to find in the list any degenerate jtbm semi joins. Currently a jtbm semi + join may occur in a mergeable semi join nest. @retval TRUE if an error occurs @retval FALSE otherwise @@ -5854,48 +5852,39 @@ setup_degenerate_jtbm_semi_joins(JOIN *join, /** - @brief Prepare jtbm semi joins for materialization + @brief + Optimize jtbm semi joins for materialization @param join the parent join for jtbm semi joins - @param join_list the list of TABLE_LIST objects where jtbm semi join can occur + @param join_list the list of TABLE_LIST objects where jtbm semi join + can occur @param eq_list IN/OUT the list where to add produced equalities @details + This method is called by the optimizer after the call of + optimize_cond() for parent select. The method traverses join_list trying to find any jtbm semi joins for - subqueries from IN predicates. It either materializes the jtbm semi join - or creates a temporary table into which the jtbm semi join will be - materialized. The IN subquery predicate is optimized first to get the - information about how it should be handled. - - The first case is when the jtbm semi join is degenerate. Usually the - jtbm semi join is found degenerate before the optimization and on this - step it is already materialized by the setup_degenerate_jtbm_semi_joins - method. Btw there can appear a situation when the jtbm semi join becomes - degenerate only after the optimization of the IN subquery predicate. - In this case the execute_degenerate_jtbm_semi_join method is called. - - Example taken from subselect.test: - - SELECT * FROM t1 - WHERE 4 IN ( - SELECT MAX(b) - FROM t2 - WHERE EXISTS (SELECT * FROM t1)); - - Only after the optimization of EXISTS it is found that the WHERE-clause - of the IN subquery predicate is impossible. So the jtbm semi join for - the subquery from this predicate is degenerate and should be materialized. - - In the other case the IN subquery predicate should be computed via a - hash semi-join. It creates a temporary table in which the jtbm semi join - of the subquery from this predicate will be materialized. - The equalities between all pairs of expressions of the IN subquery - predicate are attached to the eq_list to be later conjuncted with the + subqueries from IN predicates and optimizes them. + After the optimization some of jtbm semi joins may become degenerate. + For example the subquery SELECT MAX(b) FROM t2 from the query + SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2); + will become degenerate if there is an index on t2.b. + If a subquery becomes degenerate it is handled by the function + execute_degenerate_jtbm_semi_join(). + + Otherwise the method creates a temporary table in which the subquery + of the jtbm semi join will be materialied. + + The function saves the equalities between all pairs of the expressions + from the left part of the IN subquery predicate and the corresponding + columns of the subquery from the predicate in eq_list appending them + to the list. The equalities of eq_list will be later conjucted with the condition of the WHERE clause. - In the case when a TABLE_LIST object is nested in another TABLE_LIST - the method is recursively called for the list of TABLE_LIST objects - of the nested join. + In the case when a table is nested in another table 'nested_join' the + method is recursively called for the join_list of the 'nested_join' trying + to find in the list any degenerate jtbm semi joins. Currently a jtbm semi + join may occur in a mergeable semi join nest. @retval TRUE if an error occurs @retval FALSE otherwise

1 0

[Commits] ce4149f: Post-merge fixes: make rocksdb.use_direct_reads_writes pass
by psergey＠askmonty.org 15 Apr '18

15 Apr '18

revision-id: ce4149fc9b5eda464919cefb6ea22d02879a6882 parent(s): 0fad97a9ecbcb6a68ae396ea6f7a1dd4d933279c committer: Sergei Petrunia branch nick: 10.2-r10 timestamp: 2018-04-15 21:15:21 +0300 message: Post-merge fixes: make rocksdb.use_direct_reads_writes pass --- .../rocksdb/r/use_direct_reads_writes.result | 35 ++++++---------------- .../rocksdb/t/use_direct_reads_writes.test | 1 - 2 files changed, 9 insertions(+), 27 deletions(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/use_direct_reads_writes.result b/storage/rocksdb/mysql-test/rocksdb/r/use_direct_reads_writes.result index f601f75..e845645 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/use_direct_reads_writes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/use_direct_reads_writes.result @@ -1,35 +1,18 @@ +call mtr.add_suppression("rocksdb"); +call mtr.add_suppression("Aborting"); +# This shows that RocksDB plugin is loaded: +select plugin_name, plugin_type from information_schema.plugins where plugin_name='RocksDB'; +plugin_name plugin_type +ROCKSDB STORAGE ENGINE Checking direct reads +FOUND 1 /enable both use_direct_reads/ in use_direct_reads_writes.err Checking direct writes +FOUND 1 /enable both use_direct_io_for_flush_and_compaction/ in use_direct_reads_writes.err Checking rocksdb_flush_log_at_trx_commit +FOUND 1 /rocksdb_flush_log_at_trx_commit needs to be/ in use_direct_reads_writes.err Validate flush_log settings when direct writes is enabled set global rocksdb_flush_log_at_trx_commit=0; set global rocksdb_flush_log_at_trx_commit=1; ERROR 42000: Variable 'rocksdb_flush_log_at_trx_commit' can't be set to the value of '1' set global rocksdb_flush_log_at_trx_commit=2; ERROR 42000: Variable 'rocksdb_flush_log_at_trx_commit' can't be set to the value of '2' -call mtr.add_suppression("rocksdb"); -call mtr.add_suppression("Aborting"); -# This shows that RocksDB plugin is loaded: -select plugin_name, plugin_type from information_schema.plugins where plugin_name='RocksDB'; -plugin_name plugin_type -ROCKSDB STORAGE ENGINE -# Check that ROCKSDB plugin is not loaded: -select plugin_name, plugin_type from information_schema.plugins where plugin_name='RocksDB'; -plugin_name plugin_type -# Check that MyRocks has printed an error message into server error log: -FOUND 1 /enable both use_direct_reads/ in mysqld.1.err -# Now, restart the server back with regular settings -select plugin_name, plugin_type from information_schema.plugins where plugin_name='RocksDB'; -plugin_name plugin_type -ROCKSDB STORAGE ENGINE -# -# Now, repeat the same with another set of invalid arguments -# -# Check that ROCKSDB plugin is not loaded: -select plugin_name, plugin_type from information_schema.plugins where plugin_name='RocksDB'; -plugin_name plugin_type -FOUND 1 /enable both use_direct_io_for_flush_and_compaction/ in mysqld.1.err -# Now, restart the server back with regular settings -select plugin_name, plugin_type from information_schema.plugins where plugin_name='RocksDB'; -plugin_name plugin_type -ROCKSDB STORAGE ENGINE diff --git a/storage/rocksdb/mysql-test/rocksdb/t/use_direct_reads_writes.test b/storage/rocksdb/mysql-test/rocksdb/t/use_direct_reads_writes.test index 550cbd2..8dfbe31 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/use_direct_reads_writes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/use_direct_reads_writes.test @@ -34,7 +34,6 @@ select plugin_name, plugin_type from information_schema.plugins where plugin_nam --source include/search_pattern_in_file.inc --remove_file $LOG -select plugin_name, plugin_type from information_schema.plugins where plugin_name='RocksDB'; # Verify invalid direct-writes and --rocksdb_flush_log_at_trx_commit combination at startup fails --echo Checking rocksdb_flush_log_at_trx_commit

1 0

[Commits] 9c2f39e: MDEV-14667 Assertion `used_parts > 0' failed in ha_partition::init_record_priority_queue.
by holyfoot＠askmonty.org 15 Apr '18

15 Apr '18

revision-id: 9c2f39ea72e0f11565016df4d13200ccfa5e0dca (mariadb-10.3.5-154-g9c2f39e) parent(s): 560743198604caf677c543db9719cef871df09ce committer: Alexey Botchkov timestamp: 2018-04-15 21:35:30 +0400 message: MDEV-14667 Assertion `used_parts > 0' failed in ha_partition::init_record_priority_queue. ha_partition::init_record_priority_queue() made tolerant to the case when all the partitions were pruned away. --- mysql-test/main/partition_pruning.result | 10 ++++++++++ mysql-test/main/partition_pruning.test | 13 +++++++++++++ sql/ha_partition.cc | 5 ++++- 3 files changed, 27 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/partition_pruning.result b/mysql-test/main/partition_pruning.result index 422132d..7986e09 100644 --- a/mysql-test/main/partition_pruning.result +++ b/mysql-test/main/partition_pruning.result @@ -3484,3 +3484,13 @@ select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) O a b c d 1 a b 1 drop table t1; +# +# MDEV-14667 Assertion `used_parts > 0' failed in ha_partition::init_record_priority_queue. +# +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int, c int, key(c,b)) partition by hash(b) partitions 2; +insert into t2 values (3,4),(5,6); +select straight_join * from t1, t2 where b != NULL; +a b c +drop table t1, t2; diff --git a/mysql-test/main/partition_pruning.test b/mysql-test/main/partition_pruning.test index 9d72e9c..592f5ba 100644 --- a/mysql-test/main/partition_pruning.test +++ b/mysql-test/main/partition_pruning.test @@ -1536,3 +1536,16 @@ select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) O drop table t1; +--echo # +--echo # MDEV-14667 Assertion `used_parts > 0' failed in ha_partition::init_record_priority_queue. +--echo # + +create table t1 (a int); +insert into t1 values (1),(2); + +create table t2 (b int, c int, key(c,b)) partition by hash(b) partitions 2; +insert into t2 values (3,4),(5,6); + +select straight_join * from t1, t2 where b != NULL; +drop table t1, t2; + diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 30d4b33..b0f82e8 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -5210,7 +5210,10 @@ bool ha_partition::init_record_priority_queue() { size_t alloc_len; uint used_parts= bitmap_bits_set(&m_part_info->read_partitions); - DBUG_ASSERT(used_parts > 0); + + if (used_parts == 0) /* Do nothing since no records expected. */ + DBUG_RETURN(false); + /* Allocate record buffer for each used partition. */ m_priority_queue_rec_len= m_rec_length + PARTITION_BYTES_IN_POS; if (!m_using_extended_keys)

1 0

[Commits] 0fad97a: Post-merge fixes: add a suppression for rocksdb.skip_validate_tmP_table
by psergey＠askmonty.org 15 Apr '18

15 Apr '18

revision-id: 0fad97a9ecbcb6a68ae396ea6f7a1dd4d933279c parent(s): 261d4755f4418d3a1b4dadf8d1ed5098ae3aa92f committer: Sergei Petrunia branch nick: 10.2-r10 timestamp: 2018-04-15 15:34:06 +0300 message: Post-merge fixes: add a suppression for rocksdb.skip_validate_tmP_table --- storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result | 1 + storage/rocksdb/mysql-test/rocksdb/t/skip_validate_tmp_table.test | 3 +++ 2 files changed, 4 insertions(+) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result b/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result index d324793..92906f2 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result @@ -3,6 +3,7 @@ show tables; Tables_in_test #mysql50#t1#sql-test t1 +call mtr.add_suppression('Invalid .old.. table or database name .t1#sql-test.'); set session debug_dbug="+d,gen_sql_table_name"; rename table t1 to t2; set session debug_dbug= "-d,gen_sql_table_name"; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/skip_validate_tmp_table.test b/storage/rocksdb/mysql-test/rocksdb/t/skip_validate_tmp_table.test index 80c366d2..c432146 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/skip_validate_tmp_table.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/skip_validate_tmp_table.test @@ -12,6 +12,9 @@ create table t1 (pk int primary key) engine=rocksdb; show tables; +# MariaDB produces a warning: +call mtr.add_suppression('Invalid .old.. table or database name .t1#sql-test.'); + # This will append '#sql-test' to the end of new name set session debug_dbug="+d,gen_sql_table_name"; rename table t1 to t2;

1 0

[Commits] 261d475: Post-merge fixes: rocksdb.check_ignore_unknown_options
by psergey＠askmonty.org 15 Apr '18

15 Apr '18

revision-id: 261d4755f4418d3a1b4dadf8d1ed5098ae3aa92f parent(s): 8496e84f05e69a31e93653468b6e0579e154e8d8 committer: Sergei Petrunia branch nick: 10.2-r10 timestamp: 2018-04-15 14:34:56 +0300 message: Post-merge fixes: rocksdb.check_ignore_unknown_options --- .../mysql-test/rocksdb/r/check_ignore_unknown_options.result | 1 + .../rocksdb/mysql-test/rocksdb/t/check_ignore_unknown_options.test | 7 ++++--- 2 files changed, 5 insertions(+), 3 deletions(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/check_ignore_unknown_options.result b/storage/rocksdb/mysql-test/rocksdb/r/check_ignore_unknown_options.result index 24c1b73..6ff4990 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/check_ignore_unknown_options.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/check_ignore_unknown_options.result @@ -1,6 +1,7 @@ select variable_name, variable_value from information_schema.global_variables where variable_name="rocksdb_ignore_unknown_options"; variable_name variable_value ROCKSDB_IGNORE_UNKNOWN_OPTIONS ON +FOUND 1 /RocksDB: Compatibility check against existing database options failed/ in my_restart.err select variable_name, variable_value from information_schema.global_variables where variable_name="rocksdb_ignore_unknown_options"; variable_name variable_value ROCKSDB_IGNORE_UNKNOWN_OPTIONS ON diff --git a/storage/rocksdb/mysql-test/rocksdb/t/check_ignore_unknown_options.test b/storage/rocksdb/mysql-test/rocksdb/t/check_ignore_unknown_options.test index 2521354..b4866de 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/check_ignore_unknown_options.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/check_ignore_unknown_options.test @@ -4,12 +4,13 @@ let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; let $error_log= $MYSQLTEST_VARDIR/log/my_restart.err; select variable_name, variable_value from information_schema.global_variables where variable_name="rocksdb_ignore_unknown_options"; ---exec find $MYSQLD_DATADIR/.rocksdb/OPTIONS* | sort -n | tail -1 | xargs -0 -I {} -t sh -c "echo hello=world>>{}" +--exec find $MYSQLD_DATADIR/#rocksdb/OPTIONS* | sort -n | tail -1 | xargs -0 -I {} -t sh -c "echo hello=world>>{}" --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --shutdown_server 10 + --error 1 ---exec $MYSQLD_CMD --rocksdb_ignore_unknown_options=0 --loose-console > $error_log 2>&1 +--exec $MYSQLD_CMD --plugin_load=$HA_ROCKSDB_SO --rocksdb_ignore_unknown_options=0 --loose-console --log-error=$error_log let SEARCH_FILE= $error_log; let SEARCH_PATTERN= RocksDB: Compatibility check against existing database options failed; @@ -17,5 +18,5 @@ let SEARCH_PATTERN= RocksDB: Compatibility check against existing database optio --enable_reconnect --exec echo "restart" > $restart_file --source include/wait_until_connected_again.inc ---exec find $MYSQLD_DATADIR/.rocksdb/OPTIONS* | sort -n | tail -1 | xargs -0 -I {} -t sh -c "sed -i '/hello=world/d' {}" +--exec find $MYSQLD_DATADIR/#rocksdb/OPTIONS* | sort -n | tail -1 | xargs -0 -I {} -t sh -c "sed -i '/hello=world/d' {}" select variable_name, variable_value from information_schema.global_variables where variable_name="rocksdb_ignore_unknown_options";

1 0

[Commits] 8496e84: Trivial post-merge fixes
by psergey＠askmonty.org 15 Apr '18

15 Apr '18

revision-id: 8496e84f05e69a31e93653468b6e0579e154e8d8 parent(s): 78e42153b51d6166b916a5bdb39e8726913c263e committer: Sergei Petrunia branch nick: 10.2-r10 timestamp: 2018-04-15 12:52:27 +0300 message: Trivial post-merge fixes --- storage/rocksdb/mysql-test/rocksdb/r/autoinc_debug.result | 12 ++++++------ .../mysql-test/rocksdb/r/skip_validate_tmp_table.result | 2 +- .../mysql-test/rocksdb/r/ttl_primary_read_filtering.result | 2 +- storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test | 12 ++++++------ 4 files changed, 14 insertions(+), 14 deletions(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/autoinc_debug.result b/storage/rocksdb/mysql-test/rocksdb/r/autoinc_debug.result index 79030e3..fe08cd7 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/autoinc_debug.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/autoinc_debug.result @@ -2,7 +2,7 @@ # Testing upgrading from server without merges for auto_increment # to new server with such support. # -set debug='+d,myrocks_autoinc_upgrade'; +set debug_dbug='+d,myrocks_autoinc_upgrade'; create table t (i int primary key auto_increment); insert into t values (); insert into t values (); @@ -20,7 +20,7 @@ select table_name, index_name, auto_increment from information_schema.rocksdb_ddl where table_name = 't'; table_name index_name auto_increment t PRIMARY NULL -set debug='-d,myrocks_autoinc_upgrade'; +set debug_dbug='-d,myrocks_autoinc_upgrade'; insert into t values (); insert into t values (); insert into t values (); @@ -56,7 +56,7 @@ insert into t values (); begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_before"; +set debug_dbug="+d,crash_commit_before"; commit; ERROR HY000: Lost connection to MySQL server during query select table_schema, table_name, auto_increment from information_schema.tables where table_name = 't'; @@ -69,7 +69,7 @@ max(i) begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_after_prepare"; +set debug_dbug="+d,crash_commit_after_prepare"; commit; ERROR HY000: Lost connection to MySQL server during query select table_schema, table_name, auto_increment from information_schema.tables where table_name = 't'; @@ -82,7 +82,7 @@ max(i) begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_after_log"; +set debug_dbug="+d,crash_commit_after_log"; commit; ERROR HY000: Lost connection to MySQL server during query select table_schema, table_name, auto_increment from information_schema.tables where table_name = 't'; @@ -95,7 +95,7 @@ max(i) begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_after"; +set debug_dbug="+d,crash_commit_after"; commit; ERROR HY000: Lost connection to MySQL server during query select table_schema, table_name, auto_increment from information_schema.tables where table_name = 't'; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result b/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result index df04874..d324793 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/skip_validate_tmp_table.result @@ -1,9 +1,9 @@ create table t1 (pk int primary key) engine=rocksdb; show tables; -set session debug_dbug="+d,gen_sql_table_name"; Tables_in_test #mysql50#t1#sql-test t1 +set session debug_dbug="+d,gen_sql_table_name"; rename table t1 to t2; set session debug_dbug= "-d,gen_sql_table_name"; show tables; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary_read_filtering.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary_read_filtering.result index 5bb3229..c66b179 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary_read_filtering.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_primary_read_filtering.result @@ -210,8 +210,8 @@ select variable_value-@a from information_schema.global_status where variable_na variable_value-@a 1 # Switching to connection 1 -select variable_value into @a from information_schema.global_status where variable_name='rocksdb_rows_filtered'; connection con1; +select variable_value into @a from information_schema.global_status where variable_name='rocksdb_rows_filtered'; SELECT * FROM t1; a 1 diff --git a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test index df67338..abcae8d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test @@ -7,7 +7,7 @@ --echo # to new server with such support. --echo # -set debug='+d,myrocks_autoinc_upgrade'; +set debug_dbug='+d,myrocks_autoinc_upgrade'; create table t (i int primary key auto_increment); insert into t values (); insert into t values (); @@ -20,7 +20,7 @@ select * from t; select table_name, index_name, auto_increment from information_schema.rocksdb_ddl where table_name = 't'; -set debug='-d,myrocks_autoinc_upgrade'; +set debug_dbug='-d,myrocks_autoinc_upgrade'; --source include/restart_mysqld.inc @@ -55,7 +55,7 @@ insert into t values (); begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_before"; +set debug_dbug="+d,crash_commit_before"; --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --error 2013 commit; @@ -71,7 +71,7 @@ select max(i) from t; begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_after_prepare"; +set debug_dbug="+d,crash_commit_after_prepare"; --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --error 2013 commit; @@ -87,7 +87,7 @@ select max(i) from t; begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_after_log"; +set debug_dbug="+d,crash_commit_after_log"; --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --error 2013 commit; @@ -103,7 +103,7 @@ select max(i) from t; begin; insert into t values (); insert into t values (); -set debug="+d,crash_commit_after"; +set debug_dbug="+d,crash_commit_after"; --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --error 2013 commit;

1 0

[Commits] a91fdd1: Fixed the bug mdev-15575 different results when using CTE and big_tables=1.
by IgorBabaev 15 Apr '18

15 Apr '18

revision-id: a91fdd1288b3cbc5616f2ef9e0ce04bf7f0ef9ce (mariadb-10.2.14-35-ga91fdd1) parent(s): 740fc2ae084f8f81990de557d696aefbc507752d author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-14 21:32:41 -0700 message: Fixed the bug mdev-15575 different results when using CTE and big_tables=1. This bug happened due to a defect of the implementation of the handler function ha_delete_all_rows() for the ARIA engine. The function maria_delete_all_rows() truncated the table, but it didn't touch the write cache, so the cache's write offset was not reset. In the scenario like in the function st_select_lex_unit::exec_recursive when first all records were deleted from the table and then several new records were added some metadata became inconsistent with the state of the cache. As a result the table scan function could not read records at the end of the table. The same defect could be found in the implementation of ha_delete_all_rows() for the MYISAM engine mi_delete_all_rows(). Additionally made late instantiation for the temporary table used to store rows that were used for each new iteration when executing a recursive CTE. --- mysql-test/r/cte_recursive.result | 17 +++++++++++++++++ mysql-test/t/cte_recursive.test | 12 ++++++++++++ sql/sql_union.cc | 31 ++++++++++++++++++------------- storage/maria/ma_delete_all.c | 3 +++ storage/myisam/mi_delete_all.c | 4 ++++ 5 files changed, 54 insertions(+), 13 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 4e9b4ae..55cd30f 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3063,3 +3063,20 @@ SELECT * FROM cte; 5 2 3 +# +# MDEV-15575: using recursive cte with big_tables enabled +# +set big_tables=1; +with recursive qn as +(select 123 as a union all select 1+a from qn where a<130) +select * from qn; +a +123 +124 +125 +126 +127 +128 +129 +130 +set big_tables=default; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index c3a5ed5..373d6ca 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2097,3 +2097,15 @@ WITH RECURSIVE cte AS UNION SELECT @c:=@c+1 FROM cte WHERE @c<3) SELECT * FROM cte; + +--echo # +--echo # MDEV-15575: using recursive cte with big_tables enabled +--echo # + +set big_tables=1; + +with recursive qn as +(select 123 as a union all select 1+a from qn where a<130) +select * from qn; + +set big_tables=default; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 19c9330..3fe8a24 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -214,19 +214,13 @@ select_union_recursive::create_result_table(THD *thd_arg, if (! (incr_table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, (ORDER*) 0, false, 1, options, HA_POS_ERROR, "", - !create_table, keep_row_order))) + true, keep_row_order))) return true; incr_table->keys_in_use_for_query.clear_all(); for (uint i=0; i < table->s->fields; i++) incr_table->field[i]->flags &= ~PART_KEY_FLAG; - if (create_table) - { - incr_table->file->extra(HA_EXTRA_WRITE_CACHE); - incr_table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); - } - TABLE *rec_table= 0; if (! (rec_table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, (ORDER*) 0, false, 1, @@ -270,8 +264,11 @@ void select_union_recursive::cleanup() if (incr_table) { - incr_table->file->extra(HA_EXTRA_RESET_STATE); - incr_table->file->ha_delete_all_rows(); + if (incr_table->is_created()) + { + incr_table->file->extra(HA_EXTRA_RESET_STATE); + incr_table->file->ha_delete_all_rows(); + } free_tmp_table(thd, incr_table); } @@ -1228,16 +1225,24 @@ bool st_select_lex_unit::exec_recursive() if (!was_executed) save_union_explain(thd->lex->explain); - if ((saved_error= incr_table->file->ha_delete_all_rows())) - goto err; - if (with_element->level == 0) { + if (!incr_table->is_created() && + instantiate_tmp_table(incr_table, + tmp_table_param->keyinfo, + tmp_table_param->start_recinfo, + &tmp_table_param->recinfo, + 0)) + DBUG_RETURN(1); + incr_table->file->extra(HA_EXTRA_WRITE_CACHE); + incr_table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); start= first_select(); if (with_element->with_anchor) end= with_element->first_recursive; } - + else if ((saved_error= incr_table->file->ha_delete_all_rows())) + goto err; + for (st_select_lex *sl= start ; sl != end; sl= sl->next_select()) { thd->lex->current_select= sl; diff --git a/storage/maria/ma_delete_all.c b/storage/maria/ma_delete_all.c index a14603b..ee75218 100644 --- a/storage/maria/ma_delete_all.c +++ b/storage/maria/ma_delete_all.c @@ -135,6 +135,9 @@ int maria_delete_all_rows(MARIA_HA *info) goto err; } + if (info->opt_flag & WRITE_CACHE_USED) + reinit_io_cache(&info->rec_cache, WRITE_CACHE, 0, 1, 1); + _ma_writeinfo(info, WRITEINFO_UPDATE_KEYFILE); #ifdef HAVE_MMAP /* Map again */ diff --git a/storage/myisam/mi_delete_all.c b/storage/myisam/mi_delete_all.c index 37fdf2d..c772e84 100644 --- a/storage/myisam/mi_delete_all.c +++ b/storage/myisam/mi_delete_all.c @@ -62,6 +62,10 @@ int mi_delete_all_rows(MI_INFO *info) if (mysql_file_chsize(info->dfile, 0, 0, MYF(MY_WME)) || mysql_file_chsize(share->kfile, share->base.keystart, 0, MYF(MY_WME))) goto err; + + if (info->opt_flag & WRITE_CACHE_USED) + reinit_io_cache(&info->rec_cache, WRITE_CACHE, 0, 1, 1); + (void) _mi_writeinfo(info,WRITEINFO_UPDATE_KEYFILE); DBUG_RETURN(0);

1 0

[Commits] 5db956f: Post review changes for MDEV-12387
by galina.shalygina＠mariadb.com 14 Apr '18

14 Apr '18

revision-id: 5db956f9df61eb11410cc8331853d43f6fd774d0 (mariadb-10.3.4-57-g5db956f) parent(s): f80281d8d8afec96d89e2f18342f0acd28b3e55e author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-04-14 19:14:25 +0200 message: Post review changes for MDEV-12387 Comments changed, test results added --- .../suite/sys_vars/r/optimizer_switch_basic.result | 36 +- .../sys_vars/r/sysvars_server_notembedded.result | 8 +- sql/item_subselect.h | 10 +- sql/opt_subselect.cc | 592 ++++++++++----------- sql/opt_subselect.h | 16 +- sql/sql_select.cc | 30 +- .../mysql-test/tokudb/r/ext_key_2_tokudb.result | 2 +- 7 files changed, 339 insertions(+), 355 deletions(-) diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result index 87c8379..bc22fe1 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,63 +1,63 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off set optimizer_switch = replace(@@optimizer_switch, '=off', '=on'); Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on set global optimizer_switch=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_switch' set global optimizer_switch=1e1; @@ -69,4 +69,4 @@ ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'foobar' SET @@global.optimizer_switch = @start_global_value; SELECT @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 9ee1d72..b9286aa 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2883,17 +2883,17 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SWITCH -SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on -GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_USE_CONDITION_SELECTIVITY diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 411407b..49dc582 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -905,12 +905,10 @@ class subselect_single_select_engine: public subselect_engine friend class subselect_hash_sj_engine; friend class Item_in_subselect; - friend bool - exec_engine_for_degenerated_jtbm_semi_join(THD *thd, - JOIN *join, - select_value_catcher **new_sink, - Item_in_subselect *subq_pred); - + friend bool execute_degenerate_jtbm_semi_join(THD *thd, + TABLE_LIST *tbl, + Item_in_subselect *subq_pred, + List<Item> &eq_list); }; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index d2b15eb..69b96ec 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5431,12 +5431,11 @@ int select_value_catcher::send_data(List<Item> &items) /** @brief - Try to merge item if it is an equality with the set of multiple - equalities + Try to add an equality to some multiple equality - @param thd the thread handle - @param cond_equal set of multiple equalities - @param item item that can be merged + @param thd the thread handle + @param cond_equal the set of multiple equalities + @param item the item that can be added @note To check this fact check_simple_equality is called @@ -5445,8 +5444,8 @@ int select_value_catcher::send_data(List<Item> &items) @retval FALSE otherwise */ bool join_equalities_for_setup_jtbm_semi_joins(THD *thd, - COND_EQUAL *cond_equal, - Item *item) + COND_EQUAL *cond_equal, + Item *item) { if (!(item->type() == Item::FUNC_ITEM && ((Item_func *)item)->functype() == Item_func::EQ_FUNC)) @@ -5457,9 +5456,9 @@ bool join_equalities_for_setup_jtbm_semi_joins(THD *thd, if (check_simple_equality(thd, Item::Context(Item::ANY_SUBST, - ((Item_func_equal *)item)->compare_type_handler(), + ((Item_func_equal *)item)->compare_type_handler(), ((Item_func_equal *)item)->compare_collation()), - left_item, right_item, cond_equal)) + left_item, right_item, cond_equal)) return true; return false; @@ -5467,32 +5466,31 @@ bool join_equalities_for_setup_jtbm_semi_joins(THD *thd, /** - @brief Push down equalities into the internal levels of condition if needed + @brief Push down equalities into the internal levels of the condition + if needed @param thd the thread handle - @param level current level number - @param conds current level condition - @param cond_equal equalities from the upper level that are pushed down - @param cond_value the resulting value of the condition + @param level the current level number + @param conds the current level condition + @param cond_equal the equalities from the upper level that should + be pushed down @details - During the setup_jtbm_semi_joins work in the case when the join of the - subquery is degenerated some new equalities appear. These equalities are - attached to the top level of the WHERE clause (if it is a condition) + During the setup_jtbm_semi_joins work new equalities can appear. + These equalities are attached to the top level of the WHERE clause but are not attached to the inner levels. This procedure pushes equalities down to the inner levels if needed merging - them with the existed equalities on the this level. + them with the exist equalities on the this level. */ Item *search_for_missing_parts_of_equalities(THD *thd, - uint level, - Item *conds, - COND_EQUAL *cond_equal, - Item::cond_result *cond_value) + uint level, + Item *conds, + COND_EQUAL *cond_equal) { if (conds->type() != Item::COND_ITEM) { /* - If item is on the top level there is no need to attach anything to it + If current level is top level — nothing to add */ if (level<2) return conds; @@ -5520,8 +5518,8 @@ Item *search_for_missing_parts_of_equalities(THD *thd, cond_equal= &((Item_cond_and *) conds)->m_cond_equal; while ((item=li++)) - item= search_for_missing_parts_of_equalities(thd, level, item, - cond_equal, cond_value); + item= search_for_missing_parts_of_equalities(thd, level, + item, cond_equal); } return conds; } @@ -5529,28 +5527,43 @@ Item *search_for_missing_parts_of_equalities(THD *thd, /** @brief - Try to merge the set of equalities with the multiple equalities of - the WHERE clause + Attach new equalities to the WHERE-clause condition - @param thd the thread handle - @param join pointer to the structure providing all context info for - the query - @param equal set of equalities to add - @param cond_value the resulting value of the condition + @param join join of the select where equalities should be + attached + @param eq_list the set of the equalities to add @details - This method modifies WHERE clause trying to make a new condition - consists of: - 1. conds - 2. multiple equalities from conds merged with the equal - 3. the rest of equalities from the equal + The method modifies the condition of the join (the condition of the + WHERE clause) by adding new equalities from the eq_list. It adds new + equalities to the remain multiple equalities of the WHERE clause condition + or if it is not possible just attaches them to the WHERE clause condition. + + First multiple equalities are disjoined from the join condition to avoid + repetitions. These multiple equalities are merged with the equalities from + the eq_list. + + Example: + + MULT_EQ(t1.a, t1.b) and (t1.a = 2) will become + MULT_EQ(2, t1.a, t1.b) + + Sometimes it is not possible and in this case the eqialities that can't + be merged are saved to be attached to the condition later. + This situation can appear after the optimize of the IN subquery + predicate it is transformed in the knowlingly false equality. + For example, (3 = 1) equality. + + Than a new condition is created. It consists of the old condition from + which multiple equalities were disjoint, new multiple equalities and + the equalities that weren't attached to the multiple equalities. @retval TRUE if an error occurs @retval FALSE otherwise */ -bool join_equalities_after_optimize_cond(THD *thd, JOIN *join, Item *equal, - Item::cond_result *cond_value) +bool join_equalities_after_optimize_cond(JOIN *join, + List<Item> &eq_list) { DBUG_ENTER("join_equalities_after_optimize_cond"); Item *conds= join->conds; @@ -5559,6 +5572,7 @@ bool join_equalities_after_optimize_cond(THD *thd, JOIN *join, Item *equal, Item_equal *item_equal; List<Item_equal> *cond_equal_list= (List<Item_equal> *) &join->cond_equal->current_level; + THD *thd= join->thd; if (conds && conds->type() == Item::COND_ITEM && ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC) @@ -5574,10 +5588,11 @@ bool join_equalities_after_optimize_cond(THD *thd, JOIN *join, Item *equal, else if (join->cond_equal && join->cond_equal->current_level.elements) { /* - Set to 0 to avoid repetitions from cond_equal after merging + If the condition of the WHERE clause is a multiple equality itself + it is set to 0. */ if (conds && conds->type() == Item::FUNC_ITEM && - ((Item_func*) conds)->functype() == Item_func::MULT_EQUAL_FUNC) + ((Item_func*) conds)->functype() == Item_func::MULT_EQUAL_FUNC) conds= 0; cond_equal.current_level.append(cond_equal_list); @@ -5585,34 +5600,24 @@ bool join_equalities_after_optimize_cond(THD *thd, JOIN *join, Item *equal, } /* - Merge equalities with multiple equalities of the WHERE clause. - If the equality can't be merged leave it in the list to add to the - WHERE clause later. + Merges the equalities from the equal_list with the multiple equalities + of the condition of the WHERE clause. If the equality can't be merged it + is left in the eq_list list so it can be later added to the WHERE clause. */ - if (equal && equal->type() == Item::COND_ITEM && - ((Item_cond*) equal)->functype() == Item_func::COND_AND_FUNC) + List_iterator<Item> li(eq_list); + Item *item; + while ((item=li++)) { - List_iterator<Item> li(*((Item_cond *) equal)->argument_list()); - Item *item; - while ((item=li++)) - { - if (join_equalities_for_setup_jtbm_semi_joins(thd, &cond_equal, item)) - li.remove(); - } + if (join_equalities_for_setup_jtbm_semi_joins(thd, &cond_equal, item)) + li.remove(); } - else if (join_equalities_for_setup_jtbm_semi_joins(thd, - &cond_equal, - equal)) - equal= 0; if (conds) conds= conds->propagate_equal_fields(thd, - Item::Context_boolean(), - &cond_equal); + Item::Context_boolean(), + &cond_equal); - /* - Fix just created multiple equalities - */ + /* Fix just created multiple equalities */ List_iterator_fast<Item_equal> it(cond_equal.current_level); while ((item_equal= it++)) { @@ -5626,22 +5631,22 @@ bool join_equalities_after_optimize_cond(THD *thd, JOIN *join, Item *equal, } /* - Creates AND-condition for WITH clause if at list one of these conditions - is satisfied: + Creates AND-condition for the WITH clause if at least one of these + conditions is satisfied: 1. there are several multiple inequalities 2. there remain several equalities in the equality list 3. in the {conds, equalities, cond_equal.current_level} set - there are at least 2 not-null elements. + there are at least 2 non-empty elements. */ - uint eq_cnt= cond_equal.current_level.elements; + uint mult_eq_cnt= cond_equal.current_level.elements; + uint eq_cnt= eq_list.elements; if (!and_args && - ((eq_cnt > 1) || - (equal && equal->type() == Item::COND_ITEM && - ((Item_cond*) equal)->functype() == Item_func::COND_AND_FUNC) || - (eq_cnt && equal) || - (eq_cnt && conds) || - (conds && equal))) + ((mult_eq_cnt > 1) || + (eq_cnt > 1) || + (mult_eq_cnt && eq_cnt) || + (mult_eq_cnt && conds) || + (conds && eq_cnt))) { if (!conds) conds= new (thd->mem_root) Item_cond_and(thd); @@ -5654,42 +5659,29 @@ bool join_equalities_after_optimize_cond(THD *thd, JOIN *join, Item *equal, and_args= ((Item_cond*) conds)->argument_list(); } - if (conds && !conds->fixed && conds->fix_fields(thd, NULL)) - DBUG_RETURN(TRUE); - - /* - Joins conds with the remaining equalities - */ - if (equal) - { - if (equal->type() == Item::COND_ITEM && - ((Item_cond*) equal)->functype() == Item_func::COND_AND_FUNC) - and_args->append(((Item_cond *) equal)->argument_list()); - else if (and_args) - and_args->push_back(equal, thd->mem_root); - else - conds= equal; - } - - /* - Joins conds with the multiple equalities - */ + /* Attaches the remaining equalities from the eq_list to the WHERE clause */ if (and_args) - { - ((Item_cond_and *)conds)->m_cond_equal.copy(cond_equal); - cond_equal.current_level= - ((Item_cond_and *)conds)->m_cond_equal.current_level; + and_args->append(&eq_list); + else + conds= new (thd->mem_root) Item_cond_and(thd, eq_list); - and_args->append((List<Item> *)&cond_equal.current_level); + if (conds && !conds->fixed && conds->fix_fields(thd, NULL)) + DBUG_RETURN(TRUE); - conds= search_for_missing_parts_of_equalities(thd, 0, conds, - &cond_equal, cond_value); - } - else if (eq_cnt == 1) + /* Attaches the multiple equalities to the WHERE clause condition */ + ((Item_cond_and *)conds)->m_cond_equal.copy(cond_equal); + cond_equal.current_level= + ((Item_cond_and *)conds)->m_cond_equal.current_level; + if (mult_eq_cnt == 1) { it.rewind(); conds= it++; } + else + { + and_args->append((List<Item> *)&cond_equal.current_level); + conds= search_for_missing_parts_of_equalities(thd, 0, conds, &cond_equal); + } join->conds= conds; @@ -5698,87 +5690,138 @@ bool join_equalities_after_optimize_cond(THD *thd, JOIN *join, Item *equal, /** - Execute the engine for the IN subquery -**/ -bool exec_engine_for_degenerated_jtbm_semi_join(THD *thd, - JOIN *join, - select_value_catcher **new_sink, - Item_in_subselect *subq_pred) + @brief Materialize a degenerate jtbm semi join + + @param thd thread handler + @param tbl table list for the target jtbm semi join table + @param subq_pred IN subquery predicate with the degenerate jtbm semi join + @param eq_list IN/OUT the list where to add produced equalities + + @details + The method materializes the degenerate jtbm semi join for the + subquery from the IN subquery predicate subq_pred taking table + as the target for materialization. + Any degenerate table is guaranteed to produce 0 or 1 record. + Examples of both cases: + + select * from ot where col in (select ... from it where 2>3) + select * from ot where col in (select MY_MIN(it.key) from it) + + in this case, there is no necessity to create a temp.table for + materialization. + We now just need to + 1. Check whether 1 or 0 records are produced, setup this as a + constant join tab. + 2. Create a dummy temporary table, because all of the join + optimization code relies on TABLE object being present. + + In the case when materialization produces one row the function + additionally creates equalities between the expressions from the + left part of the IN subquery predicate and the corresponding + columns of the produced row. These equalities are added to the + list eq_list. They are supposed to be conjuncted with the condition + of the WHERE clause. + + @retval TRUE if an error occurs + @retval FALSE otherwise +*/ + +bool execute_degenerate_jtbm_semi_join(THD *thd, + TABLE_LIST *tbl, + Item_in_subselect *subq_pred, + List<Item> &eq_list) { - DBUG_ENTER("exec_engine_for_degenerated_jtbm_semi_join"); + DBUG_ENTER("execute_degenerate_jtbm_semi_join"); + select_value_catcher *new_sink; + DBUG_ASSERT(subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE); subselect_single_select_engine *engine= (subselect_single_select_engine*)subq_pred->engine; - if (!(*new_sink= - new (thd->mem_root) select_value_catcher(thd, subq_pred))) + if (!(new_sink= new (thd->mem_root) select_value_catcher(thd, subq_pred))) DBUG_RETURN(TRUE); - if ((*new_sink)->setup(&engine->select_lex->join->fields_list) || - engine->select_lex->join->change_result(*new_sink, NULL) || + if (new_sink->setup(&engine->select_lex->join->fields_list) || + engine->select_lex->join->change_result(new_sink, NULL) || engine->exec()) { DBUG_RETURN(TRUE); } - DBUG_RETURN(FALSE); -} - - -/** - @brief Set up a dummy TABLE*, optimizer code needs JOIN_TABs to have TABLE + subq_pred->is_jtbm_const_tab= TRUE; - @param thd the thread handle - @param table table for the materialized semijoin + if (new_sink->assigned) + { + /* + Subselect produced one row, which is saved in new_sink->row. + Save "left_expr[i] == row[i]" equalities into the eq_list. + */ + subq_pred->jtbm_const_row_found= TRUE; - @note - The created table may be freed by: - 1. JOIN_TAB::cleanup(), when the parent join is a regular join. - 2. cleanup_empty_jtbm_semi_joins(), when the parent join is a - degenerate join (e.g. one with "Impossible where"). + Item *eq_cond; + for (uint i= 0; i < subq_pred->left_expr->cols(); i++) + { + eq_cond= + new (thd->mem_root) Item_func_eq(thd, + subq_pred->left_expr->element_index(i), + new_sink->row[i]); + if (!eq_cond) + DBUG_RETURN(TRUE); - @retval TRUE if an error occurs - @retval FALSE otherwise -**/ -bool setup_dummy_table_for_degenerated_jtbm_semi_join(THD *thd, - TABLE_LIST *table) -{ - DBUG_ENTER("setup_dummy_table_for_degenerated_jtbm_semi_join"); + eq_list.push_back(eq_cond, thd->mem_root); + } + } + else + { + /* Subselect produced no rows. Just set the flag */ + subq_pred->jtbm_const_row_found= FALSE; + } TABLE *dummy_table; if (!(dummy_table= create_dummy_tmp_table(thd))) DBUG_RETURN(TRUE); - table->table= dummy_table; - table->table->pos_in_table_list= table; - setup_table_map(table->table, table, table->jtbm_table_no); + tbl->table= dummy_table; + tbl->table->pos_in_table_list= tbl; + /* + Note: the table created above may be freed by: + 1. JOIN_TAB::cleanup(), when the parent join is a regular join. + 2. cleanup_empty_jtbm_semi_joins(), when the parent join is a + degenerate join (e.g. one with "Impossible where"). + */ + setup_table_map(tbl->table, tbl, tbl->jtbm_table_no); DBUG_RETURN(FALSE); } /** - @brief Setup degenerated JTBM join tables for execution + @brief Find degenerate jtbm semi joins - @param join join being processed - @param join_list list of tables - @param join_where IN/OUT WHERE clause - @param cond_value the returned value of the WHERE clause of the join + @param join the parent join for jtbm semi joins + @param join_list the list of TABLE_LIST objects where jtbm semi join can occur + @param eq_list IN/OUT the list where to add produced equalities @details - Calls setup_degenerated_jtbm_semi_join for each table used in join - and for the nested joins lists of tables. + The method traverses join_list trying to find any degenerate jtbm semi + joins for subqueries of IN predicates. For each degenerate jtbm + semi join execute_degenerate_jtbm_semi_join is called. + + In the case when a TABLE_LIST object is nested in another TABLE_LIST + 'nested_join' method is recursively called for the list + of TABLE_LIST objects of the 'nested_join' trying to find in the list + any degenerate jtbm semi joins. @retval TRUE if an error occurs @retval FALSE otherwise -**/ +*/ + bool -setup_degenerated_semi_joins_before_optimize_cond(JOIN *join, - List<TABLE_LIST> *join_list, - Item **join_where, - Item::cond_result *cond_value) +setup_degenerate_jtbm_semi_joins(JOIN *join, + List<TABLE_LIST> *join_list, + List<Item> &eq_list) { TABLE_LIST *table; NESTED_JOIN *nested_join; List_iterator<TABLE_LIST> li(*join_list); THD *thd= join->thd; - DBUG_ENTER("setup_degenerated_semi_joins_before_optimize_cond"); + DBUG_ENTER("setup_degenerate_jtbm_semi_joins"); while ((table= li++)) { @@ -5790,87 +5833,84 @@ setup_degenerated_semi_joins_before_optimize_cond(JOIN *join, if (!subq_join->tables_list || !subq_join->table_count) { - /* - A special case; subquery's join is degenerate, and it either produces - 0 or 1 record. Examples of both cases: - - select * from ot where col in (select ... from it where 2>3) - select * from ot where col in (select MY_MIN(it.key) from it) - - in this case, the subquery predicate has not been setup for - materialization. In particular, there is no materialized temp.table. - We'll now need to - 1. Check whether 1 or 0 records are produced, setup this as a - constant join tab. - 2. Create a dummy temporary table, because all of the join - optimization code relies on TABLE object being present (here we - follow a bad tradition started by derived tables) - */ - select_value_catcher *new_sink; - - if (exec_engine_for_degenerated_jtbm_semi_join(thd, join, - &new_sink, subq_pred)) - DBUG_RETURN(TRUE); - subq_pred->is_jtbm_const_tab= TRUE; - - if (new_sink->assigned) - { - /* - Subselect produced one row, which is saved in new_sink->row. - Inject "left_expr[i] == row[i] equalities into parent's WHERE. - */ - subq_pred->jtbm_const_row_found= TRUE; - Item *eq_cond; - - for (uint i= 0; i < subq_pred->left_expr->cols(); i++) - { - eq_cond= new (thd->mem_root) - Item_func_eq(thd, subq_pred->left_expr->element_index(i), - new_sink->row[i]); - if (!eq_cond) - DBUG_RETURN(TRUE); - - if (!((*join_where)= and_items(thd, *join_where, eq_cond)) || - (*join_where)->fix_fields(thd, join_where)) - DBUG_RETURN(TRUE); - } - } - else - { - /* Subselect produced no rows. Just set the flag, */ - subq_pred->jtbm_const_row_found= FALSE; - } - if (setup_dummy_table_for_degenerated_jtbm_semi_join(thd, table)) + if (execute_degenerate_jtbm_semi_join(thd, + table, + subq_pred, + eq_list)) DBUG_RETURN(TRUE); - join->is_orig_degenerated= true; + join->is_orig_degenerated= true; } - - if ((nested_join= table->nested_join)) - { - if (setup_degenerated_semi_joins_before_optimize_cond(join, - &nested_join->join_list, - join_where, cond_value)) + } + if ((nested_join= table->nested_join)) + { + if (setup_degenerate_jtbm_semi_joins(join, &nested_join->join_list, eq_list)) DBUG_RETURN(TRUE); - } } } + DBUG_RETURN(FALSE); } -/* - Setup JTBM join tabs for execution +/** + @brief Prepare jtbm semi joins for materialization + + @param join the parent join for jtbm semi joins + @param join_list the list of TABLE_LIST objects where jtbm semi join can occur + @param eq_list IN/OUT the list where to add produced equalities + + @details + The method traverses join_list trying to find any jtbm semi joins for + subqueries from IN predicates. It either materializes the jtbm semi join + or creates a temporary table into which the jtbm semi join will be + materialized. The IN subquery predicate is optimized first to get the + information about how it should be handled. + + The first case is when the jtbm semi join is degenerate. Usually the + jtbm semi join is found degenerate before the optimization and on this + step it is already materialized by the setup_degenerate_jtbm_semi_joins + method. Btw there can appear a situation when the jtbm semi join becomes + degenerate only after the optimization of the IN subquery predicate. + In this case the execute_degenerate_jtbm_semi_join method is called. + + Example taken from subselect.test: + + SELECT * FROM t1 + WHERE 4 IN ( + SELECT MAX(b) + FROM t2 + WHERE EXISTS (SELECT * FROM t1)); + + Only after the optimization of EXISTS it is found that the WHERE-clause + of the IN subquery predicate is impossible. So the jtbm semi join for + the subquery from this predicate is degenerate and should be materialized. + + In the other case the IN subquery predicate should be computed via a + hash semi-join. It creates a temporary table in which the jtbm semi join + of the subquery from this predicate will be materialized. + The equalities between all pairs of expressions of the IN subquery + predicate are attached to the eq_list to be later conjuncted with the + condition of the WHERE clause. + + In the case when a TABLE_LIST object is nested in another TABLE_LIST + the method is recursively called for the list of TABLE_LIST objects + of the nested join. + + @retval TRUE if an error occurs + @retval FALSE otherwise */ + bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, - Item **join_where, Item::cond_result *cond_value) + List<Item> &eq_list) { TABLE_LIST *table; NESTED_JOIN *nested_join; List_iterator<TABLE_LIST> li(*join_list); THD *thd= join->thd; DBUG_ENTER("setup_jtbm_semi_joins"); - + eq_list.empty(); + while ((table= li++)) { Item_in_subselect *subq_pred; @@ -5894,77 +5934,10 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, if (!subq_join->tables_list || !subq_join->table_count) { - JOIN *subq_join= subq_pred->unit->first_select()->join; - - if (!join->is_orig_degenerated && - subq_join && - (!subq_join->tables_list || !subq_join->table_count)) - { - /* - If setup for degenerated joins wasn't done before - */ - select_value_catcher *new_sink; - - if (exec_engine_for_degenerated_jtbm_semi_join(thd, join, - &new_sink, - subq_pred)) - DBUG_RETURN(TRUE); - subq_pred->is_jtbm_const_tab= TRUE; - - if (new_sink->assigned) - { - /* - Subselect produced one row, which is saved in new_sink->row. - Create "left_expr[i] == row[i] equalities and join them with - the created after optimize multiple equalities of parent's WHERE. - */ - subq_pred->jtbm_const_row_found= TRUE; - - Item *eq_cond; - Item *new_cond; - - if (join->conds) - new_cond= new (thd->mem_root) Item_cond_and(thd); - - for (uint i= 0; i < subq_pred->left_expr->cols(); i++) - { - eq_cond= new (thd->mem_root) - Item_func_eq(thd, subq_pred->left_expr->element_index(i), - new_sink->row[i]); - if (!eq_cond) - DBUG_RETURN(TRUE); - - if (join->conds) - { - if (!(new_cond= and_items(thd, new_cond, eq_cond)) || - new_cond->fix_fields(thd, &new_cond)) - DBUG_RETURN(TRUE); - } - else - { - new_cond= eq_cond; - if (new_cond->fix_fields(thd, &new_cond)) - DBUG_RETURN(TRUE); - } - } - - if (join_equalities_after_optimize_cond(thd, join, new_cond, - cond_value)) - DBUG_RETURN(TRUE); - } - else - { - /* Subselect produced no rows. Just set the flag, */ - subq_pred->jtbm_const_row_found= FALSE; - } - - if (setup_dummy_table_for_degenerated_jtbm_semi_join(thd, table)) - DBUG_RETURN(TRUE); - - join->is_orig_degenerated= true; - } - - join->conds= join->conds->remove_eq_conds(thd, cond_value, true); + if (!join->is_orig_degenerated && + execute_degenerate_jtbm_semi_join(thd, table, subq_pred, + eq_list)) + DBUG_RETURN(TRUE); } else { @@ -5978,22 +5951,16 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, setup_table_map(table->table, table, table->jtbm_table_no); - /* - Add to the condition equalities that join left and right parts - of the subquery - */ - if (join_equalities_after_optimize_cond(thd, join, - hash_sj_engine->semi_join_conds, - cond_value)) - DBUG_RETURN(TRUE); + 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); } table->table->maybe_null= MY_TEST(join->mixed_implicit_grouping); } - if ((nested_join= table->nested_join)) { - if (setup_jtbm_semi_joins(join, &nested_join->join_list, - join_where, cond_value)) + if (setup_jtbm_semi_joins(join, &nested_join->join_list, eq_list)) DBUG_RETURN(TRUE); } } @@ -6543,9 +6510,9 @@ Item *Item_field::in_subq_field_transformer_for_having(THD *thd, uchar *arg) of the left part of IN subquery */ void grouping_fields_in_the_in_subq_left_part(THD *thd, - st_select_lex *sel, - List<In_subq_field> *fields, - ORDER *grouping_list) + st_select_lex *sel, + List<In_subq_field> *fields, + ORDER *grouping_list) { sel->grouping_tmp_fields.empty(); List_iterator<In_subq_field> it(*fields); @@ -6556,12 +6523,11 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd, { if ((*ord->item)->eq(item->right_it, 0)) { - Field *field= - ((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); + Field *field= + ((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); } } } @@ -6596,11 +6562,11 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd, The implementation scheme: - 1. It extracts the condition extr from condition cond depending only on + 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 depending only on the + 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. @@ -6629,7 +6595,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) /* Create the list of In_subq_field items for this IN subquery: - it consists of pairs of fields from the left part of the 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 right part of the IN subquery (fields from the projections list of the select of the right part of the IN subquery 'right_part' that stay on the same places in @@ -6650,7 +6616,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) } /* - Build the new condition from the cond that can be pushed in sel + Build the new condition from the cond that can be pushed into sel */ Item *extracted_cond; cond->check_pushable_cond(0, this); @@ -6697,19 +6663,19 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) } /* - Check what can be pushed in the WHERE clause of the sel from the + Checks what can be pushed into the WHERE clause of the 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->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); /* - Transform the references to the left_part fields so they can be pushed - into the sel WHERE clause. + Transforms the references to the left_part fields so they can be pushed + into the sel of the WHERE clause. */ if (cond_over_grouping_fields) cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, @@ -6719,7 +6685,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) if (cond_over_grouping_fields) { /* - Remove from extracted_cond all parts that can be pushed in WHERE clause + Removes from extracted_cond all parts that can be pushed into the WHERE clause */ extracted_cond= remove_pushed_top_conjuncts(thd, extracted_cond); @@ -6731,8 +6697,8 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) goto exit; } /* - Transform the references to the left_part fields so they can be pushed in - the sel HAVING clause + Transforms the references to the left_part fields so they can be pushed into + the sel of the HAVING clause */ extracted_cond= extracted_cond->transform(thd, &Item::in_subq_field_transformer_for_having, diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h index 70cf47f..e116fdf 100644 --- a/sql/opt_subselect.h +++ b/sql/opt_subselect.h @@ -26,17 +26,13 @@ int check_and_do_in_subquery_rewrites(JOIN *join); bool convert_join_subqueries_to_semijoins(JOIN *join); int pull_out_semijoin_tables(JOIN *join); bool optimize_semijoin_nests(JOIN *join, table_map all_table_map); -bool setup_degenerated_jtbm_semi_join(THD *thd, JOIN *join, - TABLE_LIST *table, - Item **join_where, - Item_in_subselect *subq_pred, - Item::cond_result *cond_value); +bool join_equalities_after_optimize_cond(JOIN *join, + List<Item> &eq_list); +bool setup_degenerate_jtbm_semi_joins(JOIN *join, + List<TABLE_LIST> *join_list, + List<Item> &eq_list); bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, - Item **join_where, Item::cond_result *cond_value); -bool setup_degenerated_semi_joins_before_optimize_cond(JOIN *join, - List<TABLE_LIST> *join_list, - Item **join_where, - Item::cond_result *cond_value); + List<Item> &eq_list); void cleanup_empty_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list); // used by Loose_scan_opt diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 97b2ccc..5a342c8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1730,10 +1730,27 @@ JOIN::optimize_inner() thd->restore_active_arena(arena, &backup); } - if (setup_degenerated_semi_joins_before_optimize_cond(this, join_list, - &conds, &cond_value)) + List<Item> eq_list; + + if (setup_degenerate_jtbm_semi_joins(this, join_list, eq_list)) DBUG_RETURN(1); + if (eq_list.elements != 0) + { + Item *new_cond; + + if (eq_list.elements == 1) + new_cond= eq_list.pop(); + else + new_cond= new (thd->mem_root) Item_cond_and(thd, eq_list); + + if (new_cond && + (new_cond->fix_fields(thd, &new_cond) || + !(conds= and_items(thd, conds, new_cond)) || + conds->fix_fields(thd, &conds))) + DBUG_RETURN(TRUE); + } + if (select_lex->cond_pushed_into_where) { conds= and_conds(thd, conds, select_lex->cond_pushed_into_where); @@ -1768,9 +1785,16 @@ JOIN::optimize_inner() } } - if (setup_jtbm_semi_joins(this, join_list, &conds, &cond_value)) + if (setup_jtbm_semi_joins(this, join_list, eq_list)) DBUG_RETURN(1); + if (eq_list.elements != 0) + { + if (join_equalities_after_optimize_cond(this, eq_list)) + DBUG_RETURN(TRUE); + conds= conds->remove_eq_conds(thd, &cond_value, true); + } + if (thd->lex->sql_command == SQLCOM_SELECT && optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) { diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result index 1dcb1ee..9d9fb4c 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=tokudb; insert into t values (0,0,0,0),(0,1,0,1); explain select c,a,b from t where c=0 and a=0 and b=1;

1 0

[Commits] 78e4215: Fix compile on windows: O_SYNC is not available, use a my_sync() call instead.
by psergey＠askmonty.org 13 Apr '18

13 Apr '18

revision-id: 78e42153b51d6166b916a5bdb39e8726913c263e parent(s): 5545753b0b21b98fbdd2d8b894bc141d1ed558b3 committer: Sergei Petrunia branch nick: 10.2-r10 timestamp: 2018-04-13 20:26:40 +0300 message: Fix compile on windows: O_SYNC is not available, use a my_sync() call instead. --- storage/rocksdb/rdb_utils.cc | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/storage/rocksdb/rdb_utils.cc b/storage/rocksdb/rdb_utils.cc index 19469d0..723e079 100644 --- a/storage/rocksdb/rdb_utils.cc +++ b/storage/rocksdb/rdb_utils.cc @@ -358,7 +358,8 @@ bool rdb_check_rocksdb_corruption() { void rdb_persist_corruption_marker() { const std::string &fileName(myrocks::rdb_corruption_marker_file_name()); - int fd = my_open(fileName.c_str(), O_CREAT | O_SYNC, MYF(MY_WME)); + /* O_SYNC is not supported on windows */ + int fd = my_open(fileName.c_str(), O_CREAT | IF_WIN(0, O_SYNC), MYF(MY_WME)); if (fd < 0) { sql_print_error("RocksDB: Can't create file %s to mark rocksdb as " "corrupted.", @@ -370,6 +371,12 @@ void rdb_persist_corruption_marker() { fileName.c_str()); } +#ifdef _WIN32 + /* A replacement for O_SYNC flag above */ + if (fd >= 0) + my_sync(fd, MYF(0)); +#endif + int ret = my_close(fd, MYF(MY_WME)); if (ret) { // NO_LINT_DEBUG

1 0