test/window9.test

   1 # 2019 June 8
   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 # This file implements regression tests for SQLite library.
  12 #
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix window9
  17
  18 ifcapable !windowfunc {
  19   finish_test
  20   return
  21 }
  22
  23 do_execsql_test 1.0 {
  24   CREATE TABLE fruits(
  25      name TEXT COLLATE NOCASE,
  26      color TEXT COLLATE NOCASE
  27   );
  28 }
  29
  30 do_execsql_test 1.1 {
  31   INSERT INTO fruits (name, color) VALUES ('apple', 'RED');
  32   INSERT INTO fruits (name, color) VALUES ('APPLE', 'yellow');
  33   INSERT INTO fruits (name, color) VALUES ('pear', 'YELLOW');
  34   INSERT INTO fruits (name, color) VALUES ('PEAR', 'green');
  35 }
  36
  37 do_execsql_test 1.2 {
  38   SELECT name, color, dense_rank() OVER (ORDER BY name) FROM fruits;
  39 } {
  40   apple RED    1
  41   APPLE yellow 1
  42   pear  YELLOW 2
  43   PEAR  green  2
  44 }
  45
  46 do_execsql_test 1.3 {
  47   SELECT name, color,
  48     dense_rank() OVER (PARTITION BY name ORDER BY color)
  49   FROM fruits;
  50 } {
  51   apple RED    1
  52   APPLE yellow 2
  53   PEAR green   1
  54   pear YELLOW  2
  55 }
  56
  57 do_execsql_test 1.4 {
  58   SELECT name, color,
  59     dense_rank() OVER (ORDER BY name),
  60     dense_rank() OVER (PARTITION BY name ORDER BY color)
  61   FROM fruits;
  62 } {
  63   apple RED    1 1
  64   APPLE yellow 1 2
  65   PEAR  green  2 1
  66   pear  YELLOW 2 2
  67 }
  68
  69 do_execsql_test 1.5 {
  70   SELECT name, color,
  71     dense_rank() OVER (ORDER BY name),
  72     dense_rank() OVER (PARTITION BY name ORDER BY color)
  73   FROM fruits ORDER BY color;
  74 } {
  75   PEAR  green  2 1
  76   apple RED    1 1
  77   APPLE yellow 1 2
  78   pear  YELLOW 2 2
  79 }
  80
  81 do_execsql_test 2.0 {
  82   CREATE TABLE t1(a BLOB, b INTEGER, c COLLATE nocase);
  83   INSERT INTO t1 VALUES(1, 2, 'abc');
  84   INSERT INTO t1 VALUES(3, 4, 'ABC');
  85 }
  86
  87 do_execsql_test 2.1.1 {
  88   SELECT c=='Abc' FROM t1
  89 } {1     1}
  90 do_execsql_test 2.1.2 {
  91   SELECT c=='Abc', rank() OVER (ORDER BY b) FROM t1
  92 } {1 1   1 2}
  93
  94 do_execsql_test 2.2.1 {
  95   SELECT b=='2' FROM t1
  96 } {1     0}
  97 do_execsql_test 2.2.2 {
  98   SELECT b=='2', rank() OVER (ORDER BY a) FROM t1
  99 } {1 1   0 2}
 100
 101 #-------------------------------------------------------------------------
 102 reset_db
 103 do_execsql_test 3.0 {
 104   CREATE TABLE t1(a);
 105   CREATE TABLE t2(a,b,c);
 106 }
 107
 108 do_execsql_test 3.1 {
 109   SELECT EXISTS(SELECT 1 FROM t1 ORDER BY sum(a) OVER ()) FROM t1;
 110 }
 111
 112 do_execsql_test 3.2 {
 113   SELECT sum(a) OVER () FROM t2
 114    ORDER BY EXISTS(SELECT 1 FROM t2 ORDER BY sum(a) OVER ());
 115 }
 116
 117 do_catchsql_test 3.3 {
 118   SELECT a, sum(a) OVER (ORDER BY a DESC) FROM t2
 119   ORDER BY EXISTS(
 120     SELECT 1 FROM t2 ORDER BY sum(a) OVER (ORDER BY a)
 121   ) OVER (ORDER BY a);
 122 } {1 {near "OVER": syntax error}}
 123
 124 do_catchsql_test 3.4 {
 125   SELECT y, y+1, y+2 FROM (
 126       SELECT c IN (
 127         SELECT min(a) OVER (),
 128         (abs(row_number() OVER())+22)/19,
 129         max(a) OVER () FROM t1
 130         ) AS y FROM t2
 131       );
 132 } {1 {sub-select returns 3 columns - expected 1}}
 133
 134 #-------------------------------------------------------------------------
 135 reset_db
 136 do_execsql_test 4.0 {
 137   CREATE TABLE t1(a, b TEXT);
 138   INSERT INTO t1 VALUES('A', 1), ('A', 2), ('2', 1), ('2', 2);
 139 }
 140
 141 do_execsql_test 4.1.1 {
 142   SELECT b, b=count(*), '1,2'                   FROM t1 GROUP BY b;
 143 } {1 0 1,2 2 1 1,2}
 144 do_execsql_test 4.1.2 {
 145   SELECT b, b=count(*), group_concat(b) OVER () FROM t1 GROUP BY b;
 146 } {1 0 1,2 2 1 1,2}
 147
 148 #--------------------------------------------------------------------------
 149 reset_db
 150 do_execsql_test 5.0 {
 151   CREATE TABLE t1(a, b, c, d, e);
 152   CREATE INDEX i1 ON t1(a, b, c, d, e);
 153 }
 154
 155 foreach {tn sql} {
 156   1 {
 157     SELECT
 158       sum(e) OVER (),
 159       sum(e) OVER (ORDER BY a),
 160       sum(e) OVER (PARTITION BY a ORDER BY b),
 161       sum(e) OVER (PARTITION BY a, b ORDER BY c),
 162       sum(e) OVER (PARTITION BY a, b, c ORDER BY d)
 163     FROM t1;
 164   }
 165   2 {
 166     SELECT sum(e) OVER (PARTITION BY a ORDER BY b) FROM t1 ORDER BY a;
 167   }
 168 } {
 169   do_test 5.1.$tn {
 170     execsql "EXPLAIN QUERY PLAN $sql"
 171   } {~/ORDER/}
 172 }
 173
 174 #-------------------------------------------------------------------------
 175 reset_db
 176 do_execsql_test 6.0 {
 177   CREATE TABLE t0(c0);
 178   INSERT INTO t0(c0) VALUES (0);
 179 }
 180
 181 do_execsql_test 6.1 {
 182   SELECT * FROM t0 WHERE
 183   EXISTS (
 184     SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
 185   ) >=1 AND
 186   EXISTS (
 187     SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
 188   ) <=1;
 189 } {0}
 190
 191 do_execsql_test 6.2 {
 192   SELECT * FROM t0 WHERE EXISTS (
 193     SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
 194   )
 195   BETWEEN 1 AND 1;
 196 } {0}
 197
 198 #-------------------------------------------------------------------------
 199 reset_db
 200 do_execsql_test 7.0 {
 201   DROP TABLE IF EXISTS t1;
 202   CREATE TABLE t1(x, y);
 203   INSERT INTO t1 VALUES(10, 1);
 204   INSERT INTO t1 VALUES(20, 2);
 205   INSERT INTO t1 VALUES(3, 3);
 206   INSERT INTO t1 VALUES(2, 4);
 207   INSERT INTO t1 VALUES(1, 5);
 208 } {}
 209
 210
 211 do_execsql_test 7.1 {
 212   SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z
 213 } {
 214   7.2 8.75 10.0 11.0 15.0
 215 }
 216
 217 do_execsql_test 7.2 {
 218   SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY (z IS y);
 219 } {
 220   10.0 15.0 11.0 8.75 7.2
 221 }
 222
 223 do_execsql_test 7.3 {
 224   SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY (y IS z);
 225 } {
 226   10.0 15.0 11.0 8.75 7.2
 227 }
 228
 229 do_execsql_test 7.4 {
 230   SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY z + 0.0;
 231 } {
 232   7.2 8.75 10.0 11.0 15.0
 233 }
 234
 235 #-------------------------------------------------------------------------
 236 reset_db
 237 do_execsql_test 8.1.1 {
 238   CREATE TABLE t1(a, b);
 239   INSERT INTO t1 VALUES(1, 2), (3, 4);
 240   SELECT min( sum(a) ) OVER () FROM t1;
 241 } {4}
 242
 243 do_execsql_test 8.1.2 {
 244   SELECT min( sum(a) ) OVER () FROM t1 GROUP BY a;
 245 } {1 1}
 246
 247 do_execsql_test 8.2 {
 248   CREATE VIEW v1 AS
 249     SELECT 0 AS x
 250       UNION
 251     SELECT count() OVER() FROM (SELECT 0)
 252     ORDER BY 1
 253   ;
 254 }
 255
 256 do_catchsql_test 8.3 {
 257   SELECT min( max((SELECT x FROM v1)) ) OVER()
 258 } {0 0}
 259
 260 do_execsql_test 8.4 {
 261   SELECT(
 262       SELECT x UNION
 263       SELECT sum( avg((SELECT x FROM v1)) ) OVER()
 264   )
 265   FROM v1;
 266 } {0.0 0.0}
 267
 268 #--------------------------------------------------------------------------
 269 reset_db
 270 do_execsql_test 9.0 {
 271   CREATE TABLE t1(a, b, c);
 272   INSERT INTO t1 VALUES(NULL,'bb',356);
 273   INSERT INTO t1 VALUES('CB','aa',158);
 274   INSERT INTO t1 VALUES('BB','aa',399);
 275   INSERT INTO t1 VALUES('FF','bb',938);
 276 }
 277
 278 do_catchsql_test 9.1 {
 279   SELECT sum(c) OVER (
 280     ORDER BY c RANGE BETWEEN 0 PRECEDING AND '-700' PRECEDING
 281   )
 282   FROM t1
 283 } {1 {frame ending offset must be a non-negative number}}
 284
 285 finish_test