# 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}} } } finish_test