revision-id: 4f1527567845cb5a1c4445c1f08f8a4dc066757b (mariadb-10.2.16-43-g4f15275) parent(s): aad70e9b4cdd719c3f7a559764e37ba7eea7bc3e author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-25 23:24:11 -0700 message: MDEV-15087 Item_func::fix_fields: Assertion `used_tables_cache == 0' failed This bug manifested itself when executing queries over materialized derived tables /vies and with conjunctive always true predicates containing inexpensive subqueries. This bug disappeared after the patch mdev-15035 had been applied. --- mysql-test/r/derived_cond_pushdown.result | 19 +++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 25 +++++++++++++++++++++++++ sql/sql_select.cc | 14 +++++++------- 3 files changed, 51 insertions(+), 7 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index a2e84a1..3adfe0b 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9808,3 +9808,22 @@ EXPLAIN } } DROP TABLE t1; +# +# MDEV-15087: error from inexpensive subquery before check +# for condition pushdown into derived +# +CREATE TABLE t1 (i1 int, v1 varchar(1)); +INSERT INTO t1 VALUES (7,'x'); +CREATE TABLE t2 (i1 int); +INSERT INTO t2 VALUES (8); +CREATE TABLE t3 (i1 int ,v1 varchar(1), v2 varchar(1)); +INSERT INTO t3 VALUES (4, 'v','v'),(62,'v','k'),(7, 'n', NULL); +SELECT 1 +FROM (t1 AS a1 +JOIN (((SELECT DISTINCT t3.* +FROM t3) AS a2 +JOIN t1 ON (t1.v1 = a2.v2))) ON (t1.v1 = a2.v1)) +WHERE (SELECT BIT_COUNT(t2.i1) +FROM (t2 JOIN t3)) IS NULL; +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1, t2, t3; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 3103ddb..872acb5 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1840,3 +1840,28 @@ EVAL $query; EVAL EXPLAIN FORMAT=JSON $query; DROP TABLE t1; + +--echo # +--echo # MDEV-15087: error from inexpensive subquery before check +--echo # for condition pushdown into derived +--echo # + +CREATE TABLE t1 (i1 int, v1 varchar(1)); +INSERT INTO t1 VALUES (7,'x'); + +CREATE TABLE t2 (i1 int); +INSERT INTO t2 VALUES (8); + +CREATE TABLE t3 (i1 int ,v1 varchar(1), v2 varchar(1)); +INSERT INTO t3 VALUES (4, 'v','v'),(62,'v','k'),(7, 'n', NULL); + +--error ER_SUBQUERY_NO_1_ROW +SELECT 1 +FROM (t1 AS a1 + JOIN (((SELECT DISTINCT t3.* + FROM t3) AS a2 + JOIN t1 ON (t1.v1 = a2.v2))) ON (t1.v1 = a2.v1)) +WHERE (SELECT BIT_COUNT(t2.i1) + FROM (t2 JOIN t3)) IS NULL; + +DROP TABLE t1, t2, t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4cabf5c..47ca733 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1368,6 +1368,13 @@ JOIN::optimize_inner() conds= optimize_cond(this, conds, join_list, FALSE, &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS); + if (thd->is_error()) + { + error= 1; + DBUG_PRINT("error",("Error from optimize_cond")); + DBUG_RETURN(1); + } + if (thd->lex->sql_command == SQLCOM_SELECT && optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) { @@ -1402,13 +1409,6 @@ JOIN::optimize_inner() DBUG_RETURN(1); } - if (thd->is_error()) - { - error= 1; - DBUG_PRINT("error",("Error from optimize_cond")); - DBUG_RETURN(1); - } - { having= optimize_cond(this, having, join_list, TRUE, &having_value, &having_equal);