# 2019 July 2 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # #################################################### # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! #################################################### set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix filter2 ifcapable !windowfunc { finish_test ; return } do_execsql_test 1.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); INSERT INTO t1 VALUES (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27), (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47), (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37), (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29), (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46), (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46), (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23); } {} do_execsql_test 1.1 { SELECT sum(b) FROM t1 } {1041} do_execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 } {141} do_execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 } {31} do_execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 } {31} do_execsql_test 1.5 { SELECT min(b) FILTER (WHERE a>19), min(b) FILTER (WHERE a>0), max(a+b) FILTER (WHERE a>19), max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) FROM t1; } {3 3 88 85} do_execsql_test 1.6 { SELECT min(b), min(b), max(a+b), max(b+a) FROM t1 GROUP BY (a%10) ORDER BY 1, 2, 3, 4; } {3 3 58 58 3 3 66 66 3 3 71 71 3 3 88 88 4 4 61 61 5 5 54 54 7 7 85 85 11 11 79 79 16 16 81 81 24 24 68 68} do_execsql_test 1.7 { SELECT min(b) FILTER (WHERE a>19), min(b) FILTER (WHERE a>0), max(a+b) FILTER (WHERE a>19), max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) FROM t1 GROUP BY (a%10) ORDER BY 1, 2, 3, 4; } {3 3 58 58 3 3 71 39 4 4 38 61 7 7 85 85 11 5 54 45 16 16 81 81 18 3 66 61 21 3 88 68 23 11 79 79 24 24 68 68} do_execsql_test 1.8 { SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1 } {{}} do_execsql_test 1.9 { SELECT (a%5) FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) > 34 ORDER BY 1 } {3 4} do_execsql_test 1.10 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 ORDER BY 1 } {3 49 4 46} do_execsql_test 1.11 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 ORDER BY 2 } {4 46 3 49} do_execsql_test 1.12 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb, count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc FROM t1 GROUP BY (a%5) ORDER BY 2 } {2 25 3 0 34 2 1 34 4 4 46 4 3 49 5} do_execsql_test 1.13 { SELECT string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0), group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1), count(*) FILTER (WHERE b%2!=0), count(*) FILTER (WHERE b%2!=1) FROM t1; } {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} do_test 1.14 { set myres {} foreach r [db eval {SELECT avg(b) FILTER (WHERE b>a), avg(b) FILTER (WHERE b([set r2]+0.0001)} { error "list element [set i] does not match: got=[set r] expected=[set r2]" } incr i } set {} {} } {} do_execsql_test 1.15 { SELECT a/5, sum(b) FILTER (WHERE a%5=0), sum(b) FILTER (WHERE a%5=1), sum(b) FILTER (WHERE a%5=2), sum(b) FILTER (WHERE a%5=3), sum(b) FILTER (WHERE a%5=4) FROM t1 GROUP BY (a/5) ORDER BY 1; } {0 {} 7 3 5 30 1 26 23 27 3 17 2 26 33 25 {} 47 3 36 13 45 31 11 4 36 37 21 22 14 5 16 3 7 29 50 6 38 3 36 12 4 7 46 3 48 23 {} 8 24 5 46 11 {} 9 18 25 15 18 23} finish_test