test/windowA.test

   1 # 2019-08-30
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 # Test cases for RANGE BETWEEN and especially with NULLS LAST
  12 #
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix windowA
  17
  18 ifcapable !windowfunc {
  19   finish_test
  20   return
  21 }
  22
  23 do_execsql_test 1.0 {
  24   CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), d FLOAT);
  25   INSERT INTO t1 VALUES
  26    (1, 'A', 5.4),
  27    (2, 'B', 5.55),
  28    (3, 'C', 8.0),
  29    (4, 'D', 10.25),
  30    (5, 'E', 10.26),
  31    (6, 'N', NULL),
  32    (7, 'N', NULL);
  33 } {}
  34
  35 do_execsql_test 1.1 {
  36   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
  37   WINDOW w1 AS
  38      (ORDER BY d DESC NULLS LAST
  39       RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
  40   ORDER BY +d DESC NULLS LAST, +a;
  41 } [list \
  42   5 E 10.26 ED   \
  43   4 D 10.25 EDC  \
  44   3 C   8.0 EDC  \
  45   2 B  5.55 CBA  \
  46   1 A   5.4 BA   \
  47   6 N  NULL NN   \
  48   7 N  NULL NN   \
  49 ]
  50
  51 do_execsql_test 1.2 {
  52   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
  53   WINDOW w1 AS
  54      (ORDER BY d DESC NULLS FIRST
  55       RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
  56   ORDER BY +d DESC NULLS FIRST, +a;
  57 } [list \
  58   6 N  NULL NN   \
  59   7 N  NULL NN   \
  60   5 E 10.26 ED   \
  61   4 D 10.25 EDC  \
  62   3 C   8.0 EDC  \
  63   2 B  5.55 CBA  \
  64   1 A   5.4 BA   \
  65 ]
  66
  67 do_execsql_test 1.3 {
  68   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
  69   WINDOW w1 AS
  70      (ORDER BY d DESC NULLS LAST
  71       RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
  72   ORDER BY +d DESC NULLS LAST, +a;
  73 } [list \
  74   5 E 10.26 EDCBANN  \
  75   4 D 10.25 EDCBANN  \
  76   3 C   8.0 EDCBANN  \
  77   2 B  5.55 CBANN    \
  78   1 A   5.4 BANN     \
  79   6 N  NULL NN       \
  80   7 N  NULL NN       \
  81 ]
  82
  83 do_execsql_test 1.4 {
  84   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
  85   WINDOW w1 AS
  86      (ORDER BY d DESC NULLS FIRST
  87       RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
  88   ORDER BY +d DESC NULLS FIRST, +a;
  89 } [list \
  90   6 N  NULL NNEDCBA  \
  91   7 N  NULL NNEDCBA  \
  92   5 E 10.26 EDCBA    \
  93   4 D 10.25 EDCBA    \
  94   3 C   8.0 EDCBA    \
  95   2 B  5.55 CBA      \
  96   1 A   5.4 BA       \
  97 ]
  98
  99 do_execsql_test 1.5 {
 100   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 101   WINDOW w1 AS
 102      (ORDER BY d DESC NULLS LAST
 103       RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
 104   ORDER BY +d DESC NULLS LAST, +a;
 105 } [list \
 106   5 E 10.26 E    \
 107   4 D 10.25 ED   \
 108   3 C   8.0 EDC  \
 109   2 B  5.55 CB   \
 110   1 A   5.4 BA   \
 111   6 N  NULL NN   \
 112   7 N  NULL NN   \
 113 ]
 114
 115 do_execsql_test 1.6 {
 116   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 117   WINDOW w1 AS
 118      (ORDER BY d DESC NULLS FIRST
 119       RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
 120   ORDER BY +d DESC NULLS FIRST, +a;
 121 } [list \
 122   6 N  NULL NN   \
 123   7 N  NULL NN   \
 124   5 E 10.26 E    \
 125   4 D 10.25 ED   \
 126   3 C   8.0 EDC  \
 127   2 B  5.55 CB   \
 128   1 A   5.4 BA   \
 129 ]
 130
 131 do_execsql_test 2.1 {
 132   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 133   WINDOW w1 AS
 134      (ORDER BY d DESC NULLS LAST
 135       RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
 136   ORDER BY +d DESC NULLS LAST, +a;
 137 } [list \
 138   5 E 10.26 ED       \
 139   4 D 10.25 EDC      \
 140   3 C   8.0 EDC      \
 141   2 B  5.55 EDCBA    \
 142   1 A   5.4 EDCBA    \
 143   6 N  NULL EDCBANN  \
 144   7 N  NULL EDCBANN  \
 145 ]
 146
 147 do_execsql_test 2.2 {
 148   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 149   WINDOW w1 AS
 150      (ORDER BY d DESC NULLS FIRST
 151       RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
 152   ORDER BY +d DESC NULLS FIRST, +a;
 153 } [list \
 154   6 N  NULL NN         \
 155   7 N  NULL NN         \
 156   5 E 10.26 NNED       \
 157   4 D 10.25 NNEDC      \
 158   3 C   8.0 NNEDC      \
 159   2 B  5.55 NNEDCBA    \
 160   1 A   5.4 NNEDCBA    \
 161 ]
 162
 163 do_execsql_test 2.3 {
 164   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 165   WINDOW w1 AS
 166      (ORDER BY d DESC NULLS LAST
 167       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
 168   ORDER BY +d DESC NULLS LAST, +a;
 169 } [list \
 170   5 E 10.26 EDCBANN  \
 171   4 D 10.25 EDCBANN  \
 172   3 C   8.0 EDCBANN  \
 173   2 B  5.55 EDCBANN  \
 174   1 A   5.4 EDCBANN  \
 175   6 N  NULL EDCBANN  \
 176   7 N  NULL EDCBANN  \
 177 ]
 178
 179 do_execsql_test 2.4 {
 180   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 181   WINDOW w1 AS
 182      (ORDER BY d DESC NULLS FIRST
 183       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
 184   ORDER BY +d DESC NULLS FIRST, +a;
 185 } [list \
 186   6 N  NULL NNEDCBA  \
 187   7 N  NULL NNEDCBA  \
 188   5 E 10.26 NNEDCBA  \
 189   4 D 10.25 NNEDCBA  \
 190   3 C   8.0 NNEDCBA  \
 191   2 B  5.55 NNEDCBA  \
 192   1 A   5.4 NNEDCBA  \
 193 ]
 194
 195 do_execsql_test 2.5 {
 196   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 197   WINDOW w1 AS
 198      (ORDER BY d DESC NULLS LAST
 199       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 200   ORDER BY +d DESC NULLS LAST, +a;
 201 } [list \
 202   5 E 10.26 E        \
 203   4 D 10.25 ED       \
 204   3 C   8.0 EDC      \
 205   2 B  5.55 EDCB     \
 206   1 A   5.4 EDCBA    \
 207   6 N  NULL EDCBANN  \
 208   7 N  NULL EDCBANN  \
 209 ]
 210
 211 do_execsql_test 2.6 {
 212   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 213   WINDOW w1 AS
 214      (ORDER BY d DESC NULLS FIRST
 215       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 216   ORDER BY +d DESC NULLS FIRST, +a;
 217 } [list \
 218   6 N  NULL NN       \
 219   7 N  NULL NN       \
 220   5 E 10.26 NNE      \
 221   4 D 10.25 NNED     \
 222   3 C   8.0 NNEDC    \
 223   2 B  5.55 NNEDCB   \
 224   1 A   5.4 NNEDCBA  \
 225 ]
 226
 227
 228 do_execsql_test 3.1 {
 229   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 230   WINDOW w1 AS
 231      (ORDER BY d DESC NULLS LAST
 232       RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
 233   ORDER BY +d DESC NULLS LAST, +a;
 234 } [list \
 235   5 E 10.26 ED       \
 236   4 D 10.25 DC       \
 237   3 C   8.0 C        \
 238   2 B  5.55 BA       \
 239   1 A   5.4 A        \
 240   6 N  NULL NN       \
 241   7 N  NULL NN       \
 242 ]
 243
 244 do_execsql_test 3.2 {
 245   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 246   WINDOW w1 AS
 247      (ORDER BY d DESC NULLS FIRST
 248       RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
 249   ORDER BY +d DESC NULLS FIRST, +a;
 250 } [list \
 251   6 N  NULL NN       \
 252   7 N  NULL NN       \
 253   5 E 10.26 ED       \
 254   4 D 10.25 DC       \
 255   3 C   8.0 C        \
 256   2 B  5.55 BA       \
 257   1 A   5.4 A        \
 258 ]
 259
 260 do_execsql_test 3.3 {
 261   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 262   WINDOW w1 AS
 263      (ORDER BY d DESC NULLS LAST
 264       RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
 265   ORDER BY +d DESC NULLS LAST, +a;
 266 } [list \
 267   5 E 10.26 EDCBANN  \
 268   4 D 10.25 DCBANN   \
 269   3 C   8.0 CBANN    \
 270   2 B  5.55 BANN     \
 271   1 A   5.4 ANN      \
 272   6 N  NULL NN       \
 273   7 N  NULL NN       \
 274 ]
 275
 276 do_execsql_test 3.4 {
 277   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 278   WINDOW w1 AS
 279      (ORDER BY d DESC NULLS FIRST
 280       RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
 281   ORDER BY +d DESC NULLS FIRST, +a;
 282 } [list \
 283   6 N  NULL NNEDCBA  \
 284   7 N  NULL NNEDCBA  \
 285   5 E 10.26 EDCBA    \
 286   4 D 10.25 DCBA     \
 287   3 C   8.0 CBA      \
 288   2 B  5.55 BA       \
 289   1 A   5.4 A        \
 290 ]
 291
 292 do_execsql_test 4.0 {
 293   SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
 294   WINDOW w1 AS
 295      (ORDER BY d DESC NULLS FIRST
 296       RANGE BETWEEN 2.50 PRECEDING AND 0.5 PRECEDING)
 297   ORDER BY +d DESC NULLS FIRST, +a;
 298 } [list \
 299   6 N  NULL NN  \
 300   7 N  NULL NN  \
 301   5 E 10.26 {}  \
 302   4 D 10.25 {}  \
 303   3 C   8.0 ED  \
 304   2 B  5.55 C   \
 305   1 A   5.4 {}  \
 306 ]
 307
 308
 309 finish_test