AryaWu/sqlite

1# 2018 May 82#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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix filter117 18ifcapable !windowfunc {19  finish_test20  return21}22 23do_execsql_test 1.0 {24  CREATE TABLE t1(a);25  CREATE INDEX i1 ON t1(a);26  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);27}28 29do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 4530do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 1031 32do_execsql_test 1.3 { 33  SELECT sum(a) FILTER( WHERE a>9 ),34         sum(a) FILTER( WHERE a>8 ),35         sum(a) FILTER( WHERE a>7 ),36         sum(a) FILTER( WHERE a>6 ),37         sum(a) FILTER( WHERE a>5 ),38         sum(a) FILTER( WHERE a>4 ),39         sum(a) FILTER( WHERE a>3 ),40         sum(a) FILTER( WHERE a>2 ),41         sum(a) FILTER( WHERE a>1 ),42         sum(a) FILTER( WHERE a>0 )43  FROM t1;44} {{} 9 17 24 30 35 39 42 44 45}45 46do_execsql_test 1.4 {47  SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t148} {8}49 50do_execsql_test 1.5 {51  SELECT min(a) FILTER (WHERE a>4) FROM t152} {5}53 54do_execsql_test 1.6 {55  SELECT count(*) FILTER (WHERE a!=5) FROM t156} {8}57 58do_execsql_test 1.7 {59  SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1;60} {4 5}61 62do_execsql_test 1.8 {63  CREATE VIEW vv AS 64  SELECT sum(a) FILTER( WHERE a>9 ),65         sum(a) FILTER( WHERE a>8 ),66         sum(a) FILTER( WHERE a>7 ),67         sum(a) FILTER( WHERE a>6 ),68         sum(a) FILTER( WHERE a>5 ),69         sum(a) FILTER( WHERE a>4 ),70         sum(a) FILTER( WHERE a>3 ),71         sum(a) FILTER( WHERE a>2 ),72         sum(a) FILTER( WHERE a>1 ),73         sum(a) FILTER( WHERE a>0 )74  FROM t1;75  SELECT * FROM vv;76} {{} 9 17 24 30 35 39 42 44 45}77 78 79#-------------------------------------------------------------------------80# Test some errors:81#82#   .1 FILTER on a non-aggregate function,83#   .2 Window function in FILTER clause,84#   .3 Aggregate function in FILTER clause,85#86reset_db87do_execsql_test 2.0 {88  CREATE TABLE t1(a);89  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);90}91 92do_catchsql_test 2.1 {93  SELECT upper(a) FILTER (WHERE a=1) FROM t194} {1 {FILTER may not be used with non-aggregate upper()}}95 96do_catchsql_test 2.2 {97  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t198} {1 {misuse of window function max()}}99 100do_catchsql_test 2.3 {101  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1102} {1 {misuse of aggregate function count()}}103 104#-------------------------------------------------------------------------105reset_db106do_execsql_test 3.0 {107  CREATE TABLE t1(a,b);108  INSERT INTO t1 VALUES(1, 1);109}110do_execsql_test 3.1 {111  SELECT b, max(a) FILTER (WHERE b='x') FROM t1;112} {1 {}}113 114do_execsql_test 3.2 {115  CREATE TABLE t2(a, b, c);116  INSERT INTO t2 VALUES(1, 2, 3);117  INSERT INTO t2 VALUES(1, 3, 4);118  INSERT INTO t2 VALUES(2, 5, 6);119  INSERT INTO t2 VALUES(2, 7, 8);120}121do_execsql_test 3.3 {122  SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;123} {1 3 {} 2 6 {}}124 125do_execsql_test 3.4 {126  DELETE FROM t2;127  INSERT INTO t2 VALUES(1, 5, 'x');128  INSERT INTO t2 VALUES(1, 2, 3);129  INSERT INTO t2 VALUES(1, 4, 'x');130  INSERT INTO t2 VALUES(2, 5, 6);131  INSERT INTO t2 VALUES(2, 7, 8);132}133do_execsql_test 3.5 {134  SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;135} {1 x 5 2 6 {}}136 137#-------------------------------------------------------------------------138reset_db139do_execsql_test 4.0 {140  CREATE TABLE t1(a, b, c);141  INSERT INTO t1 VALUES('a', 0, 5);142  INSERT INTO t1 VALUES('a', 1, 10);143  INSERT INTO t1 VALUES('a', 0, 15);144 145  INSERT INTO t1 VALUES('b', 0, 5);146  INSERT INTO t1 VALUES('b', 1, 1000);147  INSERT INTO t1 VALUES('b', 0, 5);148 149  INSERT INTO t1 VALUES('c', 0, 1);150  INSERT INTO t1 VALUES('c', 1, 2);151  INSERT INTO t1 VALUES('c', 0, 3);152}153 154do_execsql_test 4.1 {155  SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY h;156} {2.0 5.0 10.0}157do_execsql_test 4.2 {158  SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY (h+1.0);159} {2.0 5.0 10.0}160do_execsql_test 4.3 {161  SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);162} {c 2.0 a 10.0 b 5.0}163do_execsql_test 4.4 {164  SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2165} {c 2.0 b 5.0 a 10.0}166 167#-------------------------------------------------------------------------168reset_db169do_execsql_test 5.0 {170  CREATE TABLE t1(a, b);171  INSERT INTO t1 VALUES(1, 2);172  INSERT INTO t1 VALUES(1, 3);173}174 175do_execsql_test 5.1 {176  SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1) 177} {1}178 179do_execsql_test 5.2 {180  SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1) 181} {1 1}182 183do_execsql_test 5.3 {184  SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1) 185} {0 1}186 187#-------------------------------------------------------------------------188reset_db189do_execsql_test 6.0 {190  CREATE TABLE t1(a,b);191  INSERT INTO t1 VALUES(1,1);192  INSERT INTO t1 VALUES(2,2);193  CREATE TABLE t2(x,y);194  INSERT INTO t2 VALUES(1,1);195}196 197do_execsql_test 6.1 {198  SELECT (SELECT COUNT(a) FILTER(WHERE x) FROM t2) FROM t1;199} {1 1}200do_execsql_test 6.2 {201  SELECT (SELECT COUNT(a+x) FROM t2) FROM t1;202} {1 1}203do_execsql_test 6.3 {204  SELECT (SELECT COUNT(a) FROM t2) FROM t1;205} {2}206 207#-------------------------------------------------------------------------208reset_db209do_execsql_test 7.0 {210  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);211  INSERT INTO t1 VALUES(321, 100000);212  INSERT INTO t1 VALUES(111, 110000);213  INSERT INTO t1 VALUES(444, 120000);214  INSERT INTO t1 VALUES(222, 130000);215}216 217do_execsql_test 7.1 {218  SELECT max(a), max(a) FILTER (WHERE b<12345), b FROM t1;219} {220  444 {} 120000221}222 223# 2023-02-17 dbsqlfuzz 4f8e0de6e272bbbb3e1b41cb5aea31e0b47297e3224# count() with FILTER clause using the COUNTOFVIEW optimization.225#226reset_db227do_execsql_test 8.0 {228  CREATE TABLE t0(c0 INT);229  CREATE TABLE t1a(a INTEGER PRIMARY KEY, b TEXT);230  INSERT INTO t1a VALUES(1,'one'),(2,NULL),(3,'three');231  CREATE TABLE t1b(c INTEGER PRIMARY KEY, d TEXT);232  INSERT INTO t1b VALUES(4,'four'),(5,NULL),(6,'six');233  CREATE VIEW t1 AS SELECT a, b FROM t1a UNION ALL SELECT c, d FROM t1b;234  SELECT count()FILTER(WHERE b IS NULL) FROM t1;235} 2236 237finish_test238