github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/windowerr.tcl (about)

     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