- commits - lists.mariadb.org

[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 28 Apr '21

28 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 26 Apr '21

26 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

[Commits] c72c77ca3bc: MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker
by psergey 25 Apr '21

25 Apr '21

revision-id: c72c77ca3bcb9d29903f95bf37c9930224984d29 (mariadb-10.2.31-897-gc72c77ca3bc) parent(s): 14a18d7d7f6293ad0e106288eab4fdcb3a72ebd9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-25 21:22:54 +0300 message: MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker (trivial backport to 10.2) The optimizer removes redundant GROUP BY operations. If GROUP BY element is a subselect, it is "eliminated". However one must not eliminate the item if it is used both in the select list and in the GROUP BY, like so: select (select ... ) as SUBQ from ... group by SUBQ Do not eliminate such items. --- mysql-test/r/subselect4.result | 50 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect4.test | 32 +++++++++++++++++++++++++++ sql/sql_select.cc | 11 +++++++++- 3 files changed, 92 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 2e24cbcb40c..ef75bd97fcc 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2722,3 +2722,53 @@ SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); a DROP TABLE t1,t2; # End of 10.2 tests +# +# MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +# +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +) +1 +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +) +1 +DROP TABLE t1; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index f19a654de64..dae9e71fd92 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2237,3 +2237,35 @@ SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); DROP TABLE t1,t2; --echo # End of 10.2 tests + +--echo # +--echo # MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +--echo # +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY f + ); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY 1 + ); + +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 90c071803a1..b85bd31e23c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -585,7 +585,16 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) { for (ORDER *ord= subq_select_lex->group_list.first; ord; ord= ord->next) { - (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); + /* + Do not remove the item if it is used in select list and then referred + from GROUP BY clause by its name or number. Example: + + select (select ... ) as SUBQ ... group by SUBQ + + Here SUBQ cannot be removed. + */ + if (!ord->in_field_list) + (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); } subq_select_lex->join->group_list= NULL; subq_select_lex->group_list.empty();

1 0

[Commits] 393cf51c045: MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker
by psergey 25 Apr '21

25 Apr '21

revision-id: 393cf51c045878c717ee7e17478755d093675802 (mariadb-10.3.26-139-g393cf51c045) parent(s): 2c9bf0ae8758b2c46ea5e02d1ea3d3ab5cab63b2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-25 10:32:09 +0300 message: MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker The optimizer removes redundant GROUP BY operations. If GROUP BY element is a subselect, it is "eliminated". However one must not eliminate the item if it is used both in the select list and in the GROUP BY, like so: select (select ... ) as SUBQ from ... group by SUBQ Do not eliminate such items. --- mysql-test/main/subselect4.result | 50 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 32 +++++++++++++++++++++++++ sql/sql_select.cc | 11 ++++++++- 3 files changed, 92 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 156e78e7778..456b9bcd829 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2786,4 +2786,54 @@ id select_type table type possible_keys key key_len ref rows Extra set names default; set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; DROP TABLE t1,t2; +# +# MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +# +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +) +1 +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +); +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +) +1 +DROP TABLE t1; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index a5fcc507905..ab311d3e505 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2308,4 +2308,36 @@ set names default; set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; DROP TABLE t1,t2; +--echo # +--echo # MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +--echo # +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY f + ); + +SELECT + 1 IN ( + SELECT + (SELECT COUNT(id) + FROM t1 + WHERE t1_outer.id <> id + ) AS f + FROM + t1 AS t1_outer + GROUP BY 1 + ); + +DROP TABLE t1; + --echo # End of 10.3 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b4e6c505261..d53c592ff7b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -596,7 +596,16 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) { for (ORDER *ord= subq_select_lex->group_list.first; ord; ord= ord->next) { - (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); + /* + Do not remove the item if it is used in select list and then referred + from GROUP BY clause by its name or number. Example: + + select (select ... ) as SUBQ ... group by SUBQ + + Here SUBQ cannot be removed. + */ + if (!ord->in_field_list) + (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); } subq_select_lex->join->group_list= NULL; subq_select_lex->group_list.empty();

1 0

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

25 Apr '21

revision-id: 42aad65b895dff937aa0618b3237eb4f653ce0e4 (mariadb-10.3.26-140-g42aad65b895) parent(s): 393cf51c045878c717ee7e17478755d093675802 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-25 10:32:09 +0300 message: MDEV-24898: Server crashes in st_select_lex::next_select Add a testcase --- mysql-test/main/subselect4.result | 11 +++++++++++ mysql-test/main/subselect4.test | 13 +++++++++++++ 2 files changed, 24 insertions(+) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 456b9bcd829..6940795a9c1 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2836,4 +2836,15 @@ 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; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index ab311d3e505..31f6f55b4e4 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2340,4 +2340,17 @@ 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; + --echo # End of 10.3 tests

1 0