revision-id: c7443a0911a98dccfc9c5bda4c2f4d9052516d8f (mariadb-10.4.20-22-gc7443a0911a) parent(s): eebe2090c848b5cedc5235473d80dbd2c25d2943 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-01 01:08:28 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Post-merge fix in 10.4: add a testcase for pushdown into IN subquery --- mysql-test/main/derived_cond_pushdown.result | 57 +++++++++++++++++++++++++++- mysql-test/main/derived_cond_pushdown.test | 19 +++++++++- 2 files changed, 74 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 76918d975cc..01a863ccb8b 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -10814,9 +10814,64 @@ EXPLAIN } } } +# Extra test for 10.4+: Check that this works for pushdown into IN +# subqueries: +create table t4 (a int, b int, c decimal); +insert into t4 select a,a,a from t1; +# The subquery must be materialized and must have +# "attached_condition": "t1.a + 1 > 10", +# "having_condition": "`f1(a)` > 1 and `sum(b)` > 123", +explain format=json +select * +from t4 +where +(a,b,c) in (select a, f1(a), sum(b) from t1 group by a, f1(a)) +and +(a+1) > 10 AND b > 1 and c>123; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t4", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t4.a + 1 > 10 and t4.b > 1 and t4.c > 123 and t4.a is not null and t4.b is not null and t4.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "23", + "used_key_parts": ["a", "f1(a)", "sum(b)"], + "ref": ["test.t4.a", "test.t4.b", "test.t4.c"], + "rows": 1, + "filtered": 100, + "attached_condition": "t4.c = `<subquery2>`.`sum(b)`", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`f1(a)` > 1 and `sum(b)` > 123", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a + 1 > 10" + } + } + } + } + } + } +} drop view v2; drop function f1; -drop table t1; +drop table t1, t4; # End of 10.2 tests # # MDEV-14579: pushdown conditions into materialized views/derived tables diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index bc5034621b4..9544ad34572 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2305,9 +2305,26 @@ select a, f1(a), sum(b) from t1 group by a, f1(a); explain format=json select * from v2 where (s+1) > 10 AND a > 1 and a2>123; +--echo # Extra test for 10.4+: Check that this works for pushdown into IN +--echo # subqueries: + +create table t4 (a int, b int, c decimal); +insert into t4 select a,a,a from t1; + +--echo # The subquery must be materialized and must have +--echo # "attached_condition": "t1.a + 1 > 10", +--echo # "having_condition": "`f1(a)` > 1 and `sum(b)` > 123", +explain format=json +select * +from t4 +where + (a,b,c) in (select a, f1(a), sum(b) from t1 group by a, f1(a)) + and + (a+1) > 10 AND b > 1 and c>123; + drop view v2; drop function f1; -drop table t1; +drop table t1, t4; --echo # End of 10.2 tests --echo #