diff options
Diffstat (limited to 'test/select7.test')
| -rw-r--r-- | test/select7.test | 202 |
1 files changed, 202 insertions, 0 deletions
diff --git a/test/select7.test b/test/select7.test new file mode 100644 index 0000000..e8fc440 --- /dev/null +++ b/test/select7.test @@ -0,0 +1,202 @@ +# 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. The +# focus of this file is testing compute SELECT statements and nested +# views. +# +# $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $ + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +ifcapable compound { + +# A 3-way INTERSECT. Ticket #875 +ifcapable tempdb { + do_test select7-1.1 { + execsql { + create temp table t1(x); + insert into t1 values('amx'); + insert into t1 values('anx'); + insert into t1 values('amy'); + insert into t1 values('bmy'); + select * from t1 where x like 'a__' + intersect select * from t1 where x like '_m_' + intersect select * from t1 where x like '__x'; + } + } {amx} +} + + +# Nested views do not handle * properly. Ticket #826. +# +ifcapable view { +do_test select7-2.1 { + execsql { + CREATE TABLE x(id integer primary key, a TEXT NULL); + INSERT INTO x (a) VALUES ('first'); + CREATE TABLE tempx(id integer primary key, a TEXT NULL); + INSERT INTO tempx (a) VALUES ('t-first'); + CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id; + CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id; + CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b; + SELECT * FROM tv2; + } +} {1 1} +} ;# ifcapable view + +} ;# ifcapable compound + +# Do not allow GROUP BY without an aggregate. Ticket #1039. +# +# Change: force any query with a GROUP BY clause to be processed as +# an aggregate query, whether it contains aggregates or not. +# +ifcapable subquery { + # do_test select7-3.1 { + # catchsql { + # SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name + # } + # } {1 {GROUP BY may only be used on aggregate queries}} + do_test select7-3.1 { + catchsql { + SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name + } + } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]] +} + +# Ticket #2018 - Make sure names are resolved correctly on all +# SELECT statements of a compound subquery. +# +ifcapable {subquery && compound} { + do_test select7-4.1 { + execsql { + CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x); + CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name); + + SELECT P.pk from PHOTO P WHERE NOT EXISTS ( + SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk + EXCEPT + SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%' + ); + } + } {} + do_test select7-4.2 { + execsql { + INSERT INTO photo VALUES(1,1); + INSERT INTO photo VALUES(2,2); + INSERT INTO photo VALUES(3,3); + INSERT INTO tag VALUES(11,1,'one'); + INSERT INTO tag VALUES(12,1,'two'); + INSERT INTO tag VALUES(21,1,'one-b'); + SELECT P.pk from PHOTO P WHERE NOT EXISTS ( + SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk + EXCEPT + SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%' + ); + } + } {2 3} +} + +# ticket #2347 +# +ifcapable {subquery && compound} { + do_test select7-5.1 { + catchsql { + CREATE TABLE t2(a,b); + SELECT 5 IN (SELECT a,b FROM t2); + } + } [list 1 \ + {only a single result allowed for a SELECT that is part of an expression}] + do_test select7-5.2 { + catchsql { + SELECT 5 IN (SELECT * FROM t2); + } + } [list 1 \ + {only a single result allowed for a SELECT that is part of an expression}] + do_test select7-5.3 { + catchsql { + SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2); + } + } [list 1 \ + {only a single result allowed for a SELECT that is part of an expression}] + do_test select7-5.4 { + catchsql { + SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2); + } + } [list 1 \ + {only a single result allowed for a SELECT that is part of an expression}] +} + +# Verify that an error occurs if you have too many terms on a +# compound select statement. +# +ifcapable compound { + if {$SQLITE_MAX_COMPOUND_SELECT>0} { + set sql {SELECT 0} + set result 0 + for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} { + append sql " UNION ALL SELECT $i" + lappend result $i + } + do_test select7-6.1 { + catchsql $sql + } [list 0 $result] + append sql { UNION ALL SELECT 99999999} + do_test select7-6.2 { + catchsql $sql + } {1 {too many terms in compound SELECT}} + } +} + +# This block of tests verifies that bug aa92c76cd4 is fixed. +# +do_test select7-7.1 { + execsql { + CREATE TABLE t3(a REAL); + INSERT INTO t3 VALUES(44.0); + INSERT INTO t3 VALUES(56.0); + } +} {} +do_test select7-7.2 { + execsql { + pragma vdbe_trace = 0; + SELECT (CASE WHEN a=0 THEN 0 ELSE (a + 25) / 50 END) AS categ, count(*) + FROM t3 GROUP BY categ + } +} {1.38 1 1.62 1} +do_test select7-7.3 { + execsql { + CREATE TABLE t4(a REAL); + INSERT INTO t4 VALUES( 2.0 ); + INSERT INTO t4 VALUES( 3.0 ); + } +} {} +do_test select7-7.4 { + execsql { + SELECT (CASE WHEN a=0 THEN 'zero' ELSE a/2 END) AS t FROM t4 GROUP BY t; + } +} {1.0 1.5} +do_test select7-7.5 { + execsql { SELECT a=0, typeof(a) FROM t4 } +} {0 real 0 real} +do_test select7-7.6 { + execsql { SELECT a=0, typeof(a) FROM t4 GROUP BY a } +} {0 real 0 real} + +do_test select7-7.7 { + execsql { + CREATE TABLE t5(a TEXT, b INT); + INSERT INTO t5 VALUES(123, 456); + SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b; + } +} {text 123} + +finish_test |