- commits - lists.mariadb.org

[Commits] ed09f18: Applied the changes introduced in the commit
by IgorBabaev 17 Aug '22

17 Aug '22

revision-id: ed09f18ecab2992443aca7d8c771186913d62492 (mariadb-10.10.1-7-ged09f18) parent(s): 52618028b0ea9292b70ba0f63171320b5065dadc author: Igor Babaev committer: Igor Babaev timestamp: 2022-08-17 16:57:40 -0700 message: Applied the changes introduced in the commit 92a328099800486fe585a54d31ddf84c02ec6ae0 Author: Oleksandr Byelkin <sanja(a)mariadb.com> Tue Jul 12 00:25:08 2022 Committer: Oleksandr Byelkin <sanja(a)mariadb.com> Thu Jul 14 00:46:06 2022 for the code of MDEV-28883. --- sql/sql_delete.cc | 2 +- sql/sql_lex.cc | 2 +- sql/sql_lex.h | 9 --------- sql/sql_yacc.yy | 2 +- 4 files changed, 3 insertions(+), 12 deletions(-) diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 646f043..8acc502 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1537,7 +1537,7 @@ bool Sql_cmd_delete::prepare_inner(THD *thd) } if (!(result= new (thd->mem_root) multi_delete(thd, aux_tables, - lex->table_count))) + lex->table_count_update))) { DBUG_RETURN(TRUE); } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index bc11a51..8f9d042 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1304,7 +1304,7 @@ void LEX::start(THD *thd_arg) wild= 0; exchange= 0; - table_count= 0; + table_count_update= 0; DBUG_VOID_RETURN; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 44b33bb..4e16247 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1774,15 +1774,6 @@ class Query_tables_list return (lock_tables_state == LTS_LOCKED); } - /** - Number of tables which were open by open_tables() and to be locked - by lock_tables(). - Note that we set this member only in some cases, when this value - needs to be passed from open_tables() to lock_tables() which are - separated by some amount of code. - */ - uint table_count; - /* These constructor and destructor serve for creation/destruction of Query_tables_list instances which are used as backup storage. diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 20b4086..13f0dbd 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13385,7 +13385,7 @@ delete_single_table: MYSQL_YYABORT; Select->table_list.save_and_clear(&Lex->auxiliary_table_list); /* Save the number of auxiliary tables */ - Lex->table_count= 1; + Lex->table_count_update= 1; Lex->query_tables= 0; Lex->query_tables_last= &Lex->query_tables;

1 0

[Commits] b69cd6d: Correction after rebase of bb-10.10-MDEV-28883 against the latest 10.10.
by IgorBabaev 17 Aug '22

17 Aug '22

revision-id: b69cd6d4baadb7441e8afa35ac4857f619d5bb43 (mariadb-10.10.1-8-gb69cd6d) parent(s): 62b7ebb3f48596c89030064217cbafc6449554a3 author: Igor Babaev committer: Igor Babaev timestamp: 2022-08-16 21:43:40 -0700 message: Correction after rebase of bb-10.10-MDEV-28883 against the latest 10.10. --- sql/sql_yacc.yy | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 20b4086..13f0dbd 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13385,7 +13385,7 @@ delete_single_table: MYSQL_YYABORT; Select->table_list.save_and_clear(&Lex->auxiliary_table_list); /* Save the number of auxiliary tables */ - Lex->table_count= 1; + Lex->table_count_update= 1; Lex->query_tables= 0; Lex->query_tables_last= &Lex->query_tables;

1 0

[Commits] 87e8463: Fixed some compiler issues appeared after d7ffb7c3dd95c4c5e36f75b09662faafa0fb1ecd
by IgorBabaev 16 Aug '22

16 Aug '22

