- commits - lists.mariadb.org

[Commits] 2820f30dde3: MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived ...
by psergey 30 Apr '21

30 Apr '21

revision-id: 2820f30dde3148df71e1d748ac705d98d60e0787 (mariadb-10.3.26-152-g2820f30dde3) parent(s): 8f9a72a1504c73a2d432cb5a521b9ca631d1e455 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-30 21:42:14 +0300 message: MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived ... The problem was caused by the following scenario: Subquery's table has two indexes, KEY a(a), KEY a_b(a,b) - LATERAL DERIVED optimization decides to use index a. = The subquery uses ref access over key a. - test_if_skip_sort_order() sees that KEY a_b satisfies the subquery's GROUP BY clause, and attempts to switch to it. = It fails to do so, because KEYUSE objects for index a_b are switched off. Fixed by disallowing to change the ref access key if it uses KEYUSE objects injected by LATERAL DERIVED optimization. --- mysql-test/main/derived_split_innodb.result | 34 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 26 ++++++++++++++++++++++ sql/sql_select.cc | 5 ++++- sql/sql_select.h | 6 +++++ 4 files changed, 70 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index b9ed016429b..0b57e72b821 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -140,3 +140,37 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t2 index NULL PRIMARY 4 NULL 3 drop view v1; drop table t1,t2; +# +# MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived table subject to split +# +CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB; +SELECT * FROM t1 t1a JOIN t1 t1b; +a b a b +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12); +set statement optimizer_switch='split_materialized=off' for EXPLAIN +SELECT * +FROM +t1 JOIN +(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +WHERE +t1.a = dt.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 DERIVED t1 index NULL a_2 10 NULL 6 Using where; Using index +2 DERIVED t2 ref c c 5 test.t1.b 1 Using index +set statement optimizer_switch='split_materialized=on' for EXPLAIN +SELECT * +FROM +t1 JOIN +(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +WHERE +t1.a = dt.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort +2 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index +DROP TABLE t1, t2; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 4f9d2e970f7..19a6ecf216f 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -124,3 +124,29 @@ eval set statement optimizer_switch='split_materialized=off' for explain $q; drop view v1; drop table t1,t2; + +--echo # +--echo # MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived table subject to split +--echo # +CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB; + +SELECT * FROM t1 t1a JOIN t1 t1b; + +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12); + +let $query= +EXPLAIN +SELECT * +FROM + t1 JOIN + (SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +WHERE + t1.a = dt.a; + +eval set statement optimizer_switch='split_materialized=off' for $query; +eval set statement optimizer_switch='split_materialized=on' for $query; + +DROP TABLE t1, t2; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2526866e534..6c090ea5352 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10102,6 +10102,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.disable_cache= FALSE; j->ref.null_ref_part= NO_REF_PART; j->ref.const_ref_part_map= 0; + j->ref.uses_splitting= FALSE; keyuse=org_keyuse; store_key **ref_key= j->ref.key_copy; @@ -10150,6 +10151,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.null_rejecting|= (key_part_map)1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; + j->ref.uses_splitting |= (keyuse->validity_ref != NULL); /* We don't want to compute heavy expressions in EXPLAIN, an example would select * from t1 where t1.key=(select thats very heavy); @@ -22536,7 +22538,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, todo: why does JT_REF_OR_NULL mean filesort? We could find another index that satisfies the ordering. I would just set ref_key=MAX_KEY here... */ - if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT) + if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT || + tab->ref.uses_splitting) goto use_filesort; } else if (select && select->quick) // Range found by opt_range diff --git a/sql/sql_select.h b/sql/sql_select.h index 06cc86b5710..1efb2471793 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -178,6 +178,12 @@ typedef struct st_table_ref */ bool disable_cache; + /* + If true, this ref access was constructed from equalities generated by + LATERAL DERIVED (aka GROUP BY splitting) optimization + */ + bool uses_splitting; + bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, bool value, uint skip= 0); bool is_access_triggered();

1 0

[Commits] 91a46b5a457: MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived ...
by psergey 30 Apr '21

30 Apr '21

