diff options
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 37 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 37 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_mat.test | 33 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 5 | ||||
| -rw-r--r-- | sql/sql_delete.cc | 3 | ||||
| -rw-r--r-- | sql/sql_select.cc | 61 | ||||
| -rw-r--r-- | sql/sql_select.h | 4 | ||||
| -rw-r--r-- | sql/sql_show.cc | 5 |
8 files changed, 158 insertions, 27 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index a78cc1c3a9e..7eb41683dd0 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2108,6 +2108,43 @@ EXECUTE stmt; a DROP TABLE t1, t2; DROP VIEW v2; +# +# MDEV-6289 : Unexpected results when querying information_schema +# +CREATE TABLE t1 ( +id int(11) unsigned NOT NULL AUTO_INCREMENT, +db varchar(254) NOT NULL DEFAULT '', +PRIMARY KEY (id), +UNIQUE KEY db (db) +) DEFAULT CHARSET=utf8; +INSERT INTO t1 (db) VALUES ('mysqltest1'),('mysqltest2'),('mysqltest3'),('mysqltest4'); +drop database if exists mysqltest1; +drop database if exists mysqltest2; +drop database if exists mysqltest3; +drop database if exists mysqltest4; +create database mysqltest1; +create database mysqltest2; +create database mysqltest3; +create database mysqltest4; +SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.SCHEMATA) ORDER BY db DESC; +db +mysqltest4 +mysqltest3 +mysqltest2 +mysqltest1 +EXPLAIN EXTENDED +SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.SCHEMATA) ORDER BY db DESC; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 PRIMARY t1 eq_ref db db 764 information_schema.SCHEMATA.SCHEMA_NAME 1 100.00 Using where; Using index +2 MATERIALIZED SCHEMATA ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`SCHEMATA`) where (`test`.`t1`.`db` = `information_schema`.`SCHEMATA`.`SCHEMA_NAME`) order by `test`.`t1`.`db` desc +drop table t1; +drop database mysqltest1; +drop database mysqltest2; +drop database mysqltest3; +drop database mysqltest4; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index c9c2c735428..33fe045eac9 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2148,4 +2148,41 @@ EXECUTE stmt; a DROP TABLE t1, t2; DROP VIEW v2; +# +# MDEV-6289 : Unexpected results when querying information_schema +# +CREATE TABLE t1 ( +id int(11) unsigned NOT NULL AUTO_INCREMENT, +db varchar(254) NOT NULL DEFAULT '', +PRIMARY KEY (id), +UNIQUE KEY db (db) +) DEFAULT CHARSET=utf8; +INSERT INTO t1 (db) VALUES ('mysqltest1'),('mysqltest2'),('mysqltest3'),('mysqltest4'); +drop database if exists mysqltest1; +drop database if exists mysqltest2; +drop database if exists mysqltest3; +drop database if exists mysqltest4; +create database mysqltest1; +create database mysqltest2; +create database mysqltest3; +create database mysqltest4; +SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.SCHEMATA) ORDER BY db DESC; +db +mysqltest4 +mysqltest3 +mysqltest2 +mysqltest1 +EXPLAIN EXTENDED +SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.SCHEMATA) ORDER BY db DESC; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 PRIMARY t1 eq_ref db db 764 information_schema.SCHEMATA.SCHEMA_NAME 1 100.00 Using where; Using index +2 MATERIALIZED SCHEMATA ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`SCHEMATA`) where (`test`.`t1`.`db` = `information_schema`.`SCHEMATA`.`SCHEMA_NAME`) order by `test`.`t1`.`db` desc +drop table t1; +drop database mysqltest1; +drop database mysqltest2; +drop database mysqltest3; +drop database mysqltest4; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 91b69a6a09c..518b2ca878f 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1808,5 +1808,38 @@ EXECUTE stmt; DROP TABLE t1, t2; DROP VIEW v2; +--echo # +--echo # MDEV-6289 : Unexpected results when querying information_schema +--echo # +CREATE TABLE t1 ( + id int(11) unsigned NOT NULL AUTO_INCREMENT, + db varchar(254) NOT NULL DEFAULT '', + PRIMARY KEY (id), + UNIQUE KEY db (db) +) DEFAULT CHARSET=utf8; +INSERT INTO t1 (db) VALUES ('mysqltest1'),('mysqltest2'),('mysqltest3'),('mysqltest4'); + +--disable_warnings +drop database if exists mysqltest1; +drop database if exists mysqltest2; +drop database if exists mysqltest3; +drop database if exists mysqltest4; +--enable_warnings +create database mysqltest1; +create database mysqltest2; +create database mysqltest3; +create database mysqltest4; + +SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.SCHEMATA) ORDER BY db DESC; + +EXPLAIN EXTENDED +SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.SCHEMATA) ORDER BY db DESC; + +drop table t1; +drop database mysqltest1; +drop database mysqltest2; +drop database mysqltest3; +drop database mysqltest4; + --echo # End of 5.5 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 65ce50c1e3c..62df666b71f 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3200,8 +3200,9 @@ int subselect_single_select_engine::exec() pushed down into the subquery. Those optimizations are ref[_or_null] acceses. Change them to be full table scans. */ - for (JOIN_TAB *tab= first_linear_tab(join, WITHOUT_CONST_TABLES); tab; - tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) + JOIN_TAB *tab; + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); + tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { if (tab && tab->keyuse) { diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 64fd33bbc2c..97d3d10c21c 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -673,7 +673,8 @@ multi_delete::initialize_tables(JOIN *join) walk= delete_tables; - for (JOIN_TAB *tab= first_linear_tab(join, WITH_CONST_TABLES); + for (JOIN_TAB *tab= first_linear_tab(join, WITHOUT_BUSH_ROOTS, + WITH_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e6ae3b05c1d..084cbb4645d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1332,7 +1332,8 @@ JOIN::optimize() Perform the optimization on fields evaluation mentioned above for all on expressions. */ - for (JOIN_TAB *tab= first_linear_tab(this, WITHOUT_CONST_TABLES); tab; + JOIN_TAB *tab; + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { if (*tab->on_expr_ref) @@ -1355,7 +1356,7 @@ JOIN::optimize() Perform the optimization on fields evaliation mentioned above for all used ref items. */ - for (JOIN_TAB *tab= first_linear_tab(this, WITHOUT_CONST_TABLES); tab; + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { uint key_copy_index=0; @@ -1980,7 +1981,8 @@ bool JOIN::setup_subquery_caches() if (conds) conds= conds->transform(&Item::expr_cache_insert_transformer, (uchar*) thd); - for (JOIN_TAB *tab= first_linear_tab(this, WITHOUT_CONST_TABLES); + JOIN_TAB *tab; + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { if (tab->select_cond) @@ -2143,7 +2145,8 @@ JOIN::reinit() /* need to reset ref access state (see join_read_key) */ if (join_tab) { - for (JOIN_TAB *tab= first_linear_tab(this, WITH_CONST_TABLES); tab; + JOIN_TAB *tab; + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITH_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { tab->ref.key_err= TRUE; @@ -2907,8 +2910,9 @@ JOIN::destroy() { if (join_tab != tmp_join->join_tab) { - for (JOIN_TAB *tab= first_linear_tab(this, WITH_CONST_TABLES); tab; - tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) + JOIN_TAB *tab; + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITH_CONST_TABLES); + tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { tab->cleanup(); } @@ -7520,14 +7524,24 @@ JOIN_TAB *next_top_level_tab(JOIN *join, JOIN_TAB *tab) } -JOIN_TAB *first_linear_tab(JOIN *join, enum enum_with_const_tables const_tbls) +JOIN_TAB *first_linear_tab(JOIN *join, + enum enum_with_bush_roots include_bush_roots, + enum enum_with_const_tables const_tbls) { JOIN_TAB *first= join->join_tab; if (const_tbls == WITHOUT_CONST_TABLES) first+= join->const_tables; - if (first < join->join_tab + join->top_join_tab_count) - return first; - return NULL; /* All tables were const tables */ + + if (first >= join->join_tab + join->top_join_tab_count) + return NULL; /* All are const tables */ + + if (first->bush_children && include_bush_roots == WITHOUT_BUSH_ROOTS) + { + /* This JOIN_TAB is a SJM nest; Start from first table in nest */ + return first->bush_children->start; + } + + return first; } @@ -8364,9 +8378,10 @@ inline void add_cond_and_fix(THD *thd, Item **e1, Item *e2) static void add_not_null_conds(JOIN *join) { + JOIN_TAB *tab; DBUG_ENTER("add_not_null_conds"); - for (JOIN_TAB *tab= first_linear_tab(join, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -8537,7 +8552,7 @@ make_outerjoin_info(JOIN *join) tab->table->pos_in_table_list being set. */ JOIN_TAB *tab; - for (tab= first_linear_tab(join, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -8549,7 +8564,7 @@ make_outerjoin_info(JOIN *join) } } - for (JOIN_TAB *tab= first_linear_tab(join, WITHOUT_CONST_TABLES); tab; + for (JOIN_TAB *tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { TABLE *table= tab->table; @@ -9309,7 +9324,7 @@ bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array) void JOIN::drop_unused_derived_keys() { JOIN_TAB *tab; - for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { @@ -9997,7 +10012,7 @@ void check_join_cache_usage_for_tables(JOIN *join, ulonglong options, JOIN_TAB *tab; JOIN_TAB *prev_tab; - for (tab= first_linear_tab(join, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -10005,7 +10020,7 @@ void check_join_cache_usage_for_tables(JOIN *join, ulonglong options, } uint idx= join->const_tables; - for (tab= first_linear_tab(join, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -10099,7 +10114,8 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) tab->partial_join_cardinality= 1; JOIN_TAB *prev_tab= NULL; - for (tab= first_linear_tab(join, WITHOUT_CONST_TABLES), i= join->const_tables; + i= join->const_tables; + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; prev_tab=tab, tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -10124,7 +10140,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) check_join_cache_usage_for_tables(join, options, no_jbuf_after); JOIN_TAB *first_tab; - for (tab= first_tab= first_linear_tab(join, WITHOUT_CONST_TABLES); + for (tab= first_tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -10800,7 +10816,8 @@ void JOIN::cleanup(bool full) } if (full) { - JOIN_TAB *sort_tab= first_linear_tab(this, WITHOUT_CONST_TABLES); + JOIN_TAB *sort_tab= first_linear_tab(this, WITH_BUSH_ROOTS, + WITHOUT_CONST_TABLES); if (pre_sort_join_tab) { if (sort_tab && sort_tab->select == pre_sort_join_tab->select) @@ -10847,7 +10864,7 @@ void JOIN::cleanup(bool full) } else { - for (tab= first_linear_tab(this, WITH_CONST_TABLES); tab; + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITH_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { if (tab->table) @@ -11009,7 +11026,9 @@ only_eq_ref_tables(JOIN *join,ORDER *order,table_map tables) static void update_depend_map(JOIN *join) { - for (JOIN_TAB *join_tab= first_linear_tab(join, WITH_CONST_TABLES); join_tab; + JOIN_TAB *join_tab; + for (join_tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITH_CONST_TABLES); + join_tab; join_tab= next_linear_tab(join, join_tab, WITH_BUSH_ROOTS)) { TABLE_REF *ref= &join_tab->ref; diff --git a/sql/sql_select.h b/sql/sql_select.h index 30d2f4abe56..ce57376a3ec 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1458,7 +1458,9 @@ private: enum enum_with_bush_roots { WITH_BUSH_ROOTS, WITHOUT_BUSH_ROOTS}; enum enum_with_const_tables { WITH_CONST_TABLES, WITHOUT_CONST_TABLES}; -JOIN_TAB *first_linear_tab(JOIN *join, enum enum_with_const_tables const_tbls); +JOIN_TAB *first_linear_tab(JOIN *join, + enum enum_with_bush_roots include_bush_roots, + enum enum_with_const_tables const_tbls); JOIN_TAB *next_linear_tab(JOIN* join, JOIN_TAB* tab, enum enum_with_bush_roots include_bush_roots); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 6e89a871c7a..afbda8dd0b1 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -7708,8 +7708,9 @@ bool get_schema_tables_result(JOIN *join, Warnings_only_error_handler err_handler; thd->push_internal_handler(&err_handler); old_proc_info= thd_proc_info(thd, "Filling schema table"); - - for (JOIN_TAB *tab= first_linear_tab(join, WITH_CONST_TABLES); + + JOIN_TAB *tab; + for (tab= first_linear_tab(join, WITHOUT_BUSH_ROOTS, WITH_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) { |