/trunk/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 |