AryaWu/sqlite

1# 2018-08-042#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#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16source $testdir/malloc_common.tcl17set testprefix countofview18 19do_execsql_test 1.0 {20  CREATE TABLE t2(c);21  CREATE TABLE t3(f);22 23  INSERT INTO t2 VALUES(1), (2);24  INSERT INTO t3 VALUES(3);25}26 27do_execsql_test 1.1 {28  select c from t2 union all select f from t3 limit 1 offset 129} {2}30 31do_execsql_test 1.2 {32  select count(*) from (33    select c from t2 union all select f from t3 limit 1 offset 134  )35} {1}36 37do_execsql_test 1.3 {38  select count(*) from (39    select c from t2 union all select f from t340  )41} {3}42 43# 2019-05-1544do_execsql_test 2.0 {45  CREATE TABLE t1(x);46  INSERT INTO t1 VALUES(1),(99),('abc');47  CREATE VIEW v1(x,y) AS SELECT x,1 FROM t1 UNION ALL SELECT x,2 FROM t1;48  SELECT count(*) FROM v1 WHERE x<>1;49} {4}50do_execsql_test 2.1 {51  SELECT count(*) FROM v1 GROUP BY y;52} {3 3}53 54# 2023-03-01 dbsqlfuzz ef8623915d843b150c159166ee4548c78cc6895a55# count-of-view should not apply to CTEs.56#57ifcapable progress {58  proc progress_stop args {return 1}59  db progress 1000 progress_stop60  do_catchsql_test 3.1 {61    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c)62    SELECT count(*) FROM c;63  } {1 interrupted}64}65 66# 2023-03-07 dbsqlfuzz 23d782160b71c3f8f535ccb2da313dfc8eb8c63167#68do_execsql_test 4.1 {69  DROP TABLE t1;70  DROP TABLE t2;71  DROP TABLE t3;72  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);73  INSERT INTO t1 VALUES(4,'four');74  CREATE TABLE t2(c INTEGER PRIMARY KEY, d TEXT);75  CREATE VIEW t3 AS SELECT a, b FROM t1 UNION ALL SELECT c, d FROM t2;76  SELECT count(*) FROM t3 ORDER BY sum(a);77} 178 79# 2023-03-31 dbsqlfuzz 6a107e3055bd22afab31cfddabc2d9d54fcbaf6980# Having clauses should disqualify count-of-view81#82reset_db83do_execsql_test 5.1 {84  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);85  INSERT INTO t1 VALUES(1,'one'),(4,'four');86  CREATE TABLE t2(c INTEGER PRIMARY KEY, d TEXT);87  INSERT INTO t2 VALUES(2,'two'),(5,'five');88  CREATE VIEW t3 AS SELECT a, b FROM t1 UNION ALL SELECT c, d FROM t2;89  SELECT count(*) FROM t3 HAVING count(*)>0;90} 491do_execsql_test 5.2 {92  SELECT count(*) FROM t3 HAVING count(*)>5;93} {}94do_execsql_test 5.3 {95  SELECT count(*) FROM t3 HAVING max(b)>'mmm';96} 497do_execsql_test 5.4 {98  SELECT count(*) FROM t3 HAVING min(b)>'mmm';99} {}100do_execsql_test 5.5 {101  SELECT count(*) FROM (102     SELECT a, max(b) FROM t1 HAVING a<100 UNION ALL SELECT c, d FROM t2103  )104} 3105 106 107finish_test108