gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/windowerr.test (about)

     1  # 2019 March 01
     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  ####################################################
    15  # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
    16  ####################################################
    17  
    18  set testdir [file dirname $argv0]
    19  source $testdir/tester.tcl
    20  set testprefix windowerr
    21  
    22  ifcapable !windowfunc { finish_test ; return }
    23  do_execsql_test 1.0 {
    24    DROP TABLE IF EXISTS t1;
    25    CREATE TABLE t1(a INTEGER, b INTEGER);
    26    INSERT INTO t1 VALUES(1, 1);
    27    INSERT INTO t1 VALUES(2, 2);
    28    INSERT INTO t1 VALUES(3, 3);
    29    INSERT INTO t1 VALUES(4, 4);
    30    INSERT INTO t1 VALUES(5, 5);
    31  } {}
    32  
    33  # PG says ERROR:  frame starting offset must not be negative
    34  do_test 1.1 { catch { execsql {
    35    SELECT a, sum(b) OVER (
    36      ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING
    37    ) FROM t1 ORDER BY 1
    38  } } } 1
    39  
    40  # PG says ERROR:  frame ending offset must not be negative
    41  do_test 1.2 { catch { execsql {
    42    SELECT a, sum(b) OVER (
    43      ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING
    44    ) FROM t1 ORDER BY 1
    45  } } } 1
    46  
    47  # PG says ERROR:  invalid preceding or following size in window function
    48  do_test 1.3 { catch { execsql {
    49    SELECT a, sum(b) OVER (
    50      ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING
    51    ) FROM t1 ORDER BY 1
    52  } } } 1
    53  
    54  # PG says ERROR:  invalid preceding or following size in window function
    55  do_test 1.4 { catch { execsql {
    56    SELECT a, sum(b) OVER (
    57      ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING
    58    ) FROM t1 ORDER BY 1
    59  } } } 1
    60  
    61  # PG says ERROR:  frame starting offset must not be negative
    62  do_test 1.5 { catch { execsql {
    63    SELECT a, sum(b) OVER (
    64      ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING
    65    ) FROM t1 ORDER BY 1
    66  } } } 1
    67  
    68  # PG says ERROR:  frame ending offset must not be negative
    69  do_test 1.6 { catch { execsql {
    70    SELECT a, sum(b) OVER (
    71      ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING
    72    ) FROM t1 ORDER BY 1
    73  } } } 1
    74  
    75  # PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
    76  do_test 1.7 { catch { execsql {
    77    SELECT a, sum(b) OVER (
    78      ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
    79    ) FROM t1 ORDER BY 1
    80  } } } 1
    81  
    82  # PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
    83  do_test 1.8 { catch { execsql {
    84    SELECT a, sum(b) OVER (
    85      PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
    86    ) FROM t1 ORDER BY 1
    87  } } } 1
    88  
    89  # PG says ERROR:  aggregate function calls cannot contain window function calls
    90  do_test 2.1 { catch { execsql {
    91    SELECT sum( sum(a) OVER () ) FROM t1;
    92  } } } 1
    93  
    94  # PG says ERROR:  column "xyz" does not exist
    95  do_test 2.2 { catch { execsql {
    96    SELECT sum(a) OVER () AS xyz FROM t1 ORDER BY sum(xyz);
    97  } } } 1
    98  
    99  # PG says ERROR:  invalid input syntax for integer: "hello"
   100  do_test 3.0 { catch { execsql {
   101    SELECT sum(a) OVER win FROM t1
   102    WINDOW win AS (ROWS BETWEEN 'hello' PRECEDING AND 10 FOLLOWING)
   103  } } } 1
   104  
   105  # PG says ERROR:  argument of ROWS must be type bigint, not type bit
   106  do_test 3.2 { catch { execsql {
   107    SELECT sum(a) OVER win FROM t1
   108    WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING)
   109  } } } 1
   110  
   111  # PG says ERROR:  function row_number(integer) does not exist
   112  do_test 3.3 { catch { execsql {
   113    SELECT row_number(a) OVER () FROM t1;
   114  } } } 1
   115  
   116  finish_test