revision-id: 87e8463e0454a04c2bbaa38d44227c491fb07dc1 (mariadb-10.10.1-2-g87e8463) parent(s): 63e3fc0bb76ce8b4b018d1ca1e4be6b7cd55fad1 author: Igor Babaev committer: Igor Babaev timestamp: 2022-08-16 09:26:27 -0700 message: Fixed some compiler issues appeared after d7ffb7c3dd95c4c5e36f75b09662faafa0fb1ecd Disabled atomic.rename_trigger. --- mysql-test/suite/atomic/disabled.def | 12 ++++++++++++ strings/ctype-uca.c | 2 +- strings/uca-dump.c | 6 ++++-- 3 files changed, 17 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/atomic/disabled.def b/mysql-test/suite/atomic/disabled.def new file mode 100644 index 0000000..3e1f2d6 --- /dev/null +++ b/mysql-test/suite/atomic/disabled.def @@ -0,0 +1,12 @@ +############################################################################## +# +# List the test cases that are to be disabled temporarily. +# +# Separate the test case name and the comment with ':'. +# +# <testcasename> : BUG#<xxxx> <date disabled> <disabler> <comment> +# +# Do not use any TAB characters for whitespace. +# +############################################################################## +rename_trigger : MDEV-29282 2022:-08-16 Igor Babaev diff --git a/strings/ctype-uca.c b/strings/ctype-uca.c index 5b3f76f..028d2cc 100644 --- a/strings/ctype-uca.c +++ b/strings/ctype-uca.c @@ -31309,7 +31309,7 @@ my_uca1400_collation_definitions[MY_UCA1400_COLLATION_DEFINITION_COUNT]= static MY_UCA_INFO my_uca1400_info_tailored[MY_CS_ENCODING_LAST+1] - [MY_UCA1400_COLLATION_DEFINITION_COUNT]= {0}; + [MY_UCA1400_COLLATION_DEFINITION_COUNT]; /* diff --git a/strings/uca-dump.c b/strings/uca-dump.c index 3c94cb8..989fe98 100644 --- a/strings/uca-dump.c +++ b/strings/uca-dump.c @@ -762,7 +762,7 @@ int main(int ac, char **av) { char *comment; char *weight; - MY_DUCET_CHARS chr = {0}; + MY_DUCET_CHARS chr = {{0,0,0,0,0,0}, 0}; if (str[0] == '#') continue; @@ -803,7 +803,9 @@ int main(int ac, char **av) } else { - MY_DUCET_WEIGHT weights= {0}; + MY_DUCET_WEIGHT weights= {{{0,0,0,0,0,0,0,0,0}, {0,0,0,0,0,0,0,0,0}, + {0,0,0,0,0,0,0,0,0}, {0,0,0,0,0,0,0,0,0}}, + 0}; my_bool dummy; if (chr.length >= MY_UCA_MAX_CONTRACTION) {

1 0

[Commits] 66fabd3: Fixed some compiler issues appeared after d7ffb7c3dd95c4c5e36f75b09662faafa0fb1ecd
by IgorBabaev 15 Aug '22

15 Aug '22