revision-id: 91a46b5a4579d2eed05cc327db8021912918ca76 (mariadb-10.3.26-141-g91a46b5a457) parent(s): 42aad65b895dff937aa0618b3237eb4f653ce0e4 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-30 21:40:43 +0300 message: MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived ... The problem was caused by the following scenario: Subquery's table has two indexes, KEY a(a), KEY a_b(a,b) - LATERAL DERIVED optimization decides to use index a. = The subquery uses ref access over key a. - test_if_skip_sort_order() sees that KEY a_b satisfies the subquery's GROUP BY clause, and attempts to switch to it. = It fails to do so, because KEYUSE objects for index a_b are switched off. Fixed by disallowing to change the ref access key if it uses KEYUSE objects injected by LATERAL DERIVED optimization. --- mysql-test/main/derived_split_innodb.result | 34 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 26 ++++++++++++++++++++++ sql/sql_select.cc | 5 ++++- sql/sql_select.h | 6 +++++ 4 files changed, 70 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index b9ed016429b..0b57e72b821 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -140,3 +140,37 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t2 index NULL PRIMARY 4 NULL 3 drop view v1; drop table t1,t2; +# +# MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived table subject to split +# +CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB; +SELECT * FROM t1 t1a JOIN t1 t1b; +a b a b +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12); +set statement optimizer_switch='split_materialized=off' for EXPLAIN +SELECT * +FROM +t1 JOIN +(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +WHERE +t1.a = dt.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 DERIVED t1 index NULL a_2 10 NULL 6 Using where; Using index +2 DERIVED t2 ref c c 5 test.t1.b 1 Using index +set statement optimizer_switch='split_materialized=on' for EXPLAIN +SELECT * +FROM +t1 JOIN +(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +WHERE +t1.a = dt.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort +2 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index +DROP TABLE t1, t2; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 4f9d2e970f7..19a6ecf216f 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -124,3 +124,29 @@ eval set statement optimizer_switch='split_materialized=off' for explain $q; drop view v1; drop table t1,t2; + +--echo # +--echo # MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived table subject to split +--echo # +CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB; + +SELECT * FROM t1 t1a JOIN t1 t1b; + +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12); + +let $query= +EXPLAIN +SELECT * +FROM + t1 JOIN + (SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +WHERE + t1.a = dt.a; + +eval set statement optimizer_switch='split_materialized=off' for $query; +eval set statement optimizer_switch='split_materialized=on' for $query; + +DROP TABLE t1, t2; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d53c592ff7b..d2ebda402ab 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10100,6 +10100,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.disable_cache= FALSE; j->ref.null_ref_part= NO_REF_PART; j->ref.const_ref_part_map= 0; + j->ref.uses_splitting= FALSE; keyuse=org_keyuse; store_key **ref_key= j->ref.key_copy; @@ -10148,6 +10149,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.null_rejecting|= (key_part_map)1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; + j->ref.uses_splitting |= (keyuse->validity_ref != NULL); /* We don't want to compute heavy expressions in EXPLAIN, an example would select * from t1 where t1.key=(select thats very heavy); @@ -22534,7 +22536,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, todo: why does JT_REF_OR_NULL mean filesort? We could find another index that satisfies the ordering. I would just set ref_key=MAX_KEY here... */ - if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT) + if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT || + tab->ref.uses_splitting) goto use_filesort; } else if (select && select->quick) // Range found by opt_range diff --git a/sql/sql_select.h b/sql/sql_select.h index 06cc86b5710..1efb2471793 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -178,6 +178,12 @@ typedef struct st_table_ref */ bool disable_cache; + /* + If true, this ref access was constructed from equalities generated by + LATERAL DERIVED (aka GROUP BY splitting) optimization + */ + bool uses_splitting; + bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, bool value, uint skip= 0); bool is_access_triggered();

1 0

[Commits] 3c564ae0e12: MDEV-23723: Signal 11 during CTE JOIN
by psergey 29 Apr '21

29 Apr '21

