diff options
Diffstat (limited to 'mysql-test/main/table_elim.test')
-rw-r--r-- | mysql-test/main/table_elim.test | 28 |
1 files changed, 27 insertions, 1 deletions
diff --git a/mysql-test/main/table_elim.test b/mysql-test/main/table_elim.test index a1f7ef91522..6b0a46f6e55 100644 --- a/mysql-test/main/table_elim.test +++ b/mysql-test/main/table_elim.test @@ -751,7 +751,6 @@ explain select t1.* from t1 left join --echo # must not hamper the elimination explain select t1.* from t1 left join (select count(*) as cnt, b, a from t2 group by a, b) D on D.a=t1.a and D.b=t1.b; - drop view v2b, v2c, v2d, v2e; drop table t1, t11, t12, t13, t2; @@ -759,3 +758,30 @@ drop table t1, t11, t12, t13, t2; --echo # --echo # End of MDEV-26278: Table elimination does not work across derived tables --echo # + +--echo # +--echo # MDEV-28817 Derived table elimination does not work for +--echo # multiple occurencies of a field +--echo # +create table t1 (a int, b int); +insert into t1 select seq, seq+10 from seq_1_to_10; + +create table t2 (a int, b int, c int); +insert into t2 select A.seq, B.seq, 123 from seq_1_to_3 A, seq_1_to_3 B; + +explain select t1.* from t1 left join + (select a as a1, max(c), b as b1, b as b2, a as a2 + from t2 group by a, b) D + on D.a1=t1.a and D.b2=t1.b; + +explain select t1.* from t1 left join + (select a as a1, b as b1, b as b2, a as a2, count(*) + from t2 group by a, b) D + on D.a2=t1.a and D.b1=t1.b; + +explain select t1.* from t1 left join + (select a as a1, b as b1, b as b2, min(a+b) as minab, a as a2 + from t2 group by a1, b2) D + on D.a2=t1.a and D.b1=t1.b; + +drop table t1, t2; |