| 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; |