revision-id: 3c564ae0e1235990794f2d0ab6c9b5760386d19f (mariadb-10.3.26-141-g3c564ae0e12) parent(s): 42aad65b895dff937aa0618b3237eb4f653ce0e4 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-29 19:30:07 +0300 message: MDEV-23723: Signal 11 during CTE JOIN The problem was caused by the following scenario: Subquery's table has two indexes, KEY a(a), KEY a_b(a,b) - LATERAL DERIVED optimization decides to use index a. = The subquery uses ref access over key a. - test_if_skip_sort_order() sees that KEY a_b satisfies the subquery's GROUP BY clause, and attempts to switch to it. = It fails to do so, because KEYUSE objects for index a_b are switched off. Fixed by disallowing to change the ref access key if it uses KEYUSE objects injected by LATERAL DERIVED optimization. --- mysql-test/main/derived_split_innodb.result | 22 ++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 22 ++++++++++++++++++++++ sql/sql_select.cc | 5 ++++- sql/sql_select.h | 6 ++++++ 4 files changed, 54 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index b9ed016429b..19d0cbdf1a4 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -140,3 +140,25 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t2 index NULL PRIMARY 4 NULL 3 drop view v1; drop table t1,t2; +# +# MDEV-23723: Signal 11 during CTE JOIN +# +CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB; +SELECT * FROM t1 t1a JOIN t1 t1b; +a b a b +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12); +EXPLAIN +SELECT * +FROM +t1 JOIN +(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as cte +WHERE +t1.a = cte.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 LATERAL DERIVED t1 ref a,a_2 a 5 test.t1.a 1 Using where; Using temporary; Using filesort +2 LATERAL DERIVED t2 ref c c 5 test.t1.b 1 Using index +DROP TABLE t1, t2; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 4f9d2e970f7..db44b30a59b 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -124,3 +124,25 @@ eval set statement optimizer_switch='split_materialized=off' for explain $q; drop view v1; drop table t1,t2; + +--echo # +--echo # MDEV-23723: Signal 11 during CTE JOIN +--echo # +CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB; + +SELECT * FROM t1 t1a JOIN t1 t1b; + +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12); + +EXPLAIN +SELECT * +FROM + t1 JOIN + (SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as cte +WHERE + t1.a = cte.a; + +DROP TABLE t1, t2; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d53c592ff7b..d2ebda402ab 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10100,6 +10100,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.disable_cache= FALSE; j->ref.null_ref_part= NO_REF_PART; j->ref.const_ref_part_map= 0; + j->ref.uses_splitting= FALSE; keyuse=org_keyuse; store_key **ref_key= j->ref.key_copy; @@ -10148,6 +10149,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.null_rejecting|= (key_part_map)1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; + j->ref.uses_splitting |= (keyuse->validity_ref != NULL); /* We don't want to compute heavy expressions in EXPLAIN, an example would select * from t1 where t1.key=(select thats very heavy); @@ -22534,7 +22536,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, todo: why does JT_REF_OR_NULL mean filesort? We could find another index that satisfies the ordering. I would just set ref_key=MAX_KEY here... */ - if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT) + if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT || + tab->ref.uses_splitting) goto use_filesort; } else if (select && select->quick) // Range found by opt_range diff --git a/sql/sql_select.h b/sql/sql_select.h index 06cc86b5710..4ec7d47723f 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -178,6 +178,12 @@ typedef struct st_table_ref */ bool disable_cache; + /* + If true, this ref access was constructed from equalities generated by + LATERAL DERIVED optimization (aka GROUP BY splitting) + */ + bool uses_splitting; + bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, bool value, uint skip= 0); bool is_access_triggered();

1 0

[Commits] 6be6ecf: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition
by IgorBabaev 29 Apr '21

29 Apr '21

