guoxin
/
test-example-projects
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
用于EagleEye3.0 规则集漏报和误报测试的示例项目，项目收集于github和gitee
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
3 Commits
1 Branch
0 Tags
722 MiB
Tag: Branch: Tree: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/r/lead_lag.result

1917 lines
48 KiB
Raw Permalink Normal View History Unescape

first commit
5 months ago
# Test of SQL window functions LEAD/LAG
# ----------------------------------------------------------------------
SET NAMES utf8mb4;
SELECT LEAD(6, 0) OVER ();
LEAD(6, 0) OVER ()
6
SELECT LEAD(NULL, 0) OVER ();
LEAD(NULL, 0) OVER ()
NULL
SELECT LEAD(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LEAD(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
6
SELECT LEAD(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LEAD(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
NULL
SELECT LEAD(6, 1) OVER ();
LEAD(6, 1) OVER ()
NULL
SELECT LEAD(NULL, 1) OVER ();
LEAD(NULL, 1) OVER ()
NULL
SELECT LEAD(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LEAD(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
NULL
SELECT LEAD(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LEAD(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
NULL
SELECT LEAD(6, 1, 7) OVER ();
LEAD(6, 1, 7) OVER ()
7
SELECT LEAD(NULL, 1, 7) OVER ();
LEAD(NULL, 1, 7) OVER ()
7
SELECT LEAD(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LEAD(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
7
SELECT LEAD(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LEAD(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
7
SELECT LAG(6, 0) OVER ();
LAG(6, 0) OVER ()
6
SELECT LAG(NULL, 0) OVER ();
LAG(NULL, 0) OVER ()
NULL
SELECT LAG(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LAG(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
6
SELECT LAG(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LAG(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
NULL
SELECT LAG(6, 1) OVER ();
LAG(6, 1) OVER ()
NULL
SELECT LAG(NULL, 1) OVER ();
LAG(NULL, 1) OVER ()
NULL
SELECT LAG(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LAG(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
NULL
SELECT LAG(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LAG(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
NULL
SELECT LAG(6, 1, 7) OVER ();
LAG(6, 1, 7) OVER ()
7
SELECT LAG(NULL, 1, 7) OVER ();
LAG(NULL, 1, 7) OVER ()
7
SELECT LAG(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LAG(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
7
SELECT LAG(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
LAG(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
7
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);
SELECT id, sex, LEAD(id, -1) RESPECT NULLS OVER () FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1) RESPECT NULLS OVER () FROM t1' at line 1
SELECT id, sex, LEAD(id, 1.2) RESPECT NULLS OVER () FROM t1;
ERROR HY000: Incorrect arguments to lead
SELECT id, sex, LEAD(id, 'a') RESPECT NULLS OVER () FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a') RESPECT NULLS OVER () FROM t1' at line 1
SELECT id, sex, LEAD(id, NULL) RESPECT NULLS OVER () FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL) RESPECT NULLS OVER () FROM t1' at line 1
PREPARE p FROM "SELECT id, sex, LEAD(id, ?) OVER () FROM t1";
SET @p1= 3;
EXECUTE p USING @p1;
id sex LEAD(id, ?) OVER ()
1 M 4
2 F 5
3 F NULL
4 F 10
5 M 10
NULL M 11
10 NULL NULL
10 NULL NULL
11 NULL NULL
SET @p1= -3;
EXECUTE p USING @p1;
id sex LEAD(id, ?) OVER ()
1 M NULL
2 F NULL
3 F NULL
4 F 1
5 M 2
NULL M 3
10 NULL 4
10 NULL 5
11 NULL NULL
SET @p1= 'a';
EXECUTE p USING @p1;
ERROR HY000: Incorrect arguments to lead
PREPARE p FROM "SELECT id, sex, LEAD(id+?, ?, ?) RESPECT NULLS OVER () FROM t1";
SET @p1= 8;
SET @p2= 3;
SET @p3= 7;
EXECUTE p USING @p1, @p2, @p3;
id sex LEAD(id+?, ?, ?) RESPECT NULLS OVER ()
1 M 12
2 F 13
3 F NULL
4 F 18
5 M 18
NULL M 19
10 NULL 7
10 NULL 7
11 NULL 7
EXECUTE p USING @p1, @p2, @p3;
id sex LEAD(id+?, ?, ?) RESPECT NULLS OVER ()
1 M 12
2 F 13
3 F NULL
4 F 18
5 M 18
NULL M 19
10 NULL 7
10 NULL 7
11 NULL 7
DROP PREPARE p;
SELECT id, sex, LEAD(id, 1, 10) IGNORE NULLS OVER () FROM t1;
ERROR 42000: This version of MySQL doesn't yet support 'IGNORE NULLS'
Ok, default semantics:
First without default:
SELECT id, sex, LEAD(id, 1) RESPECT NULLS OVER () FROM t1;
id sex LEAD(id, 1) RESPECT NULLS OVER ()
1 M 2
2 F 3
3 F 4
4 F 5
5 M NULL
NULL M 10
10 NULL 10
10 NULL 11
11 NULL NULL
SELECT id, sex, LAG(id, 1) RESPECT NULLS OVER () FROM t1;
id sex LAG(id, 1) RESPECT NULLS OVER ()
1 M NULL
2 F 1
3 F 2
4 F 3
5 M 4
NULL M 5
10 NULL NULL
10 NULL 10
11 NULL 10
SELECT id, sex, LEAD(id, 0) RESPECT NULLS OVER () FROM t1;
id sex LEAD(id, 0) RESPECT NULLS OVER ()
1 M 1
2 F 2
3 F 3
4 F 4
5 M 5
NULL M NULL
10 NULL 10
10 NULL 10
11 NULL 11
Now with default:
SELECT id, sex, LEAD(id, 1, id) RESPECT NULLS OVER () FROM t1;
id sex LEAD(id, 1, id) RESPECT NULLS OVER ()
1 M 2
2 F 3
3 F 4
4 F 5
5 M NULL
NULL M 10
10 NULL 10
10 NULL 11
11 NULL 11
SELECT id, sex, LAG(id, 1, id) RESPECT NULLS OVER () FROM t1;
id sex LAG(id, 1, id) RESPECT NULLS OVER ()
1 M 1
2 F 1
3 F 2
4 F 3
5 M 4
NULL M 5
10 NULL NULL
10 NULL 10
11 NULL 10
SELECT id, sex, LEAD(id, 0, 7) RESPECT NULLS OVER () FROM t1;
id sex LEAD(id, 0, 7) RESPECT NULLS OVER ()
1 M 1
2 F 2
3 F 3
4 F 4
5 M 5
NULL M NULL
10 NULL 10
10 NULL 10
11 NULL 11
SELECT n, id, LEAD(id, 1, 3) OVER
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
n id L
9 11 10
7 10 10
8 10 5
5 5 4
4 4 3
3 3 2
2 2 1
1 1 NULL
6 NULL 3
SELECT n, id, LAG(id, 0, n*n) OVER
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
n id L
9 11 11
7 10 10
8 10 10
5 5 5
4 4 4
3 3 3
2 2 2
1 1 1
6 NULL NULL
SELECT n, id, LAG(id, 1, n*n) OVER
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
n id L
9 11 81
7 10 11
8 10 10
5 5 10
4 4 5
3 3 4
2 2 3
1 1 2
6 NULL 1
SELECT n, id, LEAD(id, 1, n*n) OVER
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
n id L
9 11 10
7 10 10
8 10 5
5 5 4
4 4 3
3 3 2
2 2 1
1 1 NULL
6 NULL 36
Check imcompatible character sets
CREATE TABLE t (c1 CHAR(10) CHARACTER SET big5,
i INT,
c2 VARCHAR(10) CHARACTER SET euckr);
SELECT c1, c2, LEAD(c1, 0, c2) OVER () l0 FROM t;
ERROR HY000: Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (euckr_korean_ci,IMPLICIT) for operation 'lead'
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');
Result sets should contain dolphin in columns c1 and l1.
SELECT c1, c2, LEAD(c1, 0, c2) OVER () l0 FROM t;
c1 c2 l0
A 1 A
A 3 A
🐬 NULL 🐬
A NULL A
NULL 0 NULL
SELECT c1, c2, LEAD(c1, 1, c2) OVER () l1 FROM t;
c1 c2 l1
A 1 A
A 3 🐬
🐬 NULL A
A NULL NULL
NULL 0 0
SELECT c1, c2, LEAD(c2, 1, c1) OVER () l1 FROM t;
c1 c2 l1
A 1 3
A 3 NULL
🐬 NULL NULL
A NULL 0
NULL 0 NULL
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) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE ts;
Table Create Table
ts CREATE TABLE `ts` (
`c` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t, tt, ts;
Defaults: check non obvious type pairs, comparing with IFNULL
whose type reconciliation behavior we emulate when combining
types in LEAD/LAG with default value.
CREATE TABLE t (c1 VARCHAR(10),
j1 JSON,
g1 POINT,
i1 INT,
b1 BLOB,
d1 DOUBLE,
e1 DECIMAL(5,4),
e2 DECIMAL(5,2));
INSERT INTO t VALUES (null, '[6]', ST_POINTFROMTEXT('POINT(6 6)'), 6, '6', 6.0, 10.0/3, 20.0/3),
('7', null , ST_POINTFROMTEXT('POINT(7 7)'), 7, '7', 7.0, 10.0/3, 20.0/3),
('8', '[8]' , null, 7, '8', 8.0, 10.0/3, 20.0/3),
('9', '[9]' , ST_POINTFROMTEXT('POINT(9 9)'), null, '9', 9.0, 10.0/3, 20.0/3),
('0', '[0]' , ST_POINTFROMTEXT('POINT(0 0)'), 0, null, 0.0, 10.0/3, 20.0/3),
('1', '[1]' , ST_POINTFROMTEXT('POINT(1 1)'), 1, '1', null, 10.0/3, 20.0/3),
('2', '[2]' , ST_POINTFROMTEXT('POINT(2 2)'), 2, '2', 2.0, null, 20.0/3),
('3', '[3]' , ST_POINTFROMTEXT('POINT(3 3)'), 3, '3', 3.0, 10.0/3, null);
Warnings:
Note 1265 Data truncated for column 'e1' at row 1
Note 1265 Data truncated for column 'e2' at row 1
Note 1265 Data truncated for column 'e1' at row 2
Note 1265 Data truncated for column 'e2' at row 2
Note 1265 Data truncated for column 'e1' at row 3
Note 1265 Data truncated for column 'e2' at row 3
Note 1265 Data truncated for column 'e1' at row 4
Note 1265 Data truncated for column 'e2' at row 4
Note 1265 Data truncated for column 'e1' at row 5
Note 1265 Data truncated for column 'e2' at row 5
Note 1265 Data truncated for column 'e1' at row 6
Note 1265 Data truncated for column 'e2' at row 6
Note 1265 Data truncated for column 'e2' at row 7
Note 1265 Data truncated for column 'e1' at row 8
SELECT LEAD(c1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t;
lcj ifn_cj
[6] [6]
NULL 7
[8] 8
[9] 9
[0] 0
[1] 1
[2] 2
[3] 3
SELECT LEAD(j1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t;
lcj ifn_cj
[6] [6]
NULL 7
[8] 8
[9] 9
[0] 0
[1] 1
[2] 2
[3] 3
SELECT LEAD(c1, 100, g1) OVER () lcg, IFNULL(c1, g1) ifn_cg FROM t;
lcg ifn_cg
@@ @@
@@ 7
NULL 8
"@"@ 9
 0
<EFBFBD>?<EFBFBD>? 1
@@ 2
@@ 3
SELECT LEAD(c1, 100, i1) OVER () lci, IFNULL(c1, i1) ifn_ci FROM t;
lci ifn_ci
6 6
7 7
7 8
NULL 9
0 0
1 1
2 2
3 3
SELECT LEAD(c1, 100, b1) OVER () lcb, IFNULL(c1, b1) ifn_cb FROM t;
lcb ifn_cb
6 6
7 7
8 8
9 9
NULL 0
1 1
2 2
3 3
SELECT LEAD(c1, 100, d1) OVER () lcd, IFNULL(c1, d1) ifn_cd FROM t;
lcd ifn_cd
6 6
7 7
8 8
9 9
0 0
NULL 1
2 2
3 3
SELECT LEAD(c1, 100, e1) OVER () lce1, IFNULL(c1, e1) ifn_ce1 FROM t;
lce1 ifn_ce1
3.3333 3.3333
3.3333 7
3.3333 8
3.3333 9
3.3333 0
3.3333 1
NULL 2
3.3333 3
SELECT LEAD(c1, 100, e2) OVER () lce2, IFNULL(c1, e2) ifn_ce2 FROM t;
lce2 ifn_ce2
6.67 6.67
6.67 7
6.67 8
6.67 9
6.67 0
6.67 1
6.67 2
NULL 3
SELECT LEAD(j1, 100, c1) OVER () ljc, IFNULL(j1, c1) ifn_jc FROM t;
ljc ifn_jc
NULL [6]
7 7
8 [8]
9 [9]
0 [0]
1 [1]
2 [2]
3 [3]
SELECT LEAD(j1, 100, g1) OVER () ljg, IFNULL(j1, g1) ifn_jg FROM t;
ljg ifn_jg
@@ [6]
@@ @@
NULL [8]
"@"@ [9]
 [0]
<EFBFBD>?<EFBFBD>? [1]
@@ [2]
@@ [3]
SELECT LEAD(j1, 100, i1) OVER () lji, IFNULL(j1, i1) ifn_ji FROM t;
lji ifn_ji
6 [6]
7 7
7 [8]
NULL [9]
0 [0]
1 [1]
2 [2]
3 [3]
SELECT LEAD(j1, 100, b1) OVER () ljb, IFNULL(j1, b1) ifn_jb FROM t;
ljb ifn_jb
6 [6]
7 7
8 [8]
9 [9]
NULL [0]
1 [1]
2 [2]
3 [3]
SELECT LEAD(j1, 100, d1) OVER () ljd, IFNULL(j1, d1) ifn_jd FROM t;
ljd ifn_jd
6 [6]
7 7
8 [8]
9 [9]
0 [0]
NULL [1]
2 [2]
3 [3]
SELECT LEAD(j1, 100, e1) OVER () lje1, IFNULL(j1, e1) ifn_je1 FROM t;
lje1 ifn_je1
3.3333 [6]
3.3333 3.3333
3.3333 [8]
3.3333 [9]
3.3333 [0]
3.3333 [1]
NULL [2]
3.3333 [3]
SELECT LEAD(j1, 100, e2) OVER () lje2, IFNULL(j1, e2) ifn_je2 FROM t;
lje2 ifn_je2
6.67 [6]
6.67 6.67
6.67 [8]
6.67 [9]
6.67 [0]
6.67 [1]
6.67 [2]
NULL [3]
SELECT LEAD(g1, 100, c1) OVER () lgc, IFNULL(g1, c1) ifn_gc FROM t;
lgc ifn_gc
NULL @@
7 @@
8 8
9 "@"@
0 
1 <EFBFBD>?<EFBFBD>?
2 @@
3 @@
SELECT LEAD(g1, 100, j1) OVER () lgj, IFNULL(g1, j1) ifn_gj FROM t;
lgj ifn_gj
[6] @@
NULL @@
[8] [8]
[9] "@"@
[0] 
[1] <EFBFBD>?<EFBFBD>?
[2] @@
[3] @@
SELECT LEAD(g1, 100, i1) OVER () lgi, IFNULL(g1, i1) ifn_gi FROM t;
lgi ifn_gi
6 @@
7 @@
7 7
NULL "@"@
0 
1 <EFBFBD>?<EFBFBD>?
2 @@
3 @@
SELECT LEAD(g1, 100, b1) OVER () lgb, IFNULL(g1, b1) ifn_gb FROM t;
lgb ifn_gb
6 @@
7 @@
8 8
9 "@"@
NULL 
1 <EFBFBD>?<EFBFBD>?
2 @@
3 @@
SELECT LEAD(g1, 100, d1) OVER () lgd, IFNULL(g1, d1) ifn_gd FROM t;
lgd ifn_gd
6 @@
7 @@
8 8
9 "@"@
0 
NULL <EFBFBD>?<EFBFBD>?
2 @@
3 @@
SELECT LEAD(g1, 100, e1) OVER () lge1, IFNULL(g1, e1) ifn_ge1 FROM t;
lge1 ifn_ge1
3.3333 @@
3.3333 @@
3.3333 3.3333
3.3333 "@"@
3.3333 
3.3333 <EFBFBD>?<EFBFBD>?
NULL @@
3.3333 @@
SELECT LEAD(g1, 100, e2) OVER () lge2, IFNULL(g1, e2) ifn_ge2 FROM t;
lge2 ifn_ge2
6.67 @@
6.67 @@
6.67 6.67
6.67 "@"@
6.67 
6.67 <EFBFBD>?<EFBFBD>?
6.67 @@
NULL @@
SELECT LEAD(i1, 100, c1) OVER () lic, IFNULL(i1, c1) ifn_ic FROM t;
lic ifn_ic
NULL 6
7 7
8 7
9 9
0 0
1 1
2 2
3 3
SELECT LEAD(i1, 100, j1) OVER () lij, IFNULL(i1, j1) ifn_ij FROM t;
lij ifn_ij
[6] 6
NULL 7
[8] 7
[9] [9]
[0] 0
[1] 1
[2] 2
[3] 3
SELECT LEAD(i1, 100, g1) OVER () lig, IFNULL(i1, g1) ifn_ig FROM t;
lig ifn_ig
@@ 6
@@ 7
NULL 7
"@"@ "@"@
 0
<EFBFBD>?<EFBFBD>? 1
@@ 2
@@ 3
SELECT LEAD(i1, 100, b1) OVER () lib, IFNULL(i1, b1) ifn_ib FROM t;
lib ifn_ib
6 6
7 7
8 7
9 9
NULL 0
1 1
2 2
3 3
SELECT LEAD(i1, 100, d1) OVER () lid, IFNULL(i1, d1) ifn_id FROM t;
lid ifn_id
6 6
7 7
8 7
9 9
0 0
NULL 1
2 2
3 3
SELECT LEAD(i1, 100, e1) OVER () lie1, IFNULL(i1, e1) ifn_ie1 FROM t;
lie1 ifn_ie1
3.3333 6.0000
3.3333 7.0000
3.3333 7.0000
3.3333 3.3333
3.3333 0.0000
3.3333 1.0000
NULL 2.0000
3.3333 3.0000
SELECT LEAD(i1, 100, e2) OVER () lie2, IFNULL(i1, e2) ifn_ie2 FROM t;
lie2 ifn_ie2
6.67 6.00
6.67 7.00
6.67 7.00
6.67 6.67
6.67 0.00
6.67 1.00
6.67 2.00
NULL 3.00
SELECT LEAD(b1, 100, c1) OVER () lbc, IFNULL(b1, c1) ifn_bc FROM t;
lbc ifn_bc
NULL 6
7 7
8 8
9 9
0 0
1 1
2 2
3 3
SELECT LEAD(b1, 100, j1) OVER () lbj, IFNULL(b1, j1) ifn_bj FROM t;
lbj ifn_bj
[6] 6
NULL 7
[8] 8
[9] 9
[0] [0]
[1] 1
[2] 2
[3] 3
SELECT LEAD(b1, 100, g1) OVER () lbg, IFNULL(b1, g1) ifn_bg FROM t;
lbg ifn_bg
@@ 6
@@ 7
NULL 8
"@"@ 9
 
<EFBFBD>?<EFBFBD>? 1
@@ 2
@@ 3
SELECT LEAD(b1, 100, i1) OVER () lbi, IFNULL(b1, i1) ifn_bi FROM t;
lbi ifn_bi
6 6
7 7
7 8
NULL 9
0 0
1 1
2 2
3 3
SELECT LEAD(b1, 100, d1) OVER () lbd, IFNULL(b1, d1) ifn_bd FROM t;
lbd ifn_bd
6 6
7 7
8 8
9 9
0 0
NULL 1
2 2
3 3
SELECT LEAD(b1, 100, e1) OVER () lbe1, IFNULL(b1, e1) ifn_be1 FROM t;
lbe1 ifn_be1
3.3333 6
3.3333 7
3.3333 8
3.3333 9
3.3333 3.3333
3.3333 1
NULL 2
3.3333 3
SELECT LEAD(b1, 100, e2) OVER () lbe2, IFNULL(b1, e2) ifn_be2 FROM t;
lbe2 ifn_be2
6.67 6
6.67 7
6.67 8
6.67 9
6.67 6.67
6.67 1
6.67 2
NULL 3
SELECT LEAD(d1, 100, c1) OVER () ldc, IFNULL(d1, c1) ifn_dc FROM t;
ldc ifn_dc
NULL 6
7 7
8 8
9 9
0 0
1 1
2 2
3 3
SELECT LEAD(d1, 100, j1) OVER () ldj, IFNULL(d1, j1) ifn_dj FROM t;
ldj ifn_dj
[6] 6
NULL 7
[8] 8
[9] 9
[0] 0
[1] [1]
[2] 2
[3] 3
SELECT LEAD(d1, 100, g1) OVER () ldg, IFNULL(d1, g1) ifn_dg FROM t;
ldg ifn_dg
@@ 6
@@ 7
NULL 8
"@"@ 9
 0
<EFBFBD>?<EFBFBD>? <EFBFBD>?<EFBFBD>?
@@ 2
@@ 3
SELECT LEAD(d1, 100, i1) OVER () ldi, IFNULL(d1, i1) ifn_di FROM t;
ldi ifn_di
6 6
7 7
7 8
NULL 9
0 0
1 1
2 2
3 3
SELECT LEAD(d1, 100, b1) OVER () ldd, IFNULL(d1, b1) ifn_db FROM t;
ldd ifn_db
6 6
7 7
8 8
9 9
NULL 0
1 1
2 2
3 3
SELECT LEAD(d1, 100, e1) OVER () lde1, IFNULL(d1, e1) ifn_de1 FROM t;
lde1 ifn_de1
3.3333 6
3.3333 7
3.3333 8
3.3333 9
3.3333 0
3.3333 3.3333
NULL 2
3.3333 3
SELECT LEAD(d1, 100, e2) OVER () lde2, IFNULL(d1, e2) ifn_de2 FROM t;
lde2 ifn_de2
6.67 6
6.67 7
6.67 8
6.67 9
6.67 0
6.67 6.67
6.67 2
NULL 3
SELECT LEAD(e1, 100, c1) OVER () le1c, IFNULL(e1, c1) ifn_e1c FROM t;
le1c ifn_e1c
NULL 3.3333
7 3.3333
8 3.3333
9 3.3333
0 3.3333
1 3.3333
2 2
3 3.3333
SELECT LEAD(e1, 100, j1) OVER () le1j, IFNULL(e1, j1) ifn_e1j FROM t;
le1j ifn_e1j
[6] 3.3333
NULL 3.3333
[8] 3.3333
[9] 3.3333
[0] 3.3333
[1] 3.3333
[2] [2]
[3] 3.3333
SELECT LEAD(e1, 100, g1) OVER () le1g, IFNULL(e1, g1) ifn_e1g FROM t;
le1g ifn_e1g
@@ 3.3333
@@ 3.3333
NULL 3.3333
"@"@ 3.3333
 3.3333
<EFBFBD>?<EFBFBD>? 3.3333
@@ @@
@@ 3.3333
SELECT LEAD(e1, 100, i1) OVER () le1i, IFNULL(e1, i1) ifn_e1i FROM t;
le1i ifn_e1i
6.0000 3.3333
7.0000 3.3333
7.0000 3.3333
NULL 3.3333
0.0000 3.3333
1.0000 3.3333
2.0000 2.0000
3.0000 3.3333
SELECT LEAD(e1, 100, b1) OVER () le1d, IFNULL(e1, b1) ifn_e1d FROM t;
le1d ifn_e1d
6 3.3333
7 3.3333
8 3.3333
9 3.3333
NULL 3.3333
1 3.3333
2 2
3 3.3333
SELECT LEAD(e1, 100, d1) OVER () le1d, IFNULL(e1, d1) ifn_e1d FROM t;
le1d ifn_e1d
6 3.3333
7 3.3333
8 3.3333
9 3.3333
0 3.3333
NULL 3.3333
2 2
3 3.3333
SELECT LEAD(e1, 100, e2) OVER () le1e2, IFNULL(e1, e2) ifn_e1e2 FROM t;
le1e2 ifn_e1e2
6.6700 3.3333
6.6700 3.3333
6.6700 3.3333
6.6700 3.3333
6.6700 3.3333
6.6700 3.3333
6.6700 6.6700
NULL 3.3333
SELECT LEAD(e2, 100, c1) OVER () le2c, IFNULL(e2, c1) ifn_e2c FROM t;
le2c ifn_e2c
NULL 6.67
7 6.67
8 6.67
9 6.67
0 6.67
1 6.67
2 6.67
3 3
SELECT LEAD(e2, 100, j1) OVER () le2j, IFNULL(e2, j1) ifn_e2j FROM t;
le2j ifn_e2j
[6] 6.67
NULL 6.67
[8] 6.67
[9] 6.67
[0] 6.67
[1] 6.67
[2] 6.67
[3] [3]
SELECT LEAD(e2, 100, g1) OVER () le2g, IFNULL(e2, g1) ifn_e2g FROM t;
le2g ifn_e2g
@@ 6.67
@@ 6.67
NULL 6.67
"@"@ 6.67
 6.67
<EFBFBD>?<EFBFBD>? 6.67
@@ 6.67
@@ @@
SELECT LEAD(e2, 100, i1) OVER () le2i, IFNULL(e2, i1) ifn_e2i FROM t;
le2i ifn_e2i
6.00 6.67
7.00 6.67
7.00 6.67
NULL 6.67
0.00 6.67
1.00 6.67
2.00 6.67
3.00 3.00
SELECT LEAD(e2, 100, b1) OVER () le2d, IFNULL(e2, b1) ifn_e2d FROM t;
le2d ifn_e2d
6 6.67
7 6.67
8 6.67
9 6.67
NULL 6.67
1 6.67
2 6.67
3 3
SELECT LEAD(e2, 100, d1) OVER () le2d, IFNULL(e2, d1) ifn_e2d FROM t;
le2d ifn_e2d
6 6.67
7 6.67
8 6.67
9 6.67
0 6.67
NULL 6.67
2 6.67
3 3
SELECT LEAD(e2, 100, e1) OVER () le2e1, IFNULL(e2, e1) ifn_e2e1 FROM t;
le2e1 ifn_e2e1
3.3333 6.6700
3.3333 6.6700
3.3333 6.6700
3.3333 6.6700
3.3333 6.6700
3.3333 6.6700
NULL 6.6700
3.3333 3.3333
DROP TABLE t;
static wf
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);
id sex cnt ntile le1 la1 le100 la2
NULL M 1 1 1 NULL NULL 777
1 M 2 1 2 NULL NULL 777
2 F 3 1 3 1 NULL NULL
3 F 4 2 4 2 NULL 1
4 F 5 2 5 3 NULL 2
5 M 6 2 10 4 NULL 3
10 NULL 8 3 10 5 NULL 4
10 NULL 8 3 11 10 NULL 5
11 NULL 9 3 NULL 10 NULL 10
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 ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
id sex cnt nth le1 la1 le100 la2
10 NULL 3 10 10 NULL NULL 777
10 NULL 3 10 11 10 NULL 777
11 NULL 3 10 NULL 10 NULL 10
2 F 3 3 3 NULL NULL 777
3 F 3 3 4 2 NULL 777
4 F 3 3 NULL 3 NULL 2
NULL M 3 1 1 NULL NULL 777
1 M 3 1 5 NULL NULL 777
5 M 3 1 NULL 1 NULL NULL
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 ORDER BY sex ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
id sex cnt nth le1 la1 le100 la2
NULL M 1 NULL NULL NULL NULL 777
1 M 1 NULL NULL NULL NULL 777
2 F 1 NULL NULL NULL NULL 777
3 F 1 NULL NULL NULL NULL 777
4 F 1 NULL NULL NULL NULL 777
5 M 1 NULL NULL NULL NULL 777
10 NULL 2 10 10 NULL NULL 777
10 NULL 2 10 NULL 10 NULL 777
11 NULL 1 NULL NULL NULL NULL 777
unbuffered
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);
id sex cnt le1 la1 le100 la2
10 NULL 1 10 NULL NULL 777
10 NULL 2 11 10 NULL 777
11 NULL 3 NULL 10 NULL 10
2 F 1 3 NULL NULL 777
3 F 2 4 2 NULL 777
4 F 3 NULL 3 NULL 2
NULL M 1 1 NULL NULL 777
1 M 2 5 NULL NULL 777
5 M 3 NULL 1 NULL NULL
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);
id sex cnt nth le2 la2
10 NULL 2 10 11 NULL
10 NULL 2 10 NULL NULL
11 NULL 3 10 NULL 10
2 F 1 NULL 4 NULL
3 F 2 3 NULL NULL
4 F 3 3 NULL 2
NULL M 1 NULL 5 NULL
1 M 2 1 NULL NULL
5 M 3 1 NULL NULL
test unoptimized path: trick: add DOUBLE type w/SUM which is unoptimized by default
ascending
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);
d sum sex le2 la2
10 10 NULL 11 NULL
10 20 NULL NULL NULL
11 31 NULL NULL 10
2 2 F 4 NULL
3 5 F NULL NULL
4 9 F NULL 2
NULL NULL M 5 NULL
1 1 M NULL NULL
5 6 M NULL NULL
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);
d sum sex le2 la2
10 20 NULL 11 NULL
10 20 NULL NULL NULL
11 31 NULL NULL 10
2 2 F 4 NULL
3 5 F NULL NULL
4 9 F NULL 2
NULL NULL M 5 NULL
1 1 M NULL NULL
5 5 M NULL NULL
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);
d sum cnt le2 la2
NULL NULL 1 2 NULL
1 1 2 3 NULL
2 3 3 4 NULL
3 6 3 5 1
4 9 3 10 2
5 12 3 10 3
10 19 3 11 4
10 25 3 NULL 5
11 31 3 NULL 10
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);
d sum cnt le2 la2
NULL NULL 0 2 NULL
1 NULL 1 3 NULL
2 1 2 4 NULL
3 3 2 5 1
4 5 2 10 2
5 7 2 10 3
10 9 2 11 4
10 15 2 NULL 5
11 20 2 NULL 10
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);
d sum cnt le2 la2
NULL 3 2 2 NULL
1 5 2 3 NULL
2 7 2 4 NULL
3 9 2 5 1
4 15 2 10 2
5 20 2 10 3
10 21 2 11 4
10 11 1 NULL 5
11 NULL 0 NULL 10
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);
d sum cnt le2 la2
NULL NULL 1 2 NULL
1 1 1 3 NULL
2 3 2 4 NULL
3 6 3 5 1
4 9 3 10 2
5 12 3 10 3
10 20 2 11 4
10 20 2 NULL 5
11 31 3 NULL 10
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);
d sum cnt le2 la2
NULL NULL 1 2 NULL
1 3 2 3 NULL
2 6 3 4 NULL
3 9 3 5 1
4 12 3 10 2
5 9 2 10 3
10 31 3 11 4
10 31 3 NULL 5
11 31 3 NULL 10
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);
d sum cnt le2 la2
NULL NULL 1 2 NULL
1 5 2 3 NULL
2 7 2 4 NULL
3 9 2 5 1
4 5 1 10 2
5 NULL 0 10 3
10 11 1 11 4
10 11 1 NULL 5
11 NULL 0 NULL 10
descending
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);
d sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 21 2 NULL NULL NULL
10 31 3 NULL NULL 11
4 4 1 F 2 NULL
3 7 2 F NULL NULL
2 9 3 F NULL 4
5 5 1 M NULL NULL
1 6 2 M NULL NULL
NULL 6 3 M NULL 5
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);
d sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 31 3 NULL NULL NULL
10 31 3 NULL NULL 11
4 4 1 F 2 NULL
3 7 2 F NULL NULL
2 9 3 F NULL 4
5 5 1 M NULL NULL
1 1 1 M NULL NULL
NULL NULL 1 M NULL 5
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);
d sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 21 2 NULL 5 NULL
10 31 3 NULL 4 11
5 25 3 M 3 10
4 19 3 F 2 10
3 12 3 F 1 5
2 9 3 F NULL 4
1 6 3 M NULL 3
NULL 3 3 M NULL 2
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);
d sum cnt sex le2 la2
11 NULL 0 NULL 10 NULL
10 11 1 NULL 5 NULL
10 21 2 NULL 4 11
5 20 2 M 3 10
4 15 2 F 2 10
3 9 2 F 1 5
2 7 2 F NULL 4
1 5 2 M NULL 3
NULL 3 2 M NULL 2
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);
d sum cnt sex le2 la2
11 20 2 NULL 10 NULL
10 15 2 NULL 5 NULL
10 9 2 NULL 4 11
5 7 2 M 3 10
4 5 2 F 2 10
3 3 2 F 1 5
2 1 2 F NULL 4
1 NULL 1 M NULL 3
NULL NULL 0 M NULL 2
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);
d sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 31 3 NULL 5 NULL
10 31 3 NULL 4 11
5 5 1 M 3 10
4 9 2 F 2 10
3 12 3 F 1 5
2 9 3 F NULL 4
1 6 3 M NULL 3
NULL NULL 1 M NULL 2
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);
d sum cnt sex le2 la2
11 31 3 NULL 10 NULL
10 31 3 NULL 5 NULL
10 31 3 NULL 4 11
5 9 2 M 3 10
4 12 3 F 2 10
3 9 3 F 1 5
2 6 3 F NULL 4
1 3 2 M NULL 3
NULL NULL 1 M NULL 2
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);
d sum cnt sex le2 la2
11 20 2 NULL 10 NULL
10 NULL 0 NULL 5 NULL
10 NULL 0 NULL 4 11
5 7 2 M 3 10
4 5 2 F 2 10
3 3 2 F 1 5
2 1 1 F NULL 4
1 NULL 0 M NULL 3
NULL NULL 1 M NULL 2
Dynamic upper
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);
id sex cnt ntile le2 la2
NULL M 1 1 2 NULL
1 M 2 1 3 NULL
2 F 3 1 4 NULL
3 F 4 2 5 1
4 F 5 2 10 2
5 M 6 2 10 3
10 NULL 8 3 11 4
10 NULL 8 3 NULL 5
11 NULL 9 3 NULL 10
optimized path
ascending
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);
id sum sex le2 la2
10 10 NULL 11 NULL
10 20 NULL NULL NULL
11 31 NULL NULL 10
2 2 F 4 NULL
3 5 F NULL NULL
4 9 F NULL 2
NULL NULL M 5 NULL
1 1 M NULL NULL
5 6 M NULL NULL
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);
id sum sex le2 la2
10 20 NULL 11 NULL
10 20 NULL NULL NULL
11 31 NULL NULL 10
2 2 F 4 NULL
3 5 F NULL NULL
4 9 F NULL 2
NULL NULL M 5 NULL
1 1 M NULL NULL
5 5 M NULL NULL
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);
id sum cnt le2 la2
NULL NULL 1 2 NULL
1 1 2 3 NULL
2 3 3 4 NULL
3 6 3 5 1
4 9 3 10 2
5 12 3 10 3
10 19 3 11 4
10 25 3 NULL 5
11 31 3 NULL 10
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);
id sum cnt le2 la2
NULL NULL 0 2 NULL
1 NULL 1 3 NULL
2 1 2 4 NULL
3 3 2 5 1
4 5 2 10 2
5 7 2 10 3
10 9 2 11 4
10 15 2 NULL 5
11 20 2 NULL 10
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);
id sum cnt le2 la2
NULL 3 2 2 NULL
1 5 2 3 NULL
2 7 2 4 NULL
3 9 2 5 1
4 15 2 10 2
5 20 2 10 3
10 21 2 11 4
10 11 1 NULL 5
11 NULL 0 NULL 10
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);
id sum cnt le2 la2
NULL NULL 1 2 NULL
1 1 1 3 NULL
2 3 2 4 NULL
3 6 3 5 1
4 9 3 10 2
5 12 3 10 3
10 20 2 11 4
10 20 2 NULL 5
11 31 3 NULL 10
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);
id sum cnt le2 la2
NULL NULL 1 2 NULL
1 3 2 3 NULL
2 6 3 4 NULL
3 9 3 5 1
4 12 3 10 2
5 9 2 10 3
10 31 3 11 4
10 31 3 NULL 5
11 31 3 NULL 10
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);
id sum cnt le2 la2
NULL NULL 1 2 NULL
1 5 2 3 NULL
2 7 2 4 NULL
3 9 2 5 1
4 5 1 10 2
5 NULL 0 10 3
10 11 1 11 4
10 11 1 NULL 5
11 NULL 0 NULL 10
descending
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);
id sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 21 2 NULL NULL NULL
10 31 3 NULL NULL 11
4 4 1 F 2 NULL
3 7 2 F NULL NULL
2 9 3 F NULL 4
5 5 1 M NULL NULL
1 6 2 M NULL NULL
NULL 6 3 M NULL 5
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);
id sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 31 3 NULL NULL NULL
10 31 3 NULL NULL 11
4 4 1 F 2 NULL
3 7 2 F NULL NULL
2 9 3 F NULL 4
5 5 1 M NULL NULL
1 1 1 M NULL NULL
NULL NULL 1 M NULL 5
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);
id sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 21 2 NULL 5 NULL
10 31 3 NULL 4 11
5 25 3 M 3 10
4 19 3 F 2 10
3 12 3 F 1 5
2 9 3 F NULL 4
1 6 3 M NULL 3
NULL 3 3 M NULL 2
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);
id sum cnt sex le2 la2
11 NULL 0 NULL 10 NULL
10 11 1 NULL 5 NULL
10 21 2 NULL 4 11
5 20 2 M 3 10
4 15 2 F 2 10
3 9 2 F 1 5
2 7 2 F NULL 4
1 5 2 M NULL 3
NULL 3 2 M NULL 2
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);
id sum cnt sex le2 la2
11 20 2 NULL 10 NULL
10 15 2 NULL 5 NULL
10 9 2 NULL 4 11
5 7 2 M 3 10
4 5 2 F 2 10
3 3 2 F 1 5
2 1 2 F NULL 4
1 NULL 1 M NULL 3
NULL NULL 0 M NULL 2
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);
id sum cnt sex le2 la2
11 11 1 NULL 10 NULL
10 31 3 NULL 5 NULL
10 31 3 NULL 4 11
5 5 1 M 3 10
4 9 2 F 2 10
3 12 3 F 1 5
2 9 3 F NULL 4
1 6 3 M NULL 3
NULL NULL 1 M NULL 2
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);
id sum cnt sex le2 la2
11 31 3 NULL 10 NULL
10 31 3 NULL 5 NULL
10 31 3 NULL 4 11
5 9 2 M 3 10
4 12 3 F 2 10
3 9 3 F 1 5
2 6 3 F NULL 4
1 3 2 M NULL 3
NULL NULL 1 M NULL 2
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);
id sum cnt sex le2 la2
11 20 2 NULL 10 NULL
10 NULL 0 NULL 5 NULL
10 NULL 0 NULL 4 11
5 7 2 M 3 10
4 5 2 F 2 10
3 3 2 F 1 5
2 1 1 F NULL 4
1 NULL 0 M NULL 3
NULL NULL 1 M NULL 2
many nth_value calls on one window, unoptimized path
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);
d sum cnt le3 fv le1 le2 la2
NULL NULL 1 3 NULL 1 2 NULL
1 1 2 4 NULL 2 3 NULL
2 3 3 5 NULL 3 4 NULL
3 6 3 10 1 4 5 1
4 9 3 10 2 5 10 2
5 12 3 11 3 10 10 3
10 19 3 NULL 4 10 11 4
10 25 3 NULL 5 11 NULL 5
11 31 3 NULL 10 NULL NULL 10
many nth_value calls on one window, optimized path
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);
id sum cnt le3 fv le1 le2 la2
NULL NULL 1 3 NULL 1 2 NULL
1 1 2 4 NULL 2 3 NULL
2 3 3 5 NULL 3 4 NULL
3 6 3 10 1 4 5 1
4 9 3 10 2 5 10 2
5 12 3 11 3 10 10 3
10 19 3 NULL 4 10 11 4
10 25 3 NULL 5 11 NULL 5
11 31 3 NULL 10 NULL NULL 10
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);
SELECT i, PERCENT_RANK() OVER w cd
FROM t WINDOW w AS (ORDER BY i);
i cd
NULL 0
1 0.08333333333333333
2 0.16666666666666666
3 0.25
3 0.25
4 0.4166666666666667
5 0.5
6 0.5833333333333334
6 0.5833333333333334
7 0.75
8 0.8333333333333334
9 0.9166666666666666
10 1
SELECT i, PERCENT_RANK() OVER w cd FROM t
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
i cd
NULL 0
1 0.08333333333333333
2 0.16666666666666666
3 0.25
3 0.25
4 0.4166666666666667
5 0.5
6 0.5833333333333334
6 0.5833333333333334
7 0.75
8 0.8333333333333334
9 0.9166666666666666
10 1
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);
i cd ntile cnt sum
NULL 0 1 3 3
1 0.08333333333333333 1 4 6
2 0.16666666666666666 1 4 9
3 0.25 1 4 12
3 0.25 1 4 15
4 0.4166666666666667 2 4 18
5 0.5 2 4 21
6 0.5833333333333334 2 4 24
6 0.5833333333333334 2 4 27
7 0.75 3 4 30
8 0.8333333333333334 3 4 34
9 0.9166666666666666 3 3 27
10 1 3 2 19
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);
i cd ntile cnt sum le2 la
NULL 0 1 3 3 2 NULL
1 0.08333333333333333 1 4 6 3 NULL
2 0.16666666666666666 1 4 9 3 1
3 0.25 1 4 12 4 2
3 0.25 1 4 15 5 3
4 0.4166666666666667 2 4 18 6 3
5 0.5 2 4 21 6 4
6 0.5833333333333334 2 4 24 7 5
6 0.5833333333333334 2 4 27 8 6
7 0.75 3 4 30 9 6
8 0.8333333333333334 3 4 34 10 7
9 0.9166666666666666 3 3 27 NULL 8
10 1 3 2 19 NULL 9
DROP TABLE t;
Nullability bug fixed
CREATE TABLE t(i INT NOT NULL);
INSERT INTO t VALUES (1), (2), (3), (3), (4), (5), (6);
SELECT LEAD(i, 3) OVER () FROM t;
LEAD(i, 3) OVER ()
3
4
5
6
NULL
NULL
NULL
SELECT LAG(i, 3) OVER () FROM t;
LAG(i, 3) OVER ()
NULL
NULL
NULL
1
2
3
3
DROP TABLE t;
Bug#25835149
SET @savmode=@@SESSION.SQL_MODE;
SET SESSION SQL_MODE='';
CREATE TABLE `test`(
`pk` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`dt` DATETIME DEFAULT NULL,
`ge` GEOMETRY DEFAULT NULL
);
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO `test` VALUES
(1, '2007-05-08 12:10:55', ST_GeomFromText('POINT(1 1)')),
(2, NULL, NULL), (3, NULL, NULL),
(4, '2001-01-18 00:00:00', ST_GeomFromText('POINT(4 4)')),
(5, '2009-11-24 00:00:00', ST_GeomFromText('POINT(5 5)')),
(6, '2001-11-22 21:41:15', ST_GeomFromText('POINT(6 6)')),
(7, NULL, NULL),
(8, '0000-00-00 00:00:00', ST_GeomFromText('POINT(8 8)')),
(9, '2003-05-13 18:03:04', ST_GeomFromText('POINT(9 9)')),
(10, '2008-04-15 09:44:20', ST_GeomFromText('POINT(10 10)')),
(11, '2009-07-15 00:00:00', ST_GeomFromText('POINT(11 11)')),
(12, '2007-04-27 13:53:37', ST_GeomFromText('POINT(12 12)')),
(13, '0000-00-00 00:00:00', ST_GeomFromText('POINT(13 13)')),
(14, '2000-02-02 02:15:28', ST_GeomFromText('POINT(14 14)')),
(15, '2004-06-06 00:00:00', ST_GeomFromText('POINT(15 15)')),
(16, NULL, NULL),
(17, '2002-06-21 00:00:00', ST_GeomFromText('POINT(17 17)')),
(18, '2007-03-23 00:00:00', ST_GeomFromText('POINT(18 18)')),
(19, '2006-10-06 00:00:00', ST_GeomFromText('POINT(19 19)')),
(20, '2008-07-07 00:00:00', ST_GeomFromText('POINT(20 20)'));
SELECT dt, LEAD(dt, 1) OVER w1 `lead`,
CAST(LEAD(ge, 1) OVER w1 AS JSON) geo
FROM test WHERE `pk` = 2 WINDOW w1 AS ();
dt lead geo
NULL NULL NULL
SELECT dt, LAG(dt) OVER w1 `lag`,
CAST(LAG(ge) OVER w1 AS JSON) geo
FROM test WHERE `pk` > 3 WINDOW w1 AS ();
dt lag geo
2001-01-18 00:00:00 NULL NULL
2009-11-24 00:00:00 2001-01-18 00:00:00 {"type": "Point", "coordinates": [4.0, 4.0]}
2001-11-22 21:41:15 2009-11-24 00:00:00 {"type": "Point", "coordinates": [5.0, 5.0]}
NULL 2001-11-22 21:41:15 {"type": "Point", "coordinates": [6.0, 6.0]}
0000-00-00 00:00:00 NULL NULL
2003-05-13 18:03:04 0000-00-00 00:00:00 {"type": "Point", "coordinates": [8.0, 8.0]}
2008-04-15 09:44:20 2003-05-13 18:03:04 {"type": "Point", "coordinates": [9.0, 9.0]}
2009-07-15 00:00:00 2008-04-15 09:44:20 {"type": "Point", "coordinates": [10.0, 10.0]}
2007-04-27 13:53:37 2009-07-15 00:00:00 {"type": "Point", "coordinates": [11.0, 11.0]}
0000-00-00 00:00:00 2007-04-27 13:53:37 {"type": "Point", "coordinates": [12.0, 12.0]}
2000-02-02 02:15:28 0000-00-00 00:00:00 {"type": "Point", "coordinates": [13.0, 13.0]}
2004-06-06 00:00:00 2000-02-02 02:15:28 {"type": "Point", "coordinates": [14.0, 14.0]}
NULL 2004-06-06 00:00:00 {"type": "Point", "coordinates": [15.0, 15.0]}
2002-06-21 00:00:00 NULL NULL
2007-03-23 00:00:00 2002-06-21 00:00:00 {"type": "Point", "coordinates": [17.0, 17.0]}
2006-10-06 00:00:00 2007-03-23 00:00:00 {"type": "Point", "coordinates": [18.0, 18.0]}
2008-07-07 00:00:00 2006-10-06 00:00:00 {"type": "Point", "coordinates": [19.0, 19.0]}
SELECT dt, LEAD(CAST(dt AS TIME), 1) OVER w1
FROM test WHERE `pk` > 3 WINDOW w1 AS ();
dt LEAD(CAST(dt AS TIME), 1) OVER w1
2001-01-18 00:00:00 00:00:00
2009-11-24 00:00:00 21:41:15
2001-11-22 21:41:15 NULL
NULL 00:00:00
0000-00-00 00:00:00 18:03:04
2003-05-13 18:03:04 09:44:20
2008-04-15 09:44:20 00:00:00
2009-07-15 00:00:00 13:53:37
2007-04-27 13:53:37 00:00:00
0000-00-00 00:00:00 02:15:28
2000-02-02 02:15:28 00:00:00
2004-06-06 00:00:00 NULL
NULL 00:00:00
2002-06-21 00:00:00 00:00:00
2007-03-23 00:00:00 00:00:00
2006-10-06 00:00:00 00:00:00
2008-07-07 00:00:00 NULL
SET SESSION SQL_MODE=@savmode;
DROP TABLE `test`;
Bug with missing update of cached example after split_sum_func
for NTH_VALUE
CREATE TABLE t(a INT, b INT, c INT, d INT);
INSERT INTO t VALUES (1,1,1,1), (2,2,4,2), (3,3,9,3);
SELECT SUM(c/d), LEAD(SUM(c/d), 1) OVER (ORDER BY a) FROM t GROUP BY a,b;
SUM(c/d) LEAD(SUM(c/d), 1) OVER (ORDER BY a)
1.0000 2.0000
2.0000 3.0000
3.0000 NULL
SELECT SUM(c/d), LEAD(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) FROM t GROUP BY a,b;
SUM(c/d) LEAD(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a)
1.0000 2.0000
2.0000 3.0000
3.0000 3.0000
SELECT SUM(c/d), LAG(SUM(c/d), 1) OVER (ORDER BY a) FROM t GROUP BY a,b;
SUM(c/d) LAG(SUM(c/d), 1) OVER (ORDER BY a)
1.0000 NULL
2.0000 1.0000
3.0000 2.0000
SELECT SUM(c/d), LAG(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) FROM t GROUP BY a,b;
SUM(c/d) LAG(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a)
1.0000 1.0000
2.0000 1.0000
3.0000 2.0000
SELECT LEAD(SUM(c/d), 2) OVER (ORDER BY a) FROM t GROUP BY a,b;
LEAD(SUM(c/d), 2) OVER (ORDER BY a)
3.0000
NULL
NULL
SELECT 1+LEAD(SUM(c/d), 1) OVER (ORDER BY a) FROM t GROUP BY a,b;
1+LEAD(SUM(c/d), 1) OVER (ORDER BY a)
3.0000
4.0000
NULL
SELECT ROW_NUMBER() OVER () rn,
1 + LEAD(SUM(c/d), 1) OVER (ORDER BY a) le1,
1 + LAG(SUM(c/d), 1) OVER (ORDER BY a) la1,
1 + LEAD(SUM(c/d), 2) OVER (ORDER BY a) le2,
1 + LAG(SUM(c/d), 2) OVER (ORDER BY a) la2,
1 + LEAD(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) le1d,
1 + LAG(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) la1d,
1 + LEAD(SUM(c/d), 2, SUM(c/d)) OVER (ORDER BY a) le2d,
1 + LAG(SUM(c/d), 2, SUM(c/d)) OVER (ORDER BY a) la2d,
1 + LEAD(SUM(c/d), 1, 1 + SUM(c/d)) OVER (ORDER BY a) le1dp,
1 + LAG(SUM(c/d), 1, 1 + SUM(c/d)) OVER (ORDER BY a) la1dp,
1 + LEAD(SUM(c/d), 2, 1 + SUM(c/d)) OVER (ORDER BY a) le2dp,
1 + LAG(SUM(c/d), 2, 1 + SUM(c/d)) OVER (ORDER BY a) la2dp
FROM t GROUP BY a,b;
rn le1 la1 le2 la2 le1d la1d le2d la2d le1dp la1dp le2dp la2dp
1 3.0000 NULL 4.0000 NULL 3.0000 2.0000 4.0000 2.0000 3.0000 3.0000 4.0000 3.0000
2 4.0000 2.0000 NULL NULL 4.0000 2.0000 3.0000 3.0000 4.0000 2.0000 4.0000 4.0000
3 NULL 3.0000 NULL 2.0000 4.0000 3.0000 4.0000 2.0000 5.0000 3.0000 5.0000 2.0000
DROP TABLE t;
#
# Bug#25883997 : WL#9603: SIG11 AT STRING::LENGTH() IN INCLUDE/SQL_STRING.H
#
CREATE TABLE t1 (a int, b char(1), c varchar(1));
INSERT INTO t1 VALUES (1,'s','k'),(NULL,'e','t'),(NULL,'w','i'),(2,'i','k');
SELECT LEAD(a, 7,'abc') OVER w1, LAG(a) OVER w1 FROM t1 WINDOW w1 AS (PARTITION BY a);
LEAD(a, 7,'abc') OVER w1 LAG(a) OVER w1
abc NULL
abc NULL
abc NULL
abc NULL
SELECT a, LEAD(a, 7, 'abc') over w1,
LEAD(a, 2, 'abc') over w1
FROM t1 WINDOW w1 AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
a LEAD(a, 7, 'abc') over w1 LEAD(a, 2, 'abc') over w1
NULL abc 1
NULL abc 2
1 abc abc
2 abc abc
SELECT a, LEAD(a, 1, 'abc') OVER w1,
LEAD(a, 2, 'abc') over w1
FROM t1 WINDOW w1 AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
a LEAD(a, 1, 'abc') OVER w1 LEAD(a, 2, 'abc') over w1
NULL NULL 1
NULL 1 2
1 2 abc
2 abc abc
DROP TABLE t1;
Coverage for LEAD/LAG ::get_time, get_date
SELECT ADDTIME(LEAD(time'18:00:00', 0) OVER (ORDER BY NULL), '01:00:00');
ADDTIME(LEAD(time'18:00:00', 0) OVER (ORDER BY NULL), '01:00:00')
19:00:00
SELECT ADDTIME(LEAD(NULL, 1, time'18:00:00') OVER (ORDER BY NULL), '01:00:00');
ADDTIME(LEAD(NULL, 1, time'18:00:00') OVER (ORDER BY NULL), '01:00:00')
19:00:00
SELECT ADDDATE(LEAD(NULL, 1, date'1955-05-15') OVER (ORDER BY NULL), 1);
ADDDATE(LEAD(NULL, 1, date'1955-05-15') OVER (ORDER BY NULL), 1)
1955-05-16
SELECT ADDDATE(LEAD(date'1955-05-15', 0) OVER (ORDER BY NULL), 1);
ADDDATE(LEAD(date'1955-05-15', 0) OVER (ORDER BY NULL), 1)
1955-05-16
#
# Bug#26100985 WL9603: LEAD IN VIEW GIVES WRONG RESULT
#
CREATE VIEW v AS
SELECT LEAD(d, 2) OVER () FROM
(SELECT 1 AS d UNION SELECT 2 UNION SELECT 3) dt;
SELECT * FROM v;
LEAD(d, 2) OVER ()
3
NULL
NULL
DROP VIEW v;
#
# Bug#26178042 WL#9603: LEAD(<>,0) RESET RESULTS OF OTHER WF'S ON SAME WINDOW
#
CREATE TABLE t1e(a int);
INSERT INTO t1e VALUES(1),(2),(3),(3),(NULL);
OK
SELECT LEAD(a,2) over w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as ();
LEAD(a,2) over w1 LEAD(a,1) OVER w1
3 2
3 3
NULL 3
NULL NULL
NULL NULL
Used to give wrong results for (a,2) and (a,1)
SELECT LEAD(a,2) over w1, LEAD(a,0) OVER w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as ();
LEAD(a,2) over w1 LEAD(a,0) OVER w1 LEAD(a,1) OVER w1
3 1 2
3 2 3
NULL 3 3
NULL 3 NULL
NULL NULL NULL
DROP TABLE t1e;
#
# Bug#26703246 ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP >= 0
# Also solves Bug#26703156.
Assertion used to fail: delsum+(int) y/4-temp >= 0
DO TO_SECONDS(LAG(POINT(2.804466E+307,-2032),75) OVER());
DO IS_IPV4(TIMESTAMP(LAG(-19131 ,188) OVER(),@F));
DO WEEKOFYEAR(LAG(-16726 ,247)RESPECT NULLS OVER());
DO DAYOFYEAR(LEAD(-6653420797178186265 ,29)OVER());
DO YEARWEEK(LEAD(FOUND_ROWS(),250)OVER());
Warnings:
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
DO DAYOFYEAR(LAG('] .| /= ',63) OVER());
DO IFNULL((DAYNAME(LEAD(STDDEV(@F),162) OVER())),(0xD73E));
DO TO_DAYS(LEAD(((LOCATE(0xA812,0xE8DE))OR(@G)),148)OVER());
DO WEEKOFYEAR(LEAD(('5183-10-18 06:15:35.076079')SOUNDS LIKE(0x9E335D89),68)OVER());
DO DAYOFYEAR(LEAD(ROW_COUNT(),177)OVER());
DO MONTHNAME(LAG(' =',74) OVER());
DO SHA(BIN(MONTHNAME(LEAD(UNCOMPRESSED_LENGTH(SHA1("")),224)OVER())));
DO (((NOT(1)))^(HOUR(MONTHNAME(LEAD(DATABASE(),89)OVER()))));
DO MONTHNAME(LEAD(CHARSET(CONVERT((MOD(CURTIME(4),STDDEV(NULL)))USING CP850)),12) OVER());
DO LAST_DAY(LAG(NULL,113) OVER());
DO LAST_DAY(LEAD(-2201 ,98)RESPECT NULLS OVER());
DO OCTET_LENGTH(((LAST_DAY(LEAD(-28178,163)OVER()))OR(COLLATION(@E))));
DO LAST_DAY(LAG('*',36)RESPECT NULLS OVER());
DO LAST_DAY(LEAD( _CP932 '',241)OVER(RANGE UNBOUNDED PRECEDING));
Assertion used to fail: !check_time_mmssff_range(ltime)
DO ((CAST((LEAD(NULL,152) OVER()) AS TIME))*(1));
DO CAST((LAG(SHA(STDDEV('-0E%_')),224) OVER()) AS TIME);
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: '-0E%_'
DO CAST((LAG(_UJIS '8CA} ',144) OVER()) AS TIME);
DO CAST((LAG(-16520,156) OVER()) AS TIME);
DO CAST((LEAD(-6011,202) OVER()) AS TIME);
DO CAST((LEAD(0x45,104) OVER())AS TIME);
DO CAST((LAG(POLYGON( LINESTRING( POINT(4099,17421),
POINT(22259,-5875.0796),
POINT(30179,6542),
POINT(12331,-18840)),
LINESTRING( POINT( 8162.2539,-29332),
POINT(24157,-23393),
POINT(268435459,-26835),
POINT(3.933871E+307,-25808)),
LINESTRING( POINT(30360,-1049),
POINT(15405,5.816757E+307),
POINT( 3717.3555,1.599730E+308),
POINT(-23002,-19077))),
70)
OVER())
AS DATETIME );
DO UNIX_TIMESTAMP(
LAG(ROUND(((UTC_TIME()) OR
((NOT (INET6_ATON(IS_IPV4(((MAKETIME(-10966,
233,
2795.439453))
OR(MAKEDATE(-3030,19)))))))))),
140) OVER());
DO LAST_DAY(SQRT(CAST((LAG(REPEAT('B',64),91)OVER())AS DATETIME)));
We Must disable warning for next statement because we get two instead of
one with -ps protocol vs plain protocol.
DO CAST((LEAD(-197994311,60)OVER())AS DATETIME);
DO IFNULL((NULL ),(UNIX_TIMESTAMP(LEAD(NULL,12)RESPECT NULLS OVER())));
Assertion used to fail: !check_datetime_range(ltime)
DO ((CONVERT((VARIANCE(-18951))USING CP866)) <=
(JSON_ARRAY(LEAD(CAST((35184372088833)AS DATETIME),126)OVER())));
DO ((LAG(LAST_DAY("]<$*_#[DB!^+ : 3"),89) OVER (RANGE UNBOUNDED PRECEDING)) >
(CONVERT(("1985-10-19 03:36:29.304455" -
INTERVAL(0x1446C5A2627FB06D88DC63D66B36DF) DAY_MICROSECOND)
USING BIG5)));
#
# Bug#26740557: WINDOW FUNC + JSON: ASSERTION FAILED:
# FALSE IN ITEM::VAL_JSON
#
SELECT ((MAKETIME(((QUARTER('| !*c>*{/'))<=>
(LAG(JSON_OBJECTAGG('key4',0x067c13d0d0d7d8c8d768aef7)
,7)OVER())),'9236-05-27',0xe2a7d4))^(0x1109));
((MAKETIME(((QUARTER('| !*c>*{/'))<=>
(LAG(JSON_OBJECTAGG('key4',0x067c13d0d0d7d8c8d768aef7)
,7)OVER())),'9236-05-27',0xe2a7d4))^(0x1109))
NULL
Warnings:
Warning 1292 Incorrect datetime value: '| !*c>*{/'
Warning 1292 Incorrect datetime value: '| !*c>*{/'
Warning 1292 Truncated incorrect INTEGER value: '9236-05-27'
SELECT ((LAG(JSON_MERGE_PATCH(1.755913e+308,'{ }'),246)OVER())<=(1));
((LAG(JSON_MERGE_PATCH(1.755913e+308,'{ }'),246)OVER())<=(1))
NULL
SELECT ((LAG(JSON_MERGE_PATCH(1.755913e+308,'{ }'),0)OVER())<=(1));
ERROR 22032: Invalid data type for JSON data in argument 1 to function json_merge_patch; a JSON string or JSON type is required.
SELECT ((QUOTE(JSON_KEYS(LEAD(JSON_KEYS(EXP(-15676),ABS('d0')),
162)OVER())))>=(CONNECTION_ID()));
((QUOTE(JSON_KEYS(LEAD(JSON_KEYS(EXP(-15676),ABS('d0')),
162)OVER())))>=(CONNECTION_ID()))
0
SELECT ((QUOTE(JSON_KEYS(LEAD(JSON_KEYS(EXP(-15676),ABS('d0')),
0)OVER())))>=(CONNECTION_ID()));
ERROR 22032: Invalid data type for JSON data in argument 1 to function json_keys; a JSON string or JSON type is required.
SELECT JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),172)OVER());
JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),172)OVER())
NULL
SELECT JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),0)OVER());
JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),0)OVER())
1
# End of test for Bug#26740557