test/windowerr.tcl

   1 # 2018 May 19
   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 #
  12
  13 source [file join [file dirname $argv0] pg_common.tcl]
  14
  15 #=========================================================================
  16
  17 start_test windowerr "2019 March 01"
  18 ifcapable !windowfunc
  19
  20 execsql_test 1.0 {
  21   DROP TABLE IF EXISTS t1;
  22   CREATE TABLE t1(a INTEGER, b INTEGER);
  23   INSERT INTO t1 VALUES(1, 1);
  24   INSERT INTO t1 VALUES(2, 2);
  25   INSERT INTO t1 VALUES(3, 3);
  26   INSERT INTO t1 VALUES(4, 4);
  27   INSERT INTO t1 VALUES(5, 5);
  28 }
  29
  30 foreach {tn frame} {
  31   1 "ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  32   2 "ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
  33
  34   3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  35   4 "ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING"
  36
  37   5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  38   6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
  39
  40   7 "ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
  41
  42   8 "PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
  43 } {
  44   errorsql_test 1.$tn "
  45   SELECT a, sum(b) OVER (
  46     $frame
  47   ) FROM t1 ORDER BY 1
  48   "
  49 }
  50 errorsql_test 2.1 {
  51   SELECT sum( sum(a) OVER () ) FROM t1;
  52 }
  53
  54 errorsql_test 2.2 {
  55   SELECT sum(a) OVER () AS xyz FROM t1 ORDER BY sum(xyz);
  56 }
  57
  58 errorsql_test 3.0 {
  59   SELECT sum(a) OVER win FROM t1
  60   WINDOW win AS (ROWS BETWEEN 'hello' PRECEDING AND 10 FOLLOWING)
  61 }
  62 errorsql_test 3.2 {
  63   SELECT sum(a) OVER win FROM t1
  64   WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING)
  65 }
  66
  67 errorsql_test 3.3 {
  68   SELECT row_number(a) OVER () FROM t1;
  69 }
  70
  71 finish_test
  72