revision-id: 6be6ecf2f0dd1822b3573a4cd3d28d5173a7d6c7 (mariadb-10.3.26-139-g6be6ecf) parent(s): 2c9bf0ae8758b2c46ea5e02d1ea3d3ab5cab63b2 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-28 23:24:48 -0700 message: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition --- mysql-test/main/derived.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/derived.test | 29 +++++++++++++++++++++++++++++ sql/sql_base.cc | 1 - sql/sql_lex.cc | 2 -- sql/sql_show.cc | 4 +++- 5 files changed, 63 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 0f91750..975c18c 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1260,3 +1260,34 @@ a a 4 4 6 6 drop table t1,t2,t3; +# +# MDEV-21603: materialized derived used in SHOW TABLES +# +create table t1 (nm varchar(32), a int); +insert into t1 values ('1',1),('2',2),('3',3); +use mysql; +show tables +where tables_in_mysql in (select * +from (select nm from test.t1 group by nm) dt); +Tables_in_mysql +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +insert into test.t1 values ('nm',0); +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +show fields from test.t1 +where Field in +(select * from (select column_name from information_schema.columns +where table_name='t1' + group by column_name) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +a int(11) YES NULL +use test; +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index f4477ce..cad22aa 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1069,3 +1069,32 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; + +--echo # +--echo # MDEV-21603: materialized derived used in SHOW TABLES +--echo # + +create table t1 (nm varchar(32), a int); +insert into t1 values ('1',1),('2',2),('3',3); +use mysql; +show tables + where tables_in_mysql in (select * + from (select nm from test.t1 group by nm) dt); +show fields from test.t1 + where Field in (select * from (select nm from test.t1 group by nm) dt); +insert into test.t1 values ('nm',0); +show fields from test.t1 + where Field in (select * from (select nm from test.t1 group by nm) dt); + +show fields from test.t1 + where Field in + (select * from (select column_name from information_schema.columns + where table_name='t1' + group by column_name) dt); + +use test; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 74ac1a3..031baee 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5187,7 +5187,6 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); DBUG_ENTER("open_normal_and_derived_tables"); - DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags, &prelocking_strategy) || mysql_handle_derived(thd->lex, dt_phases)) goto end; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 5604658..3866d82 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3358,8 +3358,6 @@ bool LEX::only_view_structure() switch (sql_command) { case SQLCOM_SHOW_CREATE: case SQLCOM_CHECKSUM: - case SQLCOM_SHOW_TABLES: - case SQLCOM_SHOW_FIELDS: case SQLCOM_REVOKE_ALL: case SQLCOM_REVOKE: case SQLCOM_GRANT: diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 6b020f6..bf72c59 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -8140,7 +8140,9 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list) CHARSET_INFO *cs= system_charset_info; MEM_ROOT *mem_root= thd->mem_root; bool need_all_fieds= table_list->schema_table_reformed || // SHOW command - thd->lex->only_view_structure(); // need table structure + thd->lex->only_view_structure() || // need table structure + thd->lex->sql_command == SQLCOM_SHOW_FIELDS || + thd->lex->sql_command == SQLCOM_SHOW_TABLES; DBUG_ENTER("create_schema_table"); for (; fields_info->field_name; fields_info++)

1 0

[Commits] dbf4955: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition
by IgorBabaev 29 Apr '21

29 Apr '21

revision-id: dbf4955d2a43b6c2f6cbd90ac26b9c53458e0cc3 (mariadb-10.3.26-139-gdbf4955) parent(s): 2c9bf0ae8758b2c46ea5e02d1ea3d3ab5cab63b2 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-28 23:22:12 -0700 message: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition --- mysql-test/main/derived.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/derived.test | 29 +++++++++++++++++++++++++++++ sql/.#sql_base.cc# | 1 + sql/sql_base.cc | 1 - sql/sql_lex.cc | 2 -- sql/sql_show.cc | 4 +++- 6 files changed, 64 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 0f91750..975c18c 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1260,3 +1260,34 @@ a a 4 4 6 6 drop table t1,t2,t3; +# +# MDEV-21603: materialized derived used in SHOW TABLES +# +create table t1 (nm varchar(32), a int); +insert into t1 values ('1',1),('2',2),('3',3); +use mysql; +show tables +where tables_in_mysql in (select * +from (select nm from test.t1 group by nm) dt); +Tables_in_mysql +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +insert into test.t1 values ('nm',0); +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +show fields from test.t1 +where Field in +(select * from (select column_name from information_schema.columns +where table_name='t1' + group by column_name) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +a int(11) YES NULL +use test; +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index f4477ce..cad22aa 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1069,3 +1069,32 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; + +--echo # +--echo # MDEV-21603: materialized derived used in SHOW TABLES +--echo # + +create table t1 (nm varchar(32), a int); +insert into t1 values ('1',1),('2',2),('3',3); +use mysql; +show tables + where tables_in_mysql in (select * + from (select nm from test.t1 group by nm) dt); +show fields from test.t1 + where Field in (select * from (select nm from test.t1 group by nm) dt); +insert into test.t1 values ('nm',0); +show fields from test.t1 + where Field in (select * from (select nm from test.t1 group by nm) dt); + +show fields from test.t1 + where Field in + (select * from (select column_name from information_schema.columns + where table_name='t1' + group by column_name) dt); + +use test; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/.#sql_base.cc# b/sql/.#sql_base.cc# new file mode 120000 index 0000000..f7a706b --- /dev/null +++ b/sql/.#sql_base.cc# @@ -0,0 +1 @@ +igor(a)stephan.7269 \ No newline at end of file diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 74ac1a3..031baee 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5187,7 +5187,6 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); DBUG_ENTER("open_normal_and_derived_tables"); - DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags, &prelocking_strategy) || mysql_handle_derived(thd->lex, dt_phases)) goto end; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 5604658..3866d82 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3358,8 +3358,6 @@ bool LEX::only_view_structure() switch (sql_command) { case SQLCOM_SHOW_CREATE: case SQLCOM_CHECKSUM: - case SQLCOM_SHOW_TABLES: - case SQLCOM_SHOW_FIELDS: case SQLCOM_REVOKE_ALL: case SQLCOM_REVOKE: case SQLCOM_GRANT: diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 6b020f6..bf72c59 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -8140,7 +8140,9 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list) CHARSET_INFO *cs= system_charset_info; MEM_ROOT *mem_root= thd->mem_root; bool need_all_fieds= table_list->schema_table_reformed || // SHOW command - thd->lex->only_view_structure(); // need table structure + thd->lex->only_view_structure() || // need table structure + thd->lex->sql_command == SQLCOM_SHOW_FIELDS || + thd->lex->sql_command == SQLCOM_SHOW_TABLES; DBUG_ENTER("create_schema_table"); for (; fields_info->field_name; fields_info++)

