diff options
Diffstat (limited to 'mysql-test/main/derived_cond_pushdown.test')
| -rw-r--r-- | mysql-test/main/derived_cond_pushdown.test | 2312 |
1 files changed, 1165 insertions, 1147 deletions
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 340aa1f5a54..659bc2026ff 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -1,46 +1,46 @@ -let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_derived=off' for; +let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_derived=off' for; create table t1 (a int, b int, c int); create table t2 (a int, b int, c int, d decimal); -insert into t1 values +insert into t1 values (1,21,345), (1,33,7), (8,33,114), (1,21,500), (1,19,107), (5,14,787), (8,33,123), (9,10,211), (5,16,207), (1,33,988), (5,27,132), (1,21,104), (6,20,309), (6,20,315), (1,21,101), (8,33,404), (9,10,800), (1,21,123), (7,11,708), (6,20,214); insert into t2 values - (2,3,207,207.0000), (1,21,909,12.0000), (7,13,312,406.0000), + (2,3,207,207.0000), (1,21,909,12.0000), (7,13,312,406.0000), (8,64,248,107.0000), (6,20,315,279.3333), (1,19,203,107.0000), (8,80,800,314.0000), (3,12,231,190.0000), (6,23,303,909.0000); - + create table t1_double(a int, b double, c double); -insert into t1_double values +insert into t1_double values (1,23.4,14.3333), (1,12.5,18.9), (3,12.5,18.9), (4,33.4,14.3333), (4,14.3333,13.65), (5,17.89,7.22), (6,33.4,14.3), (10,33.4,13.65), (11,33.4,13.65); - + create table t2_double(a int, b double, c double); -insert into t2_double values +insert into t2_double values (1,22.4,14.3333), (1,12.5,18.9), (2,22.4,18.9), (4,33.4,14.3333), (5,22.4,13.65), (7,17.89,18.9), - (6,33.4,14.3333), (10,31.4,13.65), (12,33.4,13.65); - + (6,33.4,14.3333), (10,31.4,13.65), (12,33.4,13.65); + create table t1_char(a char, b char(8), c int); insert into t1_char values ('a','Ivan',1), ('b','Vika',2), ('b','Inga',6), ('c','Vika',7), ('b','Ivan',7), ('a','Alex',6), ('b','Inga',5), ('d','Ron',9), - ('d','Harry',2), ('d','Hermione',3), ('c','Ivan',3), ('c','Harry',4); - + ('d','Harry',2), ('d','Hermione',3), ('c','Ivan',3), ('c','Harry',4); + create table t2_char(a char, b char(8), c int); insert into t2_char values ('b','Ivan',1), ('c','Vinny',3), ('c','Inga',9), ('a','Vika',1), ('c','Ivan',2), ('b','Ali',6), ('c','Inga',2), ('a','Ron',9), ('d','Harry',1), ('b','Hermes',3), ('b','Ivan',11), ('b','Harry',4); - + create table t1_decimal (a decimal(3,1), b decimal(3,1), c int); insert into t1_decimal values (1,1,23),(2,2,11),(3,3,16), (1,1,12),(1,1,14),(2,3,15), - (2,1,13),(2,3,11),(3,3,16); + (2,1,13),(2,3,11),(3,3,16); create table t2_decimal (a decimal(3,1), b decimal(3,1), c int); insert into t2_decimal values @@ -48,27 +48,27 @@ insert into t2_decimal values (1,3,22),(1,3,14),(2,2,15), (2,1,43),(2,3,11),(2,3,16); -create view v1 as select a, b, max(c) as max_c, avg(c) as avg_c from t1 +create view v1 as select a, b, max(c) as max_c, avg(c) as avg_c from t1 group by a,b having max_c < 707; - -create view v2 as select a, b, max(c) as max_c, avg(c) as avg_c from t1 - where t1.a>5 group by a,b having max_c < 707; - + +create view v2 as select a, b, max(c) as max_c, avg(c) as avg_c from t1 + where t1.a>5 group by a,b having max_c < 707; + create view v3 as select a, b, min(c) as min_c from t1 where t1.a<10 group by a,b having min_c > 109; - + create view v4 as select a, b, min(max_c) as min_c from v1 - where (v1.a<15) group by a,b; - -create view v_union as + where (v1.a<15) group by a,b; + +create view v_union as select a, b, min(c) as c from t1 where t1.a<10 group by a,b having c > 109 union select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300; - -create view v2_union as + where t1.b>10 group by a,b having c < 300; + +create view v2_union as select a, b, min(c) as c from t1 where t1.a<10 group by a,b having c > 109 union @@ -76,117 +76,117 @@ create view v2_union as where t1.b>10 group by a,b having c < 300 union select a, b, avg(c) as c from t1 - where t1.c>300 group by a,b having c < 707; + where t1.c>300 group by a,b having c < 707; -create view v3_union as +create view v3_union as select a, b, (a+1) as c from t1 where t1.a<10 union select a, b, c from t1 - where t1.b>10 and t1.c>100; - -create view v4_union as + where t1.b>10 and t1.c>100; + +create view v4_union as select a, b, max(c)-100 as c from t1 where t1.a<10 group by a,b having c > 109 union select a, b, (c+100) as c from t1 - where t1.b>10; + where t1.b>10; create view v_double as select a, avg(a/4) as avg_a, b, c from t1_double - where (b>12.2) group by b,c having (avg_a<22.333); - -create view v_char as - select a, b, max(c) as max_c from t1_char - group by a,b having max_c < 9; - + where (b>12.2) group by b,c having (avg_a<22.333); + +create view v_char as + select a, b, max(c) as max_c from t1_char + group by a,b having max_c < 9; + create view v_decimal as select a, b, avg(c) as avg_c from t1_decimal - group by a,b having (avg_c>12); + group by a,b having (avg_c>12); ---echo # conjunctive subformula : pushing into HAVING +--echo # conjunctive subformula : pushing into HAVING let $query= select * from v1,t2 where (v1.max_c>214) and (t2.a>v1.a); eval $no_pushdown $query; eval $query; eval explain $query; eval explain format=json $query; -let $query= +let $query= select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 - group by a,b having max_c < 707) v1, + group by a,b having max_c < 707) v1, t2 where (v1.a=t2.a) and (v1.max_c>300); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; ---echo # extracted or formula : pushing into HAVING -let $query= - select * from v1,t2 where +--echo # extracted or formula : pushing into HAVING +let $query= + select * from v1,t2 where ((v1.max_c>400) and (t2.a>v1.a)) or ((v1.max_c<135) and (t2.a<v1.a)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - -let $query= +eval explain format=json $query; + +let $query= select * from v1,t2 where - ((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or + ((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or ((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # conjunctive subformula : pushing into WHERE let $query= select * from v1,t2 where (v1.a>6) and (t2.b>v1.b); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; let $query= select * from v2,t2 where (v2.b>25) and (t2.a<v2.a); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # extracted or formula : pushing into WHERE -let $query= - select * from v1,t2 where +let $query= + select * from v1,t2 where ((v1.a>7) and (t2.c<v1.max_c)) or ((v1.a<2) and (t2.b<v1.b)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - -let $query= - select * from v2,t2 where +eval explain format=json $query; + +let $query= + select * from v2,t2 where ((v2.a>7) and (t2.c<v2.max_c)) or ((v2.a>5) and (t2.b<v2.b)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - -let $query= - select * from v1,t2 where - ((v1.a>4) and (v1.b>t2.b) and (v1.max_c=t2.d)) or +eval explain format=json $query; + +let $query= + select * from v1,t2 where + ((v1.a>4) and (v1.b>t2.b) and (v1.max_c=t2.d)) or ((v1.a<2) and (v1.max_c<t2.c) and (v1.max_c=t2.d)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # conjunctive subformulas : pushing into HAVING and WHERE -let $query= - select * from v1,t2 where (v1.a<2) and (v1.max_c>400) and (t2.b>v1.b); +let $query= + select * from v1,t2 where (v1.a<2) and (v1.max_c>400) and (t2.b>v1.b); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; -let $query= +let $query= select * from v_double as v,t2_double as t where (v.a=t.a) and (v.avg_a>0.45) and (v.b>10); eval $no_pushdown $query; @@ -194,57 +194,57 @@ eval $query; eval explain $query; eval explain format=json $query; -let $query= +let $query= select * from v_decimal as v,t2_decimal as t where (v.a=t.a) and (v.avg_c>15) and (v.b>1); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # extracted or formula : pushing into HAVING and WHERE -let $query= - select * from v1,t2 where - ((v1.a>7) and (v1.max_c>300) and (t2.c<v1.max_c)) or - ((v1.a<4) and (v1.max_c<500) and (t2.b<v1.b)); +let $query= + select * from v1,t2 where + ((v1.a>7) and (v1.max_c>300) and (t2.c<v1.max_c)) or + ((v1.a<4) and (v1.max_c<500) and (t2.b<v1.b)); eval $no_pushdown $query; eval $query; eval explain $query; eval explain format=json $query; - -let $query= + +let $query= select * from v1,t2 where ((v1.a<2) and (v1.max_c>120)) or (v1.a>7); eval $no_pushdown $query; eval $query; eval explain $query; eval explain format=json $query; - ---echo # extracted or formulas : pushing into WHERE and HAVING -let $query= - select * from v1,t2 where + +--echo # extracted or formulas : pushing into WHERE and HAVING +let $query= + select * from v1,t2 where ((v1.a<2) and (v1.max_c>120) and (v1.b=t2.b)) or (v1.a>7); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - -let $query= +eval explain format=json $query; + +let $query= select * from v1,t2 where ((v1.a<2) and (v1.max_c<200) and (t2.c>v1.max_c) and (v1.max_c=t2.d)) or - ((v1.a>4) and (v1.max_c<500) and (t2.b<v1.b) and (v1.max_c=t2.c)); + ((v1.a>4) and (v1.max_c<500) and (t2.b<v1.b) and (v1.max_c=t2.c)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # prepare of a query containing extracted or formula -prepare stmt from "select * from v1,t2 where +prepare stmt from "select * from v1,t2 where ((v1.max_c>400) and (t2.a>v1.a)) or ((v1.max_c<135) and (t2.a<v1.a));"; execute stmt; execute stmt; deallocate prepare stmt; -prepare stmt from - "explain format=json select * from v1,t2 where +prepare stmt from + "explain format=json select * from v1,t2 where ((v1.max_c>400) and (t2.a>v1.a)) or ((v1.max_c<135) and (t2.a<v1.a));"; execute stmt; execute stmt; @@ -252,12 +252,12 @@ deallocate prepare stmt; --echo # conjunctive subformula : pushing into WHERE --echo # pushing equalities -let $query= +let $query= select * from v1,t2 where (t2.a=v1.a) and (v1.b=t2.b) and (v1.a=1); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; let $query= select * from v1,t2 where (v1.a=5) and (v1.max_c=t2.d); eval $no_pushdown $query; @@ -270,15 +270,15 @@ let $query= select * from v1,t2 where (t2.a<5) and (v1.a=t2.a); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - -let $query= +eval explain format=json $query; + +let $query= select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # conjunctive subformula : pushing into HAVING using equalities let $query= select * from v1,t2 where (t2.c>150) and (v1.max_c=t2.c); eval $no_pushdown $query; @@ -286,73 +286,73 @@ eval $query; eval explain $query; eval explain format=json $query; ---echo # extracted and formula : pushing into WHERE +--echo # extracted and formula : pushing into WHERE --echo # pushing equalities -let $query= +let $query= select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a) and (v1.a=3); eval $no_pushdown $query; eval $query; eval explain $query; eval explain format=json $query; - -let $query= + +let $query= select * from v1,t2 where (v1.a=1) and (v1.b=21) and (t2.a=2); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; -let $query= - select * from v_char as v,t2_char as t where - (v.a='c') and (v.b<'Hermes') and ((v.b=t.b) or (v.max_c>20)); +let $query= + select * from v_char as v,t2_char as t where + (v.a='c') and (v.b<'Hermes') and ((v.b=t.b) or (v.max_c>20)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # extracted and formula : pushing into WHERE using equalities --echo # pushing equalities -let $query= +let $query= select * from v_decimal as v,t2_decimal as t where (v.a=v.b) and (v.b=t.b) and ((t.b>1) or (v.a=1)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; ---echo # extracted or formula : pushing into HAVING using equalities -let $query= +--echo # extracted or formula : pushing into HAVING using equalities +let $query= select * from v1,t2 - where ((t2.a<4) and (v1.a=t2.a)) or ((t2.c>150) and (v1.max_c=t2.c)); + where ((t2.a<4) and (v1.a=t2.a)) or ((t2.c>150) and (v1.max_c=t2.c)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # conjunctive subformulas : pushing into WHERE and HAVING using equalities -let $query= +let $query= select * from v1,t2 where ((t2.a>5) and (v1.a=t2.a)) and ((t2.c>250) and (v1.max_c=t2.c)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # conjunctive subformulas : pushing into WHERE and HAVING ---echo # pushing equalities -let $query= +--echo # pushing equalities +let $query= select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 - group by a,b having max_c < 707) v1, + group by a,b having max_c < 707) v1, t2 where (v1.a=8) and (v1.a=t2.a) and (v1.max_c=404); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - ---echo # conjunctive subformulas : pushing into WHERE and HAVING -let $query= - select * from v1,t2 where +eval explain format=json $query; + +--echo # conjunctive subformulas : pushing into WHERE and HAVING +let $query= + select * from v1,t2 where (v1.a>3) and (v1.max_c>200) and (t2.b<v1.b) and (t2.d=v1.max_c); eval $no_pushdown $query; eval $query; @@ -362,17 +362,17 @@ eval explain format=json $query; --echo # conjunctive subformula : pushing into WHERE --echo # extracted or formula : pushing into HAVING using equalities --echo # pushing equalities -let $query= +let $query= select * from v_double as v,t2_double as t where (v.b=v.c) and (v.c=t.c) and ((t.c>10) or (v.a=1)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # conjunctive subformula : pushing into WHERE ---echo # extracted or formula : pushing into HAVING using equalities -let $query= +--echo # extracted or formula : pushing into HAVING using equalities +let $query= select * from v_double as v,t2_double as t where (((v.a>0.2) or (v.b<17)) or (t.c>17)) and (t.c=v.c) and (v.c>18); eval $no_pushdown $query; @@ -383,146 +383,146 @@ eval explain format=json $query; --echo # extracted or formula : pushing into WHERE --echo # conjunctive subformula : pushing into HAVING --echo # pushing equalities -let $query= +let $query= select * from v_decimal as v,t2_decimal as t where - (((v.a>4) or (v.a=2)) or (v.b>3)) and (v.avg_c=13); + (((v.a>4) or (v.a=2)) or (v.b>3)) and (v.avg_c=13); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; -let $query= +let $query= select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 - where t1.a>5 group by a,b having max_c < 707) v1, + where t1.a>5 group by a,b having max_c < 707) v1, t2 where (v1.a=t2.a) and (v1.max_c>300) and (v1.a=v1.b); eval $no_pushdown $query; eval $query; eval explain $query; eval explain format=json $query; - + --echo # nothing to push -let $query= +let $query= select * from v1,t2 where (t2.a<2) and (t2.c>900); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.b=t2.b); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - -let $query= - select * from v1,t2 where - (t2.a=v1.a) or (v1.b=t2.b) and ((v1.a=1) or (v1.a=6)); +eval explain format=json $query; + +let $query= + select * from v1,t2 where + (t2.a=v1.a) or (v1.b=t2.b) and ((v1.a=1) or (v1.a=6)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - -let $query= +eval explain format=json $query; + +let $query= select * from v1,t2 where (v1.a=1) or (v1.b=21) or (t2.a=2); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; -let $query= - select * from v1,t2 where +let $query= + select * from v1,t2 where (t2.a<2) and (t2.c>900) and ((v1.a<t2.a) or (t2.a<11)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using several derived tables : nothing to push -let $query= select * from v1,v2,t2 where +let $query= select * from v1,v2,t2 where (v1.a=v2.a) and (v1.a=t2.a) and (v2.b<50); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; -let $query= - select * from v1,v2,t2 where +let $query= + select * from v1,v2,t2 where ((v1.a=v2.a) or (v1.a=t2.a)) and (t2.b<50) and (v1.b=v2.b); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; -let $query= - select * from v1,v2,t2 where +let $query= + select * from v1,v2,t2 where ((v1.a=v2.a) and (v1.a=t2.a)) or ((v2.b>13) and (t2.c<115)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using several derived tables : pushing in all tables --echo # conjunctive subformula : pushing into HAVING --echo # extracted or formula : pushing into WHERE ---echo # pushing equalities -let $query= - select * from v1,v2,t2 where ((v1.a=v2.a) or (v1.a=t2.a)) and +--echo # pushing equalities +let $query= + select * from v1,v2,t2 where ((v1.a=v2.a) or (v1.a=t2.a)) and ((v2.b<50) or (v2.b=19)) and (v1.max_c<300); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # using several derived tables : pushing only in one table --echo # conjunctive subformula : pushing into WHERE --echo # pushing equalities -let $query= - select * from v1,v2,t2 where - (v1.a=t2.a) and (v1.a=v1.b) and (v1.a=v2.a) and (v2.max_c<300); +let $query= + select * from v1,v2,t2 where + (v1.a=t2.a) and (v1.a=v1.b) and (v1.a=v2.a) and (v2.max_c<300); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using several derived tables : pushing only in one table --echo # extracted and formula : pushing into WHERE --echo # conjunctive subformula : pushing into WHERE using equalities --echo # pushing equalities -let $query= +let $query= select * from v1,v2,t2 where (v1.a=1) and (v1.b>10) and (v1.b=v2.b); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # extracted or formula : pushing into WHERE --echo # conjunctive subformula : pushing into WHERE using equalities --echo # pushing equalities -let $query= - select * from v_char as v,t2_char as t where +let $query= + select * from v_char as v,t2_char as t where (v.a=t.a) and (t.a='b') and ((v.b='Vika') or (v.b='Ali')); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using several derived tables : pushing in all tables --echo # extracted or formula : pushing into WHERE ---echo # conjunctive subformulas : pushing into HAVING +--echo # conjunctive subformulas : pushing into HAVING --echo # pushing equalities -let $query= - select * from v1,v2,v3,t2 where - ((v1.a=v2.a) or (v1.a=t2.a)) and ((v3.b<50) or (v3.b=33)) +let $query= + select * from v1,v2,v3,t2 where + ((v1.a=v2.a) or (v1.a=t2.a)) and ((v3.b<50) or (v3.b=33)) and (v1.max_c<500) and (v3.a=t2.a) and (v2.max_c>300); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using several derived tables : pushing in all tables ---echo # conjunctive subformulas : pushing into HAVING -let $query= +--echo # conjunctive subformulas : pushing into HAVING +let $query= select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 where t1.a>5 group by a,b having max_c < 707) v1, @@ -532,11 +532,11 @@ let $query= eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using several derived tables : pushing in all tables --echo # extracted or formulas : pushing into HAVING ---echo # conjunctive subformula : pushing into HAVING +--echo # conjunctive subformula : pushing into HAVING let $query= select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 @@ -550,51 +550,51 @@ let $query= eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # extracted or formula : pushing into HAVING ---echo # conjunctive subformula : pushing into WHERE -let $query= +--echo # conjunctive subformula : pushing into WHERE +let $query= select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 - group by a,b having max_c < 707) v1, - t2 where ((v1.a=1) or (v1.max_c<300)) and (v1.b>25); + group by a,b having max_c < 707) v1, + t2 where ((v1.a=1) or (v1.max_c<300)) and (v1.b>25); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # extracted and formula : pushing into WHERE ---echo # conjunctive subformula : pushing into HAVING -let $query= +--echo # conjunctive subformula : pushing into HAVING +let $query= select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 - where t1.a>5 group by a,b having max_c < 707) v1, - t2 where (v1.a=t2.a) and (v1.max_c>300) and (v1.b<30); + where t1.a>5 group by a,b having max_c < 707) v1, + t2 where (v1.a=t2.a) and (v1.max_c>300) and (v1.b<30); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using query with union --echo # conjunctive subformula : pushing into WHERE --echo # conjunctive subformulas : pushing into HAVING and WHERE -let $query= +let $query= select * from v1,t2 where (v1.a<5) and (v1.b=t2.b) and (t2.c>800) union - select * from v1,t2 where (v1.max_c>100) and (v1.a>7) and (t2.d>800); + select * from v1,t2 where (v1.max_c>100) and (v1.a>7) and (t2.d>800); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using query with union ---echo # extracted and formula : pushing into WHERE ---echo # extracted or formula : pushing into HAVING +--echo # extracted and formula : pushing into WHERE +--echo # extracted or formula : pushing into HAVING --echo # pushing equalities -let $query= +let $query= select * from v1,t2 where (v1.a<5) and (v1.b=t2.b) and (v1.b=19) - union + union select * from v1,t2 where ((v1.max_c>400) or (v1.avg_c>270)) and (v1.a<t2.a); eval $no_pushdown $query; eval $query; @@ -602,25 +602,25 @@ eval explain $query; eval explain format=json $query; --echo # using query with union ---echo # extracted or formula : pushing into HAVING +--echo # extracted or formula : pushing into HAVING --echo # extracted or formula : pushing into WHERE --echo # pushing equalities -let $query= - select * from v1,t2 where +let $query= + select * from v1,t2 where ((t2.a=v1.a) or (v1.b=t2.b)) and ((v1.a=1) or (v1.a=6)) union - select * from v1,t2 where ((v1.a>3) and (v1.b>27)) or (v1.max_c>550); + select * from v1,t2 where ((v1.a>3) and (v1.b>27)) or (v1.max_c>550); eval $no_pushdown $query; eval $query; eval explain $query; eval explain format=json $query; --echo # using query with union ---echo # extracted or formula : pushing into HAVING ---echo # conjunctive subformulas : pushing into WHERE +--echo # extracted or formula : pushing into HAVING +--echo # conjunctive subformulas : pushing into WHERE --echo # pushing equalities -let $query= - select * from v1,t2 where +let $query= + select * from v1,t2 where ((v1.a=1) and (v1.a=t2.a)) and ((v1.max_c<500) or (v1.avg_c>500)) union select * from v2,t2 where @@ -643,8 +643,8 @@ eval explain format=json $query; --echo # using derived table with union --echo # conjunctive subformula : pushing into WHERE ---echo # extracted or formula : pushing into HAVING -let $query= +--echo # extracted or formula : pushing into HAVING +let $query= select * from v_union,t2 where ((v_union.a<2) or (v_union.c>800)) and (v_union.b>12); eval $no_pushdown $query; @@ -656,7 +656,7 @@ eval explain format=json $query; --echo # conjunctive subformula : pushing into HAVING --echo # conjunctive subformula : pushing into WHERE --echo # pushing equalities -let $query= +let $query= select * from v_union,t2 where (v_union.a=1) and (v_union.a=t2.a) and (v_union.c<200); eval $no_pushdown $query; @@ -664,215 +664,215 @@ eval $query; eval explain $query; eval explain format=json $query; -let $query= - select * from v_char as v,t2_char as t where +let $query= + select * from v_char as v,t2_char as t where (v.a=t.a) and (v.b='Vika') and (v.max_c>2); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using derived table with union --echo # using several derived tables : pushing in all tables --echo # conjunctive subformula : pushing into WHERE using equalities --echo # pushing equalities -let $query= +let $query= select * from v_union,v1,t2 where - (v_union.a=v1.a) and (v1.a=t2.a) and (t2.a=1) - and ((v_union.c>800) or (v1.max_c>200)); + (v_union.a=v1.a) and (v1.a=t2.a) and (t2.a=1) + and ((v_union.c>800) or (v1.max_c>200)); eval $no_pushdown $query; eval $query; eval explain $query; eval explain format=json $query; ---echo # using derived table with union ---echo # extracted or formula : pushing into WHERE +--echo # using derived table with union +--echo # extracted or formula : pushing into WHERE --echo # conjunctive subformula : pushing into HAVING --echo # pushing equalities -let $query= - select * from v2_union as v,t2 where +let $query= + select * from v2_union as v,t2 where ((v.a=6) or (v.a=8)) and (v.c>200) and (v.a=t2.a); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using derived table with union of selects without aggregation --echo # extracted conjunctive predicate: pushing in WHERE of both selects -let $query= +let $query= select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using derived table with union of selects without aggregation --echo # extracted conjunctive OR subformula: pushing in WHERE using equalities -let $query= +let $query= select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using derived table with union of selects without aggregation --echo # extracted the whole condition: in WHERE of both selects -let $query= - select * from v3_union as v,t2 where +let $query= + select * from v3_union as v,t2 where (v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; ---echo # using derived table with union of +--echo # using derived table with union of --echo # a select without aggregation and a select with aggregation --echo # extracted conjunctive predicate: pushing in WHERE of both selects -let $query= +let $query= select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; ---echo # using derived table with union of +--echo # using derived table with union of --echo # a select without aggregation and a select with aggregation --echo # extracted subformula: pushing in WHERE of one select --echo # extracted subformula: pushing in HAVING of the other select --echo # extracted sub-subformula: pushing in WHERE of the other select --echo # using an equality in all pushdowns -let $query= - select * from v4_union as v,t2 where +let $query= + select * from v4_union as v,t2 where (v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using embedded derived table : pushing the same conditions --echo # using several derived tables : pushing in all tables --echo # conjunctive subformula : pushing into WHERE --echo # extracted and formula : pushing into WHERE -let $query= +let $query= select * from v4,v1 where - (v4.a<13) and (v1.a>5) and (v1.b>12); + (v4.a<13) and (v1.a>5) and (v1.b>12); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using embedded view : nothing to push --echo # using several derived tables : pushing only in one table --echo # conjunctive subformula : pushing into WHERE -let $query= +let $query= select * from v4,v1,t2 where (v4.a=t2.a) and (v4.a=v1.a) and (v1.b>30); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using embedded view : pushing different conditions --echo # using several derived tables : pushing in all tables --echo # conjunctive subformula : pushing into WHERE using equalities --echo # extracted and formula : pushing into WHERE using equalities --echo # conjunctive subformula : pushing into HAVING -let $query= +let $query= select * from v4,v1,t2 where - (v4.a=t2.a) and (v4.a>1) and (v4.a=v1.a) and (v4.min_c>100) and (v1.b<30); + (v4.a=t2.a) and (v4.a>1) and (v4.a=v1.a) and (v4.min_c>100) and (v1.b<30); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using embedded view : pushing different conditions --echo # using several derived tables : pushing in all tables --echo # extracted or formula : pushing into WHERE --echo # conjunctive subformula : pushing into HAVING -let $query= +let $query= select * from v4,v1,t2 where (((v4.b>10) and (v4.a>1)) or (v4.b<20)) and (v1.max_c>200) and (v1.a=v4.a); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # using embedded view : pushing different conditions --echo # using several derived tables : pushing only in one table --echo # extracted or formula : pushing into WHERE --echo # extracted or formula : pushing into HAVING -let $query= +let $query= select * from v4,v2 where ((v4.a>12) and (v4.min_c<300) and (v4.b>13)) or (v4.a<1); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # using embedded view : pushing different conditions --echo # using several derived tables : pushing only in one table --echo # conjunctive subformula : pushing into WHERE --echo # conjunctive subformula : pushing into HAVING --echo # pushing equalities -let $query= +let $query= select * from v4,v2 where - (v4.a=v2.b) and (v4.a=v4.b) and (v4.min_c<100); + (v4.a=v2.b) and (v4.a=v4.b) and (v4.min_c<100); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # using embedded view : pushing the same conditions --echo # using several derived tables : pushing in all tables --echo # extracted and formula : pushing into WHERE using equalities --echo # conjunctive subformula : pushing into WHERE --echo # pushing equalities -let $query= +let $query= select * from v4,v2 where - (v4.a=v2.b) and (v4.a=v4.b) and (v2.b<30); + (v4.a=v2.b) and (v4.a=v4.b) and (v2.b<30); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using embedded view : pushing the same conditions --echo # using several derived tables : pushing in all tables --echo # extracted or formula : pushing into WHERE using equalities --echo # extracted and formula : pushing into WHERE using equalities --echo # pushing equalities -let $query= +let $query= select * from v4,v2 where (v4.a=v2.b) and (v4.a=v4.b) and ((v2.b<30) or (v4.a>2)); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; - +eval explain format=json $query; + --echo # using embedded view : pushing the same conditions --echo # using several derived tables : pushing in all tables --echo # extracted or formula : pushing into WHERE --echo # conjunctive subformula : pushing into WHERE ---echo # pushing equalities -let $query= +--echo # pushing equalities +let $query= select * from v4,v2 where (((v4.a<12) and (v4.b>13)) or (v4.a>10)) and (v4.min_c=v2.max_c) and (v4.min_c>100); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # using embedded view : pushing the same conditions --echo # using several derived tables : pushing only in one table --echo # extracted or formula : pushing into WHERE -let $query= +let $query= select * from v4,v2,t2 where (((v4.a<12) and (t2.b>13)) or (v4.a>10)) and (v4.min_c=t2.c) and (t2.c>100); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; drop view v1,v2,v3,v4; drop view v_union,v2_union,v3_union,v4_union; @@ -880,580 +880,6 @@ drop view v_double,v_char,v_decimal; drop table t1,t2,t1_double,t2_double,t1_char,t2_char,t1_decimal,t2_decimal; --echo # ---echo # MDEV-14579: pushdown conditions into materialized views/derived tables ---echo # that are defined with EXIST or/and INTERSECT ---echo # - -create table t1 (a int, b int, c int); -create table t2 (a int, b int, c int); - -insert into t1 values - (1,21,345), (1,33,7), (8,33,114), (1,21,500), (1,19,117), (5,14,787), - (8,33,123), (9,10,211), (5,16,207), (1,33,988), (5,27,132), (1,21,104), - (6,20,309), (6,20,315), (1,21,101), (4,33,404), (9,10,800), (1,21,123); - -insert into t2 values - (2,3,207), (1,16,909), (5,14,312), - (5,33,207), (6,20,211), (1,19,132), - (8,33,117), (3,21,231), (6,23,303); - -create view v1 as - select a, b, min(c) as c from t1 - where t1.a<9 group by a,b having c < 300 - intersect - select a, b, min(c) as c from t1 - where t1.b>10 group by a,b having c > 100; - ---echo # using intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE ---echo # pushing equalities -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a=8); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE using equalities -let $query= select * from v1,t2 where (v1.a=t2.a) and (t2.a=8); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # conjunctive subformulas : pushing into HAVING -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.c>200); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE ---echo # conjunctive subformulas : pushing into HAVING -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>110); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # extracted or formula : pushing into WHERE -let $query= - select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19)); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # extracted or formula : pushing into HAVING -let $query= - select * from v1,t2 where - (v1.a=t2.a) and ((v1.c>200) or (v1.c<105)); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # extracted or formula : pushing into WHERE ---echo # extracted or formula : pushing into HAVING using equalities ---echo # pushing equalities -let $query= - select * from v1,t2 where - ((v1.a>3) and (t2.c>110) and (v1.c=t2.c)) or - ((v1.a=1) and (v1.c<110)); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using intersect in view definition ---echo # prepare of a query ---echo # conjunctive subformulas : pushing into WHERE ---echo # conjunctive subformulas : pushing into HAVING -prepare stmt from "select * from v1,t2 - where (v1.a=t2.a) and (v1.a<5) and (v1.c>110);"; -execute stmt; -execute stmt; -deallocate prepare stmt; - ---echo # using intersect in derived table definition ---echo # extracted or formula : pushing into WHERE using equalities ---echo # extracted or formula : pushing into HAVING ---echo # pushing equalities -let $query= - select * - from t2, - (select a, b, min(c) as c from t1 - where t1.a<9 group by a,b having c < 300 - intersect - select a, b, min(c) as c from t1 - where t1.b>10 group by a,b having c > 100) as d1 - where - (d1.b=t2.b) and - (((t2.b>13) and (t2.c=909)) or - ((d1.a<4) and (d1.c<200))); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - -create view v1 as - select a, b, max(c) as c from t1 - where t1.a<9 group by a,b having c > 200 - except - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300; - ---echo # using except in view definition ---echo # conjunctive subformulas : pushing into WHERE -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # conjunctive subformulas : pushing into WHERE ---echo # pushing equalities -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a=6); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # conjunctive subformulas : pushing into WHERE using equalities -let $query= select * from v1,t2 where (v1.a=t2.a) and (t2.a=6); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # conjunctive subformulas : pushing into HAVING -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.c>500); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # conjunctive subformulas : pushing into WHERE ---echo # conjunctive subformulas : pushing into HAVING -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>500); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # extracted or formula : pushing into WHERE -let $query= - select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19)); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # extracted or formula : pushing into HAVING -let $query= - select * from v1,t2 where - (v1.a=t2.a) and ((v1.c<400) or (v1.c>800)); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # extracted or formula : pushing into WHERE ---echo # extracted or formula : pushing into HAVING using equalities ---echo # pushing equalities -let $query= - select * from v1,t2 where - (v1.c=t2.c) and - ((v1.a>1) and (t2.c<500)) or - ((v1.a=1) and (v1.c>500)); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - ---echo # using except in view definition ---echo # prepare of a query ---echo # conjunctive subformulas : pushing into WHERE ---echo # conjunctive subformulas : pushing into HAVING -prepare stmt from "select * from v1,t2 - where (v1.a=t2.a) and (v1.a<5) and (v1.c>500);"; -execute stmt; -execute stmt; -deallocate prepare stmt; - ---echo # using except in view definition ---echo # extracted or formula : pushing into WHERE using equalities ---echo # extracted or formula : pushing into HAVING ---echo # pushing equalities -let $query= - select * - from t2, - (select a, b, max(c) as c from t1 - where t1.a<9 group by a,b having c > 200 - except - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300) as d1 - where - (d1.b=t2.b) and - (((t2.b>13) and (t2.c=988)) or - ((d1.a>4) and (d1.c>500))); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using union and intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, min(c) as c from t1 - where t1.a<9 group by a,b having c > 200 - union - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300 - intersect - select a, b, max(c) as c from t1 - where t1.a>3 group by a,b having c < 530; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using union and intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, min(c) as c from t1 - where t1.a<9 group by a,b having c > 200 - intersect - select a, b, max(c) as c from t1 - where t1.a>3 group by a,b having c < 500 - union - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using union and except in view definition ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, min(c) as c from t1 - where t1.a<9 group by a,b having c > 200 - union - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300 - except - select a, b, max(c) as c from t1 - where t1.a>3 group by a,b having c < 530; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using union and except in view definition ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, min(c) as c from t1 - where t1.a<9 group by a,b having c > 200 - except - select a, b, max(c) as c from t1 - where t1.a>3 group by a,b having c < 500 - union - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using except and intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300 - intersect - select a, b, max(c) as c from t1 - where t1.a<7 group by a,b having c < 500 - except - select a, b, max(c) as c from t1 - where t1.a<9 group by a,b having c > 150; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<150); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using except and intersect in view definition ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300 - except - select a, b, max(c) as c from t1 - where t1.a<9 group by a,b having c > 150 - intersect - select a, b, max(c) as c from t1 - where t1.a<7 group by a,b having c < 500; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using except, intersect and union in view definition ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300 - except - select a, b, max(c) as c from t1 - where t1.a<9 group by a,b having c > 150 - intersect - select a, b, max(c) as c from t1 - where t1.a<7 group by a,b having c < 500 - union - select a, b, max(c) as c from t1 - where t1.a<7 group by a,b having c < 120; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using intersect in view definition ---echo # using embedded view ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300 - intersect - select a, b, max(c) as c from t1 - where t1.a<9 group by a,b having c > 120; - -create view v2 as - select a, b, max(c) as c from v1 - where v1.a<7 group by a,b; - -let $query= select * from v2,t2 where (v2.a=t2.a) and (v2.a>4) and (v2.c<150); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1,v2; - ---echo # using except in view definition ---echo # using embedded view ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c < 300 - except - select a, b, max(c) as c from t1 - where t1.a<9 group by a,b having c > 150; - -create view v2 as - select a, b, max(c) as c from v1 - where v1.a<7 group by a,b; - -let $query= select * from v2,t2 where (v2.a=t2.a) and (v2.a>4) and (v2.c<150); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1,v2; - ---echo # using intersect in view definition ---echo # conditions are pushed in different parts of selects ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.a<9 group by a having c > 300 - intersect - select a, b, max(c) as c from t1 - where t1.b<21 group by b having c > 200; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.b>12) and (v1.c<450); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using except in view definition ---echo # conditions are pushed in different parts of selects ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.b>20 group by a having c > 300 - except - select a, b, max(c) as c from t1 - where t1.a<7 group by b having c > 150; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<2) and (v1.b<30) and (v1.c>450); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using except and union in view definition ---echo # conditions are pushed in different parts of selects ---echo # conjunctive subformulas : pushing into HAVING ---echo # extracted or formula : pushing into WHERE ---echo # extracted or formula : pushing into HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.b>20 group by a having c > 300 - except - select a, b, max(c) as c from t1 - where t1.a<7 group by b having c > 150; - -let $query= select * from v1,t2 where (v1.a=t2.a) and ((v1.a<2) or (v1.a<5)) and (v1.c>450); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using union and intersect in view definition ---echo # conditions are pushed in different parts of selects ---echo # conjunctive subformulas : pushing into WHERE and HAVING -create view v1 as - select a, b, max(c) as c from t1 - where t1.a<9 group by a having c > 100 - intersect - select a, b, max(c) as c from t1 - where t1.a>3 group by b having c < 800 - union - select a, b, max(c) as c from t1 - where t1.b>10 group by a,b having c > 300; - -let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>1) and (v1.b > 12) and (v1.c>400); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - -create table t3 (a int, b int, c int); -insert into t3 values - (1,21,345), (2,33,7), (8,33,114), (3,21,500), (1,19,107), (5,14,787), - (4,33,123), (9,10,211), (11,16,207), (10,33,988), (5,27,132), (12,21,104), - (6,20,309), (16,20,315), (16,21,101), (18,33,404), (19,10,800), (10,21,123), - (17,11,708), (6,20,214); - -create index i1 on t3(a); - ---echo # conjunctive subformulas : pushing into WHERE ---echo # pushed condition gives range access -create view v1 as - select a, b, max(c) as max_c from t3 - where a>0 group by a; - -let $query= select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using union in view definition ---echo # conjunctive subformulas : pushing into WHERE ---echo # pushed condition gives range access -create view v1 as - select a, b, max(c) as c from t3 - where t3.a>1 group by a - union - select a, b, max(c) as c from t3 - where t3.a>2 group by a; - -let $query= select * from v1,t2 where (v1.b=t2.b) and (v1.a<4); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - ---echo # using union in view definition ---echo # conjunctive subformulas : pushing into WHERE ---echo # pushed condition gives range access in one of the selects -create view v1 as - select a, b, max(c) as c from t3 - where t3.a>1 group by a - union - select a, b, max(c) as c from t3 - where t3.b<21 group by b; - -let $query= select * from v1,t2 where (v1.b=t2.b) and (v1.a<3); -eval $no_pushdown $query; -eval $query; -eval explain $query; -eval explain format=json $query; - -drop view v1; - -alter table t3 drop index i1; - -drop table t1,t2,t3; - ---echo # --echo # MDEV-10782: condition extracted from a multiple equality --echo # pushed into HAVING --echo # @@ -1480,8 +906,8 @@ CREATE TABLE t1 (i int) ENGINE=MyISAM; CREATE VIEW v AS SELECT 5; SELECT * FROM t1 WHERE 1 IN ( SELECT * FROM v ); DROP VIEW v; -DROP TABLE t1; - +DROP TABLE t1; + --echo # --echo # MDEV-10785: second execution of a query with condition --echo # pushed into view @@ -1510,17 +936,17 @@ select a from t1 order by a limit 5; set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from (select a from t1 order by a limit 5) t where t.a not in (1,2,3); -set statement optimizer_switch='condition_pushdown_for_derived=on' for +set statement optimizer_switch='condition_pushdown_for_derived=on' for select * from (select a from t1 order by a limit 5) t where t.a not in (1,2,3); select a from t1 where a < 4 union select a from t1 where a > 5 order by a limit 5; set statement optimizer_switch='condition_pushdown_for_derived=off' for -select * from +select * from (select a from t1 where a < 4 union select a from t1 where a > 5 order by a limit 5) t where t.a not in (2,9); -set statement optimizer_switch='condition_pushdown_for_derived=on' for -select * from +set statement optimizer_switch='condition_pushdown_for_derived=on' for +select * from (select a from t1 where a < 4 union select a from t1 where a > 5 order by a limit 5) t where t.a not in (2,9); @@ -1537,45 +963,45 @@ CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; CREATE TABLE t3 (c INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; -SELECT * FROM t1 WHERE a IN ( - SELECT b FROM v2 WHERE b < a OR b IN ( - SELECT c FROM v3 WHERE c = a - ) +SELECT * FROM t1 WHERE a IN ( + SELECT b FROM v2 WHERE b < a OR b IN ( + SELECT c FROM v3 WHERE c = a + ) ); INSERT INTO t1 VALUES (2); INSERT INTO t2 VALUES (3), (2); INSERT INTO t3 VALUES (4), (1), (2), (7); -SELECT * FROM t1 WHERE a IN ( - SELECT b FROM v2 WHERE b < a OR b IN ( - SELECT c FROM v3 WHERE c = a - ) +SELECT * FROM t1 WHERE a IN ( + SELECT b FROM v2 WHERE b < a OR b IN ( + SELECT c FROM v3 WHERE c = a + ) ); EXPLAIN FORMAT=JSON -SELECT * FROM t1 WHERE a IN ( - SELECT b FROM v2 WHERE b < a OR b IN ( - SELECT c FROM v3 WHERE c = a - ) +SELECT * FROM t1 WHERE a IN ( + SELECT b FROM v2 WHERE b < a OR b IN ( + SELECT c FROM v3 WHERE c = a + ) ); CREATE TABLE t4 (d INT, e INT) ENGINE=MyISAM; INSERT INTO t4 VALUES (1,10),(3,11),(2,10),(2,20),(3,21); -CREATE OR REPLACE VIEW v4 AS +CREATE OR REPLACE VIEW v4 AS SELECT d, sum(e) AS s FROM t4 GROUP BY d; -let $query = -SELECT * FROM t1 WHERE a IN ( - SELECT b FROM v2 WHERE b < a OR b IN ( - SELECT d FROM v4 WHERE s > a - ) +let $query = +SELECT * FROM t1 WHERE a IN ( + SELECT b FROM v2 WHERE b < a OR b IN ( + SELECT d FROM v4 WHERE s > a + ) ); eval $no_pushdown $query; eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; DROP VIEW v2,v3,v4; DROP TABLE t1,t2,t3,t4; @@ -1588,17 +1014,17 @@ DROP TABLE t1,t2,t3,t4; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1); - + CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (3),(4); CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; - -SELECT * FROM + +SELECT * FROM ( SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM v2 WHERE b = a ) ) AS sq; EXPLAIN FORMAT=JSON -SELECT * FROM +SELECT * FROM ( SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM v2 WHERE b = a ) ) AS sq; @@ -1606,18 +1032,18 @@ DROP VIEW v2; DROP TABLE t1,t2; --echo # ---echo # MDEV-11102: condition pushdown into materialized inner table ---echo # of outer join is not applied as not being valid +--echo # MDEV-11102: condition pushdown into materialized inner table +--echo # of outer join is not applied as not being valid --echo # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (0),(2); - + CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(2); - + CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; - + SELECT * FROM t1 LEFT JOIN t2 ON a = b WHERE b IS NULL; SELECT * FROM t1 LEFT JOIN v2 ON a = b WHERE b IS NULL; @@ -1650,26 +1076,26 @@ DROP TABLE t1; CREATE TABLE t1 (pk1 INT PRIMARY KEY, a INT, b INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (10,7,1),(11,0,2); - + CREATE TABLE t2 (pk2 INT PRIMARY KEY, c INT, d DATETIME) ENGINE=MyISAM; -INSERT INTO t2 VALUES +INSERT INTO t2 VALUES (1,4,'2008-09-27 00:34:58'), (2,5,'2007-05-28 00:00:00'), (3,6,'2009-07-25 09:21:20'); - + CREATE VIEW v1 AS SELECT * FROM t1; CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; -SELECT * FROM v1 AS sq +SELECT * FROM v1 AS sq WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100; EXPLAIN FORMAT=JSON -SELECT * FROM v1 AS sq +SELECT * FROM v1 AS sq WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100; -SELECT * FROM ( SELECT * FROM t1 ) AS sq +SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100; EXPLAIN FORMAT=JSON -SELECT * FROM ( SELECT * FROM t1 ) AS sq +SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100; DROP VIEW v1,v2; @@ -1729,7 +1155,7 @@ DROP TABLE t1,t2; CREATE TABLE t (pk INT PRIMARY KEY, f INT) ENGINE=MyISAM; CREATE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t; INSERT INTO t VALUES (1,1),(3,2); - + SELECT * FROM v AS v1, v AS v2 WHERE v2.pk > v1.f AND v1.f IN ( SELECT COUNT(pk) FROM t ); @@ -1871,10 +1297,10 @@ DROP TABLE t1; CREATE TABLE t1 (c VARCHAR(3)); CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; INSERT INTO t1 VALUES ('foo'),('bar'); - + CREATE TABLE t2 (c VARCHAR(3)); INSERT INTO t2 VALUES ('foo'),('xyz'); - + SELECT * FROM v1 WHERE v1.c IN ( SELECT MIN(c) FROM t2 WHERE 0 ); EXPLAIN FORMAT=JSON SELECT * FROM v1 WHERE v1.c IN ( SELECT MIN(c) FROM t2 WHERE 0 ); @@ -1885,18 +1311,18 @@ DROP TABLE t1,t2; CREATE TABLE t1 (d DECIMAL(10,2)); CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; INSERT INTO t1 VALUES (5.37),(1.1); - + CREATE TABLE t2 (d DECIMAL(10,2)); INSERT INTO t2 VALUES ('1.1'),('2.23'); - + SELECT * FROM v1 WHERE v1.d IN ( SELECT MIN(d) FROM t2 WHERE 0 ); DROP VIEW v1; DROP TABLE t1,t2; --echo # ---echo # MDEV-11820: second execution of PS for query ---echo # with false subquery predicate in WHERE +--echo # MDEV-11820: second execution of PS for query +--echo # with false subquery predicate in WHERE --echo # CREATE TABLE t1 (c VARCHAR(3)) ENGINE=MyISAM; @@ -1905,10 +1331,10 @@ CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; CREATE TABLE t2 (a INT); INSERT INTO t2 VALUES (3), (4); -PREPARE stmt1 FROM +PREPARE stmt1 FROM " SELECT * FROM v1 WHERE 1 IN (SELECT a FROM t2) OR c = 'foo'"; -PREPARE stmt2 FROM -"EXPLAIN FORMAT=JSON +PREPARE stmt2 FROM +"EXPLAIN FORMAT=JSON SELECT * FROM v1 WHERE 1 IN (SELECT a FROM t2) OR c = 'foo'"; EXECUTE stmt1; EXECUTE stmt2; @@ -1921,14 +1347,14 @@ INSERT INTO t2 SELECT a+1 FROM t2; EXECUTE stmt1; EXECUTE stmt2; DEALLOCATE PREPARE stmt1; -# the result here will change after the merge with the fix for mdev-11859 +# the result here will change after the merge with the fix for mdev-11859 DEALLOCATE PREPARE stmt2; DROP VIEW v1; DROP TABLE t1,t2; --echo # ---echo # MDEV-12373: pushdown into derived with side effects is prohibited +--echo # MDEV-12373: pushdown into derived with side effects is prohibited --echo # CREATE TABLE sales_documents ( @@ -1946,9 +1372,9 @@ CREATE TABLE sales_documents ( PRIMARY KEY (id) ); -INSERT INTO sales_documents -(id, sale_id, type, order_number, data, created_at, - updated_at, date, generated, synced_at, sum) +INSERT INTO sales_documents +(id, sale_id, type, order_number, data, created_at, + updated_at, date, generated, synced_at, sum) VALUES (555, 165, 3, 5, '{}', 1486538300, 1486722835, '2017-02-17', 0, 1486538313, 2320.00), (556, 165, 2, 3, '{}', 1486538304, 1486563125, '2017-02-08', 1, 1486538302, 2320.00), @@ -2024,11 +1450,11 @@ SELECT * FROM GROUP BY t.order_number ) a, (SELECT @row := 0) r) t WHERE row <> order_number; - + DROP TABLE sales_documents; --echo # ---echo # MDEV-12845: pushdown from merged derived using equalities +--echo # MDEV-12845: pushdown from merged derived using equalities --echo # create table t1 (a int); @@ -2414,20 +1840,829 @@ eval $query; eval EXPLAIN FORMAT=JSON $query; DROP TABLE t1; -# Start of 10.3 tests +--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; --echo # ---echo # MDEV-10855: Pushdown into derived with window functions +--echo # MDEV-16614 signal 7 after calling stored procedure, that uses regexp +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(m1 varchar(5), m2 varchar(5)) +BEGIN +SELECT a FROM + (SELECT "aa" a) t + JOIN (SELECT "aa" b) t1 on t.a=t1.b +WHERE t.a regexp m1 and t1.b regexp m2 +GROUP BY a; +END$$ +DELIMITER ;$$ +CALL p1('a','a'); +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1(m1 varchar(5)) +BEGIN + SELECT a FROM (SELECT "aa" a) t WHERE t.a regexp m1; +END$$ +DELIMITER ;$$ +CALL p1('a'); +DROP PROCEDURE p1; + + +SELECT a FROM (SELECT "aa" a) t WHERE REGEXP_INSTR(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); + + +DELIMITER $$; +CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT +BEGIN + RETURN 1; +END;$$ +CREATE OR REPLACE PROCEDURE p1(m1 varchar(5)) +BEGIN + SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, m1); +END$$ +DELIMITER ;$$ +CALL p1('a'); +DROP PROCEDURE p1; +DROP FUNCTION f1; + + +DELIMITER $$; +CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT +BEGIN + RETURN 1; +END;$$ +DELIMITER ;$$ +SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); +DROP FUNCTION f1; + +--echo # +--echo # MDEV-17011: condition pushdown into materialized derived used +--echo # in INSERT SELECT, multi-table UPDATE and DELETE +--echo # + +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); + +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES + (3), (7), (1), (4), (1); + +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; + +let $q1= +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; + +eval EXPLAIN FORMAT=JSON $q1; +eval $q1; + +SELECT * FROM t3; + +let $q2= +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 + WHERE t2.a= t.c and t.a>=3; + +eval EXPLAIN FORMAT=JSON $q2; +eval $q2; + +SELECT * FROM t2; + +let $q3= +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t + WHERE t2.a= t.c+9 and t.a=2; + +eval EXPLAIN FORMAT=JSON $q3; +eval $q3; + +SELECT * FROM t2; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-16765: pushdown condition with the CASE structure +--echo # defined with Item_cond item +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,2), (3,4), (2,3); + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1; + +--echo # +--echo # MDEV-16803: pushdown condition with IN predicate in the derived table +--echo # defined with several SELECT statements +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,2),(1,1); + +SELECT * FROM +( + SELECT a,b,1 as c + FROM t1 + UNION ALL + SELECT a,b,2 as c + FROM t1 +) AS tab +WHERE ((a,b) IN ((1,2),(3,2))); + +DROP TABLE t1; + +--echo # +--echo # MDEV-17354: INSERT SELECT with condition pushdown into derived +--echo # + +CREATE TABLE t1 (f INT NOT NULL); +INSERT INTO t1 VALUES (3), (7), (3); + +CREATE ALGORITHM= TEMPTABLE VIEW v1 AS SELECT * FROM ( SELECT * FROM t1 ) AS sq; + +let $q1= +INSERT INTO t1 +SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ) AS t WHERE f IS NOT NULL; + +eval $q1; +eval EXPLAIN $q1; +eval EXPLAIN FORMAT=JSON $q1; +SELECT * FROM t1; + +DELETE FROM t1; +INSERT INTO t1 VALUES (3), (7), (3); + +let $q2= +INSERT INTO t1 +SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ON v1.f=t1.f) AS t + WHERE f IS NOT NULL; + +eval $q2; +eval EXPLAIN FORMAT=JSON $q2; +SELECT * FROM t1; + +DROP VIEW v1; +DROP TABLE t1; + +--echo # End of 10.2 tests + +--echo # +--echo # MDEV-14579: pushdown conditions into materialized views/derived tables +--echo # that are defined with EXIST or/and INTERSECT +--echo # + +create table t1 (a int, b int, c int); +create table t2 (a int, b int, c int); + +insert into t1 values + (1,21,345), (1,33,7), (8,33,114), (1,21,500), (1,19,117), (5,14,787), + (8,33,123), (9,10,211), (5,16,207), (1,33,988), (5,27,132), (1,21,104), + (6,20,309), (6,20,315), (1,21,101), (4,33,404), (9,10,800), (1,21,123); + +insert into t2 values + (2,3,207), (1,16,909), (5,14,312), + (5,33,207), (6,20,211), (1,19,132), + (8,33,117), (3,21,231), (6,23,303); + +create view v1 as + select a, b, min(c) as c from t1 + where t1.a<9 group by a,b having c < 300 + intersect + select a, b, min(c) as c from t1 + where t1.b>10 group by a,b having c > 100; + +--echo # using intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE +--echo # pushing equalities +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a=8); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE using equalities +let $query= select * from v1,t2 where (v1.a=t2.a) and (t2.a=8); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # conjunctive subformulas : pushing into HAVING +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.c>200); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE +--echo # conjunctive subformulas : pushing into HAVING +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>110); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # extracted or formula : pushing into WHERE +let $query= + select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # extracted or formula : pushing into HAVING +let $query= + select * from v1,t2 where + (v1.a=t2.a) and ((v1.c>200) or (v1.c<105)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # extracted or formula : pushing into WHERE +--echo # extracted or formula : pushing into HAVING using equalities +--echo # pushing equalities +let $query= + select * from v1,t2 where + ((v1.a>3) and (t2.c>110) and (v1.c=t2.c)) or + ((v1.a=1) and (v1.c<110)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using intersect in view definition +--echo # prepare of a query +--echo # conjunctive subformulas : pushing into WHERE +--echo # conjunctive subformulas : pushing into HAVING +prepare stmt from "select * from v1,t2 + where (v1.a=t2.a) and (v1.a<5) and (v1.c>110);"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--echo # using intersect in derived table definition +--echo # extracted or formula : pushing into WHERE using equalities +--echo # extracted or formula : pushing into HAVING +--echo # pushing equalities +let $query= + select * + from t2, + (select a, b, min(c) as c from t1 + where t1.a<9 group by a,b having c < 300 + intersect + select a, b, min(c) as c from t1 + where t1.b>10 group by a,b having c > 100) as d1 + where + (d1.b=t2.b) and + (((t2.b>13) and (t2.c=909)) or + ((d1.a<4) and (d1.c<200))); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +create view v1 as + select a, b, max(c) as c from t1 + where t1.a<9 group by a,b having c > 200 + except + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300; + +--echo # using except in view definition +--echo # conjunctive subformulas : pushing into WHERE +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # conjunctive subformulas : pushing into WHERE +--echo # pushing equalities +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a=6); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # conjunctive subformulas : pushing into WHERE using equalities +let $query= select * from v1,t2 where (v1.a=t2.a) and (t2.a=6); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # conjunctive subformulas : pushing into HAVING +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.c>500); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # conjunctive subformulas : pushing into WHERE +--echo # conjunctive subformulas : pushing into HAVING +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>500); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # extracted or formula : pushing into WHERE +let $query= + select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # extracted or formula : pushing into HAVING +let $query= + select * from v1,t2 where + (v1.a=t2.a) and ((v1.c<400) or (v1.c>800)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # extracted or formula : pushing into WHERE +--echo # extracted or formula : pushing into HAVING using equalities +--echo # pushing equalities +let $query= + select * from v1,t2 where + (v1.c=t2.c) and + ((v1.a>1) and (t2.c<500)) or + ((v1.a=1) and (v1.c>500)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +--echo # using except in view definition +--echo # prepare of a query +--echo # conjunctive subformulas : pushing into WHERE +--echo # conjunctive subformulas : pushing into HAVING +prepare stmt from "select * from v1,t2 + where (v1.a=t2.a) and (v1.a<5) and (v1.c>500);"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--echo # using except in view definition +--echo # extracted or formula : pushing into WHERE using equalities +--echo # extracted or formula : pushing into HAVING +--echo # pushing equalities +let $query= + select * + from t2, + (select a, b, max(c) as c from t1 + where t1.a<9 group by a,b having c > 200 + except + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300) as d1 + where + (d1.b=t2.b) and + (((t2.b>13) and (t2.c=988)) or + ((d1.a>4) and (d1.c>500))); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using union and intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, min(c) as c from t1 + where t1.a<9 group by a,b having c > 200 + union + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300 + intersect + select a, b, max(c) as c from t1 + where t1.a>3 group by a,b having c < 530; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using union and intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, min(c) as c from t1 + where t1.a<9 group by a,b having c > 200 + intersect + select a, b, max(c) as c from t1 + where t1.a>3 group by a,b having c < 500 + union + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using union and except in view definition +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, min(c) as c from t1 + where t1.a<9 group by a,b having c > 200 + union + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300 + except + select a, b, max(c) as c from t1 + where t1.a>3 group by a,b having c < 530; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using union and except in view definition +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, min(c) as c from t1 + where t1.a<9 group by a,b having c > 200 + except + select a, b, max(c) as c from t1 + where t1.a>3 group by a,b having c < 500 + union + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using except and intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300 + intersect + select a, b, max(c) as c from t1 + where t1.a<7 group by a,b having c < 500 + except + select a, b, max(c) as c from t1 + where t1.a<9 group by a,b having c > 150; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<150); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using except and intersect in view definition +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300 + except + select a, b, max(c) as c from t1 + where t1.a<9 group by a,b having c > 150 + intersect + select a, b, max(c) as c from t1 + where t1.a<7 group by a,b having c < 500; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using except, intersect and union in view definition +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300 + except + select a, b, max(c) as c from t1 + where t1.a<9 group by a,b having c > 150 + intersect + select a, b, max(c) as c from t1 + where t1.a<7 group by a,b having c < 500 + union + select a, b, max(c) as c from t1 + where t1.a<7 group by a,b having c < 120; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using intersect in view definition +--echo # using embedded view +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300 + intersect + select a, b, max(c) as c from t1 + where t1.a<9 group by a,b having c > 120; + +create view v2 as + select a, b, max(c) as c from v1 + where v1.a<7 group by a,b; + +let $query= select * from v2,t2 where (v2.a=t2.a) and (v2.a>4) and (v2.c<150); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1,v2; + +--echo # using except in view definition +--echo # using embedded view +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c < 300 + except + select a, b, max(c) as c from t1 + where t1.a<9 group by a,b having c > 150; + +create view v2 as + select a, b, max(c) as c from v1 + where v1.a<7 group by a,b; + +let $query= select * from v2,t2 where (v2.a=t2.a) and (v2.a>4) and (v2.c<150); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1,v2; + +--echo # using intersect in view definition +--echo # conditions are pushed in different parts of selects +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.a<9 group by a having c > 300 + intersect + select a, b, max(c) as c from t1 + where t1.b<21 group by b having c > 200; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.b>12) and (v1.c<450); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using except in view definition +--echo # conditions are pushed in different parts of selects +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.b>20 group by a having c > 300 + except + select a, b, max(c) as c from t1 + where t1.a<7 group by b having c > 150; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a<2) and (v1.b<30) and (v1.c>450); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using except and union in view definition +--echo # conditions are pushed in different parts of selects +--echo # conjunctive subformulas : pushing into HAVING +--echo # extracted or formula : pushing into WHERE +--echo # extracted or formula : pushing into HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.b>20 group by a having c > 300 + except + select a, b, max(c) as c from t1 + where t1.a<7 group by b having c > 150; + +let $query= select * from v1,t2 where (v1.a=t2.a) and ((v1.a<2) or (v1.a<5)) and (v1.c>450); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using union and intersect in view definition +--echo # conditions are pushed in different parts of selects +--echo # conjunctive subformulas : pushing into WHERE and HAVING +create view v1 as + select a, b, max(c) as c from t1 + where t1.a<9 group by a having c > 100 + intersect + select a, b, max(c) as c from t1 + where t1.a>3 group by b having c < 800 + union + select a, b, max(c) as c from t1 + where t1.b>10 group by a,b having c > 300; + +let $query= select * from v1,t2 where (v1.a=t2.a) and (v1.a>1) and (v1.b > 12) and (v1.c>400); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +create table t3 (a int, b int, c int); +insert into t3 values + (1,21,345), (2,33,7), (8,33,114), (3,21,500), (1,19,107), (5,14,787), + (4,33,123), (9,10,211), (11,16,207), (10,33,988), (5,27,132), (12,21,104), + (6,20,309), (16,20,315), (16,21,101), (18,33,404), (19,10,800), (10,21,123), + (17,11,708), (6,20,214); + +create index i1 on t3(a); + +--echo # conjunctive subformulas : pushing into WHERE +--echo # pushed condition gives range access +create view v1 as + select a, b, max(c) as max_c from t3 + where a>0 group by a; + +let $query= select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using union in view definition +--echo # conjunctive subformulas : pushing into WHERE +--echo # pushed condition gives range access +create view v1 as + select a, b, max(c) as c from t3 + where t3.a>1 group by a + union + select a, b, max(c) as c from t3 + where t3.a>2 group by a; + +let $query= select * from v1,t2 where (v1.b=t2.b) and (v1.a<4); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +--echo # using union in view definition +--echo # conjunctive subformulas : pushing into WHERE +--echo # pushed condition gives range access in one of the selects +create view v1 as + select a, b, max(c) as c from t3 + where t3.a>1 group by a + union + select a, b, max(c) as c from t3 + where t3.b<21 group by b; + +let $query= select * from v1,t2 where (v1.b=t2.b) and (v1.a<3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; + +drop view v1; + +alter table t3 drop index i1; + +drop table t1,t2,t3; + +--echo # +--echo # MDEV-10855: Pushdown into derived with window functions --echo # set @save_optimizer_switch= @@optimizer_switch; set optimizer_switch='split_materialized=off'; - + create table t1 (a int, c varchar(16)); insert into t1 values (8,'aa'), (5,'cc'), (1,'bb'), (2,'aa'), (9,'cc'), (7,'aa'), (2,'aa'), (7,'bb'); - + create table t2 (a int, b int, c varchar(16), index idx(a,c)); insert into t2 values (7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), @@ -2439,27 +2674,31 @@ let $q1= select * from (select a, c, sum(b) over (partition by a,c) from t2) as t where t.a > 2 and t.c in ('aa','bb','cc'); +--sorted_result eval $no_pushdown $q1; +--sorted_result eval $q1; eval explain $q1; eval explain format=json $q1; let $q2= select * from -( +( select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 union all select 2 as n, a, c, sum(b) over (partition by a) as s from t2 ) as t where t.a > 2 and t.c in ('aa','bb','cc'); +--sorted_result eval $no_pushdown $q2; +--sorted_result eval $q2; eval explain $q2; eval explain format=json $q2; let $q3= -select * +select * from (select a, c, sum(b) over (partition by a,c) as s from t2) as t, t1 where t1.a=t.a and t1.c=t.c and t1.c in ('aa','bb','cc'); @@ -2467,10 +2706,10 @@ eval $no_pushdown $q3; eval $q3; eval explain $q3; eval explain format=json $q3; - + let $q4= select * from -( +( select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 union all select 2 as n, a, c, sum(b) over (partition by a) as s from t2 @@ -2479,7 +2718,9 @@ select * from ) as t where t.a > 2 and t.c in ('aa','bb','cc'); +--sorted_result eval $no_pushdown $q4; +--sorted_result eval $q4; eval explain $q4; eval explain format=json $q4; @@ -2491,7 +2732,9 @@ select * from (select a, c, from t2 ) as t where t.a > 2 and t.c in ('aa','bb','cc'); +--sorted_result eval $no_pushdown $q5; +--sorted_result eval $q5; eval explain $q5; eval explain format=json $q5; @@ -2503,7 +2746,9 @@ select * from (select a, c, from t2 ) as t where t.a > 2 and t.c in ('aa','bb','cc'); +--sorted_result eval $no_pushdown $q6; +--sorted_result eval $q6; eval explain $q6; eval explain format=json $q6; @@ -2515,7 +2760,9 @@ select * from (select a, c, from t2 ) as t where t.a > 2 and t.c in ('aa','bb','cc'); +--sorted_result eval $no_pushdown $q7; +--sorted_result eval $q7; eval explain $q7; eval explain format=json $q7; @@ -2525,14 +2772,14 @@ drop table t1,t2; set optimizer_switch= @save_optimizer_switch; --echo # ---echo # MDEV-13369: Optimization for equi-joins of grouping derived tables +--echo # MDEV-13369: Optimization for equi-joins of grouping derived tables --echo # (Splitting derived tables / views with GROUP BY) --echo # MDEV-13389: Optimization for equi-joins of derived tables with WF --echo # (Splitting derived tables / views with window functions) --echo # let -$no_splitting= set statement optimizer_switch='split_materialized=off' for; +$no_splitting= set statement optimizer_switch='split_materialized=off' for; create table t1 (a int, b int, index idx_b(b)) engine=myisam; insert into t1 values @@ -2605,7 +2852,7 @@ insert into t3 values (8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), (7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), (5,14,'dd'), (9,12,'ee'); - + create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; insert into t4 values (7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), @@ -2627,7 +2874,7 @@ where t3.b > 15; eval $no_splitting $q3; eval $q3; eval explain extended $q3; -eval explain format=json $q3; +eval explain format=json $q3; let $q30= select t3.a,t3.c,t.max,t.min @@ -2639,7 +2886,7 @@ where t3.b <= 15; eval $no_splitting $q30; eval $q30; eval explain extended $q30; -eval explain format=json $q30; +eval explain format=json $q30; let $q4= select t3.a,t3.c,t.max,t.min @@ -2651,7 +2898,7 @@ where t3.b > 15; eval $no_splitting $q4; eval $q4; eval explain extended $q4; -eval explain format=json $q4; +eval explain format=json $q4; let $q40= select t3.a,t3.c,t.max,t.min @@ -2663,7 +2910,7 @@ where t3.b <= 15; eval $no_splitting $q40; eval $q40; eval explain extended $q40; -eval explain format=json $q40; +eval explain format=json $q40; drop index idx_a on t2; create index idx on t2(c,b); @@ -2681,7 +2928,9 @@ select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c; +--sorted_result eval $no_splitting $q5; +--sorted_result eval $q5; eval explain extended $q5; eval explain format=json $q5; @@ -2701,7 +2950,9 @@ select * from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c; +--sorted_result eval $no_splitting $q6; +--sorted_result eval $q6; eval explain extended $q6; eval explain format=json $q6; @@ -2711,7 +2962,9 @@ select * from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t where t2.b < 40 and t2.a=t3.a and t3.c=t.c; +--sorted_result eval $no_splitting $q60; +--sorted_result eval $q60; eval explain extended $q60; eval explain format=json $q60; @@ -2879,6 +3132,7 @@ cte as (select median(f2) over (partition by f1) as k1 from t1 order by f1), cte1 as (select median(f4) over (partition by f1) as k2 from t1) select k1,k2 from cte1, cte; +--sorted_result eval $q; eval explain $q; @@ -2908,244 +3162,6 @@ DROP VIEW v1; 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; - ---echo # ---echo # MDEV-16614 signal 7 after calling stored procedure, that uses regexp ---echo # - -DELIMITER $$; -CREATE PROCEDURE p1(m1 varchar(5), m2 varchar(5)) -BEGIN -SELECT a FROM - (SELECT "aa" a) t - JOIN (SELECT "aa" b) t1 on t.a=t1.b -WHERE t.a regexp m1 and t1.b regexp m2 -GROUP BY a; -END$$ -DELIMITER ;$$ -CALL p1('a','a'); -DROP PROCEDURE p1; - - -DELIMITER $$; -CREATE PROCEDURE p1(m1 varchar(5)) -BEGIN - SELECT a FROM (SELECT "aa" a) t WHERE t.a regexp m1; -END$$ -DELIMITER ;$$ -CALL p1('a'); -DROP PROCEDURE p1; - - -SELECT a FROM (SELECT "aa" a) t WHERE REGEXP_INSTR(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); - - -DELIMITER $$; -CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT -BEGIN - RETURN 1; -END;$$ -CREATE OR REPLACE PROCEDURE p1(m1 varchar(5)) -BEGIN - SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, m1); -END$$ -DELIMITER ;$$ -CALL p1('a'); -DROP PROCEDURE p1; -DROP FUNCTION f1; - - -DELIMITER $$; -CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT -BEGIN - RETURN 1; -END;$$ -DELIMITER ;$$ -SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); -DROP FUNCTION f1; - ---echo # ---echo # MDEV-17011: condition pushdown into materialized derived used ---echo # in INSERT SELECT, multi-table UPDATE and DELETE ---echo # - -CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; -INSERT INTO t1 VALUES - (1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); - -CREATE TABLE t2 (a int) ENGINE MYISAM; -INSERT INTO t2 VALUES - (3), (7), (1), (4), (1); - -CREATE TABLE t3 (a int, b int) ENGINE MYISAM; - -let $q1= -INSERT INTO t3 -SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; - -eval EXPLAIN FORMAT=JSON $q1; -eval $q1; - -SELECT * FROM t3; - -let $q2= -UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 - WHERE t2.a= t.c and t.a>=3; - -eval EXPLAIN FORMAT=JSON $q2; -eval $q2; - -SELECT * FROM t2; - -let $q3= -DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t - WHERE t2.a= t.c+9 and t.a=2; - -eval EXPLAIN FORMAT=JSON $q3; -eval $q3; - -SELECT * FROM t2; - -DROP TABLE t1,t2,t3; - ---echo # ---echo # MDEV-16765: pushdown condition with the CASE structure ---echo # defined with Item_cond item ---echo # - -CREATE TABLE t1(a INT, b INT); -INSERT INTO t1 VALUES (1,2), (3,4), (2,3); - -LET $query= -SELECT * -FROM -( - SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) - THEN 1 ELSE 0 END AS max_a,b - FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; - -LET $query= -SELECT * -FROM -( - SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) - THEN 1 ELSE 0 END AS max_a,b - FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; - -LET $query= -SELECT * -FROM -( - SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) - THEN 1 ELSE 0 END AS max_a,b - FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; - -LET $query= -SELECT * -FROM -( - SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) - THEN 1 ELSE 0 END AS max_a,b - FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; - -DROP TABLE t1; - ---echo # ---echo # MDEV-16803: pushdown condition with IN predicate in the derived table ---echo # defined with several SELECT statements ---echo # - -CREATE TABLE t1 (a INT, b INT); -INSERT INTO t1 VALUES (1,2),(3,2),(1,1); - -SELECT * FROM -( - SELECT a,b,1 as c - FROM t1 - UNION ALL - SELECT a,b,2 as c - FROM t1 -) AS tab -WHERE ((a,b) IN ((1,2),(3,2))); - -DROP TABLE t1; - - ---echo # ---echo # MDEV-17354: INSERT SELECT with condition pushdown into derived ---echo # - -CREATE TABLE t1 (f INT NOT NULL); -INSERT INTO t1 VALUES (3), (7), (3); - -CREATE ALGORITHM= TEMPTABLE VIEW v1 AS SELECT * FROM ( SELECT * FROM t1 ) AS sq; - -let $q1= -INSERT INTO t1 -SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ) AS t WHERE f IS NOT NULL; - -eval $q1; -eval EXPLAIN $q1; -eval EXPLAIN FORMAT=JSON $q1; -SELECT * FROM t1; - -DELETE FROM t1; -INSERT INTO t1 VALUES (3), (7), (3); - -let $q2= -INSERT INTO t1 -SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ON v1.f=t1.f) AS t - WHERE f IS NOT NULL; - -eval $q2; -eval EXPLAIN FORMAT=JSON $q2; -SELECT * FROM t1; - -DROP VIEW v1; -DROP TABLE t1; - ---echo # Start of 10.3 tests - ---echo # --echo # MDEV-16801: splittable materialized derived/views with --echo # one grouping field from table without keys --echo # @@ -3218,3 +3234,5 @@ eval $q; set join_cache_level=default; DROP TABLE t1,t2; + +--echo # End of 10.3 tests |