CREATE TABLE t0 (
i0 INTEGER
);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
KEY(f1), KEY(f1,f2), KEY(f3));
INSERT INTO t1
SELECT i0, i0 + 10*i0,
i0 + 10*i0 + 100*i0
FROM t0 AS a0;
INSERT INTO t1
SELECT i0, i0 + 10*i0,
i0 + 10*i0 + 100*i0
FROM t0 AS a0;
INSERT INTO t1 VALUES (NULL, 1, 2);
INSERT INTO t1 VALUES (NULL, 1, 3);
ANALYZE TABLE t0, t1;
Table	Op	Msg_type	Msg_text
test.t0	analyze	status	OK
test.t1	analyze	status	OK
SET optimizer_switch="derived_merge=off,derived_condition_pushdown=on";
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, f2 FROM t1) as dt WHERE f1 > 2;
EXPLAIN
-> Table scan on dt  (rows=4)
    -> Materialize  (rows=4)
        -> Filter: (t1.f1 > 2)  (rows=4)
            -> Covering index range scan on t1 using f1_2 over (2 < f1)  (rows=4)

SELECT * FROM (SELECT f1, f2 FROM t1) as dt WHERE f1 > 2;
f1	f2
3	33
3	33
4	44
4	44
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, f2 FROM t1) as dt
WHERE f1 < 3 and f2 > 11;
EXPLAIN
-> Table scan on dt  (rows=2)
    -> Materialize  (rows=2)
        -> Filter: ((t1.f1 < 3) and (t1.f2 > 11))  (rows=2)
            -> Covering index range scan on t1 using f1_2 over (NULL < f1 < 3)  (rows=6)

SELECT * FROM (SELECT f1, f2 FROM t1) as dt
WHERE f1 < 3 and f2 > 11;
f1	f2
2	22
2	22
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE f1 > 2 and f2 < 25 and f3 > 200;
EXPLAIN
-> Table scan on dt  (rows=0.667)
    -> Materialize  (rows=0.667)
        -> Filter: ((t1.f2 < 25) and (t1.f3 > 200))  (rows=0.667)
            -> Index range scan on t1 using f1 over (2 < f1), with index condition: (t1.f1 > 2)  (rows=4)

SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE f1 > 2 and f2 < 25 and f3 > 200;
f1	f2	f3
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE f1 > 3 and f2 < 50 or f3 > 200;
EXPLAIN
-> Table scan on dt  (rows=4.89)
    -> Materialize  (rows=4.89)
        -> Filter: (((t1.f1 > 3) and (t1.f2 < 50)) or (t1.f3 > 200))  (rows=4.89)
            -> Table scan on t1  (rows=12)

SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE f1 > 3 and f2 < 50 or f3 > 200;
f1	f2	f3
2	22	222
3	33	333
4	44	444
2	22	222
3	33	333
4	44	444
EXPLAIN FORMAT=tree SELECT t1.f2 as f2, dt.f1 as f1, dt.f3 as f3 FROM t1,
(SELECT f1, f2, f3 FROM t1) as dt
WHERE (dt.f1 = 6) or( t1.f2 = 50 and dt.f3 = 200);
EXPLAIN
-> Filter: ((dt.f1 = 6) or ((dt.f3 = 200) and (t1.f2 = 50)))  (rows=27.4)
    -> Inner hash join (no condition)  (rows=27.4)
        -> Index scan on t1 using f1_2  (rows=12)
        -> Hash
            -> Filter: ((dt.f1 = 6) or (dt.f3 = 200))  (rows=2.28)
                -> Table scan on dt  (rows=12)
                    -> Materialize  (rows=12)
                        -> Table scan on t1  (rows=12)

SELECT t1.f2 as f2, dt.f1 as f1, dt.f3 as f3 FROM t1,
(SELECT f1, f2, f3 FROM t1) as dt
WHERE (dt.f1 = 6) or( t1.f2 = 50 and dt.f3 = 200);
f2	f1	f3
EXPLAIN FORMAT=tree SELECT /*+ JOIN_ORDER(t0, dt) */ * FROM
(SELECT f1, f2, f3 FROM t1) as dt, t0
WHERE f1 > 3 and f2 < 50 and i0 > 3;
EXPLAIN
-> Inner hash join (no condition)  (rows=1.11)
    -> Table scan on dt  (rows=0.667)
        -> Materialize  (rows=0.667)
            -> Filter: (t1.f2 < 50)  (rows=0.667)
                -> Index range scan on t1 using f1 over (3 < f1), with index condition: (t1.f1 > 3)  (rows=2)
    -> Hash
        -> Filter: (t0.i0 > 3)  (rows=1.67)
            -> Table scan on t0  (rows=5)

SELECT /*+ JOIN_ORDER(t0, dt) */ * FROM
(SELECT f1, f2, f3 FROM t1) as dt, t0
WHERE f1 > 3 and f2 < 50 and i0 > 3;
f1	f2	f3	i0
4	44	444	4
4	44	444	4
EXPLAIN FORMAT=tree SELECT * FROM (SELECT * FROM (SELECT * FROM t1) as dt1) as dt2
WHERE f1 > 3 and f2 < 50 and f3 > 200;
EXPLAIN
-> Table scan on dt2  (rows=0.333)
    -> Materialize  (rows=0.333)
        -> Table scan on dt1  (rows=0.333)
            -> Materialize  (rows=0.333)
                -> Filter: ((t1.f2 < 50) and (t1.f3 > 200))  (rows=0.333)
                    -> Index range scan on t1 using f1 over (3 < f1), with index condition: (t1.f1 > 3)  (rows=2)

SELECT * FROM (SELECT * FROM (SELECT * FROM t1) as dt1) as dt2
WHERE f1 > 3 and f2 < 50 and f3 > 200;
f1	f2	f3
4	44	444
4	44	444
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE (f1 > 2 and f2 < 35) and (f1+f3) > 300;
EXPLAIN
-> Table scan on dt  (rows=1.33)
    -> Materialize  (rows=1.33)
        -> Filter: ((t1.f2 < 35) and ((t1.f1 + t1.f3) > 300))  (rows=1.33)
            -> Index range scan on t1 using f1 over (2 < f1), with index condition: (t1.f1 > 2)  (rows=4)

SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE (f1 > 2 and f2 < 35) and (f1+f3) > 300;
f1	f2	f3
3	33	333
3	33	333
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE (f1 > 2 and f2 < 35) or (f1+f3) > 300 or (f1 < 2);
EXPLAIN
-> Table scan on dt  (rows=12)
    -> Materialize  (rows=12)
        -> Filter: (((t1.f1 > 2) and (t1.f2 < 35)) or ((t1.f1 + t1.f3) > 300) or (t1.f1 < 2))  (rows=12)
            -> Table scan on t1  (rows=12)

SELECT * FROM (SELECT f1, f2, f3 FROM t1) as dt
WHERE (f1 > 2 and f2 < 35) or (f1+f3) > 300 or (f1 < 2);
f1	f2	f3
0	0	0
1	11	111
3	33	333
4	44	444
0	0	0
1	11	111
3	33	333
4	44	444
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, f2 FROM t1) as dt1,
(SELECT f3 FROM t1) as dt2
WHERE (f1 > 2 and f2 < 35) and (f1+f3) > 300
and (f3 < 400);
EXPLAIN
-> Filter: ((dt1.f1 + dt2.f3) > 300)  (rows=13.3)
    -> Inner hash join (no condition)  (rows=13.3)
        -> Table scan on dt2  (rows=10)
            -> Materialize  (rows=10)
                -> Filter: (t1.f3 < 400)  (rows=10)
                    -> Covering index range scan on t1 using f3 over (NULL < f3 < 400)  (rows=10)
        -> Hash
            -> Table scan on dt1  (rows=1.33)
                -> Materialize  (rows=1.33)
                    -> Filter: ((t1.f1 > 2) and (t1.f2 < 35))  (rows=1.33)
                        -> Covering index range scan on t1 using f1_2 over (2 < f1)  (rows=4)

SELECT * FROM (SELECT f1, f2 FROM t1) as dt1,
(SELECT f3 FROM t1) as dt2
WHERE (f1 > 2 and f2 < 35) and (f1+f3) > 300
and (f3 < 400);
f1	f2	f3
3	33	333
3	33	333
3	33	333
3	33	333
EXPLAIN FORMAT=tree SELECT * FROM (SELECT * FROM (SELECT f1, f2 FROM t1) as dt1,
(SELECT f3 FROM t1) as dt2) as dt3
WHERE (f1 > 2 and f2 < 35) and (f1+f3) > 200
and (f3 < 300);
EXPLAIN
-> Table scan on dt3  (rows=10.7)
    -> Materialize  (rows=10.7)
        -> Filter: ((dt1.f1 + dt2.f3) > 200)  (rows=10.7)
            -> Inner hash join (no condition)  (rows=10.7)
                -> Table scan on dt2  (rows=8)
                    -> Materialize  (rows=8)
                        -> Filter: (t1.f3 < 300)  (rows=8)
                            -> Covering index range scan on t1 using f3 over (NULL < f3 < 300)  (rows=8)
                -> Hash
                    -> Table scan on dt1  (rows=1.33)
                        -> Materialize  (rows=1.33)
                            -> Filter: ((t1.f1 > 2) and (t1.f2 < 35))  (rows=1.33)
                                -> Covering index range scan on t1 using f1_2 over (2 < f1)  (rows=4)

SELECT * FROM (SELECT * FROM (SELECT f1, f2 FROM t1) as dt1,
(SELECT f3 FROM t1) as dt2) as dt3
WHERE (f1 > 2 and f2 < 35) and (f1+f3) > 200
and (f3 < 300);
f1	f2	f3
3	33	222
3	33	222
3	33	222
3	33	222
EXPLAIN FORMAT=tree SELECT i, j, k FROM (SELECT f1 as i, f2 as j, f2 as k FROM t1) as dt
WHERE i > 1 and i+j > 40;
EXPLAIN
-> Table scan on dt  (rows=6)
    -> Materialize  (rows=6)
        -> Filter: ((t1.f1 > 1) and ((t1.f1 + t1.f2) > 40))  (rows=6)
            -> Covering index range scan on t1 using f1_2 over (1 < f1)  (rows=6)

SELECT i, j, k FROM (SELECT f1 as i, f2 as j, f2 as k FROM t1) as dt
WHERE i > 1 and i+j > 40;
i	j	k
4	44	44
4	44	44
EXPLAIN FORMAT=tree SELECT i, j, k
FROM (SELECT l as i, m as j, n as k
FROM (SELECT f1 as l, f2 as m, f3 as n
FROM t1) as dt1 ) as dt2
WHERE i > 1 and i+j > 40;
EXPLAIN
-> Table scan on dt2  (rows=6)
    -> Materialize  (rows=6)
        -> Table scan on dt1  (rows=6)
            -> Materialize  (rows=6)
                -> Filter: ((t1.f1 + t1.f2) > 40)  (rows=6)
                    -> Index range scan on t1 using f1 over (1 < f1), with index condition: (t1.f1 > 1)  (rows=6)

SELECT i, j, k
FROM (SELECT l as i, m as j, n as k
FROM (SELECT f1 as l, f2 as m, f3 as n
FROM t1) as dt1 ) as dt2
WHERE i > 1 and i+j > 40;
i	j	k
4	44	444
4	44	444
EXPLAIN FORMAT=tree SELECT i, j, l, m, n
FROM (SELECT f1 as i, f2 as j FROM t1 ) as dt1 ,
(SELECT f1 as l, f2 as m, f3 as n FROM t1) as dt2
WHERE i > 1 and i+j > 40 and m < 20 and m+i > 20;
EXPLAIN
-> Filter: ((dt2.m + dt1.i) > 20)  (rows=24)
    -> Inner hash join (no condition)  (rows=24)
        -> Table scan on dt1  (rows=6)
            -> Materialize  (rows=6)
                -> Filter: ((t1.f1 > 1) and ((t1.f1 + t1.f2) > 40))  (rows=6)
                    -> Covering index range scan on t1 using f1_2 over (1 < f1)  (rows=6)
        -> Hash
            -> Table scan on dt2  (rows=4)
                -> Materialize  (rows=4)
                    -> Filter: (t1.f2 < 20)  (rows=4)
                        -> Table scan on t1  (rows=12)

