1# 2001 September 15 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this file is testing the SELECT statement. 13# 14# $Id: select1.test,v 1.70 2009/05/28 01:00:56 drh Exp $ 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Try to select on a non-existant table. 20# 21do_test select1-1.1 { 22 set v [catch {execsql {SELECT * FROM test1}} msg] 23 lappend v $msg 24} {1 {no such table: test1}} 25 26 27execsql {CREATE TABLE test1(f1 int, f2 int)} 28 29do_test select1-1.2 { 30 set v [catch {execsql {SELECT * FROM test1, test2}} msg] 31 lappend v $msg 32} {1 {no such table: test2}} 33do_test select1-1.3 { 34 set v [catch {execsql {SELECT * FROM test2, test1}} msg] 35 lappend v $msg 36} {1 {no such table: test2}} 37 38execsql {INSERT INTO test1(f1,f2) VALUES(11,22)} 39 40 41# Make sure the columns are extracted correctly. 42# 43do_test select1-1.4 { 44 execsql {SELECT f1 FROM test1} 45} {11} 46do_test select1-1.5 { 47 execsql {SELECT f2 FROM test1} 48} {22} 49do_test select1-1.6 { 50 execsql {SELECT f2, f1 FROM test1} 51} {22 11} 52do_test select1-1.7 { 53 execsql {SELECT f1, f2 FROM test1} 54} {11 22} 55do_test select1-1.8 { 56 execsql {SELECT * FROM test1} 57} {11 22} 58do_test select1-1.8.1 { 59 execsql {SELECT *, * FROM test1} 60} {11 22 11 22} 61do_test select1-1.8.2 { 62 execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1} 63} {11 22 11 22} 64do_test select1-1.8.3 { 65 execsql {SELECT 'one', *, 'two', * FROM test1} 66} {one 11 22 two 11 22} 67 68execsql {CREATE TABLE test2(r1 real, r2 real)} 69execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)} 70 71do_test select1-1.9 { 72 execsql {SELECT * FROM test1, test2} 73} {11 22 1.1 2.2} 74do_test select1-1.9.1 { 75 execsql {SELECT *, 'hi' FROM test1, test2} 76} {11 22 1.1 2.2 hi} 77do_test select1-1.9.2 { 78 execsql {SELECT 'one', *, 'two', * FROM test1, test2} 79} {one 11 22 1.1 2.2 two 11 22 1.1 2.2} 80do_test select1-1.10 { 81 execsql {SELECT test1.f1, test2.r1 FROM test1, test2} 82} {11 1.1} 83do_test select1-1.11 { 84 execsql {SELECT test1.f1, test2.r1 FROM test2, test1} 85} {11 1.1} 86do_test select1-1.11.1 { 87 execsql {SELECT * FROM test2, test1} 88} {1.1 2.2 11 22} 89do_test select1-1.11.2 { 90 execsql {SELECT * FROM test1 AS a, test1 AS b} 91} {11 22 11 22} 92do_test select1-1.12 { 93 execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2) 94 FROM test2, test1} 95} {11 2.2} 96do_test select1-1.13 { 97 execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2) 98 FROM test1, test2} 99} {1.1 22} 100 101set long {This is a string that is too big to fit inside a NBFS buffer} 102do_test select1-2.0 { 103 execsql " 104 DROP TABLE test2; 105 DELETE FROM test1; 106 INSERT INTO test1 VALUES(11,22); 107 INSERT INTO test1 VALUES(33,44); 108 CREATE TABLE t3(a,b); 109 INSERT INTO t3 VALUES('abc',NULL); 110 INSERT INTO t3 VALUES(NULL,'xyz'); 111 INSERT INTO t3 SELECT * FROM test1; 112 CREATE TABLE t4(a,b); 113 INSERT INTO t4 VALUES(NULL,'$long'); 114 SELECT * FROM t3; 115 " 116} {abc {} {} xyz 11 22 33 44} 117 118# Error messges from sqliteExprCheck 119# 120do_test select1-2.1 { 121 set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg] 122 lappend v $msg 123} {1 {wrong number of arguments to function count()}} 124do_test select1-2.2 { 125 set v [catch {execsql {SELECT count(f1) FROM test1}} msg] 126 lappend v $msg 127} {0 2} 128do_test select1-2.3 { 129 set v [catch {execsql {SELECT Count() FROM test1}} msg] 130 lappend v $msg 131} {0 2} 132do_test select1-2.4 { 133 set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg] 134 lappend v $msg 135} {0 2} 136do_test select1-2.5 { 137 set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg] 138 lappend v $msg 139} {0 3} 140do_test select1-2.5.1 { 141 execsql {SELECT count(*),count(a),count(b) FROM t3} 142} {4 3 3} 143do_test select1-2.5.2 { 144 execsql {SELECT count(*),count(a),count(b) FROM t4} 145} {1 0 1} 146do_test select1-2.5.3 { 147 execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5} 148} {0 0 0} 149do_test select1-2.6 { 150 set v [catch {execsql {SELECT min(*) FROM test1}} msg] 151 lappend v $msg 152} {1 {wrong number of arguments to function min()}} 153do_test select1-2.7 { 154 set v [catch {execsql {SELECT Min(f1) FROM test1}} msg] 155 lappend v $msg 156} {0 11} 157do_test select1-2.8 { 158 set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg] 159 lappend v [lsort $msg] 160} {0 {11 33}} 161do_test select1-2.8.1 { 162 execsql {SELECT coalesce(min(a),'xyzzy') FROM t3} 163} {11} 164do_test select1-2.8.2 { 165 execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3} 166} {11} 167do_test select1-2.8.3 { 168 execsql {SELECT min(b), min(b) FROM t4} 169} [list $long $long] 170do_test select1-2.9 { 171 set v [catch {execsql {SELECT MAX(*) FROM test1}} msg] 172 lappend v $msg 173} {1 {wrong number of arguments to function MAX()}} 174do_test select1-2.10 { 175 set v [catch {execsql {SELECT Max(f1) FROM test1}} msg] 176 lappend v $msg 177} {0 33} 178do_test select1-2.11 { 179 set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg] 180 lappend v [lsort $msg] 181} {0 {22 44}} 182do_test select1-2.12 { 183 set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg] 184 lappend v [lsort $msg] 185} {0 {23 45}} 186do_test select1-2.13 { 187 set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] 188 lappend v $msg 189} {0 34} 190do_test select1-2.13.1 { 191 execsql {SELECT coalesce(max(a),'xyzzy') FROM t3} 192} {abc} 193do_test select1-2.13.2 { 194 execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3} 195} {xyzzy} 196do_test select1-2.14 { 197 set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] 198 lappend v $msg 199} {1 {wrong number of arguments to function SUM()}} 200do_test select1-2.15 { 201 set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg] 202 lappend v $msg 203} {0 44} 204do_test select1-2.16 { 205 set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg] 206 lappend v $msg 207} {1 {wrong number of arguments to function sum()}} 208do_test select1-2.17 { 209 set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] 210 lappend v $msg 211} {0 45} 212do_test select1-2.17.1 { 213 execsql {SELECT sum(a) FROM t3} 214} {44.0} 215do_test select1-2.18 { 216 set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg] 217 lappend v $msg 218} {1 {no such function: XYZZY}} 219do_test select1-2.19 { 220 set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] 221 lappend v $msg 222} {0 44} 223do_test select1-2.20 { 224 set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg] 225 lappend v $msg 226} {1 {misuse of aggregate function min()}} 227 228# Ticket #2526 229# 230do_test select1-2.21 { 231 catchsql { 232 SELECT min(f1) AS m FROM test1 GROUP BY f1 HAVING max(m+5)<10 233 } 234} {1 {misuse of aliased aggregate m}} 235do_test select1-2.22 { 236 catchsql { 237 SELECT coalesce(min(f1)+5,11) AS m FROM test1 238 GROUP BY f1 239 HAVING max(m+5)<10 240 } 241} {1 {misuse of aliased aggregate m}} 242do_test select1-2.23 { 243 execsql { 244 CREATE TABLE tkt2526(a,b,c PRIMARY KEY); 245 INSERT INTO tkt2526 VALUES('x','y',NULL); 246 INSERT INTO tkt2526 VALUES('x','z',NULL); 247 } 248 catchsql { 249 SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn) 250 } 251} {1 {misuse of aliased aggregate cn}} 252 253# WHERE clause expressions 254# 255do_test select1-3.1 { 256 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg] 257 lappend v $msg 258} {0 {}} 259do_test select1-3.2 { 260 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg] 261 lappend v $msg 262} {0 11} 263do_test select1-3.3 { 264 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg] 265 lappend v $msg 266} {0 11} 267do_test select1-3.4 { 268 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg] 269 lappend v [lsort $msg] 270} {0 {11 33}} 271do_test select1-3.5 { 272 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg] 273 lappend v [lsort $msg] 274} {0 33} 275do_test select1-3.6 { 276 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg] 277 lappend v [lsort $msg] 278} {0 33} 279do_test select1-3.7 { 280 set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg] 281 lappend v [lsort $msg] 282} {0 33} 283do_test select1-3.8 { 284 set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg] 285 lappend v [lsort $msg] 286} {0 {11 33}} 287do_test select1-3.9 { 288 set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg] 289 lappend v $msg 290} {1 {wrong number of arguments to function count()}} 291 292# ORDER BY expressions 293# 294do_test select1-4.1 { 295 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg] 296 lappend v $msg 297} {0 {11 33}} 298do_test select1-4.2 { 299 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg] 300 lappend v $msg 301} {0 {33 11}} 302do_test select1-4.3 { 303 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] 304 lappend v $msg 305} {0 {11 33}} 306do_test select1-4.4 { 307 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] 308 lappend v $msg 309} {1 {misuse of aggregate: min()}} 310 311# The restriction not allowing constants in the ORDER BY clause 312# has been removed. See ticket #1768 313#do_test select1-4.5 { 314# catchsql { 315# SELECT f1 FROM test1 ORDER BY 8.4; 316# } 317#} {1 {ORDER BY terms must not be non-integer constants}} 318#do_test select1-4.6 { 319# catchsql { 320# SELECT f1 FROM test1 ORDER BY '8.4'; 321# } 322#} {1 {ORDER BY terms must not be non-integer constants}} 323#do_test select1-4.7.1 { 324# catchsql { 325# SELECT f1 FROM test1 ORDER BY 'xyz'; 326# } 327#} {1 {ORDER BY terms must not be non-integer constants}} 328#do_test select1-4.7.2 { 329# catchsql { 330# SELECT f1 FROM test1 ORDER BY -8.4; 331# } 332#} {1 {ORDER BY terms must not be non-integer constants}} 333#do_test select1-4.7.3 { 334# catchsql { 335# SELECT f1 FROM test1 ORDER BY +8.4; 336# } 337#} {1 {ORDER BY terms must not be non-integer constants}} 338#do_test select1-4.7.4 { 339# catchsql { 340# SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits 341# } 342#} {1 {ORDER BY terms must not be non-integer constants}} 343 344do_test select1-4.5 { 345 execsql { 346 SELECT f1 FROM test1 ORDER BY 8.4 347 } 348} {11 33} 349do_test select1-4.6 { 350 execsql { 351 SELECT f1 FROM test1 ORDER BY '8.4' 352 } 353} {11 33} 354 355do_test select1-4.8 { 356 execsql { 357 CREATE TABLE t5(a,b); 358 INSERT INTO t5 VALUES(1,10); 359 INSERT INTO t5 VALUES(2,9); 360 SELECT * FROM t5 ORDER BY 1; 361 } 362} {1 10 2 9} 363do_test select1-4.9.1 { 364 execsql { 365 SELECT * FROM t5 ORDER BY 2; 366 } 367} {2 9 1 10} 368do_test select1-4.9.2 { 369 execsql { 370 SELECT * FROM t5 ORDER BY +2; 371 } 372} {2 9 1 10} 373do_test select1-4.10.1 { 374 catchsql { 375 SELECT * FROM t5 ORDER BY 3; 376 } 377} {1 {1st ORDER BY term out of range - should be between 1 and 2}} 378do_test select1-4.10.2 { 379 catchsql { 380 SELECT * FROM t5 ORDER BY -1; 381 } 382} {1 {1st ORDER BY term out of range - should be between 1 and 2}} 383do_test select1-4.11 { 384 execsql { 385 INSERT INTO t5 VALUES(3,10); 386 SELECT * FROM t5 ORDER BY 2, 1 DESC; 387 } 388} {2 9 3 10 1 10} 389do_test select1-4.12 { 390 execsql { 391 SELECT * FROM t5 ORDER BY 1 DESC, b; 392 } 393} {3 10 2 9 1 10} 394do_test select1-4.13 { 395 execsql { 396 SELECT * FROM t5 ORDER BY b DESC, 1; 397 } 398} {1 10 3 10 2 9} 399 400 401# ORDER BY ignored on an aggregate query 402# 403do_test select1-5.1 { 404 set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg] 405 lappend v $msg 406} {0 33} 407 408execsql {CREATE TABLE test2(t1 text, t2 text)} 409execsql {INSERT INTO test2 VALUES('abc','xyz')} 410 411# Check for column naming 412# 413do_test select1-6.1 { 414 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 415 lappend v $msg 416} {0 {f1 11 f1 33}} 417do_test select1-6.1.1 { 418 db eval {PRAGMA full_column_names=on} 419 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 420 lappend v $msg 421} {0 {test1.f1 11 test1.f1 33}} 422do_test select1-6.1.2 { 423 set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg] 424 lappend v $msg 425} {0 {f1 11 f1 33}} 426do_test select1-6.1.3 { 427 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 428 lappend v $msg 429} {0 {f1 11 f2 22}} 430do_test select1-6.1.4 { 431 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 432 db eval {PRAGMA full_column_names=off} 433 lappend v $msg 434} {0 {f1 11 f2 22}} 435do_test select1-6.1.5 { 436 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 437 lappend v $msg 438} {0 {f1 11 f2 22}} 439do_test select1-6.1.6 { 440 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 441 lappend v $msg 442} {0 {f1 11 f2 22}} 443do_test select1-6.2 { 444 set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg] 445 lappend v $msg 446} {0 {xyzzy 11 xyzzy 33}} 447do_test select1-6.3 { 448 set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg] 449 lappend v $msg 450} {0 {xyzzy 11 xyzzy 33}} 451do_test select1-6.3.1 { 452 set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg] 453 lappend v $msg 454} {0 {{xyzzy } 11 {xyzzy } 33}} 455do_test select1-6.4 { 456 set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg] 457 lappend v $msg 458} {0 {xyzzy 33 xyzzy 77}} 459do_test select1-6.4a { 460 set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg] 461 lappend v $msg 462} {0 {f1+F2 33 f1+F2 77}} 463do_test select1-6.5 { 464 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 465 lappend v $msg 466} {0 {test1.f1+F2 33 test1.f1+F2 77}} 467do_test select1-6.5.1 { 468 execsql2 {PRAGMA full_column_names=on} 469 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 470 execsql2 {PRAGMA full_column_names=off} 471 lappend v $msg 472} {0 {test1.f1+F2 33 test1.f1+F2 77}} 473do_test select1-6.6 { 474 set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 475 ORDER BY f2}} msg] 476 lappend v $msg 477} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}} 478do_test select1-6.7 { 479 set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 480 ORDER BY f2}} msg] 481 lappend v $msg 482} {0 {f1 11 t1 abc f1 33 t1 abc}} 483do_test select1-6.8 { 484 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 485 ORDER BY f2}} msg] 486 lappend v $msg 487} {1 {ambiguous column name: f1}} 488do_test select1-6.8b { 489 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 490 ORDER BY f2}} msg] 491 lappend v $msg 492} {1 {ambiguous column name: f2}} 493do_test select1-6.8c { 494 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 495 ORDER BY f2}} msg] 496 lappend v $msg 497} {1 {ambiguous column name: A.f1}} 498do_test select1-6.9.1 { 499 set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 500 ORDER BY A.f1, B.f1}} msg] 501 lappend v $msg 502} {0 {11 11 11 33 33 11 33 33}} 503do_test select1-6.9.2 { 504 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 505 ORDER BY A.f1, B.f1}} msg] 506 lappend v $msg 507} {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}} 508 509do_test select1-6.9.3 { 510 db eval { 511 PRAGMA short_column_names=OFF; 512 PRAGMA full_column_names=OFF; 513 } 514 execsql2 { 515 SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1 516 } 517} {{test1 . f1} 11 {test1 . f2} 22} 518do_test select1-6.9.4 { 519 db eval { 520 PRAGMA short_column_names=OFF; 521 PRAGMA full_column_names=ON; 522 } 523 execsql2 { 524 SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1 525 } 526} {test1.f1 11 test1.f2 22} 527do_test select1-6.9.5 { 528 db eval { 529 PRAGMA short_column_names=OFF; 530 PRAGMA full_column_names=ON; 531 } 532 execsql2 { 533 SELECT 123.45; 534 } 535} {123.45 123.45} 536do_test select1-6.9.6 { 537 execsql2 { 538 SELECT * FROM test1 a, test1 b LIMIT 1 539 } 540} {a.f1 11 a.f2 22 b.f1 11 b.f2 22} 541do_test select1-6.9.7 { 542 set x [execsql2 { 543 SELECT * FROM test1 a, (select 5, 6) LIMIT 1 544 }] 545 regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x 546 set x 547} {a.f1 11 a.f2 22 sqlite_subquery.5 5 sqlite_subquery.6 6} 548do_test select1-6.9.8 { 549 set x [execsql2 { 550 SELECT * FROM test1 a, (select 5 AS x, 6 AS y) AS b LIMIT 1 551 }] 552 regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x 553 set x 554} {a.f1 11 a.f2 22 b.x 5 b.y 6} 555do_test select1-6.9.9 { 556 execsql2 { 557 SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1 558 } 559} {test1.f1 11 test1.f2 22} 560do_test select1-6.9.10 { 561 execsql2 { 562 SELECT f1, t1 FROM test1, test2 LIMIT 1 563 } 564} {test1.f1 11 test2.t1 abc} 565do_test select1-6.9.11 { 566 db eval { 567 PRAGMA short_column_names=ON; 568 PRAGMA full_column_names=ON; 569 } 570 execsql2 { 571 SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1 572 } 573} {test1.f1 11 test1.f2 22} 574do_test select1-6.9.12 { 575 execsql2 { 576 SELECT f1, t1 FROM test1, test2 LIMIT 1 577 } 578} {test1.f1 11 test2.t1 abc} 579do_test select1-6.9.13 { 580 db eval { 581 PRAGMA short_column_names=ON; 582 PRAGMA full_column_names=OFF; 583 } 584 execsql2 { 585 SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1 586 } 587} {f1 11 f1 11} 588do_test select1-6.9.14 { 589 execsql2 { 590 SELECT f1, t1 FROM test1, test2 LIMIT 1 591 } 592} {f1 11 t1 abc} 593do_test select1-6.9.15 { 594 db eval { 595 PRAGMA short_column_names=OFF; 596 PRAGMA full_column_names=ON; 597 } 598 execsql2 { 599 SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1 600 } 601} {test1.f1 11 test1.f1 11} 602do_test select1-6.9.16 { 603 execsql2 { 604 SELECT f1, t1 FROM test1, test2 LIMIT 1 605 } 606} {test1.f1 11 test2.t1 abc} 607 608 609db eval { 610 PRAGMA short_column_names=ON; 611 PRAGMA full_column_names=OFF; 612} 613 614ifcapable compound { 615do_test select1-6.10 { 616 set v [catch {execsql2 { 617 SELECT f1 FROM test1 UNION SELECT f2 FROM test1 618 ORDER BY f2; 619 }} msg] 620 lappend v $msg 621} {0 {f1 11 f1 22 f1 33 f1 44}} 622do_test select1-6.11 { 623 set v [catch {execsql2 { 624 SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 625 ORDER BY f2+101; 626 }} msg] 627 lappend v $msg 628} {1 {1st ORDER BY term does not match any column in the result set}} 629 630# Ticket #2296 631ifcapable subquery&&compound { 632do_test select1-6.20 { 633 execsql { 634 CREATE TABLE t6(a TEXT, b TEXT); 635 INSERT INTO t6 VALUES('a','0'); 636 INSERT INTO t6 VALUES('b','1'); 637 INSERT INTO t6 VALUES('c','2'); 638 INSERT INTO t6 VALUES('d','3'); 639 SELECT a FROM t6 WHERE b IN 640 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x 641 ORDER BY 1 LIMIT 1) 642 } 643} {a} 644do_test select1-6.21 { 645 execsql { 646 SELECT a FROM t6 WHERE b IN 647 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x 648 ORDER BY 1 DESC LIMIT 1) 649 } 650} {d} 651do_test select1-6.22 { 652 execsql { 653 SELECT a FROM t6 WHERE b IN 654 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x 655 ORDER BY b LIMIT 2) 656 ORDER BY a; 657 } 658} {a b} 659do_test select1-6.23 { 660 execsql { 661 SELECT a FROM t6 WHERE b IN 662 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x 663 ORDER BY x DESC LIMIT 2) 664 ORDER BY a; 665 } 666} {b d} 667} 668 669} ;#ifcapable compound 670 671do_test select1-7.1 { 672 set v [catch {execsql { 673 SELECT f1 FROM test1 WHERE f2=; 674 }} msg] 675 lappend v $msg 676} {1 {near ";": syntax error}} 677ifcapable compound { 678do_test select1-7.2 { 679 set v [catch {execsql { 680 SELECT f1 FROM test1 UNION SELECT WHERE; 681 }} msg] 682 lappend v $msg 683} {1 {near "WHERE": syntax error}} 684} ;# ifcapable compound 685do_test select1-7.3 { 686 set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg] 687 lappend v $msg 688} {1 {near "as": syntax error}} 689do_test select1-7.4 { 690 set v [catch {execsql { 691 SELECT f1 FROM test1 ORDER BY; 692 }} msg] 693 lappend v $msg 694} {1 {near ";": syntax error}} 695do_test select1-7.5 { 696 set v [catch {execsql { 697 SELECT f1 FROM test1 ORDER BY f1 desc, f2 where; 698 }} msg] 699 lappend v $msg 700} {1 {near "where": syntax error}} 701do_test select1-7.6 { 702 set v [catch {execsql { 703 SELECT count(f1,f2 FROM test1; 704 }} msg] 705 lappend v $msg 706} {1 {near "FROM": syntax error}} 707do_test select1-7.7 { 708 set v [catch {execsql { 709 SELECT count(f1,f2+) FROM test1; 710 }} msg] 711 lappend v $msg 712} {1 {near ")": syntax error}} 713do_test select1-7.8 { 714 set v [catch {execsql { 715 SELECT f1 FROM test1 ORDER BY f2, f1+; 716 }} msg] 717 lappend v $msg 718} {1 {near ";": syntax error}} 719do_test select1-7.9 { 720 catchsql { 721 SELECT f1 FROM test1 LIMIT 5+3 OFFSET 11 ORDER BY f2; 722 } 723} {1 {near "ORDER": syntax error}} 724 725do_test select1-8.1 { 726 execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1} 727} {11 33} 728do_test select1-8.2 { 729 execsql { 730 SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20' 731 ORDER BY f1 732 } 733} {11} 734do_test select1-8.3 { 735 execsql { 736 SELECT f1 FROM test1 WHERE 5-3==2 737 ORDER BY f1 738 } 739} {11 33} 740 741# TODO: This test is failing because f1 is now being loaded off the 742# disk as a vdbe integer, not a string. Hence the value of f1/(f1-11) 743# changes because of rounding. Disable the test for now. 744if 0 { 745do_test select1-8.4 { 746 execsql { 747 SELECT coalesce(f1/(f1-11),'x'), 748 coalesce(min(f1/(f1-11),5),'y'), 749 coalesce(max(f1/(f1-33),6),'z') 750 FROM test1 ORDER BY f1 751 } 752} {x y 6 1.5 1.5 z} 753} 754do_test select1-8.5 { 755 execsql { 756 SELECT min(1,2,3), -max(1,2,3) 757 FROM test1 ORDER BY f1 758 } 759} {1 -3 1 -3} 760 761 762# Check the behavior when the result set is empty 763# 764# SQLite v3 always sets r(*). 765# 766# do_test select1-9.1 { 767# catch {unset r} 768# set r(*) {} 769# db eval {SELECT * FROM test1 WHERE f1<0} r {} 770# set r(*) 771# } {} 772do_test select1-9.2 { 773 execsql {PRAGMA empty_result_callbacks=on} 774 catch {unset r} 775 set r(*) {} 776 db eval {SELECT * FROM test1 WHERE f1<0} r {} 777 set r(*) 778} {f1 f2} 779ifcapable subquery { 780 do_test select1-9.3 { 781 set r(*) {} 782 db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {} 783 set r(*) 784 } {f1 f2} 785} 786do_test select1-9.4 { 787 set r(*) {} 788 db eval {SELECT * FROM test1 ORDER BY f1} r {} 789 set r(*) 790} {f1 f2} 791do_test select1-9.5 { 792 set r(*) {} 793 db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} 794 set r(*) 795} {f1 f2} 796unset r 797 798# Check for ORDER BY clauses that refer to an AS name in the column list 799# 800do_test select1-10.1 { 801 execsql { 802 SELECT f1 AS x FROM test1 ORDER BY x 803 } 804} {11 33} 805do_test select1-10.2 { 806 execsql { 807 SELECT f1 AS x FROM test1 ORDER BY -x 808 } 809} {33 11} 810do_test select1-10.3 { 811 execsql { 812 SELECT f1-23 AS x FROM test1 ORDER BY abs(x) 813 } 814} {10 -12} 815do_test select1-10.4 { 816 execsql { 817 SELECT f1-23 AS x FROM test1 ORDER BY -abs(x) 818 } 819} {-12 10} 820do_test select1-10.5 { 821 execsql { 822 SELECT f1-22 AS x, f2-22 as y FROM test1 823 } 824} {-11 0 11 22} 825do_test select1-10.6 { 826 execsql { 827 SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50 828 } 829} {11 22} 830do_test select1-10.7 { 831 execsql { 832 SELECT f1 COLLATE nocase AS x FROM test1 ORDER BY x 833 } 834} {11 33} 835 836# Check the ability to specify "TABLE.*" in the result set of a SELECT 837# 838do_test select1-11.1 { 839 execsql { 840 DELETE FROM t3; 841 DELETE FROM t4; 842 INSERT INTO t3 VALUES(1,2); 843 INSERT INTO t4 VALUES(3,4); 844 SELECT * FROM t3, t4; 845 } 846} {1 2 3 4} 847do_test select1-11.2.1 { 848 execsql { 849 SELECT * FROM t3, t4; 850 } 851} {1 2 3 4} 852do_test select1-11.2.2 { 853 execsql2 { 854 SELECT * FROM t3, t4; 855 } 856} {a 3 b 4 a 3 b 4} 857do_test select1-11.4.1 { 858 execsql { 859 SELECT t3.*, t4.b FROM t3, t4; 860 } 861} {1 2 4} 862do_test select1-11.4.2 { 863 execsql { 864 SELECT "t3".*, t4.b FROM t3, t4; 865 } 866} {1 2 4} 867do_test select1-11.5.1 { 868 execsql2 { 869 SELECT t3.*, t4.b FROM t3, t4; 870 } 871} {a 1 b 4 b 4} 872do_test select1-11.6 { 873 execsql2 { 874 SELECT x.*, y.b FROM t3 AS x, t4 AS y; 875 } 876} {a 1 b 4 b 4} 877do_test select1-11.7 { 878 execsql { 879 SELECT t3.b, t4.* FROM t3, t4; 880 } 881} {2 3 4} 882do_test select1-11.8 { 883 execsql2 { 884 SELECT t3.b, t4.* FROM t3, t4; 885 } 886} {b 4 a 3 b 4} 887do_test select1-11.9 { 888 execsql2 { 889 SELECT x.b, y.* FROM t3 AS x, t4 AS y; 890 } 891} {b 4 a 3 b 4} 892do_test select1-11.10 { 893 catchsql { 894 SELECT t5.* FROM t3, t4; 895 } 896} {1 {no such table: t5}} 897do_test select1-11.11 { 898 catchsql { 899 SELECT t3.* FROM t3 AS x, t4; 900 } 901} {1 {no such table: t3}} 902ifcapable subquery { 903 do_test select1-11.12 { 904 execsql2 { 905 SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4) 906 } 907 } {a 1 b 2} 908 do_test select1-11.13 { 909 execsql2 { 910 SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3 911 } 912 } {a 1 b 2} 913 do_test select1-11.14 { 914 execsql2 { 915 SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx' 916 } 917 } {a 1 b 2 max(a) 3 max(b) 4} 918 do_test select1-11.15 { 919 execsql2 { 920 SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y 921 } 922 } {max(a) 3 max(b) 4 a 1 b 2} 923} 924do_test select1-11.16 { 925 execsql2 { 926 SELECT y.* FROM t3 as y, t4 as z 927 } 928} {a 1 b 2} 929 930# Tests of SELECT statements without a FROM clause. 931# 932do_test select1-12.1 { 933 execsql2 { 934 SELECT 1+2+3 935 } 936} {1+2+3 6} 937do_test select1-12.2 { 938 execsql2 { 939 SELECT 1,'hello',2 940 } 941} {1 1 'hello' hello 2 2} 942do_test select1-12.3 { 943 execsql2 { 944 SELECT 1 AS 'a','hello' AS 'b',2 AS 'c' 945 } 946} {a 1 b hello c 2} 947do_test select1-12.4 { 948 execsql { 949 DELETE FROM t3; 950 INSERT INTO t3 VALUES(1,2); 951 } 952} {} 953 954ifcapable compound { 955do_test select1-12.5 { 956 execsql { 957 SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a; 958 } 959} {1 2 3 4} 960 961do_test select1-12.6 { 962 execsql { 963 SELECT 3, 4 UNION SELECT * FROM t3; 964 } 965} {1 2 3 4} 966} ;# ifcapable compound 967 968ifcapable subquery { 969 do_test select1-12.7 { 970 execsql { 971 SELECT * FROM t3 WHERE a=(SELECT 1); 972 } 973 } {1 2} 974 do_test select1-12.8 { 975 execsql { 976 SELECT * FROM t3 WHERE a=(SELECT 2); 977 } 978 } {} 979} 980 981ifcapable {compound && subquery} { 982 do_test select1-12.9 { 983 execsql2 { 984 SELECT x FROM ( 985 SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b 986 ) ORDER BY x; 987 } 988 } {x 1 x 3} 989 do_test select1-12.10 { 990 execsql2 { 991 SELECT z.x FROM ( 992 SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b 993 ) AS 'z' ORDER BY x; 994 } 995 } {x 1 x 3} 996} ;# ifcapable compound 997 998 999# Check for a VDBE stack growth problem that existed at one point. 1000# 1001ifcapable subquery { 1002 do_test select1-13.1 { 1003 execsql { 1004 BEGIN; 1005 create TABLE abc(a, b, c, PRIMARY KEY(a, b)); 1006 INSERT INTO abc VALUES(1, 1, 1); 1007 } 1008 for {set i 0} {$i<10} {incr i} { 1009 execsql { 1010 INSERT INTO abc SELECT a+(select max(a) FROM abc), 1011 b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc; 1012 } 1013 } 1014 execsql {COMMIT} 1015 1016 # This used to seg-fault when the problem existed. 1017 execsql { 1018 SELECT count( 1019 (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) 1020 ) FROM abc AS upper; 1021 } 1022 } {0} 1023} 1024 1025foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] { 1026 db eval "DROP TABLE $tab" 1027} 1028db close 1029sqlite3 db test.db 1030 1031do_test select1-14.1 { 1032 execsql { 1033 SELECT * FROM sqlite_master WHERE rowid>10; 1034 SELECT * FROM sqlite_master WHERE rowid=10; 1035 SELECT * FROM sqlite_master WHERE rowid<10; 1036 SELECT * FROM sqlite_master WHERE rowid<=10; 1037 SELECT * FROM sqlite_master WHERE rowid>=10; 1038 SELECT * FROM sqlite_master; 1039 } 1040} {} 1041do_test select1-14.2 { 1042 execsql { 1043 SELECT 10 IN (SELECT rowid FROM sqlite_master); 1044 } 1045} {0} 1046 1047if {[db one {PRAGMA locking_mode}]=="normal"} { 1048 # Check that ticket #3771 has been fixed. This test does not 1049 # work with locking_mode=EXCLUSIVE so disable in that case. 1050 # 1051 do_test select1-15.1 { 1052 execsql { 1053 CREATE TABLE t1(a); 1054 CREATE INDEX i1 ON t1(a); 1055 INSERT INTO t1 VALUES(1); 1056 INSERT INTO t1 VALUES(2); 1057 INSERT INTO t1 VALUES(3); 1058 } 1059 } {} 1060 do_test select1-15.2 { 1061 sqlite3 db2 test.db 1062 execsql { DROP INDEX i1 } db2 1063 db2 close 1064 } {} 1065 do_test select1-15.3 { 1066 execsql { SELECT 2 IN (SELECT a FROM t1) } 1067 } {1} 1068} 1069 1070finish_test 1071