4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/xengine_main/r/lead_lag_explain.result

3252 lines
94 KiB
Plaintext
Raw Permalink Blame History

# Test of SQL window functions NTH_VALUE
# ----------------------------------------------------------------------
SET NAMES utf8mb4;
CREATE TABLE t1 (d DOUBLE, id INT, sex CHAR(1), n INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(n));
INSERT INTO t1(d, id, sex) VALUES (1.0, 1, 'M'),
(2.0, 2, 'F'),
(3.0, 3, 'F'),
(4.0, 4, 'F'),
(5.0, 5, 'M'),
(NULL, NULL, 'M'),
(10.0, 10, NULL),
(10.0, 10, NULL),
(11.0, 11, NULL);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
Ok, default semantics:
First without default:
EXPLAIN FORMAT=JSON SELECT id, sex, LEAD(id, 1) RESPECT NULLS OVER () FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,1) OVER () AS `LEAD(id, 1) RESPECT NULLS OVER ()` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT id, sex, LAG(id, 1) RESPECT NULLS OVER () FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lag"
]
}
],
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,lag(`test`.`t1`.`id`,1) OVER () AS `LAG(id, 1) RESPECT NULLS OVER ()` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT id, sex, LEAD(id, 0) RESPECT NULLS OVER () FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,0) OVER () AS `LEAD(id, 0) RESPECT NULLS OVER ()` from `test`.`t1`
Now with default:
EXPLAIN FORMAT=JSON SELECT id, sex, LEAD(id, 1, id) RESPECT NULLS OVER () FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,1,`test`.`t1`.`id`) OVER () AS `LEAD(id, 1, id) RESPECT NULLS OVER ()` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT id, sex, LAG(id, 1, id) RESPECT NULLS OVER () FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lag"
]
}
],
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,lag(`test`.`t1`.`id`,1,`test`.`t1`.`id`) OVER () AS `LAG(id, 1, id) RESPECT NULLS OVER ()` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT id, sex, LEAD(id, 0, 7) RESPECT NULLS OVER () FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,0,7) OVER () AS `LEAD(id, 0, 7) RESPECT NULLS OVER ()` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT n, id, LEAD(id, 1, 3) OVER
(ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window '<unnamed window>' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`n` AS `n`,`test`.`t1`.`id` AS `id`,lead(`test`.`t1`.`id`,1,3) OVER (ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS `L` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT n, id, LAG(id, 0, n*n) OVER
(ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lag' ignores the frame clause of window '<unnamed window>' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`n` AS `n`,`test`.`t1`.`id` AS `id`,lag(`test`.`t1`.`id`,0,(`test`.`t1`.`n` * `test`.`t1`.`n`)) OVER (ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS `L` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT n, id, LAG(id, 1, n*n) OVER
(ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lag' ignores the frame clause of window '<unnamed window>' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`n` AS `n`,`test`.`t1`.`id` AS `id`,lag(`test`.`t1`.`id`,1,(`test`.`t1`.`n` * `test`.`t1`.`n`)) OVER (ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS `L` from `test`.`t1`
EXPLAIN FORMAT=JSON SELECT n, id, LEAD(id, 1, n*n) OVER
(ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window '<unnamed window>' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`n` AS `n`,`test`.`t1`.`id` AS `id`,lead(`test`.`t1`.`id`,1,(`test`.`t1`.`n` * `test`.`t1`.`n`)) OVER (ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS `L` from `test`.`t1`
Check imcompatible character sets
CREATE TABLE t (c1 CHAR(10) CHARACTER SET big5,
i INT,
c2 VARCHAR(10) CHARACTER SET euckr);
DROP TABLE t;
Check default char set & collation promotion to result
CREATE TABLE t (c1 CHAR(10) CHARACTER SET utf8mb4,
i INT,
c2 VARCHAR(10) CHARACTER SET latin1);
INSERT INTO t VALUES('A', 1, '1');
INSERT INTO t VALUES('A', 3, '3');
INSERT INTO t VALUES(x'F09F90AC' /* dolphin */, 5, null);
INSERT INTO t VALUES('A', 5, null);
INSERT INTO t VALUES(null, 10, '0');
ANALYZE TABLE t;
Table Op Msg_type Msg_text
test.t analyze status OK
Result sets should contain dolphin in columns c1 and l1.
EXPLAIN FORMAT=JSON SELECT c1, c2, LEAD(c1, 0, c2) OVER () l0 FROM t;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.06"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 5,
"rows_produced_per_join": 5,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.56",
"eval_cost": "0.50",
"prefix_cost": "3.06",
"data_read_per_join": "320"
},
"used_columns": [
"c1",
"c2"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`c1` AS `c1`,`test`.`t`.`c2` AS `c2`,lead(`test`.`t`.`c1`,0,convert(`test`.`t`.`c2` using utf8mb4)) OVER () AS `l0` from `test`.`t`
EXPLAIN FORMAT=JSON SELECT c1, c2, LEAD(c1, 1, c2) OVER () l1 FROM t;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.06"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 5,
"rows_produced_per_join": 5,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.56",
"eval_cost": "0.50",
"prefix_cost": "3.06",
"data_read_per_join": "320"
},
"used_columns": [
"c1",
"c2"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`c1` AS `c1`,`test`.`t`.`c2` AS `c2`,lead(`test`.`t`.`c1`,1,convert(`test`.`t`.`c2` using utf8mb4)) OVER () AS `l1` from `test`.`t`
EXPLAIN FORMAT=JSON SELECT c1, c2, LEAD(c2, 1, c1) OVER () l1 FROM t;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.06"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"lead"
]
}
],
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 5,
"rows_produced_per_join": 5,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.56",
"eval_cost": "0.50",
"prefix_cost": "3.06",
"data_read_per_join": "320"
},
"used_columns": [
"c1",
"c2"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`c1` AS `c1`,`test`.`t`.`c2` AS `c2`,lead(convert(`test`.`t`.`c2` using utf8mb4),1,`test`.`t`.`c1`) OVER () AS `l1` from `test`.`t`
Use CREATE TABLE AS to show type of the resulting LEAD function
CREATE TABLE tt AS SELECT LEAD(c1, 0, c2) OVER () c FROM t;
CREATE TABLE ts AS SELECT LEAD(c2, 1, c1) OVER () c FROM t;
Both tables should have c as VARCHAR(10) CHARACTER SET utf8mb4
even though only c1 has that type.
SHOW CREATE TABLE tt;
Table Create Table
tt CREATE TABLE `tt` (
`c` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
SHOW CREATE TABLE ts;
Table Create Table
ts CREATE TABLE `ts` (
`c` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
DROP TABLE t, tt, ts;
Check non obvious type pairs, comparing with IFNULL whose behavior
we emulate when combining types in LEAD/LAG with default value.
static wf
EXPLAIN FORMAT=JSON SELECT id, sex, COUNT(*) OVER w cnt, NTILE(3) OVER w `ntile`,
LEAD(id, 1) OVER w le1,
LAG(id, 1) OVER w la1,
LEAD(id, 100) OVER w le100,
LAG(id, 2, 777) OVER w la2 FROM t1
WINDOW w AS (ORDER BY id);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"count",
"ntile",
"lead",
"lag",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,count(0) OVER `w` AS `cnt`,ntile(3) OVER `w` AS `ntile`,lead(`test`.`t1`.`id`,1) OVER `w` AS `le1`,lag(`test`.`t1`.`id`,1) OVER `w` AS `la1`,lead(`test`.`t1`.`id`,100) OVER `w` AS `le100`,lag(`test`.`t1`.`id`,2,777) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` )
EXPLAIN FORMAT=JSON SELECT id, sex, COUNT(*) OVER w cnt, NTH_VALUE(id, 2) OVER w nth,
LEAD(id, 1) OVER w le1,
LAG(id, 1) OVER w la1,
LEAD(id, 100) OVER w le100,
LAG(id, 2, 777) OVER w la2 FROM t1
WINDOW w as (PARTITION BY sex);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"count",
"nth_value",
"lead",
"lag",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,count(0) OVER `w` AS `cnt`,nth_value(`test`.`t1`.`id`,2) OVER `w` AS `nth`,lead(`test`.`t1`.`id`,1) OVER `w` AS `le1`,lag(`test`.`t1`.`id`,1) OVER `w` AS `la1`,lead(`test`.`t1`.`id`,100) OVER `w` AS `le100`,lag(`test`.`t1`.`id`,2,777) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` )
EXPLAIN FORMAT=JSON SELECT id, sex, COUNT(*) OVER w cnt, NTH_VALUE(id, 2) OVER w nth,
LEAD(id, 1) OVER w le1,
LAG(id, 1) OVER w la1,
LEAD(id, 100) OVER w le100,
LAG(id, 2, 777) OVER w la2 FROM t1
WINDOW w as (PARTITION BY id);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"count",
"nth_value",
"lead",
"lag",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,count(0) OVER `w` AS `cnt`,nth_value(`test`.`t1`.`id`,2) OVER `w` AS `nth`,lead(`test`.`t1`.`id`,1) OVER `w` AS `le1`,lag(`test`.`t1`.`id`,1) OVER `w` AS `la1`,lead(`test`.`t1`.`id`,100) OVER `w` AS `le100`,lag(`test`.`t1`.`id`,2,777) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`id` )
unbuffered
EXPLAIN FORMAT=JSON SELECT id, sex, COUNT(*) OVER w cnt,
LEAD(id, 1) OVER w le1,
LAG(id, 1) OVER w la1,
LEAD(id, 100) OVER w le100,
LAG(id, 2, 777) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY ID ROWS UNBOUNDED PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"count",
"lead",
"lag",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,1) OVER `w` AS `le1`,lag(`test`.`t1`.`id`,1) OVER `w` AS `la1`,lead(`test`.`t1`.`id`,100) OVER `w` AS `le100`,lag(`test`.`t1`.`id`,2,777) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`id` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, sex, COUNT(*) OVER w cnt, NTH_VALUE(id, 2) OVER w nth,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY ID RANGE UNBOUNDED PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"count",
"nth_value",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,count(0) OVER `w` AS `cnt`,nth_value(`test`.`t1`.`id`,2) OVER `w` AS `nth`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`id` RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
test unoptimized path: trick: add DOUBLE type w/SUM which is unoptimized by default
ascending
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY d ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`d` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY d RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`d` RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d ASC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
descending
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY d DESC ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`d` desc ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY d DESC RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`d` desc RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d DESC ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` desc ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` desc ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` desc ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d DESC RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` desc RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` desc RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY d DESC RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d` desc"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` desc RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
Dynamic upper
EXPLAIN FORMAT=JSON SELECT id, sex, COUNT(*) OVER w cnt, NTILE(3) OVER w `ntile`,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1 WINDOW w as (ORDER BY id);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"count",
"ntile",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`sex` AS `sex`,count(0) OVER `w` AS `cnt`,ntile(3) OVER `w` AS `ntile`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` )
optimized path
ascending
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY id ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`id` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY id RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`id` RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id ASC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
descending
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY id DESC ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (PARTITION BY SEX ORDER BY id DESC RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`sex`",
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (PARTITION BY `test`.`t1`.`sex` ORDER BY `test`.`t1`.`id` desc RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id DESC ROWS 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` desc ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id DESC RANGE 2 PRECEDING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` desc RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` desc RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w as (ORDER BY id DESC RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id` desc"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"sum",
"count",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"id",
"sex",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`id`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,`test`.`t1`.`sex` AS `sex`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` desc RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
many nth_value calls on one window, unoptimized path
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(d, 3) OVER w le3,
FIRST_VALUE(d) OVER w fv,
LEAD(d, 1) OVER w le1,
LEAD(d, 2) OVER w le2,
LAG(d, 2) OVER w la2 FROM t1
WINDOW w AS (ORDER BY d ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`d`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"first_value",
"lead",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`d` AS `d`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`d`,3) OVER `w` AS `le3`,first_value(`test`.`t1`.`d`) OVER `w` AS `fv`,lead(`test`.`t1`.`d`,1) OVER `w` AS `le1`,lead(`test`.`t1`.`d`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`d`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`d` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
many nth_value calls on one window, optimized path
EXPLAIN FORMAT=JSON SELECT id, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
LEAD(id, 3) OVER w le3,
FIRST_VALUE(id) OVER w fv,
LEAD(id, 1) OVER w le1,
LEAD(id, 2) OVER w le2,
LAG(id, 2) OVER w la2 FROM t1
WINDOW w AS (ORDER BY id ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.51"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"frame_buffer": {
"using_temporary_table": true
},
"functions": [
"sum",
"count",
"lead",
"first_value",
"lead",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "9.00"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.61",
"eval_cost": "0.90",
"prefix_cost": "3.51",
"data_read_per_join": "216"
},
"used_columns": [
"d",
"id",
"n"
]
}
}
}
}
Warnings:
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,sum(`test`.`t1`.`d`) OVER `w` AS `sum`,count(0) OVER `w` AS `cnt`,lead(`test`.`t1`.`id`,3) OVER `w` AS `le3`,first_value(`test`.`t1`.`id`) OVER `w` AS `fv`,lead(`test`.`t1`.`id`,1) OVER `w` AS `le1`,lead(`test`.`t1`.`id`,2) OVER `w` AS `le2`,lag(`test`.`t1`.`id`,2) OVER `w` AS `la2` from `test`.`t1` window `w` AS (ORDER BY `test`.`t1`.`id` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
DROP TABLE t1;
Check interference with other two pass window functions
CREATE TABLE t(i INT);
INSERT INTO t VALUES (NULL), (1), (2), (3), (3), (4), (5), (6), (6), (7), (8), (9), (10);
ANALYZE TABLE t;
Table Op Msg_type Msg_text
test.t analyze status OK
EXPLAIN FORMAT=JSON SELECT i, PERCENT_RANK() OVER w cd
FROM t WINDOW w AS (ORDER BY i);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "16.96"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`i`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"percent_rank"
]
}
],
"cost_info": {
"sort_cost": "13.00"
},
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 13,
"rows_produced_per_join": 13,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.66",
"eval_cost": "1.30",
"prefix_cost": "3.96",
"data_read_per_join": "104"
},
"used_columns": [
"i"
]
}
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,percent_rank() OVER `w` AS `cd` from `test`.`t` window `w` AS (ORDER BY `test`.`t`.`i` )
EXPLAIN FORMAT=JSON SELECT i, PERCENT_RANK() OVER w cd FROM t
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "16.96"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`i`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"percent_rank"
]
}
],
"cost_info": {
"sort_cost": "13.00"
},
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 13,
"rows_produced_per_join": 13,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.66",
"eval_cost": "1.30",
"prefix_cost": "3.96",
"data_read_per_join": "104"
},
"used_columns": [
"i"
]
}
}
}
}
Warnings:
Note 3599 Window function 'percent_rank' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,percent_rank() OVER `w` AS `cd` from `test`.`t` window `w` AS (ORDER BY `test`.`t`.`i` ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT i, PERCENT_RANK() OVER w cd, NTILE(3) OVER w `ntile`, COUNT(*) OVER w cnt, SUM(i) OVER W `sum` FROM t
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "16.96"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`i`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"percent_rank",
"ntile",
"count",
"sum"
]
}
],
"cost_info": {
"sort_cost": "13.00"
},
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 13,
"rows_produced_per_join": 13,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.66",
"eval_cost": "1.30",
"prefix_cost": "3.96",
"data_read_per_join": "104"
},
"used_columns": [
"i"
]
}
}
}
}
Warnings:
Note 3599 Window function 'percent_rank' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'ntile' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,percent_rank() OVER `w` AS `cd`,ntile(3) OVER `w` AS `ntile`,count(0) OVER `w` AS `cnt`,sum(`test`.`t`.`i`) OVER `w` AS `sum` from `test`.`t` window `w` AS (ORDER BY `test`.`t`.`i` ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
EXPLAIN FORMAT=JSON SELECT i, PERCENT_RANK() OVER w cd, NTILE(3) OVER w `ntile`, COUNT(*) OVER w cnt, SUM(i) OVER W `sum`,
LEAD(i,2) OVER w le2,
LAG(i) OVER w la FROM t
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "16.96"
},
"windowing": {
"windows": [
{
"name": "w",
"using_filesort": true,
"filesort_key": [
"`i`"
],
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"percent_rank",
"ntile",
"count",
"sum",
"lead",
"lag"
]
}
],
"cost_info": {
"sort_cost": "13.00"
},
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 13,
"rows_produced_per_join": 13,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.66",
"eval_cost": "1.30",
"prefix_cost": "3.96",
"data_read_per_join": "104"
},
"used_columns": [
"i"
]
}
}
}
}
Warnings:
Note 3599 Window function 'percent_rank' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'ntile' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lead' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 3599 Window function 'lag' ignores the frame clause of window 'w' and aggregates over the whole partition
Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,percent_rank() OVER `w` AS `cd`,ntile(3) OVER `w` AS `ntile`,count(0) OVER `w` AS `cnt`,sum(`test`.`t`.`i`) OVER `w` AS `sum`,lead(`test`.`t`.`i`,2) OVER `w` AS `le2`,lag(`test`.`t`.`i`) OVER `w` AS `la` from `test`.`t` window `w` AS (ORDER BY `test`.`t`.`i` ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
DROP TABLE t;
Reference in New Issue View Git Blame Copy Permalink