SELECT i, j, l, m, n
FROM (SELECT f1 as i, f2 as j FROM t1 ) as dt1 ,
(SELECT f1 as l, f2 as m, f3 as n FROM t1) as dt2
WHERE i > 1 and i+j > 40 and m < 20 and m+i > 20;
i	j	l	m	n
EXPLAIN FORMAT=tree SELECT * FROM
(SELECT (i+j) AS g, f1 FROM
(SELECT (f1+f2) AS i, (f1-f2) AS j FROM
(SELECT f1+10 AS f1, f2+10 AS f2 FROM t1) AS dt0)
AS dt1,
(SELECT f1, f2 FROM t1) AS dt2) AS dt3 WHERE g > 26 and g+f1 > 31;
EXPLAIN
-> Table scan on dt3  (rows=144)
    -> Materialize  (rows=144)
        -> Filter: (((dt1.i + dt1.j) + dt2.f1) > 31)  (rows=144)
            -> Inner hash join (no condition)  (rows=144)
                -> Table scan on dt2  (rows=12)
                    -> Materialize  (rows=12)
                        -> Index scan on t1 using f1_2  (rows=12)
                -> Hash
                    -> Table scan on dt1  (rows=12)
                        -> Materialize  (rows=12)
                            -> Table scan on dt0  (rows=12)
                                -> Materialize  (rows=12)
                                    -> Filter: ((((t1.f1 + 10) + (t1.f2 + 10)) + ((t1.f1 + 10) - (t1.f2 + 10))) > 26)  (rows=12)
                                        -> Index scan on t1 using f1_2  (rows=12)

SELECT * FROM
(SELECT (i+j) AS g, f1 FROM
(SELECT (f1+f2) AS i, (f1-f2) AS j FROM
(SELECT f1+10 AS f1, f2+10 AS f2 FROM t1) AS dt0)
AS dt1,
(SELECT f1, f2 FROM t1) AS dt2) AS dt3 WHERE g > 26 and g+f1 > 31;
g	f1
28	4
28	4
28	4
28	4
EXPLAIN FORMAT=tree SELECT l, m  FROM (SELECT (i+3) as l, (j+4) as m FROM
(SELECT (f1+f2) as i, (f3+10) as j FROM t1) as
dt1) as dt2 WHERE l > 20 and l+m > 10 ;
EXPLAIN
-> Table scan on dt2  (rows=12)
    -> Materialize  (rows=12)
        -> Table scan on dt1  (rows=12)
            -> Materialize  (rows=12)
                -> Filter: ((((t1.f1 + t1.f2) + 3) > 20) and ((((t1.f1 + t1.f2) + 3) + ((t1.f3 + 10) + 4)) > 10))  (rows=12)
                    -> Table scan on t1  (rows=12)

SELECT l, m  FROM (SELECT (i+3) as l, (j+4) as m FROM
(SELECT (f1+f2) as i, (f3+10) as j FROM t1) as
dt1) as dt2 WHERE l > 20 and l+m > 10 ;
l	m
27	236
39	347
51	458
27	236
39	347
51	458
EXPLAIN FORMAT=tree SELECT i FROM (SELECT f1 FROM t1) as dt(i) WHERE i > 10;
EXPLAIN
-> Table scan on dt  (rows=1)
    -> Materialize  (rows=1)
        -> Filter: (t1.f1 > 10)  (rows=1)
            -> Covering index range scan on t1 using f1 over (10 < f1)  (rows=1)

SELECT i FROM (SELECT f1 FROM t1) as dt(i) WHERE i > 10;
i
EXPLAIN FORMAT=tree SELECT m FROM (SELECT k+2 as l
FROM (SELECT f1+f2 as j
FROM t1) as dt1(k)) as dt(m)
WHERE m > 30;
EXPLAIN
-> Table scan on dt  (rows=12)
    -> Materialize  (rows=12)
        -> Table scan on dt1  (rows=12)
            -> Materialize  (rows=12)
                -> Filter: (((t1.f1 + t1.f2) + 2) > 30)  (rows=12)
                    -> Index scan on t1 using f1_2  (rows=12)

SELECT m FROM (SELECT k+2 as l
FROM (SELECT f1+f2 as j
FROM t1) as dt1(k)) as dt(m)
WHERE m > 30;
m
38
38
50
50
EXPLAIN FORMAT=tree SELECT f1 FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1) as dt
WHERE f1 > 3;
EXPLAIN
-> Table scan on dt  (rows=1.41)
    -> Materialize  (rows=1.41)
        -> Group aggregate: sum(t1.f2)  (rows=1.41)
            -> Filter: (t1.f1 > 3)  (rows=2)
                -> Covering index range scan on t1 using f1_2 over (3 < f1)  (rows=2)

SELECT f1 FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1) as dt
WHERE f1 > 3;
f1
4
EXPLAIN FORMAT=tree SELECT f1 FROM (SELECT f1, f2, SUM(f3) FROM t1 GROUP BY f1,f2) as dt
WHERE f1+f2 > 30;
EXPLAIN
-> Table scan on dt  (rows=6)
    -> Materialize  (rows=6)
        -> Group aggregate: sum(t1.f3)  (rows=6)
            -> Filter: ((t1.f1 + t1.f2) > 30)  (rows=12)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT f1 FROM (SELECT f1, f2, SUM(f3) FROM t1 GROUP BY f1,f2) as dt
WHERE f1+f2 > 30;
f1
3
4
EXPLAIN FORMAT=tree SELECT f1
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1 WITH ROLLUP) as dt
WHERE f1 IS NULL;
EXPLAIN
-> Table scan on dt  (rows=7)
    -> Materialize  (rows=7)
        -> Filter: (rollup_group_item(t1.f1,0) is null)  (rows=7)
            -> Group aggregate with rollup: sum(t1.f2)  (rows=7)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT f1
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1 WITH ROLLUP) as dt
WHERE f1 IS NULL;
f1
NULL
NULL
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1 WITH ROLLUP) as dt WHERE j+sum > 50 OR
j IS NULL;
EXPLAIN
-> Table scan on dt  (rows=7)
    -> Materialize  (rows=7)
        -> Filter: (((rollup_group_item(t1.f1,0) + rollup_sum_switcher(sum(t1.f2))) > 50) or (rollup_group_item(t1.f1,0) is null))  (rows=7)
            -> Group aggregate with rollup: sum(t1.f2)  (rows=7)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT *
FROM (SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1 WITH ROLLUP) as dt WHERE j+sum > 50 OR
j IS NULL;
j	sum
NULL	2
3	66
4	88
NULL	222
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1 WITH ROLLUP
HAVING AVG(f2) > 1) AS dt WHERE j+sum > 50 OR
j IS NULL;
EXPLAIN
-> Table scan on dt  (rows=7)
    -> Materialize  (rows=7)
        -> Filter: ((rollup_sum_switcher(avg(t1.f2)) > 1) and (((rollup_group_item(t1.f1,0) + rollup_sum_switcher(sum(t1.f2))) > 50) or (rollup_group_item(t1.f1,0) is null)))  (rows=7)
            -> Group aggregate with rollup: avg(t1.f2), sum(t1.f2)  (rows=7)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT *
FROM (SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1 WITH ROLLUP
HAVING AVG(f2) > 1) AS dt WHERE j+sum > 50 OR
j IS NULL;
j	sum
3	66
4	88
NULL	222
EXPLAIN FORMAT=tree SELECT f1 FROM (SELECT f1, f2 FROM t1 ORDER BY f2) as dt
WHERE f1 > 3;
EXPLAIN
-> Table scan on dt  (rows=2)
    -> Materialize  (rows=2)
        -> Sort: t1.f2  (rows=2)
            -> Filter: (t1.f1 > 3)  (rows=2)
                -> Covering index range scan on t1 using f1_2 over (3 < f1)  (rows=2)

SELECT f1 FROM (SELECT f1, f2 FROM t1 ORDER BY f2) as dt
WHERE f1 > 3;
f1
4
4
EXPLAIN FORMAT=tree SELECT f1 FROM (SELECT f1, f2 FROM t1 ORDER BY f2 LIMIT 4) as dt
WHERE f1 > 0 ;
EXPLAIN
-> Filter: (dt.f1 > 0)  (rows=1.33)
    -> Table scan on dt  (rows=4)
        -> Materialize  (rows=4)
            -> Limit: 4 row(s)  (rows=4)
                -> Sort: t1.f2, limit input to 4 row(s) per chunk  (rows=12)
                    -> Index scan on t1 using f1_2  (rows=12)

SELECT f1 FROM (SELECT f1, f2 FROM t1 ORDER BY f2 LIMIT 4) as dt
WHERE f1 > 0 ;
f1
EXPLAIN FORMAT=tree SELECT f1 FROM (SELECT f1, f2 FROM t1 LIMIT 4) as dt WHERE f1 > 0;
EXPLAIN
-> Filter: (dt.f1 > 0)  (rows=1.33)
    -> Table scan on dt  (rows=4)
        -> Materialize  (rows=4)
            -> Limit: 4 row(s)  (rows=4)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT f1 FROM (SELECT f1, f2 FROM t1 LIMIT 4) as dt WHERE f1 > 0;
f1
set sql_mode="";
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, SUM(f2) OVER() FROM t1 GROUP BY f1) as dt
WHERE f1 > 2;
EXPLAIN
-> Filter: (dt.f1 > 2)  (rows=2.33)
    -> Table scan on dt  (rows=0)
        -> Materialize  (rows=0)
            -> Window aggregate with buffering: sum(t1.f2) OVER () 
                -> Table scan on <temporary>  (rows=7)
                    -> Temporary table with deduplication  (rows=7)
                        -> Covering index skip scan for deduplication on t1 using f1_2  (rows=7)

SELECT * FROM (SELECT f1, SUM(f2) OVER() FROM t1 GROUP BY f1) as dt
WHERE f1 > 2;
f1	SUM(f2) OVER()
3	111
4	111
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) OVER(PARTITION BY f1)
FROM t1 GROUP BY f1) as dt WHERE f1 > 2;
EXPLAIN
-> Table scan on dt  (rows=0)
    -> Materialize  (rows=0)
        -> Window aggregate with buffering: sum(t1.f2) OVER (PARTITION BY t1.f1 ) 
            -> Sort: t1.f1
                -> Table scan on <temporary>  (rows=4)
                    -> Temporary table with deduplication  (rows=4)
                        -> Filter: (t1.f1 > 2)  (rows=4)
                            -> Covering index range scan on t1 using f1_2 over (2 < f1)  (rows=4)

SELECT *
FROM (SELECT f1, SUM(f2) OVER(PARTITION BY f1)
FROM t1 GROUP BY f1) as dt WHERE f1 > 2;
f1	SUM(f2) OVER(PARTITION BY f1)
3	33
4	44
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f2)
FROM t1 GROUP BY f1) as dt WHERE f2 > 30;
EXPLAIN
-> Table scan on dt  (rows=0)
    -> Materialize  (rows=0)
        -> Window aggregate with buffering: sum(t1.f3) OVER (PARTITION BY t1.f2 ) 
            -> Filter: (t1.f2 > 30)
                -> Sort: t1.f2
                    -> Table scan on <temporary>  (rows=12)
                        -> Temporary table with deduplication  (rows=12)
                            -> Table scan on t1  (rows=12)

SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f2)
FROM t1 GROUP BY f1) as dt WHERE f2 > 30;
f1	f2	SUM(f3) OVER(PARTITION BY f2)
3	33	333
4	44	444
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1)
FROM t1 GROUP BY f1) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
EXPLAIN
-> Filter: ((dt.f2 > 30) and ((dt.f1 + dt.f2) > 40))  (rows=1.33)
    -> Table scan on dt  (rows=0)
        -> Materialize  (rows=0)
            -> Window aggregate with buffering: sum(t1.f3) OVER (PARTITION BY t1.f1 ) 
                -> Sort: t1.f1
                    -> Table scan on <temporary>  (rows=4)
                        -> Temporary table with deduplication  (rows=4)
                            -> Index range scan on t1 using f1 over (2 < f1), with index condition: (t1.f1 > 2)  (rows=4)

SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1)
FROM t1 GROUP BY f1) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
f1	f2	SUM(f3) OVER(PARTITION BY f1)
4	44	444
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1,f2)
FROM t1 GROUP BY f1) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
EXPLAIN
-> Table scan on dt  (rows=0)
    -> Materialize  (rows=0)
        -> Window aggregate with buffering: sum(t1.f3) OVER (PARTITION BY t1.f1,t1.f2 ) 
            -> Filter: ((t1.f2 > 30) and ((t1.f1 + t1.f2) > 40))
                -> Sort: t1.f1, t1.f2
                    -> Table scan on <temporary>  (rows=4)
                        -> Temporary table with deduplication  (rows=4)
                            -> Index range scan on t1 using f1 over (2 < f1), with index condition: (t1.f1 > 2)  (rows=4)

SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1,f2)
FROM t1 GROUP BY f1) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
f1	f2	SUM(f3) OVER(PARTITION BY f1,f2)
4	44	444
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1,f2),
AVG(f3) OVER (PARTITION BY f1)
FROM t1 GROUP BY f1) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
EXPLAIN
-> Filter: ((dt.f2 > 30) and ((dt.f1 + dt.f2) > 40))  (rows=1.33)
    -> Table scan on dt  (rows=0)
        -> Materialize  (rows=0)
            -> Window aggregate with buffering: avg(t1.f3) OVER (PARTITION BY t1.f1 ) 
                -> Sort: t1.f1
                    -> Table scan on <temporary>  (rows=0)
                        -> Temporary table  (rows=0)
                            -> Window aggregate with buffering: sum(t1.f3) OVER (PARTITION BY t1.f1,t1.f2 ) 
                                -> Sort: t1.f1, t1.f2
                                    -> Table scan on <temporary>  (rows=4)
                                        -> Temporary table with deduplication  (rows=4)
                                            -> Index range scan on t1 using f1 over (2 < f1), with index condition: (t1.f1 > 2)  (rows=4)