1 0

[Commits] 1fd7d71: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition
by IgorBabaev 29 Apr '21

29 Apr '21

revision-id: 1fd7d716d99ecbf4de97a5346a77b87bef32ca8d (mariadb-10.2.31-918-g1fd7d71) parent(s): b1ac251bf1668d5d2472c2c520d6db71fb835065 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-28 23:12:19 -0700 message: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition --- mysql-test/r/derived.result | 28 ++++++++++++++++++++++++++++ sql/sql_base.cc | 1 - sql/sql_lex.cc | 2 -- 3 files changed, 28 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 2106ba5..c374669 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1204,5 +1204,33 @@ REPLACE INTO v2 ( SELECT * FROM v4 ) UNION ( SELECT f FROM v2 ); drop view v1,v2,v3,v4; drop table t1,t2,t3; # +# MDEV-21603: materialized derived used in SHOW TABLES +# +create table t1 (nm varchar(32), a int); +insert into t1 values ('1',1),('2',2),('3',3); +use mysql; +show tables +where tables_in_mysql in (select * +from (select nm from test.t1 group by nm) dt); +Tables_in_mysql +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +insert into test.t1 values ('nm',0); +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +show fields from test.t1 +where Field in +(select * from (select column_name from information_schema.columns +where table_name='t1' + group by column_name) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +a int(11) YES NULL +use test; +drop table t1; +# # End of 10.2 tests # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 3403f9e..16689f8 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4939,7 +4939,6 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); DBUG_ENTER("open_normal_and_derived_tables"); - DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags, &prelocking_strategy) || mysql_handle_derived(thd->lex, dt_phases)) goto end; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c534ba7..149a2bb 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3095,8 +3095,6 @@ bool LEX::only_view_structure() { switch (sql_command) { case SQLCOM_SHOW_CREATE: - case SQLCOM_SHOW_TABLES: - case SQLCOM_SHOW_FIELDS: case SQLCOM_REVOKE_ALL: case SQLCOM_REVOKE: case SQLCOM_GRANT:

1 0

[Commits] b1ac251: Another correction of the patch for MDEV-24823.
by IgorBabaev 29 Apr '21

29 Apr '21

