AryaWu/sqlite

1# 2019 July 22#3# The author disclaims copyright to this source code.  In place of4# 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 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20set testprefix filter221 22ifcapable !windowfunc { finish_test ; return }23do_execsql_test 1.0 {24  DROP TABLE IF EXISTS t1;25  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);26  INSERT INTO t1 VALUES27   (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 36do_execsql_test 1.1 {37  SELECT sum(b) FROM t138} {1041}39 40do_execsql_test 1.2 {41  SELECT sum(b) FILTER (WHERE a<10) FROM t142} {141}43 44do_execsql_test 1.3 {45  SELECT count(DISTINCT b) FROM t146} {31}47 48do_execsql_test 1.4 {49  SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t150} {31}51 52do_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 60do_execsql_test 1.6 {61  SELECT min(b),62         min(b),63         max(a+b),64         max(b+a)65  FROM t166  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 5469  7 7 85 85   11 11 79 79   16 16 81 81   24 24 68 68}70 71do_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 t177  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 8180  18 3 66 61   21 3 88 68   23 11 79 79   24 24 68 68}81 82do_execsql_test 1.8 {83  SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t184} {{}}85 86do_execsql_test 1.9 {87  SELECT (a%5) FROM t1 GROUP BY (a%5) 88  HAVING sum(b) FILTER (WHERE b<20) > 3489  ORDER BY 190} {3   4}91 92do_execsql_test 1.10 {93  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb94  FROM t195  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >3496  ORDER BY 197} {3 49   4 46}98 99do_execsql_test 1.11 {100  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb101  FROM t1102  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34103  ORDER BY 2104} {4 46   3 49}105 106do_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 ccc110  FROM t1 GROUP BY (a%5)111  ORDER BY 2112} {2 25 3   0 34 2   1 34 4   4 46 4   3 49 5}113 114do_execsql_test 1.13 {115  SELECT 116    string_agg(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 124do_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 0134  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 i139  }140  set {} {}141} {}142 143do_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 11153  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 156finish_test157