revision-id: 66fabd3e88d794477a43ffdb75faeac282daa3ed (mariadb-10.10.1-2-g66fabd3) parent(s): 63e3fc0bb76ce8b4b018d1ca1e4be6b7cd55fad1 author: Igor Babaev committer: Igor Babaev timestamp: 2022-08-15 15:58:20 -0700 message: Fixed some compiler issues appeared after d7ffb7c3dd95c4c5e36f75b09662faafa0fb1ecd --- strings/ctype-uca.c | 2 +- strings/uca-dump.c | 6 ++++-- 2 files changed, 5 insertions(+), 3 deletions(-) diff --git a/strings/ctype-uca.c b/strings/ctype-uca.c index 5b3f76f..028d2cc 100644 --- a/strings/ctype-uca.c +++ b/strings/ctype-uca.c @@ -31309,7 +31309,7 @@ my_uca1400_collation_definitions[MY_UCA1400_COLLATION_DEFINITION_COUNT]= static MY_UCA_INFO my_uca1400_info_tailored[MY_CS_ENCODING_LAST+1] - [MY_UCA1400_COLLATION_DEFINITION_COUNT]= {0}; + [MY_UCA1400_COLLATION_DEFINITION_COUNT]; /* diff --git a/strings/uca-dump.c b/strings/uca-dump.c index 3c94cb8..989fe98 100644 --- a/strings/uca-dump.c +++ b/strings/uca-dump.c @@ -762,7 +762,7 @@ int main(int ac, char **av) { char *comment; char *weight; - MY_DUCET_CHARS chr = {0}; + MY_DUCET_CHARS chr = {{0,0,0,0,0,0}, 0}; if (str[0] == '#') continue; @@ -803,7 +803,9 @@ int main(int ac, char **av) } else { - MY_DUCET_WEIGHT weights= {0}; + MY_DUCET_WEIGHT weights= {{{0,0,0,0,0,0,0,0,0}, {0,0,0,0,0,0,0,0,0}, + {0,0,0,0,0,0,0,0,0}, {0,0,0,0,0,0,0,0,0}}, + 0}; my_bool dummy; if (chr.length >= MY_UCA_MAX_CONTRACTION) {

1 0

[Commits] c5c307a: MDEV-28617 Crash with INSERT...SELECT using derived table in GROUP BY clause
by IgorBabaev 02 Aug '22

02 Aug '22

revision-id: c5c307abdd823d36be920e0937a96daed15af8c0 (mariadb-10.3.35-93-gc5c307a) parent(s): bd935a41060199a17019453d6e187e8edd7929ba author: Igor Babaev committer: Igor Babaev timestamp: 2022-08-01 20:52:10 -0700 message: MDEV-28617 Crash with INSERT...SELECT using derived table in GROUP BY clause This bug manifested itself for INSERT...SELECT and DELETE statements whose WHERE condition used an IN/ANY/ALL predicand or a EXISTS predicate with such grouping subquery that: - its GROUP BY clause could be eliminated, - the GROUP clause contained a subquery over a mergeable derived table referencing the updated table. The bug ultimately caused a server crash when the prepare phase of the statement processing was executed. This happened after removal redundant subqueries used in the eliminated GROUP BY clause from the statement tree. The function that excluded the subqueries from the did not do it properly. As a result the specification of any derived table contained in a removed subquery was not marked as excluded. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/insert_select.result | 89 ++++++++++++++++++++++++++++++++++++ mysql-test/main/insert_select.test | 82 +++++++++++++++++++++++++++++++++ sql/sql_lex.cc | 7 +-- 3 files changed, 175 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index ea77053..3f9ac8c 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -954,3 +954,92 @@ ERROR 23000: Duplicate entry '-128' for key 'a' DROP TABLE t1, t2; DROP PROCEDURE p1; # End of 10.2 test +# +# MDEV-28617: INSERT ... SELECT with redundant IN subquery in GROUP BY +# list that uses mergeable derived table containing +# reference to target table +# +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); +insert into t1 +select b from t2 +where b in (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +insert into t1 +select b from t2 +where b >= any (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +3 +2 +delete from t1; +insert into t1 values (3), (1); +insert into t1 +select b from t2 +where b <= all (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +insert into t1 +select b from t2 +where exists (select c from t3 +group by (select * from (select a from t1) dt where a = 1)); +select * from t1; +a +3 +1 +3 +2 +delete from t1; +insert into t1 values (3), (1); +prepare stmt from " +insert into t1 +select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +"; +execute stmt; +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +execute stmt; +select * from t1; +a +3 +1 +2 +delete from t1; +insert into t1 values (3), (1); +delete from t1 +where exists (select b from t2 +where b in (select c from t3 +group by (select * from (select a from t1) dt +where a = 1))); +select * from t1; +a +deallocate prepare stmt; +drop table t1,t2,t3; +# End of 10.3 test diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 1f672ac..6baa7e4 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -514,3 +514,85 @@ DROP TABLE t1, t2; DROP PROCEDURE p1; --echo # End of 10.2 test + +--echo # +--echo # MDEV-28617: INSERT ... SELECT with redundant IN subquery in GROUP BY +--echo # list that uses mergeable derived table containing +--echo # reference to target table +--echo # + +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); + +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); + +insert into t1 +select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +insert into t1 +select b from t2 + where b >= any (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +insert into t1 +select b from t2 + where b <= all (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +insert into t1 +select b from t2 + where exists (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +prepare stmt from " +insert into t1 +select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt where a = 1)); +"; + +execute stmt; +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +execute stmt; +select * from t1; + +delete from t1; +insert into t1 values (3), (1); + +delete from t1 + where exists (select b from t2 + where b in (select c from t3 + group by (select * from (select a from t1) dt + where a = 1))); +select * from t1; + +deallocate prepare stmt; + +drop table t1,t2,t3; + +--echo # End of 10.3 test diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index e4c5b7c..3400e31 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2530,6 +2530,7 @@ void st_select_lex_node::fast_exclude() for (; slave; slave= slave->next) slave->fast_exclude(); + prev= NULL; // to ensure correct behavior of st_select_lex_unit::is_excluded() } @@ -2604,9 +2605,7 @@ void st_select_lex_node::exclude_from_tree() */ void st_select_lex_node::exclude() { - /* exclude from global list */ - fast_exclude(); - /* exclude from other structures */ + /* exclude the node from the tree */ exclude_from_tree(); /* We do not need following statements, because prev pointer of first @@ -2614,6 +2613,8 @@ void st_select_lex_node::exclude() if (master->slave == this) master->slave= next; */ + /* exclude all nodes under this excluded node */ + fast_exclude(); }

