AryaWu/sqlite

1# 2018 May 192#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#12 13source [file join [file dirname $argv0] pg_common.tcl]14 15#=========================================================================16 17 18start_test filter2 "2019 July 2"19 20ifcapable !windowfunc21 22execsql_test 1.0 {23  DROP TABLE IF EXISTS t1;24  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);25  INSERT INTO t1 VALUES26   (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 35execsql_test 1.1 { SELECT sum(b) FROM t1 }36 37execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 }38 39execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 }40 41execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 }42 43execsql_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 51execsql_test 1.6 { 52  SELECT min(b),53         min(b),54         max(a+b),55         max(b+a)56  FROM t157  GROUP BY (a%10)58  ORDER BY 1, 2, 3, 4;59}60 61execsql_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 t167  GROUP BY (a%10)68  ORDER BY 1, 2, 3, 4;69}70 71execsql_test 1.8 { 72  SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t173}74 75execsql_test 1.9 {76  SELECT (a%5) FROM t1 GROUP BY (a%5) 77  HAVING sum(b) FILTER (WHERE b<20) > 3478  ORDER BY 179}80 81execsql_test 1.10 {82  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb83  FROM t184  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >3485  ORDER BY 186}87 88execsql_test 1.11 {89  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb90  FROM t191  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >3492  ORDER BY 293}94 95execsql_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 ccc99  FROM t1 GROUP BY (a%5)100  ORDER BY 2101}102 103execsql_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 112execsql_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 119execsql_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 130finish_test131 132 133