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 } {1 {sub-select returns 2 columns - expected 1}} 118 do_test select7-5.2 { 119 catchsql { 120 SELECT 5 IN (SELECT * FROM t2); 121 } 122 } {1 {sub-select returns 2 columns - expected 1}} 123 do_test select7-5.3 { 124 catchsql { 125 SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2); 126 } 127 } {1 {sub-select returns 2 columns - expected 1}} 128 do_test select7-5.4 { 129 catchsql { 130 SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2); 131 } 132 } {1 {sub-select returns 2 columns - expected 1}} 133} 134 135# Verify that an error occurs if you have too many terms on a 136# compound select statement. 137# 138if {[clang_sanitize_address]==0} { 139 ifcapable compound { 140 if {$SQLITE_MAX_COMPOUND_SELECT>0} { 141 set sql {SELECT 0} 142 set result 0 143 for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} { 144 append sql " UNION ALL SELECT $i" 145 lappend result $i 146 } 147 do_test select7-6.1 { 148 catchsql $sql 149 } [list 0 $result] 150 append sql { UNION ALL SELECT 99999999} 151 do_test select7-6.2 { 152 catchsql $sql 153 } {1 {too many terms in compound SELECT}} 154 } 155 } 156} 157 158# This block of tests verifies that bug aa92c76cd4 is fixed. 159# 160do_test select7-7.1 { 161 execsql { 162 CREATE TABLE t3(a REAL); 163 INSERT INTO t3 VALUES(44.0); 164 INSERT INTO t3 VALUES(56.0); 165 } 166} {} 167do_test select7-7.2 { 168 execsql { 169 pragma vdbe_trace = 0; 170 SELECT (CASE WHEN a=0 THEN 0 ELSE (a + 25) / 50 END) AS categ, count(*) 171 FROM t3 GROUP BY categ 172 } 173} {1.38 1 1.62 1} 174do_test select7-7.3 { 175 execsql { 176 CREATE TABLE t4(a REAL); 177 INSERT INTO t4 VALUES( 2.0 ); 178 INSERT INTO t4 VALUES( 3.0 ); 179 } 180} {} 181do_test select7-7.4 { 182 execsql { 183 SELECT (CASE WHEN a=0 THEN 'zero' ELSE a/2 END) AS t FROM t4 GROUP BY t; 184 } 185} {1.0 1.5} 186do_test select7-7.5 { 187 execsql { SELECT a=0, typeof(a) FROM t4 } 188} {0 real 0 real} 189do_test select7-7.6 { 190 execsql { SELECT a=0, typeof(a) FROM t4 GROUP BY a } 191} {0 real 0 real} 192 193do_test select7-7.7 { 194 execsql { 195 CREATE TABLE t5(a TEXT, b INT); 196 INSERT INTO t5 VALUES(123, 456); 197 SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b; 198 } 199} {text 123} 200 201do_execsql_test 8.0 { 202 CREATE TABLE t01(x, y); 203 CREATE TABLE t02(x, y); 204} 205 206do_catchsql_test 8.1 { 207 SELECT * FROM ( 208 SELECT * FROM t01 UNION SELECT x FROM t02 209 ) WHERE y=1 210} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} 211 212do_catchsql_test 8.2 { 213 CREATE VIEW v0 as SELECT x, y FROM t01 UNION SELECT x FROM t02; 214 EXPLAIN QUERY PLAN SELECT * FROM v0 WHERE x='0' OR y; 215} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} 216 217 218finish_test 219