1# Copyright (c) 1999, 2000 D. Richard Hipp 2# 3# This program is free software; you can redistribute it and/or 4# modify it under the terms of the GNU General Public 5# License as published by the Free Software Foundation; either 6# version 2 of the License, or (at your option) any later version. 7# 8# This program is distributed in the hope that it will be useful, 9# but WITHOUT ANY WARRANTY; without even the implied warranty of 10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 11# General Public License for more details. 12# 13# You should have received a copy of the GNU General Public 14# License along with this library; if not, write to the 15# Free Software Foundation, Inc., 59 Temple Place - Suite 330, 16# Boston, MA 02111-1307, USA. 17# 18# Author contact information: 19# [email protected] 20# http://www.hwaci.com/drh/ 21# 22#*********************************************************************** 23# This file implements regression tests for SQLite library. The 24# focus of this file is testing the SELECT statement. 25# 26# $Id: select1.test,v 1.7 2000/07/29 13:07:00 drh Exp $ 27 28set testdir [file dirname $argv0] 29source $testdir/tester.tcl 30 31# Try to select on a non-existant table. 32# 33do_test select1-1.1 { 34 set v [catch {execsql {SELECT * FROM test1}} msg] 35 lappend v $msg 36} {1 {no such table: test1}} 37 38execsql {CREATE TABLE test1(f1 int, f2 int)} 39 40do_test select1-1.2 { 41 set v [catch {execsql {SELECT * FROM test1, test2}} msg] 42 lappend v $msg 43} {1 {no such table: test2}} 44do_test select1-1.3 { 45 set v [catch {execsql {SELECT * FROM test2, test1}} msg] 46 lappend v $msg 47} {1 {no such table: test2}} 48 49execsql {INSERT INTO test1(f1,f2) VALUES(11,22)} 50 51 52# Make sure the columns are extracted correctly. 53# 54do_test select1-1.4 { 55 execsql {SELECT f1 FROM test1} 56} {11} 57do_test select1-1.5 { 58 execsql {SELECT f2 FROM test1} 59} {22} 60do_test select1-1.6 { 61 execsql {SELECT f2, f1 FROM test1} 62} {22 11} 63do_test select1-1.7 { 64 execsql {SELECT f1, f2 FROM test1} 65} {11 22} 66do_test select1-1.8 { 67 execsql {SELECT * FROM test1} 68} {11 22} 69 70execsql {CREATE TABLE test2(r1 real, r2 real)} 71execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)} 72 73do_test select1-1.9 { 74 execsql {SELECT * FROM test1, test2} 75} {11 22 1.1 2.2} 76do_test select1-1.10 { 77 execsql {SELECT test1.f1, test2.r1 FROM test1, test2} 78} {11 1.1} 79do_test select1-1.11 { 80 execsql {SELECT test1.f1, test2.r1 FROM test2, test1} 81} {11 1.1} 82do_test select1-1.12 { 83 execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2) 84 FROM test2, test1} 85} {11 2.2} 86do_test select1-1.13 { 87 execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2) 88 FROM test1, test2} 89} {1.1 22} 90 91execsql {DROP TABLE test2} 92execsql {DELETE FROM test1} 93execsql {INSERT INTO test1 VALUES(11,22)} 94execsql {INSERT INTO test1 VALUES(33,44)} 95 96# Error messges from sqliteExprCheck 97# 98do_test select1-2.1 { 99 set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg] 100 lappend v $msg 101} {1 {too many arguments to function count()}} 102do_test select1-2.2 { 103 set v [catch {execsql {SELECT count(f1) FROM test1}} msg] 104 lappend v $msg 105} {0 2} 106do_test select1-2.3 { 107 set v [catch {execsql {SELECT Count() FROM test1}} msg] 108 lappend v $msg 109} {0 2} 110do_test select1-2.4 { 111 set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg] 112 lappend v $msg 113} {0 2} 114do_test select1-2.5 { 115 set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg] 116 lappend v $msg 117} {0 3} 118do_test select1-2.6 { 119 set v [catch {execsql {SELECT min(*) FROM test1}} msg] 120 lappend v $msg 121} {1 {too few arguments to function min()}} 122do_test select1-2.7 { 123 set v [catch {execsql {SELECT Min(f1) FROM test1}} msg] 124 lappend v $msg 125} {0 11} 126do_test select1-2.8 { 127 set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg] 128 lappend v [lsort $msg] 129} {0 {11 33}} 130do_test select1-2.9 { 131 set v [catch {execsql {SELECT MAX(*) FROM test1}} msg] 132 lappend v $msg 133} {1 {too few arguments to function MAX()}} 134do_test select1-2.10 { 135 set v [catch {execsql {SELECT Max(f1) FROM test1}} msg] 136 lappend v $msg 137} {0 33} 138do_test select1-2.11 { 139 set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg] 140 lappend v [lsort $msg] 141} {0 {22 44}} 142do_test select1-2.12 { 143 set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg] 144 lappend v [lsort $msg] 145} {0 {23 45}} 146do_test select1-2.13 { 147 set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] 148 lappend v $msg 149} {0 34} 150do_test select1-2.14 { 151 set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] 152 lappend v $msg 153} {1 {too few arguments to function SUM()}} 154do_test select1-2.15 { 155 set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg] 156 lappend v $msg 157} {0 44} 158do_test select1-2.16 { 159 set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg] 160 lappend v $msg 161} {1 {too many arguments to function sum()}} 162do_test select1-2.17 { 163 set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] 164 lappend v $msg 165} {0 45} 166do_test select1-2.18 { 167 set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg] 168 lappend v $msg 169} {1 {no such function: XYZZY}} 170do_test select1-2.19 { 171 set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] 172 lappend v $msg 173} {0 44} 174do_test select1-2.20 { 175 set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg] 176 lappend v $msg 177} {1 {too few arguments to function min()}} 178 179# WHERE clause expressions 180# 181do_test select1-3.1 { 182 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg] 183 lappend v $msg 184} {0 {}} 185do_test select1-3.2 { 186 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg] 187 lappend v $msg 188} {0 11} 189do_test select1-3.3 { 190 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg] 191 lappend v $msg 192} {0 11} 193do_test select1-3.4 { 194 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg] 195 lappend v [lsort $msg] 196} {0 {11 33}} 197do_test select1-3.5 { 198 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg] 199 lappend v [lsort $msg] 200} {0 33} 201do_test select1-3.6 { 202 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg] 203 lappend v [lsort $msg] 204} {0 33} 205do_test select1-3.7 { 206 set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg] 207 lappend v [lsort $msg] 208} {0 33} 209do_test select1-3.8 { 210 set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg] 211 lappend v [lsort $msg] 212} {0 {11 33}} 213do_test select1-3.9 { 214 set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg] 215 lappend v $msg 216} {1 {no such function: count}} 217 218# ORDER BY expressions 219# 220do_test select1-4.1 { 221 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg] 222 lappend v $msg 223} {0 {11 33}} 224do_test select1-4.2 { 225 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg] 226 lappend v $msg 227} {0 {33 11}} 228do_test select1-4.3 { 229 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] 230 lappend v $msg 231} {0 {11 33}} 232do_test select1-4.4 { 233 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] 234 lappend v $msg 235} {1 {too few arguments to function min()}} 236 237# ORDER BY ignored on an aggregate query 238# 239do_test select1-5.1 { 240 set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg] 241 lappend v $msg 242} {0 33} 243 244execsql {CREATE TABLE test2(t1 test, t2 text)} 245execsql {INSERT INTO test2 VALUES('abc','xyz')} 246 247# Check for column naming 248# 249do_test select1-6.1 { 250 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 251 lappend v $msg 252} {0 {f1 11 f1 33}} 253do_test select1-6.2 { 254 set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg] 255 lappend v $msg 256} {0 {xyzzy 11 xyzzy 33}} 257do_test select1-6.3 { 258 set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg] 259 lappend v $msg 260} {0 {xyzzy 11 xyzzy 33}} 261do_test select1-6.4 { 262 set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg] 263 lappend v $msg 264} {0 {xyzzy 33 xyzzy 77}} 265do_test select1-6.5 { 266 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 267 lappend v $msg 268} {0 {test1.f1+F2 33 test1.f1+F2 77}} 269do_test select1-6.6 { 270 set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 271 ORDER BY f2}} msg] 272 lappend v $msg 273} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}} 274do_test select1-6.7 { 275 set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 276 ORDER BY f2}} msg] 277 lappend v $msg 278} {0 {A.f1 11 t1 abc A.f1 33 t1 abc}} 279do_test select1-6.8 { 280 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 281 ORDER BY f2}} msg] 282 lappend v $msg 283} {1 {ambiguous column name: f1}} 284do_test select1-6.8b { 285 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 286 ORDER BY f2}} msg] 287 lappend v $msg 288} {1 {ambiguous column name: f2}} 289do_test select1-6.8c { 290 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 291 ORDER BY f2}} msg] 292 lappend v $msg 293} {1 {ambiguous column name: A.f1}} 294do_test select1-6.9 { 295 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 296 ORDER BY A.f1, B.f1}} msg] 297 lappend v $msg 298} {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}} 299 300finish_test 301