Debian Codegraph @

mysql-test/suite/opt_trace/t/derived_condition_pushdown.test

raw
1
2 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
3 --source include/no_protocol.inc
4
5 --source include/have_optimizer_trace.inc
6 --source include/not_hypergraph.inc # Does not output the same optimizer trace.
7
8 LET $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
9 EVAL SET optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
10 SET @@session.optimizer_trace="enabled=on";
11
12 # Check optimizer trace information printed while pushing conditions down
13 set optimizer_switch="derived_condition_pushdown=on";
14 set optimizer_switch="derived_merge=off";
15 set sql_mode="";
16
17 CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
18 KEY(f1), KEY(f1,f2), KEY(f3));
19 INSERT INTO t1 VALUES (1,26,1);
20 INSERT INTO t1 VALUES (2,32,1);
21 INSERT INTO t1 VALUES (3,90,1);
22 ANALYZE TABLE t1;
23
24 SELECT * FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1,f2)
25 FROM t1 GROUP BY f1) as dt WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
26 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
27 SELECT trace FROM information_schema.optimizer_trace;
28
29 SELECT * FROM
30 (SELECT (i+j) AS g, f1 FROM
31 (SELECT (f1+f2) AS i, (f1-f2) AS j FROM
32 (SELECT f1+10 AS f1, f2+10 AS f2 FROM t1) AS dt0) AS dt1,
33 (SELECT f1, f2 FROM t1) AS dt2) AS dt3
34 WHERE g > 26 and g+f1 > 31;
35 SELECT trace FROM information_schema.optimizer_trace;
36
37 SELECT * FROM
38 (SELECT f1, SUM(f1) OVER() AS f2 FROM t1 GROUP BY f1 UNION
39 SELECT f1, CAST(f1 AS DECIMAL(10,2)) FROM t1
40 ) as dt1 WHERE f1 > 26 and f2 > 31;
41 SELECT trace FROM information_schema.optimizer_trace;
42
43 set optimizer_switch=default;
44 set sql_mode=default;
45
46 DROP TABLE t1;