1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/select7.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,159 @@
1.4 +# The author disclaims copyright to this source code. In place of
1.5 +# a legal notice, here is a blessing:
1.6 +#
1.7 +# May you do good and not evil.
1.8 +# May you find forgiveness for yourself and forgive others.
1.9 +# May you share freely, never taking more than you give.
1.10 +#
1.11 +#***********************************************************************
1.12 +# This file implements regression tests for SQLite library. The
1.13 +# focus of this file is testing compute SELECT statements and nested
1.14 +# views.
1.15 +#
1.16 +# $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $
1.17 +
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +ifcapable compound {
1.23 +
1.24 +# A 3-way INTERSECT. Ticket #875
1.25 +ifcapable tempdb {
1.26 + do_test select7-1.1 {
1.27 + execsql {
1.28 + create temp table t1(x);
1.29 + insert into t1 values('amx');
1.30 + insert into t1 values('anx');
1.31 + insert into t1 values('amy');
1.32 + insert into t1 values('bmy');
1.33 + select * from t1 where x like 'a__'
1.34 + intersect select * from t1 where x like '_m_'
1.35 + intersect select * from t1 where x like '__x';
1.36 + }
1.37 + } {amx}
1.38 +}
1.39 +
1.40 +
1.41 +# Nested views do not handle * properly. Ticket #826.
1.42 +#
1.43 +ifcapable view {
1.44 +do_test select7-2.1 {
1.45 + execsql {
1.46 + CREATE TABLE x(id integer primary key, a TEXT NULL);
1.47 + INSERT INTO x (a) VALUES ('first');
1.48 + CREATE TABLE tempx(id integer primary key, a TEXT NULL);
1.49 + INSERT INTO tempx (a) VALUES ('t-first');
1.50 + CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;
1.51 + CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;
1.52 + CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;
1.53 + SELECT * FROM tv2;
1.54 + }
1.55 +} {1 1}
1.56 +} ;# ifcapable view
1.57 +
1.58 +} ;# ifcapable compound
1.59 +
1.60 +# Do not allow GROUP BY without an aggregate. Ticket #1039.
1.61 +#
1.62 +# Change: force any query with a GROUP BY clause to be processed as
1.63 +# an aggregate query, whether it contains aggregates or not.
1.64 +#
1.65 +ifcapable subquery {
1.66 + # do_test select7-3.1 {
1.67 + # catchsql {
1.68 + # SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
1.69 + # }
1.70 + # } {1 {GROUP BY may only be used on aggregate queries}}
1.71 + do_test select7-3.1 {
1.72 + catchsql {
1.73 + SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
1.74 + }
1.75 + } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]
1.76 +}
1.77 +
1.78 +# Ticket #2018 - Make sure names are resolved correctly on all
1.79 +# SELECT statements of a compound subquery.
1.80 +#
1.81 +ifcapable {subquery && compound} {
1.82 + do_test select7-4.1 {
1.83 + execsql {
1.84 + CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x);
1.85 + CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name);
1.86 +
1.87 + SELECT P.pk from PHOTO P WHERE NOT EXISTS (
1.88 + SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk
1.89 + EXCEPT
1.90 + SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
1.91 + );
1.92 + }
1.93 + } {}
1.94 + do_test select7-4.2 {
1.95 + execsql {
1.96 + INSERT INTO photo VALUES(1,1);
1.97 + INSERT INTO photo VALUES(2,2);
1.98 + INSERT INTO photo VALUES(3,3);
1.99 + INSERT INTO tag VALUES(11,1,'one');
1.100 + INSERT INTO tag VALUES(12,1,'two');
1.101 + INSERT INTO tag VALUES(21,1,'one-b');
1.102 + SELECT P.pk from PHOTO P WHERE NOT EXISTS (
1.103 + SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk
1.104 + EXCEPT
1.105 + SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
1.106 + );
1.107 + }
1.108 + } {2 3}
1.109 +}
1.110 +
1.111 +# ticket #2347
1.112 +#
1.113 +ifcapable {subquery && compound} {
1.114 + do_test select7-5.1 {
1.115 + catchsql {
1.116 + CREATE TABLE t2(a,b);
1.117 + SELECT 5 IN (SELECT a,b FROM t2);
1.118 + }
1.119 + } [list 1 \
1.120 + {only a single result allowed for a SELECT that is part of an expression}]
1.121 + do_test select7-5.2 {
1.122 + catchsql {
1.123 + SELECT 5 IN (SELECT * FROM t2);
1.124 + }
1.125 + } [list 1 \
1.126 + {only a single result allowed for a SELECT that is part of an expression}]
1.127 + do_test select7-5.3 {
1.128 + catchsql {
1.129 + SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2);
1.130 + }
1.131 + } [list 1 \
1.132 + {only a single result allowed for a SELECT that is part of an expression}]
1.133 + do_test select7-5.4 {
1.134 + catchsql {
1.135 + SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2);
1.136 + }
1.137 + } [list 1 \
1.138 + {only a single result allowed for a SELECT that is part of an expression}]
1.139 +}
1.140 +
1.141 +# Verify that an error occurs if you have too many terms on a
1.142 +# compound select statement.
1.143 +#
1.144 +ifcapable compound {
1.145 + if {$SQLITE_MAX_COMPOUND_SELECT>0} {
1.146 + set sql {SELECT 0}
1.147 + set result 0
1.148 + for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} {
1.149 + append sql " UNION ALL SELECT $i"
1.150 + lappend result $i
1.151 + }
1.152 + do_test select7-6.1 {
1.153 + catchsql $sql
1.154 + } [list 0 $result]
1.155 + append sql { UNION ALL SELECT 99999999}
1.156 + do_test select7-6.2 {
1.157 + catchsql $sql
1.158 + } {1 {too many terms in compound SELECT}}
1.159 + }
1.160 +}
1.161 +
1.162 +finish_test