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.11 2007/09/12 17:01:45 danielk1977 Exp $ 14 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix select7 19 20ifcapable compound { 21 22# A 3-way INTERSECT. Ticket #875 23ifcapable tempdb { 24 do_test select7-1.1 { 25 execsql { 26 create temp table t1(x); 27 insert into t1 values('amx'); 28 insert into t1 values('anx'); 29 insert into t1 values('amy'); 30 insert into t1 values('bmy'); 31 select * from t1 where x like 'a__' 32 intersect select * from t1 where x like '_m_' 33 intersect select * from t1 where x like '__x'; 34 } 35 } {amx} 36} 37 38 39# Nested views do not handle * properly. Ticket #826. 40# 41ifcapable view { 42do_test select7-2.1 { 43 execsql { 44 CREATE TABLE x(id integer primary key, a TEXT NULL); 45 INSERT INTO x (a) VALUES ('first'); 46 CREATE TABLE tempx(id integer primary key, a TEXT NULL); 47 INSERT INTO tempx (a) VALUES ('t-first'); 48 CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id; 49 CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id; 50 CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b; 51 SELECT * FROM tv2; 52 } 53} {1 1} 54} ;# ifcapable view 55 56} ;# ifcapable compound 57 58# Do not allow GROUP BY without an aggregate. Ticket #1039. 59# 60# Change: force any query with a GROUP BY clause to be processed as 61# an aggregate query, whether it contains aggregates or not. 62# 63ifcapable subquery { 64 # do_test select7-3.1 { 65 # catchsql { 66 # SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name 67 # } 68 # } {1 {GROUP BY may only be used on aggregate queries}} 69 do_test select7-3.1 { 70 catchsql { 71 SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name 72 } 73 } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]] 74} 75 76# Ticket #2018 - Make sure names are resolved correctly on all 77# SELECT statements of a compound subquery. 78# 79ifcapable {subquery && compound} { 80 do_test select7-4.1 { 81 execsql { 82 CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x); 83 CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name); 84 85 SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 86 SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 87 EXCEPT 88 SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%' 89 ); 90 } 91 } {} 92 do_test select7-4.2 { 93 execsql { 94 INSERT INTO photo VALUES(1,1); 95 INSERT INTO photo VALUES(2,2); 96 INSERT INTO photo VALUES(3,3); 97 INSERT INTO tag VALUES(11,1,'one'); 98 INSERT INTO tag VALUES(12,1,'two'); 99 INSERT INTO tag VALUES(21,1,'one-b'); 100 SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 101 SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 102 EXCEPT 103 SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%' 104 ); 105 } 106 } {2 3} 107} 108 109# ticket #2347 110# 111ifcapable {subquery && compound} { 112 do_test select7-5.1 { 113 catchsql { 114 CREATE TABLE t2(a,b); 115 SELECT 5 IN (SELECT a,b FROM t2); 116 } 117 } [list 1 \ 118 {only a single result allowed for a SELECT that is part of an expression}] 119 do_test select7-5.2 { 120 catchsql { 121 SELECT 5 IN (SELECT * FROM t2); 122 } 123 } [list 1 \ 124 {only a single result allowed for a SELECT that is part of an expression}] 125 do_test select7-5.3 { 126 catchsql { 127 SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2); 128 } 129 } [list 1 \ 130 {only a single result allowed for a SELECT that is part of an expression}] 131 do_test select7-5.4 { 132 catchsql { 133 SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2); 134 } 135 } [list 1 \ 136 {only a single result allowed for a SELECT that is part of an expression}] 137} 138 139# Verify that an error occurs if you have too many terms on a 140# compound select statement. 141# 142if {[clang_sanitize_address]==0} { 143 ifcapable compound { 144 if {$SQLITE_MAX_COMPOUND_SELECT>0} { 145 set sql {SELECT 0} 146 set result 0 147 for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} { 148 append sql " UNION ALL SELECT $i" 149 lappend result $i 150 } 151 do_test select7-6.1 { 152 catchsql $sql 153 } [list 0 $result] 154 append sql { UNION ALL SELECT 99999999} 155 do_test select7-6.2 { 156 catchsql $sql 157 } {1 {too many terms in compound SELECT}} 158 } 159 } 160} 161 162# This block of tests verifies that bug aa92c76cd4 is fixed. 163# 164do_test select7-7.1 { 165 execsql { 166 CREATE TABLE t3(a REAL); 167 INSERT INTO t3 VALUES(44.0); 168 INSERT INTO t3 VALUES(56.0); 169 } 170} {} 171do_test select7-7.2 { 172 execsql { 173 pragma vdbe_trace = 0; 174 SELECT (CASE WHEN a=0 THEN 0 ELSE (a + 25) / 50 END) AS categ, count(*) 175 FROM t3 GROUP BY categ 176 } 177} {1.38 1 1.62 1} 178do_test select7-7.3 { 179 execsql { 180 CREATE TABLE t4(a REAL); 181 INSERT INTO t4 VALUES( 2.0 ); 182 INSERT INTO t4 VALUES( 3.0 ); 183 } 184} {} 185do_test select7-7.4 { 186 execsql { 187 SELECT (CASE WHEN a=0 THEN 'zero' ELSE a/2 END) AS t FROM t4 GROUP BY t; 188 } 189} {1.0 1.5} 190do_test select7-7.5 { 191 execsql { SELECT a=0, typeof(a) FROM t4 } 192} {0 real 0 real} 193do_test select7-7.6 { 194 execsql { SELECT a=0, typeof(a) FROM t4 GROUP BY a } 195} {0 real 0 real} 196 197do_test select7-7.7 { 198 execsql { 199 CREATE TABLE t5(a TEXT, b INT); 200 INSERT INTO t5 VALUES(123, 456); 201 SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b; 202 } 203} {text 123} 204 205do_execsql_test 8.0 { 206 CREATE TABLE t01(x, y); 207 CREATE TABLE t02(x, y); 208} 209 210do_catchsql_test 8.1 { 211 SELECT * FROM ( 212 SELECT * FROM t01 UNION SELECT x FROM t02 213 ) WHERE y=1 214} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} 215 216do_catchsql_test 8.2 { 217 CREATE VIEW v0 as SELECT x, y FROM t01 UNION SELECT x FROM t02; 218 EXPLAIN QUERY PLAN SELECT * FROM v0 WHERE x='0' OR y; 219} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} 220 221 222finish_test 223