# 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