revision-id: 57fe50eab9ff01590ec3396dca0de9081ef0ef00 (mariadb-10.2.42-2-g57fe50eab9f) parent(s): 941bc7053616d5ca8c9e6538828c4a65802e8c3d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-02-10 14:25:07 +0300 message: MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294 The asserion failure was caused by this query select /*id=1*/ from t1 where col= ( select /*id=2*/ from ... where corr_cond1 union select /*id=4*/ from ... where corr_cond2) Here, - select with id=2 was correlated due to corr_cond1. - select with id=4 was initially correlated due to corr_cond2, but then the optimizer optimized away the correlation, making the select with id=4 uncorrelated. However, since select with id=2 remained correlated, the execution had to re-compute the whole UNION. When it tried to execute select with id=4, it hit an assertion (join buffer already free'd). This is because select with id=4 has freed its execution structures after it has been executed once. The select is uncorrelated, so it did not expect it would need to be executed for the second time. Fixed this by adding this logic in st_select_lex::optimize_unflattened_subqueries(): If a member of a UNION is correlated, mark all its members as correlated, so that they are prepared to be executed multiple times. --- mysql-test/r/subselect4.result | 4 +++- mysql-test/r/union_innodb.result | 34 ++++++++++++++++++++++++++++++ mysql-test/t/subselect4.test | 2 ++ mysql-test/t/union_innodb.test | 45 ++++++++++++++++++++++++++++++++++++++++ sql/sql_lex.cc | 16 +++++++++++++- 5 files changed, 99 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index b1db309ec18..2657977dae7 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1358,6 +1358,8 @@ INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0); CREATE TABLE t3 (f4 int,KEY (f4)) ; INSERT IGNORE INTO t3 VALUES (0),(0); set @@optimizer_switch='semijoin=off'; +# NOTE: the following should have 'SUBQUERY', not 'DEPENDENT SUBQUERY' +# for line with id=2, see MDEV-27794. EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 @@ -1367,7 +1369,7 @@ FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary 3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE diff --git a/mysql-test/r/union_innodb.result b/mysql-test/r/union_innodb.result new file mode 100644 index 00000000000..876897fa865 --- /dev/null +++ b/mysql-test/r/union_innodb.result @@ -0,0 +1,34 @@ +# +# MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294 +# +CREATE TABLE t1 (i1 int)engine=innodb; +INSERT INTO `t1` VALUES (62),(66); +CREATE TABLE t2 (i1 int) engine=innodb; +SELECT 1 FROM t1 +WHERE t1.i1 =( SELECT t1.i1 FROM t2 +UNION SELECT i1 FROM (t1 AS dt1 natural JOIN t2) +window w1 as (partition by t1.i1)); +1 +drop table t1,t2; +# Another testcase +CREATE TABLE t1 (i3 int NOT NULL, i1 int , i2 int , i4 int , PRIMARY key(i2)); +INSERT INTO t1 VALUES (6,72,98,98),(46,1,6952,0); +SELECT i1 FROM t1 +WHERE t1.i3 = +(SELECT ref_4.i2 FROM t1 AS ref_4 +WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4) +UNION +SELECT ref_6.i2 +FROM (t1 AS ref_5 JOIN t1 AS ref_6 ON ((ref_6.i1 > ref_6.i2) OR (ref_5.i4 < ref_5.i4))) +WHERE (t1.i2 >= t1.i2)); +i1 +drop table t1; +# +# MDEV-25761: Assertion `aggr != __null' failed in sub_select_postjoin_aggr +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY) engine=innodb; +INSERT INTO t1 VALUES (0),(4),(31); +CREATE TABLE t2 (i int) engine=innodb; +DELETE FROM t1 WHERE t1.a = +(SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a); +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index bd1e20cb5d6..93389571c5c 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1039,6 +1039,8 @@ INSERT IGNORE INTO t3 VALUES (0),(0); set @@optimizer_switch='semijoin=off'; +--echo # NOTE: the following should have 'SUBQUERY', not 'DEPENDENT SUBQUERY' +--echo # for line with id=2, see MDEV-27794. EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 diff --git a/mysql-test/t/union_innodb.test b/mysql-test/t/union_innodb.test new file mode 100644 index 00000000000..cb805a30bb4 --- /dev/null +++ b/mysql-test/t/union_innodb.test @@ -0,0 +1,45 @@ +--source include/have_innodb.inc + +--echo # +--echo # MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294 +--echo # + +CREATE TABLE t1 (i1 int)engine=innodb; +INSERT INTO `t1` VALUES (62),(66); +CREATE TABLE t2 (i1 int) engine=innodb; + +SELECT 1 FROM t1 +WHERE t1.i1 =( SELECT t1.i1 FROM t2 + UNION SELECT i1 FROM (t1 AS dt1 natural JOIN t2) + window w1 as (partition by t1.i1)); + +drop table t1,t2; + +--echo # Another testcase +CREATE TABLE t1 (i3 int NOT NULL, i1 int , i2 int , i4 int , PRIMARY key(i2)); +INSERT INTO t1 VALUES (6,72,98,98),(46,1,6952,0); + +SELECT i1 FROM t1 +WHERE t1.i3 = + (SELECT ref_4.i2 FROM t1 AS ref_4 + WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4) + UNION + SELECT ref_6.i2 + FROM (t1 AS ref_5 JOIN t1 AS ref_6 ON ((ref_6.i1 > ref_6.i2) OR (ref_5.i4 < ref_5.i4))) + WHERE (t1.i2 >= t1.i2)); + +drop table t1; + +--echo # +--echo # MDEV-25761: Assertion `aggr != __null' failed in sub_select_postjoin_aggr +--echo # + +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY) engine=innodb; +INSERT INTO t1 VALUES (0),(4),(31); + +CREATE TABLE t2 (i int) engine=innodb; + +DELETE FROM t1 WHERE t1.a = + (SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a); + +DROP TABLE t1,t2; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 125bbfe1bfd..47ff2836aba 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3898,7 +3898,21 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) } if (empty_union_result) subquery_predicate->no_rows_in_result(); - if (!is_correlated_unit) + + if (is_correlated_unit) + { + /* + Some parts of UNION are not correlated. This means we will need to + re-execute the whole UNION every time. Mark all parts of the UNION + as correlated so that they are prepared to be executed multiple + times (if we don't do that, some part of the UNION may free its + execution data at the end of first execution and crash on the second + execution) + */ + for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select()) + sl->uncacheable |= UNCACHEABLE_DEPENDENT; + } + else un->uncacheable&= ~UNCACHEABLE_DEPENDENT; subquery_predicate->is_correlated= is_correlated_unit; }