revision-id: b1ac251bf1668d5d2472c2c520d6db71fb835065 (mariadb-10.2.31-917-gb1ac251) parent(s): e788738e18bd5cedb21f75b21f902f1bbcc34719 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-28 17:39:04 -0700 message: Another correction of the patch for MDEV-24823. This commits replaces the call of the function setup_tables() with a call of the function setup_tables_and_check_access() in the method Multiupdate_prelocking_strategy::handle_end(). There is no known bug that would require this change. However the change aligns this piece of code with the code existed before the patch for MDEV-24823. --- sql/sql_update.cc | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/sql/sql_update.cc b/sql/sql_update.cc index e8b973c..ec27ccd 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1390,8 +1390,9 @@ bool Multiupdate_prelocking_strategy::handle_end(THD *thd) call in setup_tables()). */ - if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, - table_list, select_lex->leaf_tables, FALSE, TRUE)) + if (setup_tables_and_check_access(thd, &select_lex->context, + &select_lex->top_join_list, table_list, select_lex->leaf_tables, + FALSE, UPDATE_ACL, SELECT_ACL, TRUE)) DBUG_RETURN(1); List<Item> *fields= &lex->select_lex.item_list;

1 0

[Commits] e1bafe4: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition
by IgorBabaev 29 Apr '21

29 Apr '21

revision-id: e1bafe440600a2909aaab476e9e7715945e450c1 (mariadb-10.3.26-139-ge1bafe4) parent(s): 2c9bf0ae8758b2c46ea5e02d1ea3d3ab5cab63b2 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-28 16:55:02 -0700 message: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition --- mysql-test/main/derived.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/derived.test | 29 +++++++++++++++++++++++++++++ sql/sql_base.cc | 2 +- sql/sql_lex.cc | 2 -- sql/sql_show.cc | 4 +++- 5 files changed, 64 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 0f91750..975c18c 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1260,3 +1260,34 @@ a a 4 4 6 6 drop table t1,t2,t3; +# +# MDEV-21603: materialized derived used in SHOW TABLES +# +create table t1 (nm varchar(32), a int); +insert into t1 values ('1',1),('2',2),('3',3); +use mysql; +show tables +where tables_in_mysql in (select * +from (select nm from test.t1 group by nm) dt); +Tables_in_mysql +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +insert into test.t1 values ('nm',0); +show fields from test.t1 +where Field in (select * from (select nm from test.t1 group by nm) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +show fields from test.t1 +where Field in +(select * from (select column_name from information_schema.columns +where table_name='t1' + group by column_name) dt); +Field Type Null Key Default Extra +nm varchar(32) YES NULL +a int(11) YES NULL +use test; +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index f4477ce..cad22aa 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1069,3 +1069,32 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; + +--echo # +--echo # MDEV-21603: materialized derived used in SHOW TABLES +--echo # + +create table t1 (nm varchar(32), a int); +insert into t1 values ('1',1),('2',2),('3',3); +use mysql; +show tables + where tables_in_mysql in (select * + from (select nm from test.t1 group by nm) dt); +show fields from test.t1 + where Field in (select * from (select nm from test.t1 group by nm) dt); +insert into test.t1 values ('nm',0); +show fields from test.t1 + where Field in (select * from (select nm from test.t1 group by nm) dt); + +show fields from test.t1 + where Field in + (select * from (select column_name from information_schema.columns + where table_name='t1' + group by column_name) dt); + +use test; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 74ac1a3..dda9452 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5187,7 +5187,7 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); DBUG_ENTER("open_normal_and_derived_tables"); - DBUG_ASSERT(!thd->fill_derived_tables()); + // DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags, &prelocking_strategy) || mysql_handle_derived(thd->lex, dt_phases)) goto end; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 5604658..3866d82 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3358,8 +3358,6 @@ bool LEX::only_view_structure() switch (sql_command) { case SQLCOM_SHOW_CREATE: case SQLCOM_CHECKSUM: - case SQLCOM_SHOW_TABLES: - case SQLCOM_SHOW_FIELDS: case SQLCOM_REVOKE_ALL: case SQLCOM_REVOKE: case SQLCOM_GRANT: diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 6b020f6..bf72c59 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -8140,7 +8140,9 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list) CHARSET_INFO *cs= system_charset_info; MEM_ROOT *mem_root= thd->mem_root; bool need_all_fieds= table_list->schema_table_reformed || // SHOW command - thd->lex->only_view_structure(); // need table structure + thd->lex->only_view_structure() || // need table structure + thd->lex->sql_command == SQLCOM_SHOW_FIELDS || + thd->lex->sql_command == SQLCOM_SHOW_TABLES; DBUG_ENTER("create_schema_table"); for (; fields_info->field_name; fields_info++)

