github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/filter2.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  
    18  start_test filter2 "2019 July 2"
    19  
    20  ifcapable !windowfunc
    21  
    22  execsql_test 1.0 {
    23    DROP TABLE IF EXISTS t1;
    24    CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
    25    INSERT INTO t1 VALUES
    26     (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27),
    27     (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47),
    28     (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37),
    29     (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29),
    30     (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46),
    31     (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46),
    32     (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23);
    33  }
    34  
    35  execsql_test 1.1 { SELECT sum(b) FROM t1 }
    36  
    37  execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 }
    38  
    39  execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 }
    40  
    41  execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 }
    42  
    43  execsql_test 1.5 { 
    44    SELECT min(b) FILTER (WHERE a>19),
    45           min(b) FILTER (WHERE a>0),
    46           max(a+b) FILTER (WHERE a>19),
    47           max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
    48    FROM t1;
    49  }
    50  
    51  execsql_test 1.6 { 
    52    SELECT min(b),
    53           min(b),
    54           max(a+b),
    55           max(b+a)
    56    FROM t1
    57    GROUP BY (a%10)
    58    ORDER BY 1, 2, 3, 4;
    59  }
    60  
    61  execsql_test 1.7 { 
    62    SELECT min(b) FILTER (WHERE a>19),
    63           min(b) FILTER (WHERE a>0),
    64           max(a+b) FILTER (WHERE a>19),
    65           max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
    66    FROM t1
    67    GROUP BY (a%10)
    68    ORDER BY 1, 2, 3, 4;
    69  }
    70  
    71  execsql_test 1.8 { 
    72    SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1
    73  }
    74  
    75  execsql_test 1.9 {
    76    SELECT (a%5) FROM t1 GROUP BY (a%5) 
    77    HAVING sum(b) FILTER (WHERE b<20) > 34
    78    ORDER BY 1
    79  }
    80  
    81  execsql_test 1.10 {
    82    SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
    83    FROM t1
    84    GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
    85    ORDER BY 1
    86  }
    87  
    88  execsql_test 1.11 {
    89    SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
    90    FROM t1
    91    GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
    92    ORDER BY 2
    93  }
    94  
    95  execsql_test 1.12 {
    96    SELECT (a%5), 
    97      sum(b) FILTER (WHERE b<20) AS bbb,
    98      count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc
    99    FROM t1 GROUP BY (a%5)
   100    ORDER BY 2
   101  }
   102  
   103  execsql_test 1.13 {
   104    SELECT 
   105      string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0),
   106      string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1),
   107      count(*) FILTER (WHERE b%2!=0),
   108      count(*) FILTER (WHERE b%2!=1)
   109    FROM t1;
   110  }
   111  
   112  execsql_float_test 1.14 {
   113    SELECT 
   114      avg(b) FILTER (WHERE b>a),
   115      avg(b) FILTER (WHERE b<a)
   116    FROM t1 GROUP BY (a%2) ORDER BY 1,2;
   117  }
   118  
   119  execsql_test 1.15 {
   120    SELECT 
   121      a/5,
   122      sum(b) FILTER (WHERE a%5=0),
   123      sum(b) FILTER (WHERE a%5=1),
   124      sum(b) FILTER (WHERE a%5=2),
   125      sum(b) FILTER (WHERE a%5=3),
   126      sum(b) FILTER (WHERE a%5=4)
   127    FROM t1 GROUP BY (a/5) ORDER BY 1;
   128  }
   129  
   130  finish_test
   131  
   132