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