1 0

[Commits] 4a24472: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition
by IgorBabaev 27 Apr '21

27 Apr '21

revision-id: 4a24472601eb9d9f4e14f3ad9c1c1537b6e46bfb (mariadb-10.2.31-895-g4a24472) parent(s): 1288dfffe77a99d6c5906d12010a1677ee149308 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-26 15:38:07 -0700 message: MDEV-21603 Crashing SHOW TABLES with derived table in WHERE condition --- mysql-test/r/derived.result | 12 ++++++++++++ mysql-test/t/derived.test | 14 ++++++++++++++ sql/sql_base.cc | 1 - sql/sql_lex.cc | 2 -- 4 files changed, 26 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 2106ba5..c55c5af 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1204,5 +1204,17 @@ REPLACE INTO v2 ( SELECT * FROM v4 ) UNION ( SELECT f FROM v2 ); drop view v1,v2,v3,v4; drop table t1,t2,t3; # +# MDEV-21603: materialized derived used in SHOW TABLES +# +create table t1 (nm varchar(32)); +insert into t1 values ('1'),('2'),('3'); +use mysql; +show tables +where tables_in_mysql in (select * +from (select * from test.t1 group by nm) dt); +Tables_in_mysql +use test; +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 6d9d5e2..b099ace 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -1036,6 +1036,20 @@ REPLACE INTO v2 ( SELECT * FROM v4 ) UNION ( SELECT f FROM v2 ); drop view v1,v2,v3,v4; drop table t1,t2,t3; + +--echo # +--echo # MDEV-21603: materialized derived used in SHOW TABLES +--echo # + +create table t1 (nm varchar(32)); +insert into t1 values ('1'),('2'),('3'); +use mysql; +show tables + where tables_in_mysql in (select * + from (select * from test.t1 group by nm) dt); +use test; +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 3403f9e..16689f8 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4939,7 +4939,6 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); DBUG_ENTER("open_normal_and_derived_tables"); - DBUG_ASSERT(!thd->fill_derived_tables()); if (open_tables(thd, &tables, &counter, flags, &prelocking_strategy) || mysql_handle_derived(thd->lex, dt_phases)) goto end; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 57c6dfa..0b78d53 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3105,8 +3105,6 @@ bool LEX::only_view_structure() { switch (sql_command) { case SQLCOM_SHOW_CREATE: - case SQLCOM_SHOW_TABLES: - case SQLCOM_SHOW_FIELDS: case SQLCOM_REVOKE_ALL: case SQLCOM_REVOKE: case SQLCOM_GRANT:

1 0

[Commits] 2f6912dabcb: MDEV-24898: Server crashes in st_select_lex::next_select
by psergey 25 Apr '21

25 Apr '21

revision-id: 2f6912dabcb85382eea004f590ad51815c20e5c5 (mariadb-10.2.31-898-g2f6912dabcb) parent(s): c72c77ca3bcb9d29903f95bf37c9930224984d29 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-25 21:23:56 +0300 message: MDEV-24898: Server crashes in st_select_lex::next_select (trivial backport to 10.2) Add a testcase --- mysql-test/r/subselect4.result | 11 +++++++++++ mysql-test/t/subselect4.test | 13 +++++++++++++ 2 files changed, 24 insertions(+) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index ef75bd97fcc..4021f717964 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2772,3 +2772,14 @@ GROUP BY 1 ) 1 DROP TABLE t1; +# +# MDEV-24898: Server crashes in st_select_lex::next_select / Item_subselect::is_expensive +# (Testcase) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +SELECT 1 IN (SELECT (SELECT a FROM t1) AS x FROM t2 GROUP BY x); +ERROR 21000: Subquery returns more than 1 row +drop table t1,t2; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index dae9e71fd92..e218e3aab18 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2269,3 +2269,16 @@ SELECT ); DROP TABLE t1; + +--echo # +--echo # MDEV-24898: Server crashes in st_select_lex::next_select / Item_subselect::is_expensive +--echo # (Testcase) +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); # Optional, fails either way +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); # Optional, fails either way + +--error ER_SUBQUERY_NO_1_ROW +SELECT 1 IN (SELECT (SELECT a FROM t1) AS x FROM t2 GROUP BY x); +drop table t1,t2;

1 0