SELECT *
FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1,f2),
AVG(f3) OVER (PARTITION BY f1)
FROM t1 GROUP BY f1) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
f1	f2	SUM(f3) OVER(PARTITION BY f1,f2)	AVG(f3) OVER (PARTITION BY f1)
4	44	444	444.0000
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) as SUM, AVG(f3) OVER (PARTITION BY SUM(f2))
FROM t1 GROUP BY f1) as dt
WHERE SUM > 40;
EXPLAIN
-> Table scan on dt  (rows=0)
    -> Materialize  (rows=0)
        -> Window aggregate with buffering: avg(t1.f3) OVER (PARTITION BY sum(t1.f2) ) 
            -> Filter: (sum(t1.f2) > 40)
                -> Sort: SUM
                    -> Table scan on <temporary>
                        -> Aggregate using temporary table
                            -> Table scan on t1  (rows=12)

SELECT *
FROM (SELECT f1, SUM(f2) as SUM, AVG(f3) OVER (PARTITION BY SUM(f2))
FROM t1 GROUP BY f1) as dt
WHERE SUM > 40;
f1	SUM	AVG(f3) OVER (PARTITION BY SUM(f2))
2	44	222.0000
3	66	333.0000
4	88	444.0000
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) OVER (PARTITION by f1,f2),
AVG(f3) OVER (PARTITION BY f2,f1),
FIRST_VALUE(f3) OVER (PARTITION by f1)
FROM t1) as dt
WHERE f1 > 2 ;
EXPLAIN
-> Table scan on dt  (rows=0)
    -> Materialize  (rows=0)
        -> Window aggregate with buffering: first_value(t1.f3) OVER (PARTITION BY t1.f1 ) 
            -> Sort: t1.f1
                -> Table scan on <temporary>  (rows=0)
                    -> Temporary table  (rows=0)
                        -> Window aggregate with buffering: avg(t1.f3) OVER (PARTITION BY t1.f2,t1.f1 ) 
                            -> Sort: t1.f2, t1.f1
                                -> Table scan on <temporary>  (rows=0)
                                    -> Temporary table  (rows=0)
                                        -> Window aggregate with buffering: sum(t1.f2) OVER (PARTITION BY t1.f1,t1.f2 ) 
                                            -> Sort: t1.f1, t1.f2  (rows=4)
                                                -> Index range scan on t1 using f1 over (2 < f1), with index condition: (t1.f1 > 2)  (rows=4)

SELECT *
FROM (SELECT f1, SUM(f2) OVER (PARTITION by f1,f2),
AVG(f3) OVER (PARTITION BY f2,f1),
FIRST_VALUE(f3) OVER (PARTITION by f1)
FROM t1) as dt
WHERE f1 > 2 ;
f1	SUM(f2) OVER (PARTITION by f1,f2)	AVG(f3) OVER (PARTITION BY f2,f1)	FIRST_VALUE(f3) OVER (PARTITION by f1)
3	66	333.0000	333
3	66	333.0000	333
4	88	444.0000	444
4	88	444.0000	444
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f1) OVER (PARTITION by f2),
AVG(f2) OVER (PARTITION BY f1)
FROM t1) as dt
WHERE f1 > 2 ;
EXPLAIN
-> Filter: (dt.f1 > 2)  (rows=4)
    -> Table scan on dt  (rows=0)
        -> Materialize  (rows=0)
            -> Window aggregate with buffering: avg(t1.f2) OVER (PARTITION BY t1.f1 ) 
                -> Sort: t1.f1
                    -> Table scan on <temporary>  (rows=0)
                        -> Temporary table  (rows=0)
                            -> Window aggregate with buffering: sum(t1.f1) OVER (PARTITION BY t1.f2 ) 
                                -> Sort: t1.f2  (rows=12)
                                    -> Index scan on t1 using f1_2  (rows=12)

SELECT *
FROM (SELECT f1, SUM(f1) OVER (PARTITION by f2),
AVG(f2) OVER (PARTITION BY f1)
FROM t1) as dt
WHERE f1 > 2 ;
f1	SUM(f1) OVER (PARTITION by f2)	AVG(f2) OVER (PARTITION BY f1)
3	6	33.0000
3	6	33.0000
4	8	44.0000
4	8	44.0000
set sql_mode=default;
SET @p1 = 3;
PREPARE p FROM "SELECT f1
FROM (SELECT f1 FROM t1) as dt WHERE f1 > ?";
EXECUTE p USING @p1;
f1
4
4
PREPARE p FROM "EXPLAIN FORMAT=tree SELECT f1
FROM (SELECT f1 FROM t1) as dt WHERE f1 > ?";
EXECUTE p USING @p1;
EXPLAIN
-> Table scan on dt  (rows=2)
    -> Materialize  (rows=2)
        -> Filter: (t1.f1 > 3)  (rows=2)
            -> Covering index range scan on t1 using f1 over (3 < f1)  (rows=2)

DROP PREPARE p;
SET @p1 = 1;
PREPARE p FROM "EXPLAIN FORMAT=tree SELECT l, m
FROM (SELECT (i+3) as l, (j+4) as m
FROM (SELECT (f1+f2) AS i, (f3+?) AS j
FROM t1) AS dt1
) AS dt2
WHERE l > 20 and l+m > 10 ";
EXECUTE p USING @p1;
EXPLAIN
-> Table scan on dt2  (rows=12)
    -> Materialize  (rows=12)
        -> Table scan on dt1  (rows=12)
            -> Materialize  (rows=12)
                -> Filter: ((((t1.f1 + t1.f2) + 3) > 20) and ((((t1.f1 + t1.f2) + 3) + ((t1.f3 + 1) + 4)) > 10))  (rows=12)
                    -> Table scan on t1  (rows=12)

PREPARE p FROM "SELECT l, m
FROM (SELECT (i+3) as l, (j+4) as m
FROM (SELECT (f1+f2) AS i, (f3+?) AS j
FROM t1) AS dt1
) AS dt2
WHERE l > 20 and l+m > 10 ";
EXECUTE p USING @p1;
l	m
27	227
39	338
51	449
27	227
39	338
51	449
EXPLAIN FORMAT=tree SELECT * FROM (SELECT RAND() as a FROM t1) as dt
WHERE a > 0.5;
EXPLAIN
-> Filter: (dt.a > 0.5)  (rows=4)
    -> Table scan on dt  (rows=12)
        -> Materialize  (rows=12)
            -> Index scan on t1 using f1  (rows=12)

EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1) as dt
WHERE f1 > 3*RAND() AND f1 < 10;
EXPLAIN
-> Table scan on dt  (rows=3.16)
    -> Materialize  (rows=3.16)
        -> Filter: (t1.f1 > (3 * rand()))  (rows=3.16)
            -> Group aggregate: sum(t1.f2)  (rows=3.16)
                -> Filter: (t1.f1 < 10)  (rows=10)
                    -> Covering index range scan on t1 using f1_2 over (NULL < f1 < 10)  (rows=10)

EXPLAIN FORMAT=tree SELECT * FROM
(SELECT f1, SUM(f2) OVER(PARTITION BY f1) FROM t1) as dt
WHERE f1 > 3*RAND() AND f1 < 10;
EXPLAIN
-> Filter: (dt.f1 > (3 * rand()))  (rows=3.33)
    -> Table scan on dt  (rows=0)
        -> Materialize  (rows=0)
            -> Window aggregate with buffering: sum(t1.f2) OVER (PARTITION BY t1.f1 ) 
                -> Sort: t1.f1  (rows=10)
                    -> Filter: (t1.f1 < 10)  (rows=10)
                        -> Covering index range scan on t1 using f1_2 over (NULL < f1 < 10)  (rows=10)

EXPLAIN FORMAT=tree SELECT * FROM
(SELECT f1 FROM t1) as dt
WHERE f1 > 3*RAND() AND f1<10;
EXPLAIN
-> Table scan on dt  (rows=10)
    -> Materialize  (rows=10)
        -> Filter: ((t1.f1 > (3 * rand())) and (t1.f1 < 10))  (rows=10)
            -> Covering index range scan on t1 using f1 over (NULL < f1 < 10)  (rows=10)

EXPLAIN FORMAT=tree SELECT f1 FROM
(SELECT (SELECT 1 FROM t1 LIMIT 1) as f1 FROM t1) as dt WHERE f1 = 1;
EXPLAIN
-> Covering index lookup on dt using <auto_key0> (f1=1)  (rows=1.2)
    -> Materialize  (rows=12)
        -> Index scan on t1 using f1  (rows=12)
        -> Select #3 (subquery in projection; run only once)
            -> Limit: 1 row(s)  (rows=1)
                -> Index scan on t1 using f1  (rows=12)

SELECT f1 FROM
(SELECT (SELECT 1 FROM t1 LIMIT 1) as f1 FROM t1) as dt WHERE f1 = 1;
f1
1
1
1
1
1
1
1
1
1
1
1
1
CREATE FUNCTION p() RETURNS INTEGER
BEGIN
DECLARE retn INTEGER;
SELECT count(f1) FROM t1 INTO retn;
RETURN retn;
END|
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1) as dt
WHERE p() = 1;
EXPLAIN
-> Filter: (p() = 1)  (rows=12)
    -> Table scan on dt  (rows=6)
        -> Materialize  (rows=6)
            -> Group aggregate: sum(t1.f2)  (rows=6)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT * FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1) as dt
WHERE p() = 1;
f1	SUM(f2)
DROP FUNCTION p;
CREATE FUNCTION p() RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE retn INTEGER;
SELECT count(f1) FROM t1 INTO retn;
RETURN retn;
END|
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1) as dt
WHERE p() = 1;
EXPLAIN
-> Table scan on dt  (rows=6)
    -> Materialize  (rows=6)
        -> Group aggregate: sum(t1.f2)  (rows=6)
            -> Filter: <cache>((p() = 1))  (rows=12)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT * FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1) as dt
WHERE p() = 1;
f1	SUM(f2)
DROP FUNCTION p;
CREATE PROCEDURE p()
BEGIN
DECLARE val INTEGER;
SET val = 2;
EXPLAIN FORMAT=tree SELECT AVG(f1) FROM (SELECT * FROM t1) as dt
WHERE f2 > val;
SELECT AVG(f1) FROM (SELECT * FROM t1) as dt WHERE f2 > val;
END|
CALL p();
EXPLAIN
-> Aggregate: avg(dt.f1)  (rows=1)
    -> Table scan on dt  (rows=4)
        -> Materialize  (rows=4)
            -> Filter: (t1.f2 > <cache>(val@0))  (rows=4)
                -> Table scan on t1  (rows=12)

AVG(f1)
2.5000
DROP PROCEDURE p;
EXPLAIN FORMAT=tree SELECT * FROM ((WITH qn AS (SELECT 10*f1 as f1 FROM t1),
qn2 AS (SELECT 3*f1 AS f2 FROM qn)
SELECT * from qn2)) as dt WHERE f2 > 1;
EXPLAIN
-> Table scan on dt  (rows=12)
    -> Materialize  (rows=12)
        -> Table scan on qn2  (rows=12)
            -> Materialize CTE qn2  (rows=12)
                -> Table scan on qn  (rows=12)
                    -> Materialize CTE qn  (rows=12)
                        -> Filter: ((3 * (10 * t1.f1)) > 1)  (rows=12)
                            -> Index scan on t1 using f1  (rows=12)

SELECT * FROM ((WITH qn AS (SELECT 10*f1 as f1 FROM t1),
qn2 AS (SELECT 3*f1 AS f2 FROM qn)
SELECT * from qn2)) as dt WHERE f2 > 1;
f2
120
120
30
30
60
60
90
90
EXPLAIN FORMAT=tree SELECT * FROM ((WITH qn AS (SELECT 10*f1 as f1 FROM t1),
qn2 AS (SELECT 3*f1 AS f2 FROM qn)
SELECT * from qn,qn2)) as dt WHERE f1 < 10 and f2 > 1;
EXPLAIN
-> Table scan on dt  (rows=48)
    -> Materialize  (rows=48)
        -> Inner hash join (no condition)  (rows=48)
            -> Table scan on qn2  (rows=12)
                -> Materialize CTE qn2  (rows=12)
                    -> Filter: ((3 * qn.f1) > 1)  (rows=12)
                        -> Table scan on qn  (rows=12)
                            -> Materialize CTE qn if needed (query plan printed elsewhere)  (rows=12)
            -> Hash
                -> Filter: (qn.f1 < 10)  (rows=4)
                    -> Table scan on qn  (rows=12)
                        -> Materialize CTE qn if needed  (rows=12)
                            -> Index scan on t1 using f1  (rows=12)

