# 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": "", "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": "", "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": "", "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": "", "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": "", "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": "", "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": "", "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 '' 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": "", "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 '' 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": "", "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 '' 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": "", "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 '' 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": "", "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": "", "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": "", "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;