1 0

[Commits] 373a00f: MDEV-29189 Crash of the second execution of SF using DELETE/UPDATE
by IgorBabaev 29 Jul '22

29 Jul '22

revision-id: 373a00fea0ec8b1c949f90b2fc407b40035be4cc (mariadb-10.6.1-479-g373a00f) parent(s): b2906279f73cc9f37d436f0d3acdd05f6db5ceaa author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-29 14:20:35 -0700 message: MDEV-29189 Crash of the second execution of SF using DELETE/UPDATE This bug caused a crash of the server at the second execution of a stored function that used DELETE or UPDATE statement if the first execution of this function reported an error encountered after the prepare phase. This happened because in such cases the executed DELETE/UPDATE statement remained marked as prepared. As a result the second execution of SF missed the prepare phase for the statement altogether and the statement could not be executed properly. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/update.result | 38 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/update.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 2 ++ 3 files changed, 78 insertions(+) diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result index 15efd7e..c6c55da 100644 --- a/mysql-test/main/update.result +++ b/mysql-test/main/update.result @@ -734,3 +734,41 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1 DROP TABLE t1,t2; # End of MariaDB 10.2 tests +# +# MDEV-29189: Second execution of SF using UPDATE?DELETE +# after reported error by the first execution +# +CREATE TABLE t1 (c int); +CREATE FUNCTION f1() RETURNS int +BEGIN +UPDATE t1 SET c=c+1; +RETURN 1; +END;// +CREATE FUNCTION f2() RETURNS int +BEGIN +DELETE FROM t1 WHERE c < 7; +RETURN 1; +END;// +INSERT INTO t1 VALUES (3), (7), (1); +SELECT * FROM t1 WHERE f1() = 1; +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger +SELECT f1(); +f1() +1 +SELECT * FROM t1; +c +4 +8 +2 +SELECT * FROM t1 WHERE f2() = 1; +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger +SELECT f2(); +f2() +1 +SELECT * FROM t1; +c +8 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; +# End of MariaDB 10.10 tests diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test index 8470910..0f133b2 100644 --- a/mysql-test/main/update.test +++ b/mysql-test/main/update.test @@ -673,3 +673,41 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; DROP TABLE t1,t2; --echo # End of MariaDB 10.2 tests + +--echo # +--echo # MDEV-29189: Second execution of SF using UPDATE?DELETE +--echo # after reported error by the first execution +--echo # + +CREATE TABLE t1 (c int); + +DELIMITER //; +CREATE FUNCTION f1() RETURNS int +BEGIN + UPDATE t1 SET c=c+1; + RETURN 1; +END;// +CREATE FUNCTION f2() RETURNS int +BEGIN + DELETE FROM t1 WHERE c < 7; + RETURN 1; +END;// +DELIMITER ;// + +INSERT INTO t1 VALUES (3), (7), (1); + +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +SELECT * FROM t1 WHERE f1() = 1; +SELECT f1(); +SELECT * FROM t1; + +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +SELECT * FROM t1 WHERE f2() = 1; +SELECT f2(); +SELECT * FROM t1; + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + +--echo # End of MariaDB 10.10 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 60b14f9..0a42147 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -30677,6 +30677,8 @@ bool Sql_cmd_dml::execute(THD *thd) MYSQL_DML_DONE(thd, 1); THD_STAGE_INFO(thd, stage_end); (void)unit->cleanup(); + if (is_prepared()) + unprepare(thd); return thd->is_error(); }