SELECT * FROM ((WITH qn AS (SELECT 10*f1 as f1 FROM t1),
qn2 AS (SELECT 3*f1 AS f2 FROM qn)
SELECT * from qn,qn2)) as dt WHERE f1 < 10 and f2 > 1;
f1	f2
0	120
0	120
0	120
0	120
0	30
0	30
0	30
0	30
0	60
0	60
0	60
0	60
0	90
0	90
0	90
0	90
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN (SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 31 and t1.f2  > 40;
EXPLAIN
-> Nested loop inner join  (rows=4)
    -> Filter: ((t1.f2 > 40) and (t1.f2 is not null))  (rows=4)
        -> Table scan on t1  (rows=12)
    -> Index lookup on dt using <auto_key0> (f2=t1.f2)  (rows=2)
        -> Materialize  (rows=1)
            -> Filter: (t1.f1 > 31)  (rows=1)
                -> Covering index range scan on t1 using f1_2 over (31 < f1)  (rows=1)

SELECT * FROM t1 JOIN (SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 31 and t1.f2  > 40;
f2	f1	f3	f1
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN (SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 is null;
EXPLAIN
-> Filter: (dt.f1 is null)  (rows=14.4)
    -> Left hash join (dt.f2 = t1.f2)  (rows=14.4)
        -> Table scan on t1  (rows=12)
        -> Hash
            -> Table scan on dt  (rows=12)
                -> Materialize  (rows=12)
                    -> Index scan on t1 using f1_2  (rows=12)

SELECT * FROM t1 LEFT JOIN (SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 is null;
f2	f1	f3	f1
1	NULL	2	NULL
1	NULL	2	NULL
1	NULL	3	NULL
1	NULL	3	NULL
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN (SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 3;
EXPLAIN
-> Inner hash join (t1.f2 = dt.f2)  (rows=2.4)
    -> Table scan on t1  (rows=12)
    -> Hash
        -> Table scan on dt  (rows=2)
            -> Materialize  (rows=2)
                -> Filter: (t1.f1 > 3)  (rows=2)
                    -> Covering index range scan on t1 using f1_2 over (3 < f1)  (rows=2)

SELECT * FROM t1 LEFT JOIN (SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 3;
f2	f1	f3	f1
44	4	444	4
44	4	444	4
44	4	444	4
44	4	444	4
EXPLAIN FORMAT=tree SELECT * FROM t1 INNER JOIN (SELECT f1, f2 FROM t1) as dt
ON dt.f1 > 3;
EXPLAIN
-> Inner hash join (no condition)  (rows=24)
    -> Table scan on t1  (rows=12)
    -> Hash
        -> Table scan on dt  (rows=2)
            -> Materialize  (rows=2)
                -> Filter: (t1.f1 > 3)  (rows=2)
                    -> Covering index range scan on t1 using f1_2 over (3 < f1)  (rows=2)

SELECT * FROM t1 INNER JOIN (SELECT f1, f2 FROM t1) as dt
ON dt.f1 > 3;
f1	f2	f3	f1	f2
0	0	0	4	44
0	0	0	4	44
0	0	0	4	44
0	0	0	4	44
1	11	111	4	44
1	11	111	4	44
1	11	111	4	44
1	11	111	4	44
2	22	222	4	44
2	22	222	4	44
2	22	222	4	44
2	22	222	4	44
3	33	333	4	44
3	33	333	4	44
3	33	333	4	44
3	33	333	4	44
4	44	444	4	44
4	44	444	4	44
4	44	444	4	44
4	44	444	4	44
NULL	1	2	4	44
NULL	1	2	4	44
NULL	1	3	4	44
NULL	1	3	4	44
EXPLAIN FORMAT = tree SELECT * FROM t1 INNER JOIN(SELECT t2.f1, t2.f2 FROM t1
LEFT JOIN t1 AS t2 ON TRUE) AS dt ON dt.f1 > 3;
EXPLAIN
-> Inner hash join (no condition)  (rows=576)
    -> Table scan on dt  (rows=48)
        -> Materialize  (rows=48)
            -> Filter: (t2.f1 > 3)  (rows=48)
                -> Left hash join (no condition)  (rows=48)
                    -> Index scan on t1 using f1  (rows=12)
                    -> Hash
                        -> Index scan on t2 using f1_2  (rows=12)
    -> Hash
        -> Table scan on t1  (rows=12)

set optimizer_switch="derived_merge=on";
EXPLAIN FORMAT=tree SELECT * FROM (SELECT * FROM (SELECT f1, SUM(f2) AS sum
FROM t1 GROUP BY f1) as dt1
WHERE f1 > 10) dt2 WHERE sum > 10;
EXPLAIN
-> Table scan on dt1  (rows=1)
    -> Materialize  (rows=1)
        -> Filter: (sum(t1.f2) > 10)  (rows=1)
            -> Group aggregate: sum(t1.f2)  (rows=1)
                -> Filter: (t1.f1 > 10)  (rows=1)
                    -> Covering index range scan on t1 using f1_2 over (10 < f1)  (rows=1)

SELECT * FROM (SELECT * FROM (SELECT f1, SUM(f2) AS sum
FROM t1 GROUP BY f1) as dt1
WHERE f1 > 10) dt2 WHERE sum > 10;
f1	sum
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, SUM(f2) AS sum
FROM (SELECT f1, f2 FROM t1 WHERE f1 > 10) as dt1
GROUP BY f1) dt2 WHERE sum > 10;
EXPLAIN
-> Table scan on dt2  (rows=1)
    -> Materialize  (rows=1)
        -> Filter: (sum(t1.f2) > 10)  (rows=1)
            -> Group aggregate: sum(t1.f2)  (rows=1)
                -> Filter: (t1.f1 > 10)  (rows=1)
                    -> Covering index range scan on t1 using f1_2 over (10 < f1)  (rows=1)

SELECT * FROM (SELECT f1, SUM(f2) AS sum
FROM (SELECT f1, f2 FROM t1 WHERE f1 > 10) as dt1
GROUP BY f1) dt2 WHERE sum > 10;
f1	sum
SELECT * FROM
(SELECT f1 FROM (SELECT f1 FROM t1) AS dt1 GROUP BY f1) AS dt2
WHERE f1 > 3;
f1
4
SELECT * FROM
(SELECT dt1.f1 FROM (SELECT f1 FROM t1) AS dt1, t1 AS t0
GROUP BY dt1.f1) AS dt2
WHERE dt2.f1 > 3;
f1
4
EXPLAIN SELECT /*+ no_merge(dt,dt1) */ * FROM
((SELECT f1, f2 FROM t1) as dt, (SELECT f1, f2 FROM t1) as dt1) WHERE FALSE;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
3	DERIVED	t1	NULL	index	NULL	f1_2	10	NULL	#	100.00	Using index
2	DERIVED	t1	NULL	index	NULL	f1_2	10	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select /*+ NO_MERGE(`dt`@`select#1`) NO_MERGE(`dt1`@`select#1`) */ `dt`.`f1` AS `f1`,`dt`.`f2` AS `f2`,`dt1`.`f1` AS `f1`,`dt1`.`f2` AS `f2` from (/* select#2 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1`) `dt` join (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1`) `dt1` where false
SELECT /*+ no_merge(dt,dt1) */ * FROM
((SELECT f1, f2 FROM t1) as dt, (SELECT f1, f2 FROM t1) as dt1) WHERE FALSE;
f1	f2	f1	f2
set optimizer_switch="derived_merge=off";
EXPLAIN FORMAT=tree SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt2) */ * FROM
(SELECT * FROM (SELECT * FROM t1) as dt1) as dt2 WHERE f1 > 3;
EXPLAIN
-> Filter: (dt2.f1 > 3)  (rows=4)
    -> Table scan on dt2  (rows=12)
        -> Materialize  (rows=12)
            -> Table scan on dt1  (rows=12)
                -> Materialize  (rows=12)
                    -> Table scan on t1  (rows=12)

SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt2) */ * FROM
(SELECT * FROM (SELECT * FROM t1) as dt1) as dt2 WHERE f1 > 3;
f1	f2	f3
4	44	444
4	44	444
EXPLAIN FORMAT=tree SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM
(SELECT * FROM (SELECT * FROM t1) as dt1) as dt2 WHERE f1 > 3;
EXPLAIN
-> Filter: (dt2.f1 > 3)  (rows=4)
    -> Table scan on dt2  (rows=12)
        -> Materialize  (rows=12)
            -> Table scan on dt1  (rows=12)
                -> Materialize  (rows=12)
                    -> Table scan on t1  (rows=12)

SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM
(SELECT * FROM (SELECT * FROM t1) as dt1) as dt2 WHERE f1 > 3;
f1	f2	f3
4	44	444
4	44	444
set optimizer_switch="derived_condition_pushdown=off";
EXPLAIN FORMAT=tree SELECT /*+ DERIVED_CONDITION_PUSHDOWN(dt2) */ * FROM
(SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt1) */ * FROM
(SELECT * FROM t1) as dt1) as dt2 WHERE f1 > 3;
EXPLAIN
-> Table scan on dt2  (rows=4)
    -> Materialize  (rows=4)
        -> Filter: (dt1.f1 > 3)  (rows=4)
            -> Table scan on dt1  (rows=12)
                -> Materialize  (rows=12)
                    -> Table scan on t1  (rows=12)

SELECT /*+ DERIVED_CONDITION_PUSHDOWN(dt2) */ * FROM
(SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt1) */ * FROM
(SELECT * FROM t1) as dt1) as dt2 WHERE f1 > 3;
f1	f2	f3
4	44	444
4	44	444
set optimizer_switch=default;
# Bug#31491972: WL8084: SERVER CRASH FROM
#               JOIN::UPDATE_SARGABLE_FROM_CONST()
SELECT f1 FROM (SELECT DISTINCT * FROM t1 WHERE f2 = 4) AS alias1
WHERE ( alias1 . f1 = 24 AND alias1 . f3 = 101 );
f1
SELECT f1 FROM (SELECT DISTINCT * FROM t1 WHERE f2 = 4) AS alias1
WHERE ( alias1 . f1 BETWEEN 24 AND 30 AND alias1 . f3 BETWEEN 101 and 103);
f1
DROP TABLE t0, t1;
#
# Bug#31603289:CRASH IN TABLE_LIST::GET_CLONE_FOR_DERIVED_EXPR,
#              ASSERTION `FALSE' IN TABLE_LIST::GET_DERIVED_EXPR
#
CREATE TABLE t(f0 INTEGER PRIMARY KEY, f1 INTEGER,f2 INTEGER);
SELECT NULL IN(SELECT (f1 between 0 and 1)
FROM (SELECT f1 FROM t WHERE  (@b:=NULL) - f2)as dt
);
NULL IN(SELECT (f1 between 0 and 1)
)
0
FROM (SELECT f1 FROM t WHERE  (@b:=NULL) - f2)as dt
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warnings:
DROP TABLE t;
#
# Bug#32127562:ERROR 3566 (HY000): ACCESS TO NATIVE FUNCTION IS
#              REJECTED
#
set optimizer_switch="derived_merge=off";
SELECT 1 FROM information_schema.tables WHERE 12 IN (CONCAT_WS(TABLE_ROWS, ''));
1
set optimizer_switch="derived_merge=on";
# BUG#32150145: 8.0.22+ RELEASE BUILD QUERY FAILS SILENTLY,
#                DEBUG ASSERTION `THD->IS_ERROR()'
#
CREATE TABLE t1(g INTEGER);
SELECT w.g FROM t1 INNER JOIN (
SELECT g, ROW_NUMBER() OVER (PARTITION BY g) AS r FROM t1
) w ON w.g=t1.g AND w.r=1 WHERE w.g IS NULL;
g
DROP TABLE t1;
#
# BUG#32863713: CTE CRASH IN QUERY_BLOCK::MASTER_QUERY_EXPRESSION
#
CREATE TABLE t(f1 INTEGER);
EXPLAIN SELECT a1, a2
FROM (SELECT MAX(2) AS a1 FROM t) as dt1,
(SELECT @a AS a2 FROM t) as dt2
WHERE dt1.a1 <= dt2.a2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	DERIVED	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select NULL AS `a1`,(@`a`) AS `a2` from (/* select#2 */ select max(2) AS `a1` from `test`.`t` having (max(2) <= <cache>((@`a`)))) `dt1` join `test`.`t`
SELECT a1, a2
FROM (SELECT MAX(f1) AS a1 FROM t) as dt1,
(SELECT @a AS a2 FROM t) as dt2
WHERE dt1.a1 <= dt2.a2;
a1	a2
DROP TABLE t;
#
# Bug#32905044: CRASH AT CONDITION_PUSHDOWN::REPLACE_COLUMNS_IN_COND
#	       DURIN RQG CONCURRENCY RUNS
CREATE TABLE t(f1 INTEGER);
CREATE ALGORITHM=temptable VIEW v AS SELECT f1 FROM t;
SELECT f1 FROM (SELECT f1 FROM v) AS dt1 NATURAL JOIN v dt2 WHERE f1 > 5;
f1
DROP TABLE t;
DROP VIEW v;
#
# Bug#32959186: DERIVED CONDITION PUSHDOWN IS NOT AVAILABLE FOR
#               INSERT ... SELECT QUERIES
CREATE TABLE t1(f1 INTEGER, KEY(f1));
CREATE TABLE t2(f1 INTEGER);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
CREATE ALGORITHM=temptable VIEW v AS SELECT f1 FROM t1;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN format=tree INSERT INTO t2 SELECT * FROM v WHERE f1=2;
EXPLAIN
-> Insert into t2
    -> Table scan on v  (rows=1)
        -> Materialize  (rows=1)
            -> Covering index lookup on t1 using f1 (f1=2)  (rows=1)

INSERT INTO t2 SELECT * FROM v WHERE f1=2;
EXPLAIN format=tree UPDATE t2 SET f1=3 WHERE f1 IN (SELECT f1 FROM v WHERE f1=2);
EXPLAIN
-> Update t2 (immediate)
    -> Nested loop semijoin  (rows=1)
        -> Filter: (t2.f1 is not null)  (rows=1)
            -> Table scan on t2  (rows=1)
        -> Covering index lookup on v using <auto_key0> (f1=t2.f1)  (rows=2)
            -> Materialize  (rows=1)
                -> Covering index lookup on t1 using f1 (f1=2)  (rows=1)

UPDATE t2 SET f1=3 WHERE f1 IN (SELECT f1 FROM v WHERE f1=2);
EXPLAIN format=tree DELETE FROM t2 WHERE f1 IN (SELECT f1 FROM v WHERE f1=3);
EXPLAIN
-> Delete from t2 (immediate)  (rows=1)
    -> Nested loop semijoin  (rows=1)
        -> Filter: (t2.f1 is not null)  (rows=1)
            -> Table scan on t2  (rows=1)
        -> Covering index lookup on v using <auto_key0> (f1=t2.f1)  (rows=2)
            -> Materialize  (rows=1)
                -> Covering index lookup on t1 using f1 (f1=3)  (rows=1)

DELETE FROM t2 WHERE f1 IN (SELECT f1 FROM v WHERE f1=3);
DROP TABLE t1;
DROP TABLE t2;
DROp VIEW v;
#
# Bug#33341080: Derived condition pushdown rewrite ignores user variables
#
CREATE TABLE t1(f1 INTEGER);
INSERT INTO t1 VALUES (100),(200),(300),(400),(500);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET @rownum_r=0;
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, (@rownum_r := @rownum_r + 1) AS r FROM t1) AS dt
WHERE dt.f1 = 300;
EXPLAIN
-> Index lookup on dt using <auto_key0> (f1=300)  (rows=1)
    -> Materialize  (rows=5)
        -> Table scan on t1  (rows=5)

Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
SET @rownum_r=0;
SELECT *
FROM (SELECT f1, (@rownum_r := @rownum_r + 1) AS r FROM t1) AS dt
WHERE dt.f1 = 300;
f1	r
300	3
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
DROP TABLE t1;
CREATE TABLE t0 (
i0 INTEGER
);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
KEY(f1), KEY(f1,f2), KEY(f3));
INSERT INTO t1
SELECT i0, i0 + 10*i0,
i0 + 10*i0 + 100*i0
FROM t0 AS a0;
INSERT INTO t1
SELECT i0, i0 + 10*i0,
i0 + 10*i0 + 100*i0
FROM t0 AS a0;
INSERT INTO t1 VALUES (NULL, 1, 2);
INSERT INTO t1 VALUES (NULL, 1, 3);
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;
ANALYZE TABLE t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2 FROM t1
UNION
SELECT f1, f2 FROM t2) as dt
WHERE f1 > 2;
EXPLAIN
-> Table scan on dt  (rows=8)
    -> Union materialize with deduplication  (rows=8)
        -> Filter: (t1.f1 > 2)  (rows=4)
            -> Covering index range scan on t1 using f1_2 over (2 < f1)  (rows=4)
        -> Filter: (t2.f1 > 2)  (rows=4)
            -> Covering index range scan on t2 using f1_2 over (2 < f1)  (rows=4)

