summaryrefslogtreecommitdiff
path: root/mysql-test/main/derived_cond_pushdown.test
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2021-07-02 17:00:05 +0300
committerMarko Mäkelä <marko.makela@mariadb.com>2021-07-02 17:00:05 +0300
commit0ad8a825a8901d4b5e704fed3fc65e93cea18f18 (patch)
tree69238e9b52a7166b603e3ebbb2c7ea1b616765ed /mysql-test/main/derived_cond_pushdown.test
parent315380a4d16ddb16461d906a23be341e354c30ed (diff)
parent779262842edf86c989c099c6930ae4683cbde609 (diff)
downloadmariadb-git-0ad8a825a8901d4b5e704fed3fc65e93cea18f18.tar.gz
Merge 10.5 into 10.6
Diffstat (limited to 'mysql-test/main/derived_cond_pushdown.test')
-rw-r--r--mysql-test/main/derived_cond_pushdown.test87
1 files changed, 87 insertions, 0 deletions
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index e6ee05dd644..d738ed33128 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -2239,6 +2239,93 @@ eval explain extended $q2;
drop view v1,v2;
drop table t1;
+--echo #
+--echo # MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
+--echo #
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+
+create table t1 (
+ pk int primary key,
+ a int,
+ b int,
+ key(a)
+);
+
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t3(a int);
+insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C;
+
+insert into t1 select a,a,a from t3;
+
+create view v1 as
+select
+ t1.a as col1,
+ f1(t1.b) as col2
+from
+ t1;
+
+create view v2 as
+select
+ t1.a as col1,
+ f1(t1.b) as col2
+from
+ t1;
+create view v3 as
+select col2, col1 from v1
+union all
+select col2, col1 from v2;
+
+explain select * from v3 where col1=123;
+
+--echo # This must use ref accesses for reading table t1, not full scans:
+explain format=json
+select * from v3 where col1=123 and col2=321;
+
+drop function f1;
+drop view v1,v2,v3;
+drop table t1, t2,t3;
+
+--echo #
+--echo # Another testcase, with pushdown through GROUP BY
+--echo #
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+
+create view v2(a, a2, s) as
+select a, f1(a), sum(b) from t1 group by a, f1(a);
+
+--echo # Here,
+--echo # "(s+1) > 10" will be pushed into HAVING
+--echo # "a > 1" will be pushed all the way to the table scan on t1
+--echo # "a2>123" will be pushed into HAVING (as it refers to an SP call which
+--echo # prevents pushing it to the WHERE)
+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, t4;
--echo # End of 10.2 tests
--echo #