1 0

[Commits] b290627: Assertion failure with UPDATE of view using MERGE table
by IgorBabaev 29 Jul '22

29 Jul '22

revision-id: b2906279f73cc9f37d436f0d3acdd05f6db5ceaa (mariadb-10.6.1-478-gb290627) parent(s): cd8b27bb537d03ed4042cdca3176ad7134b892a7 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-29 14:20:35 -0700 message: Assertion failure with UPDATE of view using MERGE table The problem was caused by an assertion that is not valid anymore. --- mysql-test/main/merge.result | 12 +++++++++--- mysql-test/main/merge.test | 16 ++++++++++++---- sql/sql_base.cc | 1 - 3 files changed, 21 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/merge.result b/mysql-test/main/merge.result index 2dcc5a1..155b048 100644 --- a/mysql-test/main/merge.result +++ b/mysql-test/main/merge.result @@ -3902,9 +3902,6 @@ DROP TABLE t1; DROP TABLE m1; set global default_storage_engine=@save_default_storage_engine; # -# End of 10.0 tests -# -# # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results # create table t (a int, key(a desc)) engine=myisam; @@ -3929,3 +3926,12 @@ drop table tm, t; # # End of 10.8 tests # +# +# MDEV-29174: UPDATE of view that uses MERGE table +# +CREATE TABLE t1 (a int) ENGINE=MERGE; +CREATE VIEW v1 AS SELECT a FROM t1; +UPDATE v1 SET a=0; +DROP VIEW v1; +DROP TABLE t1; +# End of 10.10 tests diff --git a/mysql-test/main/merge.test b/mysql-test/main/merge.test index 0485f3e..d8cf792 100644 --- a/mysql-test/main/merge.test +++ b/mysql-test/main/merge.test @@ -2860,10 +2860,6 @@ set global default_storage_engine=@save_default_storage_engine; --source include/wait_until_count_sessions.inc --echo # ---echo # End of 10.0 tests ---echo # - ---echo # --echo # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results --echo # create table t (a int, key(a desc)) engine=myisam; @@ -2886,3 +2882,15 @@ drop table tm, t; --echo # --echo # End of 10.8 tests --echo # + +--echo # +--echo # MDEV-29174: UPDATE of view that uses MERGE table +--echo # + +CREATE TABLE t1 (a int) ENGINE=MERGE; +CREATE VIEW v1 AS SELECT a FROM t1; +UPDATE v1 SET a=0; +DROP VIEW v1; +DROP TABLE t1; + +--echo # End of 10.10 tests diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 725a0ff..e815da3 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4743,7 +4743,6 @@ bool open_tables(THD *thd, const DDL_options_st &options, if (tbl->file->ha_table_flags() & HA_CAN_MULTISTEP_MERGE) { /* MERGE tables need to access parent and child TABLE_LISTs. */ - DBUG_ASSERT(tbl->pos_in_table_list == tables); if (tbl->file->extra(HA_EXTRA_ATTACH_CHILDREN)) { error= TRUE;

1 0

[Commits] b99f298: MDEV-29189 Crash of the second execution of SF using DELETE/UPDATE
by IgorBabaev 29 Jul '22

29 Jul '22