SELECT *
FROM (SELECT f1, f2 FROM t1
UNION
SELECT f1, f2 FROM t2) as dt
WHERE f1 > 2;
f1	f2
3	33
4	44
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2 FROM t1
INTERSECT
SELECT f1, f2 FROM t2) as dt
WHERE f1 > 2;
EXPLAIN
-> Table scan on dt  (rows=4)
    -> Intersect materialize with deduplication  (rows=4)
        -> Filter: (t1.f1 > 2)  (rows=4)
            -> Covering index range scan on t1 using f1_2 over (2 < f1)  (rows=4)
        -> Filter: (t2.f1 > 2)  (rows=4)
            -> Covering index range scan on t2 using f1_2 over (2 < f1)  (rows=4)

SELECT *
FROM (SELECT f1, f2 FROM t1
INTERSECT
SELECT f1, f2 FROM t2) as dt
WHERE f1 > 2;
f1	f2
3	33
4	44
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2 FROM t1
EXCEPT
SELECT f1, f2 FROM t2) as dt
WHERE f1 > 2;
EXPLAIN
-> Table scan on dt  (rows=4)
    -> Except materialize with deduplication  (rows=4)
        -> Filter: (t1.f1 > 2)  (rows=4)
            -> Covering index range scan on t1 using f1_2 over (2 < f1)  (rows=4)
        -> Filter: (t2.f1 > 2)  (rows=4)
            -> Covering index range scan on t2 using f1_2 over (2 < f1)  (rows=4)

SELECT *
FROM (SELECT f1, f2 FROM t1
EXCEPT
SELECT f1, f2 FROM t2) as dt
WHERE f1 > 2;
f1	f2
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT * FROM (SELECT * FROM t1
UNION
SELECT * FROM t1) as dt1
UNION
SELECT * FROM t2) as dt2
WHERE (f1+f2) > 22;
EXPLAIN
-> Table scan on dt2  (rows=36)
    -> Union materialize with deduplication  (rows=36)
        -> Table scan on dt1  (rows=24)
            -> Union materialize with deduplication  (rows=24)
                -> Filter: ((t1.f1 + t1.f2) > 22)  (rows=12)
                    -> Table scan on t1  (rows=12)
                -> Filter: ((t1.f1 + t1.f2) > 22)  (rows=12)
                    -> Table scan on t1  (rows=12)
        -> Filter: ((t2.f1 + t2.f2) > 22)  (rows=12)
            -> Table scan on t2  (rows=12)

SELECT *
FROM (SELECT * FROM (SELECT * FROM t1
UNION
SELECT * FROM t1) as dt1
UNION
SELECT * FROM t2) as dt2
WHERE (f1+f2) > 22;
f1	f2	f3
2	22	222
3	33	333
4	44	444
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT * FROM (SELECT * FROM t1
INTERSECT
SELECT * FROM t1) as dt1
INTERSECT
SELECT * FROM t2) as dt2
WHERE (f1+f2) > 22;
EXPLAIN
-> Table scan on dt2  (rows=12)
    -> Intersect materialize with deduplication  (rows=12)
        -> Table scan on dt1  (rows=12)
            -> Intersect materialize with deduplication  (rows=12)
                -> Filter: ((t1.f1 + t1.f2) > 22)  (rows=12)
                    -> Table scan on t1  (rows=12)
                -> Filter: ((t1.f1 + t1.f2) > 22)  (rows=12)
                    -> Table scan on t1  (rows=12)
        -> Filter: ((t2.f1 + t2.f2) > 22)  (rows=12)
            -> Table scan on t2  (rows=12)

SELECT *
FROM (SELECT * FROM (SELECT * FROM t1
INTERSECT
SELECT * FROM t1) as dt1
INTERSECT
SELECT * FROM t2) as dt2
WHERE (f1+f2) > 22;
f1	f2	f3
2	22	222
3	33	333
4	44	444
EXPLAIN FORMAT=tree SELECT i, j, k
FROM (SELECT f1 as i, f2 as j, f3 as k FROM t1
UNION
SELECT f1 as l, f2 as m, f3 as n FROM t2) as dt
WHERE i > 1 and i+j > 40 and k < 500;
EXPLAIN
-> Table scan on dt  (rows=12)
    -> Union materialize with deduplication  (rows=12)
        -> Filter: (((t1.f1 + t1.f2) > 40) and (t1.f3 < 500))  (rows=6)
            -> Index range scan on t1 using f1 over (1 < f1), with index condition: (t1.f1 > 1)  (rows=6)
        -> Filter: (((t2.f1 + t2.f2) > 40) and (t2.f3 < 500))  (rows=6)
            -> Index range scan on t2 using f1 over (1 < f1), with index condition: (t2.f1 > 1)  (rows=6)

SELECT i, j, k
FROM (SELECT f1 as i, f2 as j, f3 as k FROM t1
UNION
SELECT f1 as l, f2 as m, f3 as n FROM t2) as dt
WHERE i > 1 and i+j > 40 and k < 500;
i	j	k
4	44	444
EXPLAIN FORMAT=tree SELECT i, j
FROM (SELECT f1+f2 as i, f2+f3 as j FROM t1
UNION
SELECT f1 as l, f2+f3 as m FROM t2) as dt
WHERE i > 30 and i+j > 300;
EXPLAIN
-> Table scan on dt  (rows=13)
    -> Union materialize with deduplication  (rows=13)
        -> Filter: (((t1.f1 + t1.f2) > 30) and (((t1.f1 + t1.f2) + (t1.f2 + t1.f3)) > 300))  (rows=12)
            -> Table scan on t1  (rows=12)
        -> Filter: ((t2.f1 + (t2.f2 + t2.f3)) > 300)  (rows=1)
            -> Index range scan on t2 using f1 over (30 < f1), with index condition: (t2.f1 > 30)  (rows=1)

EXPLAIN FORMAT=tree SELECT i, j
FROM (SELECT f1+f2 as i, f2+f3 as j FROM t1
INTERSECT
SELECT f1 as l, f2+f3 as m FROM t2) as dt
WHERE i > 30 and i+j > 300;
EXPLAIN
-> Table scan on dt  (rows=1)
    -> Intersect materialize with deduplication  (rows=1)
        -> Filter: (((t1.f1 + t1.f2) > 30) and (((t1.f1 + t1.f2) + (t1.f2 + t1.f3)) > 300))  (rows=12)
            -> Table scan on t1  (rows=12)
        -> Filter: ((t2.f1 + (t2.f2 + t2.f3)) > 300)  (rows=1)
            -> Index range scan on t2 using f1 over (30 < f1), with index condition: (t2.f1 > 30)  (rows=1)

EXPLAIN FORMAT=tree SELECT i
FROM (SELECT f1 FROM t1
UNION
SELECT f2 FROM t2) as dt(i)
WHERE i > 10;
EXPLAIN
-> Table scan on dt  (rows=5)
    -> Union materialize with deduplication  (rows=5)
        -> Filter: (t1.f1 > 10)  (rows=1)
            -> Covering index range scan on t1 using f1 over (10 < f1)  (rows=1)
        -> Filter: (t2.f2 > 10)  (rows=4)
            -> Index scan on t2 using f1_2  (rows=12)

SELECT i
FROM (SELECT f1 FROM t1
UNION
SELECT f2 FROM t2) as dt(i)
WHERE i > 10;
i
11
22
33
44
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
UNION
SELECT f1, f2 FROM t1) as dt
WHERE f1 > 3;
EXPLAIN
-> Table scan on dt  (rows=3.41)
    -> Union materialize with deduplication  (rows=3.41)
        -> Group aggregate: sum(t1.f2)  (rows=1.41)
            -> Filter: (t1.f1 > 3)  (rows=2)
                -> Covering index range scan on t1 using f1_2 over (3 < f1)  (rows=2)
        -> Filter: (t1.f1 > 3)  (rows=2)
            -> Covering index range scan on t1 using f1_2 over (3 < f1)  (rows=2)

SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
UNION
SELECT f1, f2 FROM t1) as dt
WHERE f1 > 3;
f1	SUM(f2)
4	44
4	88
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1 WITH ROLLUP
HAVING AVG(f2) > 1
UNION ALL
SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1) AS dt
WHERE j+sum > 50 OR j IS NULL;
EXPLAIN
-> Table scan on dt  (rows=13)
    -> Union all materialize  (rows=13)
        -> Filter: ((rollup_sum_switcher(avg(t1.f2)) > 1) and (((rollup_group_item(t1.f1,0) + rollup_sum_switcher(sum(t1.f2))) > 50) or (rollup_group_item(t1.f1,0) is null)))  (rows=7)
            -> Group aggregate with rollup: avg(t1.f2), sum(t1.f2)  (rows=7)
                -> Index scan on t1 using f1_2  (rows=12)
        -> Filter: (((t1.f1 + sum(t1.f2)) > 50) or (t1.f1 is null))  (rows=6)
            -> Group aggregate: sum(t1.f2)  (rows=6)
                -> Index scan on t1 using f1_2  (rows=12)

