mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 06:19:42 +00:00
310 lines
7.3 KiB
Plaintext
310 lines
7.3 KiB
Plaintext
# 2019-08-30
|
|
#
|
|
# The author disclaims copyright to this source code. In place of
|
|
# a legal notice, here is a blessing:
|
|
#
|
|
# May you do good and not evil.
|
|
# May you find forgiveness for yourself and forgive others.
|
|
# May you share freely, never taking more than you give.
|
|
#
|
|
#***********************************************************************
|
|
# Test cases for RANGE BETWEEN and especially with NULLS LAST
|
|
#
|
|
|
|
set testdir [file dirname $argv0]
|
|
source $testdir/tester.tcl
|
|
set testprefix windowA
|
|
|
|
ifcapable !windowfunc {
|
|
finish_test
|
|
return
|
|
}
|
|
|
|
do_execsql_test 1.0 {
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), d FLOAT);
|
|
INSERT INTO t1 VALUES
|
|
(1, 'A', 5.4),
|
|
(2, 'B', 5.55),
|
|
(3, 'C', 8.0),
|
|
(4, 'D', 10.25),
|
|
(5, 'E', 10.26),
|
|
(6, 'N', NULL),
|
|
(7, 'N', NULL);
|
|
} {}
|
|
|
|
do_execsql_test 1.1 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 ED \
|
|
4 D 10.25 EDC \
|
|
3 C 8.0 EDC \
|
|
2 B 5.55 CBA \
|
|
1 A 5.4 BA \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
]
|
|
|
|
do_execsql_test 1.2 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
5 E 10.26 ED \
|
|
4 D 10.25 EDC \
|
|
3 C 8.0 EDC \
|
|
2 B 5.55 CBA \
|
|
1 A 5.4 BA \
|
|
]
|
|
|
|
do_execsql_test 1.3 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 EDCBANN \
|
|
4 D 10.25 EDCBANN \
|
|
3 C 8.0 EDCBANN \
|
|
2 B 5.55 CBANN \
|
|
1 A 5.4 BANN \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
]
|
|
|
|
do_execsql_test 1.4 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NNEDCBA \
|
|
7 N NULL NNEDCBA \
|
|
5 E 10.26 EDCBA \
|
|
4 D 10.25 EDCBA \
|
|
3 C 8.0 EDCBA \
|
|
2 B 5.55 CBA \
|
|
1 A 5.4 BA \
|
|
]
|
|
|
|
do_execsql_test 1.5 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 E \
|
|
4 D 10.25 ED \
|
|
3 C 8.0 EDC \
|
|
2 B 5.55 CB \
|
|
1 A 5.4 BA \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
]
|
|
|
|
do_execsql_test 1.6 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
5 E 10.26 E \
|
|
4 D 10.25 ED \
|
|
3 C 8.0 EDC \
|
|
2 B 5.55 CB \
|
|
1 A 5.4 BA \
|
|
]
|
|
|
|
do_execsql_test 2.1 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 ED \
|
|
4 D 10.25 EDC \
|
|
3 C 8.0 EDC \
|
|
2 B 5.55 EDCBA \
|
|
1 A 5.4 EDCBA \
|
|
6 N NULL EDCBANN \
|
|
7 N NULL EDCBANN \
|
|
]
|
|
|
|
do_execsql_test 2.2 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
5 E 10.26 NNED \
|
|
4 D 10.25 NNEDC \
|
|
3 C 8.0 NNEDC \
|
|
2 B 5.55 NNEDCBA \
|
|
1 A 5.4 NNEDCBA \
|
|
]
|
|
|
|
do_execsql_test 2.3 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 EDCBANN \
|
|
4 D 10.25 EDCBANN \
|
|
3 C 8.0 EDCBANN \
|
|
2 B 5.55 EDCBANN \
|
|
1 A 5.4 EDCBANN \
|
|
6 N NULL EDCBANN \
|
|
7 N NULL EDCBANN \
|
|
]
|
|
|
|
do_execsql_test 2.4 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NNEDCBA \
|
|
7 N NULL NNEDCBA \
|
|
5 E 10.26 NNEDCBA \
|
|
4 D 10.25 NNEDCBA \
|
|
3 C 8.0 NNEDCBA \
|
|
2 B 5.55 NNEDCBA \
|
|
1 A 5.4 NNEDCBA \
|
|
]
|
|
|
|
do_execsql_test 2.5 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 E \
|
|
4 D 10.25 ED \
|
|
3 C 8.0 EDC \
|
|
2 B 5.55 EDCB \
|
|
1 A 5.4 EDCBA \
|
|
6 N NULL EDCBANN \
|
|
7 N NULL EDCBANN \
|
|
]
|
|
|
|
do_execsql_test 2.6 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
5 E 10.26 NNE \
|
|
4 D 10.25 NNED \
|
|
3 C 8.0 NNEDC \
|
|
2 B 5.55 NNEDCB \
|
|
1 A 5.4 NNEDCBA \
|
|
]
|
|
|
|
|
|
do_execsql_test 3.1 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 ED \
|
|
4 D 10.25 DC \
|
|
3 C 8.0 C \
|
|
2 B 5.55 BA \
|
|
1 A 5.4 A \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
]
|
|
|
|
do_execsql_test 3.2 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
5 E 10.26 ED \
|
|
4 D 10.25 DC \
|
|
3 C 8.0 C \
|
|
2 B 5.55 BA \
|
|
1 A 5.4 A \
|
|
]
|
|
|
|
do_execsql_test 3.3 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS LAST
|
|
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
ORDER BY +d DESC NULLS LAST, +a;
|
|
} [list \
|
|
5 E 10.26 EDCBANN \
|
|
4 D 10.25 DCBANN \
|
|
3 C 8.0 CBANN \
|
|
2 B 5.55 BANN \
|
|
1 A 5.4 ANN \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
]
|
|
|
|
do_execsql_test 3.4 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NNEDCBA \
|
|
7 N NULL NNEDCBA \
|
|
5 E 10.26 EDCBA \
|
|
4 D 10.25 DCBA \
|
|
3 C 8.0 CBA \
|
|
2 B 5.55 BA \
|
|
1 A 5.4 A \
|
|
]
|
|
|
|
do_execsql_test 4.0 {
|
|
SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
|
|
WINDOW w1 AS
|
|
(ORDER BY d DESC NULLS FIRST
|
|
RANGE BETWEEN 2.50 PRECEDING AND 0.5 PRECEDING)
|
|
ORDER BY +d DESC NULLS FIRST, +a;
|
|
} [list \
|
|
6 N NULL NN \
|
|
7 N NULL NN \
|
|
5 E 10.26 {} \
|
|
4 D 10.25 {} \
|
|
3 C 8.0 ED \
|
|
2 B 5.55 C \
|
|
1 A 5.4 {} \
|
|
]
|
|
|
|
|
|
finish_test
|