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

     1  # 2019 July 2
     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 filter2
    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 PRIMARY KEY, b INTEGER);
    26    INSERT INTO t1 VALUES
    27     (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27),
    28     (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47),
    29     (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37),
    30     (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29),
    31     (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46),
    32     (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46),
    33     (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23);
    34  } {}
    35  
    36  do_execsql_test 1.1 {
    37    SELECT sum(b) FROM t1
    38  } {1041}
    39  
    40  do_execsql_test 1.2 {
    41    SELECT sum(b) FILTER (WHERE a<10) FROM t1
    42  } {141}
    43  
    44  do_execsql_test 1.3 {
    45    SELECT count(DISTINCT b) FROM t1
    46  } {31}
    47  
    48  do_execsql_test 1.4 {
    49    SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1
    50  } {31}
    51  
    52  do_execsql_test 1.5 {
    53    SELECT min(b) FILTER (WHERE a>19),
    54           min(b) FILTER (WHERE a>0),
    55           max(a+b) FILTER (WHERE a>19),
    56           max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
    57    FROM t1;
    58  } {3 3 88 85}
    59  
    60  do_execsql_test 1.6 {
    61    SELECT min(b),
    62           min(b),
    63           max(a+b),
    64           max(b+a)
    65    FROM t1
    66    GROUP BY (a%10)
    67    ORDER BY 1, 2, 3, 4;
    68  } {3 3 58 58   3 3 66 66   3 3 71 71   3 3 88 88   4 4 61 61   5 5 54 54
    69    7 7 85 85   11 11 79 79   16 16 81 81   24 24 68 68}
    70  
    71  do_execsql_test 1.7 {
    72    SELECT min(b) FILTER (WHERE a>19),
    73           min(b) FILTER (WHERE a>0),
    74           max(a+b) FILTER (WHERE a>19),
    75           max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
    76    FROM t1
    77    GROUP BY (a%10)
    78    ORDER BY 1, 2, 3, 4;
    79  } {3 3 58 58   3 3 71 39   4 4 38 61   7 7 85 85   11 5 54 45   16 16 81 81
    80    18 3 66 61   21 3 88 68   23 11 79 79   24 24 68 68}
    81  
    82  do_execsql_test 1.8 {
    83    SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1
    84  } {{}}
    85  
    86  do_execsql_test 1.9 {
    87    SELECT (a%5) FROM t1 GROUP BY (a%5) 
    88    HAVING sum(b) FILTER (WHERE b<20) > 34
    89    ORDER BY 1
    90  } {3   4}
    91  
    92  do_execsql_test 1.10 {
    93    SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
    94    FROM t1
    95    GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
    96    ORDER BY 1
    97  } {3 49   4 46}
    98  
    99  do_execsql_test 1.11 {
   100    SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
   101    FROM t1
   102    GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
   103    ORDER BY 2
   104  } {4 46   3 49}
   105  
   106  do_execsql_test 1.12 {
   107    SELECT (a%5), 
   108      sum(b) FILTER (WHERE b<20) AS bbb,
   109      count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc
   110    FROM t1 GROUP BY (a%5)
   111    ORDER BY 2
   112  } {2 25 3   0 34 2   1 34 4   4 46 4   3 49 5}
   113  
   114  do_execsql_test 1.13 {
   115    SELECT 
   116      group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0),
   117      group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1),
   118      count(*) FILTER (WHERE b%2!=0),
   119      count(*) FILTER (WHERE b%2!=1)
   120    FROM t1;
   121  } {7_3_5_23_27_3_17_33_25_47_13_45_31_11_37_21_3_7_29_3_3_23_5_11_25_15_23 30_26_26_36_36_22_14_16_50_38_36_12_4_46_48_24_46_18_18 27 19}
   122  
   123  
   124  do_test 1.14 {
   125    set myres {}
   126    foreach r [db eval {SELECT 
   127      avg(b) FILTER (WHERE b>a),
   128      avg(b) FILTER (WHERE b<a)
   129    FROM t1 GROUP BY (a%2) ORDER BY 1,2;}] {
   130      lappend myres [format %.4f [set r]]
   131    }
   132    set res2 {30.8333 13.7273 31.4167 13.0000}
   133    set i 0
   134    foreach r [set myres] r2 [set res2] {
   135      if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
   136        error "list element [set i] does not match: got=[set r] expected=[set r2]"
   137      }
   138      incr i
   139    }
   140    set {} {}
   141  } {}
   142  
   143  do_execsql_test 1.15 {
   144    SELECT 
   145      a/5,
   146      sum(b) FILTER (WHERE a%5=0),
   147      sum(b) FILTER (WHERE a%5=1),
   148      sum(b) FILTER (WHERE a%5=2),
   149      sum(b) FILTER (WHERE a%5=3),
   150      sum(b) FILTER (WHERE a%5=4)
   151    FROM t1 GROUP BY (a/5) ORDER BY 1;
   152  } {0 {} 7 3 5 30   1 26 23 27 3 17   2 26 33 25 {} 47   3 36 13 45 31 11
   153    4 36 37 21 22 14   5 16 3 7 29 50   6 38 3 36 12 4   7 46 3 48 23 {}
   154    8 24 5 46 11 {}   9 18 25 15 18 23}
   155  
   156  finish_test