AryaWu/sqlite

1# The author disclaims copyright to this source code.  In place of2# a legal notice, here is a blessing:3#4#    May you do good and not evil.5#    May you find forgiveness for yourself and forgive others.6#    May you share freely, never taking more than you give.7#8#***********************************************************************9# This file implements regression tests for SQLite library.  The10# focus of this file is testing compute SELECT statements and nested11# views.12#13# $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $14 15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix select719 20ifcapable compound {21 22# A 3-way INTERSECT.  Ticket #87523ifcapable tempdb {24  do_test select7-1.1 {25    execsql {26      create temp table t1(x);27      insert into t1 values('amx');28      insert into t1 values('anx');29      insert into t1 values('amy');30      insert into t1 values('bmy');31      select * from t1 where x like 'a__'32        intersect select * from t1 where x like '_m_'33        intersect select * from t1 where x like '__x';34    }35  } {amx}36}37 38 39# Nested views do not handle * properly.  Ticket #826.40#41ifcapable view {42do_test select7-2.1 {43  execsql {44    CREATE TABLE x(id integer primary key, a TEXT NULL);45    INSERT INTO x (a) VALUES ('first');46    CREATE TABLE tempx(id integer primary key, a TEXT NULL);47    INSERT INTO tempx (a) VALUES ('t-first');48    CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;49    CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;50    CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;51    SELECT * FROM tv2;52  }53} {1 1}54} ;# ifcapable view55 56} ;# ifcapable compound57 58# Do not allow GROUP BY without an aggregate. Ticket #1039.59#60# Change: force any query with a GROUP BY clause to be processed as61# an aggregate query, whether it contains aggregates or not.62#63ifcapable subquery {64  # do_test select7-3.1 {65  #   catchsql {66  #     SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name67  #   }68  # } {1 {GROUP BY may only be used on aggregate queries}}69  do_test select7-3.1 {70    catchsql {71      SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name72    }73  } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]74}75 76# Ticket #2018 - Make sure names are resolved correctly on all77# SELECT statements of a compound subquery.78#79ifcapable {subquery && compound} {80  do_test select7-4.1 {81    execsql {82      CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x);83      CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name);84    85      SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 86           SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 87           EXCEPT 88           SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'89      );90    }91  } {}92  do_test select7-4.2 {93    execsql {94      INSERT INTO photo VALUES(1,1);95      INSERT INTO photo VALUES(2,2);96      INSERT INTO photo VALUES(3,3);97      INSERT INTO tag VALUES(11,1,'one');98      INSERT INTO tag VALUES(12,1,'two');99      INSERT INTO tag VALUES(21,1,'one-b');100      SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 101           SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 102           EXCEPT 103           SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'104      );105    }106  } {2 3}107}108 109# ticket #2347110#111ifcapable {subquery && compound} {112  do_test select7-5.1 {113    catchsql {114      CREATE TABLE t2(a,b);115      SELECT 5 IN (SELECT a,b FROM t2);116    }117  } {1 {sub-select returns 2 columns - expected 1}}118  do_test select7-5.2 {119    catchsql {120      SELECT 5 IN (SELECT * FROM t2);121    }122  } {1 {sub-select returns 2 columns - expected 1}}123  do_test select7-5.3 {124    catchsql {125      SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2);126    }127  } {1 {sub-select returns 2 columns - expected 1}}128  do_test select7-5.4 {129    catchsql {130      SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2);131    }132  } {1 {sub-select returns 2 columns - expected 1}}133}134 135# Verify that an error occurs if you have too many terms on a136# compound select statement.137#138if {[clang_sanitize_address]==0} {139  ifcapable compound {140    if {$SQLITE_MAX_COMPOUND_SELECT>0} {141      set sql {SELECT 0}142      set result 0143        for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} {144          append sql " UNION ALL SELECT $i"145            lappend result $i146        }147      do_test select7-6.1 {148        catchsql $sql149      } [list 0 $result]150      append sql { UNION ALL SELECT 99999999}151      do_test select7-6.2 {152        catchsql $sql153      } {1 {too many terms in compound SELECT}}154    }155  }156}157 158# https://issues.chromium.org/issues/358174302159# Need to support an unlimited number of terms in a VALUES clause, even160# if some of those terms contain double-quoted string literals.161#162do_execsql_test select7-6.5 {163  DROP TABLE IF EXISTS t1;164  CREATE TABLE t1(a,b,c);165}166sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 10167sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 0168do_catchsql_test select7-6.6 {169  INSERT INTO t1 VALUES170    (NULL,0,""),  (X'',0.0,0.0),  (X'',X'',""),  (0.0,0.0,""),  (NULL,NULL,0.0),171    (0,"",0),  (0.0,X'',0),  ("",X'',0.0),  (0.0,X'',NULL),  (0,NULL,""),172    (0,"",NULL),  (0.0,NULL,X''),  ("",X'',NULL),  (NULL,0,""),173    (0,NULL,0),  (X'',X'',0.0);174} {1 {no such column: "" - should this be a string literal in single-quotes?}}175do_execsql_test select7-6.7 {176  SELECT count(*) FROM t1;177} {0}178sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1179do_catchsql_test select7-6.8 {180  INSERT INTO t1 VALUES181    (NULL,0,""),  (X'',0.0,0.0),  (X'',X'',""),  (0.0,0.0,""),  (NULL,NULL,0.0),182    (0,"",0),  (0.0,X'',0),  ("",X'',0.0),  (0.0,X'',NULL),  (0,NULL,""),183    (0,"",NULL),  (0.0,NULL,X''),  ("",X'',NULL),  (NULL,0,""),184    (0,NULL,0),  (X'',X'',0.0);185} {0 {}}186do_execsql_test select7-6.9 {187  SELECT count(*) FROM t1;188} {16}189 190# This block of tests verifies that bug aa92c76cd4 is fixed.191#192do_test select7-7.1 {193  execsql {194    CREATE TABLE t3(a REAL);195    INSERT INTO t3 VALUES(44.0);196    INSERT INTO t3 VALUES(56.0);197  }198} {}199do_test select7-7.2 {200  execsql {201    pragma vdbe_trace = 0;202    SELECT (CASE WHEN a=0 THEN 0 ELSE (a + 25) / 50 END) AS categ, count(*)203    FROM t3 GROUP BY categ204  }205} {1.38 1 1.62 1}206do_test select7-7.3 {207  execsql {208    CREATE TABLE t4(a REAL);209    INSERT INTO t4 VALUES( 2.0 );210    INSERT INTO t4 VALUES( 3.0 );211  }212} {}213do_test select7-7.4 {214  execsql {215    SELECT (CASE WHEN a=0 THEN 'zero' ELSE a/2 END) AS t FROM t4 GROUP BY t;216  }217} {1.0 1.5}218do_test select7-7.5 {219  execsql { SELECT a=0, typeof(a) FROM t4 }220} {0 real 0 real}221do_test select7-7.6 {222  execsql { SELECT a=0, typeof(a) FROM t4 GROUP BY a }223} {0 real 0 real}224 225do_test select7-7.7 {226  execsql {227    CREATE TABLE t5(a TEXT, b INT);228    INSERT INTO t5 VALUES(123, 456);229    SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;230  }231} {text 123}232 233do_execsql_test 8.0 { 234  CREATE TABLE t01(x, y);235  CREATE TABLE t02(x, y);236}237 238do_catchsql_test 8.1 {239  SELECT * FROM (240    SELECT * FROM t01 UNION SELECT x FROM t02241  ) WHERE y=1242} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}243 244do_catchsql_test 8.2 {245  CREATE VIEW v0 as SELECT x, y FROM t01 UNION SELECT x FROM t02;246  EXPLAIN QUERY PLAN SELECT * FROM v0 WHERE x='0' OR y;247} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}248 249 250finish_test251