SELECT *
FROM (SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1 WITH ROLLUP
HAVING AVG(f2) > 1
UNION ALL
SELECT f1 as j, SUM(f2) as sum
FROM t1 GROUP BY f1) AS dt
WHERE j+sum > 50 OR j IS NULL;
j	sum
3	66
4	88
NULL	222
NULL	2
3	66
4	88
EXPLAIN FORMAT=tree SELECT f1
FROM (SELECT f1, f2 FROM t1
UNION
SELECT f1, f2 FROM t2 ORDER BY f2) as dt
WHERE f1 > 3;
EXPLAIN
-> Table scan on dt  (rows=4)
    -> Materialize  (rows=4)
        -> Sort: f2  (rows=4)
            -> Table scan on <union temporary>  (rows=4)
                -> Union materialize with deduplication  (rows=4)
                    -> Filter: (t1.f1 > 3)  (rows=2)
                        -> Covering index range scan on t1 using f1_2 over (3 < f1)  (rows=2)
                    -> Filter: (t2.f1 > 3)  (rows=2)
                        -> Covering index range scan on t2 using f1_2 over (3 < f1)  (rows=2)

SELECT f1
FROM (SELECT f1, f2 FROM t1
UNION
SELECT f1, f2 FROM t2 ORDER BY f2) as dt
WHERE f1 > 3;
f1
4
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t1 GROUP BY f1, f2
UNION ALL
SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t2 GROUP BY f1,f2) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
EXPLAIN
-> Table scan on dt  (rows=0)
    -> Union all materialize  (rows=0)
        -> Window aggregate with buffering: sum(t1.f2) OVER (PARTITION BY t1.f1,t1.f2 ) 
            -> Sort: t1.f1, t1.f2
                -> Table scan on <temporary>  (rows=2)
                    -> Temporary table with deduplication  (rows=2)
                        -> Filter: ((t1.f1 > 2) and (t1.f2 > 30) and ((t1.f1 + t1.f2) > 40))  (rows=2)
                            -> Covering index skip scan for deduplication on t1 using f1_2 over (2 < f1)  (rows=2)
        -> Window aggregate with buffering: sum(t2.f2) OVER (PARTITION BY t2.f1,t2.f2 ) 
            -> Sort: t2.f1, t2.f2
                -> Table scan on <temporary>  (rows=2)
                    -> Temporary table with deduplication  (rows=2)
                        -> Filter: ((t2.f1 > 2) and (t2.f2 > 30) and ((t2.f1 + t2.f2) > 40))  (rows=2)
                            -> Covering index skip scan for deduplication on t2 using f1_2 over (2 < f1)  (rows=2)

SELECT *
FROM (SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t1 GROUP BY f1, f2
UNION ALL
SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t2 GROUP BY f1,f2) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
f1	f2	SUM(f2) OVER(PARTITION BY f1,f2)
4	44	44
4	44	44
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t1 GROUP BY f1, f2
INTERSECT ALL
SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t2 GROUP BY f1,f2) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
EXPLAIN
-> Table scan on dt  (rows=0)
    -> Intersect all materialize  (rows=0)
        -> Window aggregate with buffering: sum(t1.f2) OVER (PARTITION BY t1.f1,t1.f2 ) 
            -> Sort: t1.f1, t1.f2
                -> Table scan on <temporary>  (rows=2)
                    -> Temporary table with deduplication  (rows=2)
                        -> Filter: ((t1.f1 > 2) and (t1.f2 > 30) and ((t1.f1 + t1.f2) > 40))  (rows=2)
                            -> Covering index skip scan for deduplication on t1 using f1_2 over (2 < f1)  (rows=2)
        -> Window aggregate with buffering: sum(t2.f2) OVER (PARTITION BY t2.f1,t2.f2 ) 
            -> Sort: t2.f1, t2.f2
                -> Table scan on <temporary>  (rows=2)
                    -> Temporary table with deduplication  (rows=2)
                        -> Filter: ((t2.f1 > 2) and (t2.f2 > 30) and ((t2.f1 + t2.f2) > 40))  (rows=2)
                            -> Covering index skip scan for deduplication on t2 using f1_2 over (2 < f1)  (rows=2)

SELECT *
FROM (SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t1 GROUP BY f1, f2
INTERSECT ALL
SELECT f1, f2, SUM(f2) OVER(PARTITION BY f1,f2)
FROM t2 GROUP BY f1,f2) as dt
WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
f1	f2	SUM(f2) OVER(PARTITION BY f1,f2)
4	44	44
set sql_mode=default;
SET @p1 = 3;
PREPARE p FROM "SELECT f1
FROM (SELECT f1 FROM t1
UNION
SELECT f1 FROM t1) as dt WHERE f1 > ?";
EXECUTE p USING @p1;
f1
4
PREPARE p FROM "EXPLAIN FORMAT=tree SELECT f1
FROM (SELECT f1 FROM t1
UNION
SELECT f1 FROM t1) as dt WHERE f1 > ?";
EXECUTE p USING @p1;
EXPLAIN
-> Table scan on dt  (rows=4)
    -> Union materialize with deduplication  (rows=4)
        -> Filter: (t1.f1 > 3)  (rows=2)
            -> Covering index range scan on t1 using f1 over (3 < f1)  (rows=2)
        -> Filter: (t1.f1 > 3)  (rows=2)
            -> Covering index range scan on t1 using f1 over (3 < f1)  (rows=2)

DROP PREPARE p;
SET @p1=1, @p2=2, @p3=3, @p4=4, @p5=5, @p6=6;
PREPARE p FROM "EXPLAIN FORMAT=tree SELECT l,m
FROM (SELECT (i+10+?) as l, (j+11+?) as m
FROM (SELECT (f1+f2) as i, (f3+?) as j
FROM t1 UNION
SELECT (f1+f3) as i, (f2+?) as j
FROM t2) as dt1
GROUP BY l,m) as dt2
WHERE l > 20 and ?-m+? > 10";
EXECUTE p USING @p1, @p2, @p3, @p4, @p5, @p6;
EXPLAIN
-> Table scan on dt2  (rows=24)
    -> Materialize  (rows=24)
        -> Table scan on <temporary>  (rows=24)
            -> Temporary table with deduplication  (rows=24)
                -> Table scan on dt1  (rows=24)
                    -> Union materialize with deduplication  (rows=24)
                        -> Filter: (((((t1.f1 + t1.f2) + 10) + 1) > 20) and (((5 - (((t1.f3 + 3) + 11) + 2)) + 6) > 10))  (rows=12)
                            -> Table scan on t1  (rows=12)
                        -> Filter: (((((t2.f1 + t2.f3) + 10) + 1) > 20) and (((5 - (((t2.f2 + 4) + 11) + 2)) + 6) > 10))  (rows=12)
                            -> Table scan on t2  (rows=12)

PREPARE p FROM "SELECT l,m
FROM (SELECT (i+10+?) as l, (j+11+?) as m
FROM (SELECT (f1+f2) as i, (f3+?) as j
FROM t1 UNION
SELECT (f1+f3) as i, (f2+?) as j
FROM t2) as dt1
GROUP BY l,m) as dt2
WHERE l > 20 and ?-m+? > 10";
EXECUTE p USING @p1, @p2, @p3, @p4, @p5, @p7;
l	m
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1 as a FROM t1
UNION ALL
SELECT RAND() as a FROM t1) as dt
WHERE a > 0.5;
EXPLAIN
-> Filter: (dt.a > 0.5)  (rows=8)
    -> Table scan on dt  (rows=24)
        -> Union all materialize  (rows=24)
            -> Index scan on t1 using f1  (rows=12)
            -> Index scan on t1 using f1  (rows=12)

EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
UNION ALL
SELECT f1, f2 FROM t1) as dt
WHERE f1 > 3*RAND() AND f1 < 10;
EXPLAIN
-> Filter: (dt.f1 > (3 * rand()))  (rows=6.67)
    -> Table scan on dt  (rows=13.2)
        -> Union all materialize  (rows=13.2)
            -> Group aggregate: sum(t1.f2)  (rows=3.16)
                -> Filter: (t1.f1 < 10)  (rows=10)
                    -> Covering index range scan on t1 using f1_2 over (NULL < f1 < 10)  (rows=10)
            -> Filter: (t1.f1 < 10)  (rows=10)
                -> Covering index range scan on t1 using f1_2 over (NULL < f1 < 10)  (rows=10)

EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
INTERSECT ALL
SELECT f1, f2 FROM t1) as dt
WHERE f1 > 3*RAND() AND f1 < 10;
EXPLAIN
-> Filter: (dt.f1 > (3 * rand()))  (rows=3.33)
    -> Table scan on dt  (rows=3.16)
        -> Intersect all materialize  (rows=3.16)
            -> Group aggregate: sum(t1.f2)  (rows=3.16)
                -> Filter: (t1.f1 < 10)  (rows=10)
                    -> Covering index range scan on t1 using f1_2 over (NULL < f1 < 10)  (rows=10)
            -> Filter: (t1.f1 < 10)  (rows=10)
                -> Covering index range scan on t1 using f1_2 over (NULL < f1 < 10)  (rows=10)

EXPLAIN FORMAT=tree SELECT f1
FROM (SELECT f1 FROM t1
UNION ALL
SELECT (SELECT 1 FROM t1 LIMIT 1) as f1 FROM t1) as dt
WHERE f1 = 1;
EXPLAIN
-> Covering index lookup on dt using <auto_key0> (f1=1)  (rows=2.4)
    -> Union all materialize  (rows=24)
        -> Index scan on t1 using f1  (rows=12)
        -> Index scan on t1 using f1  (rows=12)
        -> Select #4 (subquery in projection; run only once)
            -> Limit: 1 row(s)  (rows=1)
                -> Index scan on t1 using f1  (rows=12)

SELECT f1
FROM (SELECT f1 FROM t1
UNION ALL
SELECT (SELECT 1 FROM t1 LIMIT 1) as f1 FROM t1) as dt
WHERE f1 = 1;
f1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
EXPLAIN FORMAT=tree SELECT f1
FROM (SELECT f1 FROM t1
UNION ALL
SELECT f1 FROM t1 LIMIT 1) as dt
WHERE f1 = 1;
EXPLAIN
-> Covering index lookup on dt using <auto_key0> (f1=1)  (rows=2.4)
    -> Union all materialize  (rows=24)
        -> Limit table size: 1 row(s)
            -> Index scan on t1 using f1  (rows=12)
        -> Limit table size: 1 row(s)
            -> Index scan on t1 using f1  (rows=12)

EXPLAIN FORMAT=tree SELECT f1
FROM (SELECT f1 FROM t1
UNION ALL
(SELECT f1 FROM t1 LIMIT 1)) as dt
WHERE f1 = 1;
EXPLAIN
-> Covering index lookup on dt using <auto_key0> (f1=1)  (rows=1.3)
    -> Union all materialize  (rows=13)
        -> Index scan on t1 using f1  (rows=12)
        -> Limit: 1 row(s)  (rows=1)
            -> Index scan on t1 using f1  (rows=12)

CREATE FUNCTION p() RETURNS INTEGER
BEGIN
DECLARE retn INTEGER;
SELECT count(f1) FROM t1 INTO retn;
RETURN retn;
END|
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
UNION
SELECT f1, f2 FROM t1) as dt
WHERE p() = 1;
EXPLAIN
-> Filter: (p() = 1)  (rows=24)
    -> Table scan on dt  (rows=18)
        -> Union materialize with deduplication  (rows=18)
            -> Group aggregate: sum(t1.f2)  (rows=6)
                -> Index scan on t1 using f1_2  (rows=12)
            -> Index scan on t1 using f1_2  (rows=12)

SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
UNION
SELECT f1, f2 FROM t1) as dt
WHERE p() = 1;
f1	SUM(f2)
DROP FUNCTION p;
CREATE FUNCTION p() RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE retn INTEGER;
SELECT 1 INTO retn;
RETURN retn;
END|
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
UNION
SELECT f1, f2 FROM t1) as dt
WHERE p() = 1;
EXPLAIN
-> Table scan on dt  (rows=18)
    -> Union materialize with deduplication  (rows=18)
        -> Group aggregate: sum(t1.f2)  (rows=6)
            -> Filter: <cache>((p() = 1))  (rows=12)
                -> Index scan on t1 using f1_2  (rows=12)
        -> Filter: <cache>((p() = 1))  (rows=12)
            -> Index scan on t1 using f1_2  (rows=12)

SELECT *
FROM (SELECT f1, SUM(f2) FROM t1 GROUP BY f1
UNION
SELECT f1, f2 FROM t1) as dt
WHERE p() = 1;
f1	SUM(f2)
0	0
1	11
1	22
2	22
2	44
3	33
3	66
4	44
4	88
NULL	1
NULL	2
DROP FUNCTION p;
CREATE PROCEDURE p()
BEGIN
DECLARE val INTEGER;
SET val = 2;
EXPLAIN FORMAT=tree SELECT AVG(f1)
FROM (SELECT * FROM t1
UNION
SELECT * FROM t1) as dt
WHERE f2 > val;
SELECT AVG(f1)
FROM (SELECT * FROM t1
UNION
SELECT * FROM t1) as dt
WHERE f2 > val;
END|
CALL p();
EXPLAIN
-> Aggregate: avg(dt.f1)  (rows=1)
    -> Table scan on dt  (rows=8)
        -> Union materialize with deduplication  (rows=8)
            -> Filter: (t1.f2 > <cache>(val@0))  (rows=4)
                -> Table scan on t1  (rows=12)
            -> Filter: (t1.f2 > <cache>(val@0))  (rows=4)
                -> Table scan on t1  (rows=12)