revision-id: b99f29887530ecf0e9358928bc227bee5ec32f01 (mariadb-10.6.1-484-gb99f298) parent(s): 444fa763472bf2faa2e939866539f1a0816c29b3 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-28 17:52:47 -0700 message: MDEV-29189 Crash of the second execution of SF using DELETE/UPDATE This bug caused a crash of the server at the second execution of a stored function that used DELETE or UPDATE statement if the first execution of this function reported an error encountered after the prepare phase. This happened because in such cases the executed DELETE/UPDATE statement remained marked as prepared. As a result the second execution of SF missed the prepare phase for the statement altogether and the statement could not be executed properly. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/update.result | 38 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/update.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 2 ++ 3 files changed, 78 insertions(+) diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result index 15efd7e..c6c55da 100644 --- a/mysql-test/main/update.result +++ b/mysql-test/main/update.result @@ -734,3 +734,41 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1 DROP TABLE t1,t2; # End of MariaDB 10.2 tests +# +# MDEV-29189: Second execution of SF using UPDATE?DELETE +# after reported error by the first execution +# +CREATE TABLE t1 (c int); +CREATE FUNCTION f1() RETURNS int +BEGIN +UPDATE t1 SET c=c+1; +RETURN 1; +END;// +CREATE FUNCTION f2() RETURNS int +BEGIN +DELETE FROM t1 WHERE c < 7; +RETURN 1; +END;// +INSERT INTO t1 VALUES (3), (7), (1); +SELECT * FROM t1 WHERE f1() = 1; +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger +SELECT f1(); +f1() +1 +SELECT * FROM t1; +c +4 +8 +2 +SELECT * FROM t1 WHERE f2() = 1; +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger +SELECT f2(); +f2() +1 +SELECT * FROM t1; +c +8 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; +# End of MariaDB 10.10 tests diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test index 8470910..0f133b2 100644 --- a/mysql-test/main/update.test +++ b/mysql-test/main/update.test @@ -673,3 +673,41 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; DROP TABLE t1,t2; --echo # End of MariaDB 10.2 tests + +--echo # +--echo # MDEV-29189: Second execution of SF using UPDATE?DELETE +--echo # after reported error by the first execution +--echo # + +CREATE TABLE t1 (c int); + +DELIMITER //; +CREATE FUNCTION f1() RETURNS int +BEGIN + UPDATE t1 SET c=c+1; + RETURN 1; +END;// +CREATE FUNCTION f2() RETURNS int +BEGIN + DELETE FROM t1 WHERE c < 7; + RETURN 1; +END;// +DELIMITER ;// + +INSERT INTO t1 VALUES (3), (7), (1); + +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +SELECT * FROM t1 WHERE f1() = 1; +SELECT f1(); +SELECT * FROM t1; + +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +SELECT * FROM t1 WHERE f2() = 1; +SELECT f2(); +SELECT * FROM t1; + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + +--echo # End of MariaDB 10.10 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 60b14f9..0a42147 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -30677,6 +30677,8 @@ bool Sql_cmd_dml::execute(THD *thd) MYSQL_DML_DONE(thd, 1); THD_STAGE_INFO(thd, stage_end); (void)unit->cleanup(); + if (is_prepared()) + unprepare(thd); return thd->is_error(); }

1 0

[Commits] 444fa76: Assertion failure with UPDATE of view using MERGE table
by IgorBabaev 27 Jul '22

27 Jul '22

revision-id: 444fa763472bf2faa2e939866539f1a0816c29b3 (mariadb-10.6.1-483-g444fa76) parent(s): 73a3e39c25c072cd796be151054fe70dc27c67e6 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-26 21:24:01 -0700 message: Assertion failure with UPDATE of view using MERGE table The problem was caused by an assertion that is not valid anymore. --- mysql-test/main/merge.result | 12 +++++++++--- mysql-test/main/merge.test | 16 ++++++++++++---- sql/sql_base.cc | 1 - 3 files changed, 21 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/merge.result b/mysql-test/main/merge.result index 2dcc5a1..155b048 100644 --- a/mysql-test/main/merge.result +++ b/mysql-test/main/merge.result @@ -3902,9 +3902,6 @@ DROP TABLE t1; DROP TABLE m1; set global default_storage_engine=@save_default_storage_engine; # -# End of 10.0 tests -# -# # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results # create table t (a int, key(a desc)) engine=myisam; @@ -3929,3 +3926,12 @@ drop table tm, t; # # End of 10.8 tests # +# +# MDEV-29174: UPDATE of view that uses MERGE table +# +CREATE TABLE t1 (a int) ENGINE=MERGE; +CREATE VIEW v1 AS SELECT a FROM t1; +UPDATE v1 SET a=0; +DROP VIEW v1; +DROP TABLE t1; +# End of 10.10 tests diff --git a/mysql-test/main/merge.test b/mysql-test/main/merge.test index 0485f3e..d8cf792 100644 --- a/mysql-test/main/merge.test +++ b/mysql-test/main/merge.test @@ -2860,10 +2860,6 @@ set global default_storage_engine=@save_default_storage_engine; --source include/wait_until_count_sessions.inc --echo # ---echo # End of 10.0 tests ---echo # - ---echo # --echo # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results --echo # create table t (a int, key(a desc)) engine=myisam; @@ -2886,3 +2882,15 @@ drop table tm, t; --echo # --echo # End of 10.8 tests --echo # + +--echo # +--echo # MDEV-29174: UPDATE of view that uses MERGE table +--echo # + +CREATE TABLE t1 (a int) ENGINE=MERGE; +CREATE VIEW v1 AS SELECT a FROM t1; +UPDATE v1 SET a=0; +DROP VIEW v1; +DROP TABLE t1; + +--echo # End of 10.10 tests diff --git a/sql/sql_base.cc b/sql/sql_base.cc index e216caf..ae61882 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4762,7 +4762,6 @@ bool open_tables(THD *thd, const DDL_options_st &options, if (tbl->file->ha_table_flags() & HA_CAN_MULTISTEP_MERGE) { /* MERGE tables need to access parent and child TABLE_LISTs. */ - DBUG_ASSERT(tbl->pos_in_table_list == tables); if (tbl->file->extra(HA_EXTRA_ATTACH_CHILDREN)) { error= TRUE;

