1# The author disclaims copyright to this source code. In place of 2# 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. The 10# focus of this file is testing compute SELECT statements and nested 11# views. 12# 13# $Id: select7.test,v 1.4 2005/01/03 02:26:55 drh Exp $ 14 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19ifcapable compound { 20 21# A 3-way INTERSECT. Ticket #875 22do_test select7-1.1 { 23 execsql { 24 create temp table t1(x); 25 insert into t1 values('amx'); 26 insert into t1 values('anx'); 27 insert into t1 values('amy'); 28 insert into t1 values('bmy'); 29 select * from t1 where x like 'a__' 30 intersect select * from t1 where x like '_m_' 31 intersect select * from t1 where x like '__x'; 32 } 33} {amx} 34 35 36# Nested views do not handle * properly. Ticket #826. 37# 38ifcapable view { 39do_test select7-2.1 { 40 execsql { 41 CREATE TABLE x(id integer primary key, a TEXT NULL); 42 INSERT INTO x (a) VALUES ('first'); 43 CREATE TABLE tempx(id integer primary key, a TEXT NULL); 44 INSERT INTO tempx (a) VALUES ('t-first'); 45 CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id; 46 CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id; 47 CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b; 48 SELECT * FROM tv2; 49 } 50} {1 1} 51} ;# ifcapable view 52 53} ;# ifcapable compound 54 55# Do not allow GROUP BY without an aggregate. Ticket #1039. 56# 57do_test select7-3.1 { 58 catchsql { 59 SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name 60 } 61} {1 {GROUP BY may only be used on aggregate queries}} 62finish_test 63