| # 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 |