1 0

[Commits] bd935a4: MDEV-29139 Crash when using ANY predicand with redundant subquery in GROUP BY clause
by IgorBabaev 27 Jul '22

27 Jul '22

revision-id: bd935a41060199a17019453d6e187e8edd7929ba (mariadb-10.3.35-92-gbd935a4) parent(s): e8eb6d9c936bab05b6058a329f9770840e6e3cff author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-26 18:03:34 -0700 message: MDEV-29139 Crash when using ANY predicand with redundant subquery in GROUP BY clause This bug could cause a crash of the server when executing queries containing ANY/ALL predicands with redundant subqueries in GROUP BY clauses. These subqueries are eliminated by remove_redundant_subquery_clause() together with elimination of GROUP BY list containing these subqueries. However the references to the elements of the GROUP BY remained in the JOIN::all_fields list of the right operand of of the ALL/ANY predicand. Later these references confused make_aggr_tables_info() when forming proper execution structures after ALL/ANY predicands had been replaced with expressions containing MIN/MAX set functions. The patch just removes these references from JOIN::all_fields list used by the subquery of the ALL/ANY predicand when its GROUP BY clause is eliminated. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/subselect4.result | 57 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 39 +++++++++++++++++++++++++++ sql/sql_select.cc | 15 +++++++++++ 3 files changed, 111 insertions(+) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index ba5a6bb..c374040 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2981,4 +2981,61 @@ ANALYZE } DROP TABLE t1; # End of 10.2 tests +# +# MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +# +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); +explain extended select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +a +3 +2 +prepare stmt from "select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1))"; +execute stmt; +a +3 +2 +execute stmt; +a +3 +2 +deallocate prepare stmt; +explain extended select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) < <cache>(`test`.`t1`.`a`))) +select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +a +1 +2 +explain extended select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +a +3 +2 +drop table t1,t2,t3; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index aeef884..2faede5 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2438,4 +2438,43 @@ DROP TABLE t1; --echo # End of 10.2 tests + +--echo # +--echo # MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +--echo # + +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); + +let $q1= +select a from t1 + where a >= any (select b from t2 group by (select c from t3 where c = 1)); + +eval explain extended $q1; +eval $q1; + +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select a from t1 + where a <= all (select b from t2 group by (select c from t3 where c = 1)); + +eval explain extended $q2; +eval $q2; + +let $q3= +select a from t1 + where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +eval explain extended $q3; +eval $q3; + +drop table t1,t2,t3; + --echo # End of 10.3 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0803e78..33fb199 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -605,7 +605,22 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) Here SUBQ cannot be removed. */ if (!ord->in_field_list) + { (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); + /* + Remove from the JOIN::all_fields list any reference to the elements + of the eliminated GROUP BY list unless it is 'in_field_list'. + This is needed in order not to confuse JOIN::make_aggr_tables_info() + when it constructs different structure for execution phase. + */ + List_iterator<Item> li(subq_select_lex->join->all_fields); + Item *item; + while ((item= li++)) + { + if (item == *ord->item) + li.remove(); + } + } } subq_select_lex->join->group_list= NULL; subq_select_lex->group_list.empty();

1 0