AVG(f1)
2.5000
DROP PROCEDURE p;
EXPLAIN FORMAT=tree WITH recursive qn AS
(SELECT 10*f1 as f1 FROM t1
UNION
SELECT * FROM qn) SELECT * FROM qn WHERE f1 > 1;
EXPLAIN
-> Filter: (qn.f1 > 1)  (rows=8)
    -> Table scan on qn  (rows=24)
        -> Materialize recursive CTE qn with deduplication  (rows=24)
            -> Index scan on t1 using f1  (rows=12)
            -> Repeat until convergence
                -> Scan new records on qn  (rows=12)

WITH recursive qn AS
(SELECT 10*f1 as f1 FROM t1
UNION
SELECT * FROM qn) SELECT * FROM qn WHERE f1 > 1;
f1
10
20
30
40
EXPLAIN FORMAT=tree SELECT *
FROM t1 JOIN (SELECT f1, f2 FROM t1
UNION SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 31 and t1.f2  > 40;
EXPLAIN
-> Nested loop inner join  (rows=8)
    -> Filter: ((t1.f2 > 40) and (t1.f2 is not null))  (rows=4)
        -> Table scan on t1  (rows=12)
    -> Index lookup on dt using <auto_key1> (f2=t1.f2)  (rows=2)
        -> Union materialize with deduplication  (rows=2)
            -> Filter: (t1.f1 > 31)  (rows=1)
                -> Covering index range scan on t1 using f1_2 over (31 < f1)  (rows=1)
            -> Filter: (t1.f1 > 31)  (rows=1)
                -> Covering index range scan on t1 using f1_2 over (31 < f1)  (rows=1)

SELECT *
FROM t1 JOIN (SELECT f1, f2 FROM t1
UNION SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 31 and t1.f2  > 40;
f2	f1	f3	f1
EXPLAIN FORMAT=tree SELECT *
FROM t1 JOIN (SELECT f1, f2 FROM t1
EXCEPT SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 31 and t1.f2  > 40;
EXPLAIN
-> Inner hash join (dt.f2 = t1.f2)  (rows=4)
    -> Table scan on dt  (rows=1)
        -> Except materialize with deduplication  (rows=1)
            -> Filter: (t1.f1 > 31)  (rows=1)
                -> Covering index range scan on t1 using f1_2 over (31 < f1)  (rows=1)
            -> Filter: (t1.f1 > 31)  (rows=1)
                -> Covering index range scan on t1 using f1_2 over (31 < f1)  (rows=1)
    -> Hash
        -> Filter: (t1.f2 > 40)  (rows=4)
            -> Table scan on t1  (rows=12)

SELECT *
FROM t1 JOIN (SELECT f1, f2 FROM t1
EXCEPT SELECT f1, f2 FROM t1) as dt USING (f2)
WHERE dt.f1 > 31 and t1.f2  > 40;
f2	f1	f3	f1
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT *
FROM (SELECT f1, f2 FROM t1
UNION
SELECT f1, f2 FROM t1) as dt1
WHERE f1 > 10) dt2
WHERE f2 > 30;
EXPLAIN
-> Table scan on dt1  (rows=0.667)
    -> Union materialize with deduplication  (rows=0.667)
        -> Filter: ((t1.f2 > 30) and (t1.f1 > 10))  (rows=0.333)
            -> Covering index range scan on t1 using f1_2 over (10 < f1)  (rows=1)
        -> Filter: ((t1.f2 > 30) and (t1.f1 > 10))  (rows=0.333)
            -> Covering index range scan on t1 using f1_2 over (10 < f1)  (rows=1)

SELECT *
FROM (SELECT *
FROM (SELECT f1, f2 FROM t1
UNION
SELECT f1, f2 FROM t1) as dt1
WHERE f1 > 10) dt2
WHERE f2 > 30;
f1	f2
EXPLAIN FORMAT=tree SELECT *
FROM (SELECT f1, f2
FROM (SELECT f1+f2 as f1, f2 FROM t1) as dt1
UNION
SELECT f1, f2 FROM t1) as dt2
WHERE f1 > 30;
EXPLAIN
-> Table scan on dt2  (rows=13)
    -> Union materialize with deduplication  (rows=13)
        -> Filter: ((t1.f1 + t1.f2) > 30)  (rows=12)
            -> Index scan on t1 using f1_2  (rows=12)
        -> Filter: (t1.f1 > 30)  (rows=1)
            -> Covering index range scan on t1 using f1_2 over (30 < f1)  (rows=1)

SELECT *
FROM (SELECT f1, f2
FROM (SELECT f1+f2 as f1, f2 FROM t1) as dt1
UNION
SELECT f1, f2 FROM t1) as dt2
WHERE f1 > 30;
f1	f2
36	33
48	44
CREATE ALGORITHM=temptable VIEW v AS (SELECT f1,f2,f3 FROM t1
UNION
SELECT f1,f2,f3 FROM t1);
EXPLAIN format=tree INSERT INTO t2 SELECT * FROM v WHERE f1=2;
EXPLAIN
-> Insert into t2
    -> Table scan on v  (rows=4)
        -> Union materialize with deduplication  (rows=4)
            -> Index lookup on t1 using f1 (f1=2)  (rows=2)
            -> Index lookup on t1 using f1 (f1=2)  (rows=2)

INSERT INTO t2 SELECT * FROM v WHERE f1=2;
EXPLAIN format=tree UPDATE t2 SET f1=3 WHERE f1 IN (SELECT f1 FROM v WHERE f1=2);
EXPLAIN
-> Update t2 (immediate)
    -> Nested loop inner join  (rows=52)
        -> Filter: (t2.f1 is not null)  (rows=13)
            -> Table scan on t2  (rows=13)
        -> Single-row index lookup on <subquery2> using <auto_distinct_key> (f1=t2.f1)  (rows=1)
            -> Materialize with deduplication  (rows=4)
                -> Filter: (v.f1 is not null)  (rows=4)
                    -> Table scan on v  (rows=4)
                        -> Union materialize with deduplication  (rows=4)
                            -> Index lookup on t1 using f1 (f1=2)  (rows=2)
                            -> Index lookup on t1 using f1 (f1=2)  (rows=2)

UPDATE t2 SET f1=3 WHERE f1 IN (SELECT f1 FROM v WHERE f1=2);
EXPLAIN format=tree UPDATE t2 JOIN v ON t2.f2=v.f2
SET t2.f1 = t2.f1 + v.f1
WHERE v.f2 > 10;
EXPLAIN
-> Update t2 (immediate)
    -> Nested loop inner join  (rows=104)
        -> Filter: (t2.f2 is not null)  (rows=13)
            -> Table scan on t2  (rows=13)
        -> Index lookup on v using <auto_key1> (f2=t2.f2)  (rows=2)
            -> Union materialize with deduplication  (rows=8)
                -> Filter: (t1.f2 > 10)  (rows=4)
                    -> Table scan on t1  (rows=12)
                -> Filter: (t1.f2 > 10)  (rows=4)
                    -> Table scan on t1  (rows=12)

UPDATE t2 JOIN v ON t2.f2=v.f2 SET t2.f1 = t2.f1 + v.f1 WHERE v.f2 > 10;
EXPLAIN format=tree DELETE FROM t2 WHERE f1 IN (SELECT f1 FROM v WHERE f1=3);
EXPLAIN
-> Delete from t2 (buffered)  (rows=8)
    -> Nested loop inner join  (rows=8)
        -> Filter: (`<subquery2>`.f1 is not null)  (rows=4)
            -> Table scan on <subquery2>  (rows=4)
                -> Materialize with deduplication  (rows=4)
                    -> Filter: (v.f1 is not null)  (rows=4)
                        -> Table scan on v  (rows=4)
                            -> Union materialize with deduplication  (rows=4)
                                -> Index lookup on t1 using f1 (f1=3)  (rows=2)
                                -> Index lookup on t1 using f1 (f1=3)  (rows=2)
        -> Index lookup on t2 using f1 (f1=`<subquery2>`.f1)  (rows=2)

DELETE FROM t2 WHERE f1 IN (SELECT f1 FROM v WHERE f1=3);
EXPLAIN format=tree DELETE t2 FROM t2 JOIN v ON t2.f2=v.f2 WHERE v.f2 > 10;
EXPLAIN
-> Delete from t2 (immediate)  (rows=104)
    -> Nested loop inner join  (rows=104)
        -> Filter: (t2.f2 is not null)  (rows=13)
            -> Table scan on t2  (rows=13)
        -> Index lookup on v using <auto_key1> (f2=t2.f2)  (rows=2)
            -> Union materialize with deduplication  (rows=8)
                -> Filter: (t1.f2 > 10)  (rows=4)
                    -> Table scan on t1  (rows=12)
                -> Filter: (t1.f2 > 10)  (rows=4)
                    -> Table scan on t1  (rows=12)

DELETE t2 FROM t2 JOIN v ON t2.f2=v.f2 WHERE v.f2 > 10;
DROP VIEW v;
DROP TABLE t0;
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1(f1 VARBINARY(10000));
SELECT * FROM (SELECT f1 FROM t1 UNION SELECT f1 FROM t1) as dt WHERE f1 > '10';
f1
DROP TABLE t1;
#
# Bug#33791802: Query with multiple JOINs stopped working
#
CREATE TABLE t1(f1 INTEGER, f2 INTEGER);
CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM v1;
SELECT t1.f1 FROM t1 JOIN v2 USING(f2) WHERE v2.f2 = 1;
f1
DROP TABLE t1;
DROP VIEW v1;
DROP VIEW v2;
SELECT a.table_name, d.table_name
FROM information_schema.key_column_usage a
JOIN information_schema.table_constraints b
USING (table_schema , table_name , constraint_name)
JOIN information_schema.referential_constraints c
ON (c.constraint_name = b.constraint_name AND
c.table_name = b.table_name AND
c.constraint_schema = b.table_schema)
LEFT JOIN information_schema.table_constraints d
ON (a.referenced_table_schema = d.table_schema AND
a.referenced_table_name = d.table_name AND
d.constraint_type IN ('UNIQUE' , 'PRIMARY KEY'))
WHERE b.constraint_type = 'FOREIGN KEY'
ORDER BY a.table_name , a.ordinal_position;
TABLE_NAME	TABLE_NAME
SELECT a.table_name
FROM information_schema.key_column_usage a
JOIN information_schema.table_constraints b
USING (table_schema)
WHERE b.constraint_type = 'FOREIGN KEY';
TABLE_NAME
#
# Bug#33838439: ExtractValue not working properly with COUNT
#
CREATE TABLE t1 (f1 INTEGER);
SET @a = 0;
EXPLAIN SELECT COUNT(*) FROM (SELECT SUM(f1) FROM t1) as dt WHERE @a = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from (/* select#2 */ select sum(`test`.`t1`.`f1`) AS `SUM(f1)` from `test`.`t1` having false) `dt`
SELECT COUNT(*) FROM (SELECT SUM(f1) FROM t1) as dt WHERE @a = 1;
COUNT(*)
0
DROP TABLE t1;
#
# Bug#34148712: Incorrect DATETIME value: '' on latest MySQL 8.0.29
#
CREATE TABLE t1 (f1 INTEGER UNSIGNED);
PREPARE stmt FROM "SELECT *
FROM (SELECT NOW() AS time FROM t1 WHERE f1 = ?
UNION
SELECT NOW() AS time FROM t1 WHERE f1 = 0) AS dt
WHERE time <= ?";
SET @a = 1;
SET @b = '2022-05-06 16:49:45';
EXECUTE stmt USING @a, @b;
time
PREPARE stmt FROM "EXPLAIN FORMAT=tree SELECT *
FROM (SELECT NOW() AS time FROM t1 WHERE f1 = ?
UNION
SELECT NOW() AS time FROM t1 WHERE f1 = 0) AS dt
WHERE time <= ?";
EXECUTE stmt USING @a, @b;
EXPLAIN
-> Zero rows (no matching row in const table)  (rows=0)

SET @a = 2;
SET @b = '2023-05-06 16:49:45';
SET timestamp=UNIX_TIMESTAMP('2023-05-06 16:49:45');
EXECUTE stmt USING @a, @b;
EXPLAIN
-> Table scan on dt  (rows=2)
    -> Union materialize with deduplication  (rows=2)
        -> Filter: (t1.f1 = 2)  (rows=1)
            -> Table scan on t1  (rows=1)
        -> Filter: (t1.f1 = 0)  (rows=1)
            -> Table scan on t1  (rows=1)

SET timestamp=default;
DROP TABLE t1;
#
# Bug#34298238: Assertion `cond->is_bool_func()' failed in MySQL 8.0.29
#
CREATE TABLE t1 (f1 INTEGER);
INSERT INTO t1 VALUES (1);
SELECT * FROM (SELECT f1 FROM t1 UNION SELECT f1 FROM t1) AS dt WHERE f1 <> 0.5;
f1
1
DROP TABLE t1;
#
# Bug#34515868: Temptable views on information_schema fails on filters
#
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS;
SELECT * FROM v1 WHERE VIEW_DEFINITION LIKE 'x';
VIEW_DEFINITION
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM v1;
SELECT * FROM v2 WHERE VIEW_DEFINITION LIKE 'x';
VIEW_DEFINITION
CREATE ALGORITHM=TEMPTABLE VIEW v3 AS
SELECT * FROM (SELECT * FROM v1 UNION SELECT * FROM v2) AS dt;
SELECT * FROM v3 WHERE VIEW_DEFINITION LIKE 'x';
VIEW_DEFINITION
DROP VIEW v1, v2, v3;
# Bug#34359297: Incorrect work of query with union in inner select
CREATE TABLE t1 (
str VARCHAR(200) CHARACTER SET utf16 COLLATE utf16_unicode_ci
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii COLLATE ascii_general_ci;
CREATE TABLE t2 (
str VARCHAR(200) CHARACTER SET utf16 COLLATE utf16_unicode_ci
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii COLLATE ascii_general_ci;
INSERT INTO t1 VALUES (_utf8mb4'Patch');
ANALYZE TABLE t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
explain format=tree SELECT dt.str
FROM (SELECT t2.str
FROM t2
WHERE t2.str LIKE _latin1'Patc%' ESCAPE _latin1'\\'
      UNION ALL
SELECT t1.str
FROM t1
WHERE t1.str LIKE _latin1'Patc%' ESCAPE _latin1'\\'
     ) AS dt
WHERE dt.str LIKE _latin1'P%' ESCAPE _latin1'\\';
EXPLAIN
-> Table scan on dt  (...)
    -> Union all materialize  (...)
        -> Filter: ((t2.str like '\0P\0a\0t\0c\0%' escape '\0\\') and (t2.str like '\0P\0%' escape '\0\\'))  (...)
            -> Table scan on t2  (...)
        -> Filter: ((t1.str like '\0P\0a\0t\0c\0%' escape '\0\\') and (t1.str like '\0P\0%' escape '\0\\'))  (...)
            -> Table scan on t1  (...)

SELECT dt.str
FROM (SELECT t2.str
FROM t2
WHERE t2.str LIKE _latin1'Patc%' ESCAPE _latin1'\\'
      UNION ALL
SELECT t1.str
FROM t1
WHERE t1.str LIKE _latin1'Patc%' ESCAPE _latin1'\\'
     ) AS dt
WHERE dt.str LIKE _latin1'P%' ESCAPE _latin1'\\';
str
Patch
DROP TABLE t1, t2;
#
# Bug#34781533: Result set is incorrect
#
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);
INSERT INTO t1 VALUES (NULL, NULL);
SELECT 1
FROM (SELECT 1
FROM t1 LEFT JOIN (SELECT 1 AS f1 FROM t1) AS dt1
ON dt1.f1 = t1.f2
WHERE dt1.f1 IS NOT NULL) AS dt2,
(SELECT 1 FROM t1 UNION ALL SELECT 2 FROM t1) AS dt3;
1
DROP TABLE t1;
#
# Bug#34919799: Assertion `cond->is_bool_func()' failed.
#
CREATE TABLE t1 (f1 TINYINT);
SELECT f1 FROM (SELECT f1 FROM t1 UNION SELECT f1 FROM t1 ) AS dt
WHERE f1 > -32768 OR f1 = 1;
f1
DROP TABLE t1;
#
# Bug#34996488: utf8mb4 identifiers stopped working since
#               8.0.32 release
#
SELECT * FROM (SELECT 'å' AS x) AS dt WHERE x = 'å';
x
å
CREATE TABLE t1 (f1 VARCHAR(10));
INSERT INTO t1 VALUES('å');
SELECT * FROM (SELECT f1 FROM t1 UNION SELECT f1 FROM t1) AS dt WHERE f1 = 'å';
f1
å
DROP TABLE t1;
#
# Bug#35689804: Depending on the data inserted, the results of
#               the PREPARE and the general query run are different.
#
CREATE TABLE t1 (f1 INTEGER, f2 VARCHAR(30) COLLATE utf8mb4_bin NOT NULL);
INSERT INTO t1(f2) VALUES ('680519363848');
SET @a1 = 'Y';
SET @a2 = 'Y';
SET @a3 = 'N';
SET @a4 = 'Y';
SET @a5 = 'Y';
SET @a6 = 'Y';
SET @a7 = 'N';
SET @a8 = 'Y';
SET @a9 = 'Y';
PREPARE stmt FROM "SELECT *
FROM (SELECT IF(f2 = ?, ?, CASE WHEN f2 IS NULL THEN ? ELSE ? END) AS case_f
FROM t1
UNION ALL
SELECT IF(f2 = ?, ?, CASE WHEN f2 IS NULL THEN ? ELSE ? END) AS case_f
FROM t1) AS dt1
WHERE case_f = ?";
EXECUTE stmt USING @a1, @a2, @a3, @a4, @a5, @a6, @a7, @a8, @a9;
case_f
Y
Y
PREPARE stmt FROM "EXPLAIN FORMAT=tree SELECT *
FROM (SELECT IF(f2 = ?, ?, CASE WHEN f2 IS NULL THEN ? ELSE ? END) AS case_f
FROM t1
UNION ALL
SELECT IF(f2 = ?, ?, CASE WHEN f2 IS NULL THEN ? ELSE ? END) AS case_f
FROM t1) AS dt1
WHERE case_f = ?";
EXECUTE stmt USING @a1, @a2, @a3, @a4, @a5, @a6, @a7, @a8, @a9;
EXPLAIN
-> Table scan on dt1  (rows=2)
    -> Union all materialize  (rows=2)
        -> Filter: (if((t1.f2 = 'Y'),'Y',<cache>((case when (t1.f2 is null) then 'N' else 'Y' end))) = 'Y')  (rows=1)
            -> Table scan on t1  (rows=1)
        -> Filter: (if((t1.f2 = 'Y'),'Y',<cache>((case when (t1.f2 is null) then 'N' else 'Y' end))) = 'Y')  (rows=1)
            -> Table scan on t1  (rows=1)

SET optimizer_switch="derived_merge=off";
SET @a1 = 'Y';
PREPARE stmt FROM "SELECT * FROM (SELECT ? AS case_f FROM t1) as dt1 WHERE case_f = 'Y'";
EXECUTE stmt USING @a1;
case_f
Y
PREPARE stmt FROM "EXPLAIN FORMAT=tree SELECT * FROM (SELECT ? AS case_f FROM t1) as dt1 WHERE case_f = 'Y'";
EXECUTE stmt USING @a1;
EXPLAIN
-> Table scan on dt1  (rows=1)
    -> Materialize  (rows=1)
        -> Table scan on t1  (rows=1)

SET optimizer_switch=default;
DROP TABLE t1;
# Bug#35211828: Derived condition pushdown with rollup gives wrong
#               results
#
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);
INSERT INTO t1 VALUES(1,2);
INSERT INTO t1 VALUES (2,2);
SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 GROUP BY f1, f2 WITH ROLLUP) as dt
WHERE f2 IS NULL;
f1	f2
1	NULL
2	NULL
NULL	NULL
DROP TABLE t1;
#
# Bug#36246859: Collation issue: ERROR 1253 (42000):
#               COLLATION '' is not valid for CHARACTER SET
#
CREATE TABLE t1 (f1 char(10)
CHARACTER SET latin1 COLLATE latin1_spanish_ci DEFAULT NULL)
DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
INSERT INTO t1 VALUES ('C');
INSERT INTO t1 VALUES ('D');
SET character_set_client = latin1;
SET character_set_connection = latin1;
CREATE VIEW v1 AS
SELECT 'A' COLLATE latin1_spanish_ci AS field1 FROM t1
UNION
SELECT 'B' COLLATE latin1_spanish_ci AS field1 FROM t1 AS t2;
SET character_set_connection = default;
EXPLAIN FORMAT=tree SELECT * FROM v1 WHERE field1 IN ('C');
EXPLAIN
-> Zero rows (no matching row in const table)  (rows=0)

SELECT * FROM v1 WHERE field1 IN ('C');
field1
EXPLAIN FORMAT=tree SELECT * FROM (SELECT * FROM v1 UNION SELECT * FROM v1) as dt
WHERE field1 IN ('A');
EXPLAIN
-> Table scan on dt  (rows=4)
    -> Union materialize with deduplication  (rows=4)
        -> Table scan on v1  (rows=2)
            -> Union materialize with deduplication  (rows=2)
                -> Table scan on t1  (rows=2)
                -> Zero rows (Impossible WHERE)  (rows=0)
        -> Table scan on v1  (rows=2)
            -> Union materialize with deduplication  (rows=2)
                -> Table scan on t1  (rows=2)
                -> Zero rows (Impossible WHERE)  (rows=0)

SELECT * FROM (SELECT * FROM v1 UNION SELECT * FROM v1) as dt
WHERE field1 IN ('A');
field1
A
EXPLAIN FORMAT=tree SELECT * FROM v1
WHERE field1 IN ('C') OR
COALESCE('A' COLLATE utf8mb4_spanish_ci IN ('B'), field1);
EXPLAIN
-> Zero rows (no matching row in const table)  (rows=0)

SELECT * FROM v1
WHERE field1 IN ('C') OR
COALESCE('A' COLLATE utf8mb4_spanish_ci IN ('B'), field1);
field1
DROP VIEW v1;
SET character_set_client = latin1;
SET character_set_connection = latin1;
CREATE VIEW v1 AS SELECT 'A' COLLATE latin1_spanish_ci AS field1 FROM t1;
SET character_set_connection = default;
SET optimizer_switch="derived_merge=off";
EXPLAIN FORMAT=tree SELECT * FROM v1 WHERE field1 IN ('C');
EXPLAIN
-> Zero rows (no matching row in const table)  (rows=0)

SELECT * FROM v1 WHERE field1 IN ('C');
field1
SET optimizer_switch=default;
SET character_set_client = default;
DROP VIEW v1;
DROP TABLE t1;
#
# Bug#37111452: Error selecting from a view where the view and
#               the connection charset differs
#
CREATE TABLE t1 (c1 CHAR(10)) DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
CREATE TABLE t2 (c1 char(10)) DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
SET @create_view_utf8 =
"CREATE VIEW v1 AS
 SELECT '0000000001þ' COLLATE latin1_spanish_ci AS tipos FROM t1
 UNION
 SELECT '0000000001þ' COLLATE latin1_spanish_ci AS tipos from t2";
SELECT CAST(@create_view_utf8 AS CHAR CHARACTER SET latin1)
INTO @create_view_latin1;
SET @select_utf8 = "SELECT * FROM v1 WHERE tipos IN ('0000000001þ')";
SELECT CAST(@select_utf8 AS CHAR CHARACTER SET latin1) INTO @select_latin1;
SET character_set_client = latin1;
SET character_set_connection = latin1;
prepare ps FROM @create_view_latin1;
execute ps;
prepare ps_select_latin1_on FROM @select_latin1;
execute ps_select_latin1_on;
tipos
0000000001þ
SET optimizer_switch="derived_condition_pushdown=off";
prepare ps_select_latin1_off FROM @select_latin1;
execute ps_select_latin1_off;
tipos
0000000001þ
SET character_set_client = DEFAULT;
SET character_set_connection = DEFAULT;
SET optimizer_switch="derived_condition_pushdown=on";
prepare ps_select_utf8_on FROM @select_utf8;
execute ps_select_utf8_on;
tipos
0000000001þ
SET optimizer_switch="derived_condition_pushdown=off";
prepare ps_select_utf8_off FROM @select_utf8;
execute ps_select_utf8_off;
tipos
0000000001þ
SET optimizer_switch="derived_condition_pushdown=on";
DEALLOCATE PREPARE ps;
DEALLOCATE PREPARE ps_select_utf8_on;
DEALLOCATE PREPARE ps_select_utf8_off;
DEALLOCATE PREPARE ps_select_latin1_on;
DEALLOCATE PREPARE ps_select_latin1_off;
DROP VIEW v1;
DROP TABLE t1, t2;
#
# Bug#35634714: Derived condition pushdown return wrong results
#
CREATE TABLE t1(f1 INTEGER);
INSERT INTO t1 VALUES (NULL);
CREATE TABLE t2(f1 INTEGER);
SELECT dt3.f1
FROM t1 JOIN (SELECT f1
FROM (SELECT dt1.f1
FROM t1
LEFT JOIN (SELECT 1 AS f1
FROM t2) AS dt1
ON TRUE) AS dt2
GROUP BY f1) AS dt3
WHERE dt3.f1 IS NOT NULL;
f1
DROP TABLE t1,t2;