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.32 2004/05/28 11:37:29 danielk1977 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 26execsql {CREATE TABLE test1(f1 int, f2 int)} 27 28do_test select1-1.2 { 29 set v [catch {execsql {SELECT * FROM test1, test2}} msg] 30 lappend v $msg 31} {1 {no such table: test2}} 32do_test select1-1.3 { 33 set v [catch {execsql {SELECT * FROM test2, test1}} msg] 34 lappend v $msg 35} {1 {no such table: test2}} 36 37execsql {INSERT INTO test1(f1,f2) VALUES(11,22)} 38 39 40# Make sure the columns are extracted correctly. 41# 42do_test select1-1.4 { 43 execsql {SELECT f1 FROM test1} 44} {11} 45do_test select1-1.5 { 46 execsql {SELECT f2 FROM test1} 47} {22} 48do_test select1-1.6 { 49 execsql {SELECT f2, f1 FROM test1} 50} {22 11} 51do_test select1-1.7 { 52 execsql {SELECT f1, f2 FROM test1} 53} {11 22} 54do_test select1-1.8 { 55 execsql {SELECT * FROM test1} 56} {11 22} 57do_test select1-1.8.1 { 58 execsql {SELECT *, * FROM test1} 59} {11 22 11 22} 60do_test select1-1.8.2 { 61 execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1} 62} {11 22 11 22} 63do_test select1-1.8.3 { 64 execsql {SELECT 'one', *, 'two', * FROM test1} 65} {one 11 22 two 11 22} 66 67execsql {CREATE TABLE test2(r1 real, r2 real)} 68execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)} 69 70do_test select1-1.9 { 71 execsql {SELECT * FROM test1, test2} 72} {11 22 1.1 2.2} 73do_test select1-1.9.1 { 74 execsql {SELECT *, 'hi' FROM test1, test2} 75} {11 22 1.1 2.2 hi} 76do_test select1-1.9.2 { 77 execsql {SELECT 'one', *, 'two', * FROM test1, test2} 78} {one 11 22 1.1 2.2 two 11 22 1.1 2.2} 79do_test select1-1.10 { 80 execsql {SELECT test1.f1, test2.r1 FROM test1, test2} 81} {11 1.1} 82do_test select1-1.11 { 83 execsql {SELECT test1.f1, test2.r1 FROM test2, test1} 84} {11 1.1} 85do_test select1-1.11.1 { 86 execsql {SELECT * FROM test2, test1} 87} {1.1 2.2 11 22} 88do_test select1-1.11.2 { 89 execsql {SELECT * FROM test1 AS a, test1 AS b} 90} {11 22 11 22} 91do_test select1-1.12 { 92 execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2) 93 FROM test2, test1} 94} {11 2.2} 95do_test select1-1.13 { 96 execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2) 97 FROM test1, test2} 98} {1.1 22} 99 100set long {This is a string that is too big to fit inside a NBFS buffer} 101do_test select1-2.0 { 102 execsql " 103 DROP TABLE test2; 104 DELETE FROM test1; 105 INSERT INTO test1 VALUES(11,22); 106 INSERT INTO test1 VALUES(33,44); 107 CREATE TABLE t3(a,b); 108 INSERT INTO t3 VALUES('abc',NULL); 109 INSERT INTO t3 VALUES(NULL,'xyz'); 110 INSERT INTO t3 SELECT * FROM test1; 111 CREATE TABLE t4(a,b); 112 INSERT INTO t4 VALUES(NULL,'$long'); 113 SELECT * FROM t3; 114 " 115} {abc {} {} xyz 11 22 33 44} 116 117# Error messges from sqliteExprCheck 118# 119do_test select1-2.1 { 120 set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg] 121 lappend v $msg 122} {1 {wrong number of arguments to function count()}} 123do_test select1-2.2 { 124 set v [catch {execsql {SELECT count(f1) FROM test1}} msg] 125 lappend v $msg 126} {0 2} 127do_test select1-2.3 { 128 set v [catch {execsql {SELECT Count() FROM test1}} msg] 129 lappend v $msg 130} {0 2} 131do_test select1-2.4 { 132 set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg] 133 lappend v $msg 134} {0 2} 135do_test select1-2.5 { 136 set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg] 137 lappend v $msg 138} {0 3} 139do_test select1-2.5.1 { 140 execsql {SELECT count(*),count(a),count(b) FROM t3} 141} {4 3 3} 142do_test select1-2.5.2 { 143 execsql {SELECT count(*),count(a),count(b) FROM t4} 144} {1 0 1} 145do_test select1-2.5.3 { 146 execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5} 147} {0 0 0} 148do_test select1-2.6 { 149 set v [catch {execsql {SELECT min(*) FROM test1}} msg] 150 lappend v $msg 151} {1 {wrong number of arguments to function min()}} 152do_test select1-2.7 { 153 set v [catch {execsql {SELECT Min(f1) FROM test1}} msg] 154 lappend v $msg 155} {0 11} 156do_test select1-2.8 { 157 set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg] 158 lappend v [lsort $msg] 159} {0 {11 33}} 160do_test select1-2.8.1 { 161 execsql {SELECT coalesce(min(a),'xyzzy') FROM t3} 162} {11} 163do_test select1-2.8.2 { 164 execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3} 165} {11} 166do_test select1-2.8.3 { 167 execsql {SELECT min(b), min(b) FROM t4} 168} [list $long $long] 169do_test select1-2.9 { 170 set v [catch {execsql {SELECT MAX(*) FROM test1}} msg] 171 lappend v $msg 172} {1 {wrong number of arguments to function MAX()}} 173do_test select1-2.10 { 174 set v [catch {execsql {SELECT Max(f1) FROM test1}} msg] 175 lappend v $msg 176} {0 33} 177do_test select1-2.11 { 178 set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg] 179 lappend v [lsort $msg] 180} {0 {22 44}} 181do_test select1-2.12 { 182 set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg] 183 lappend v [lsort $msg] 184} {0 {23 45}} 185do_test select1-2.13 { 186 set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] 187 lappend v $msg 188} {0 34} 189do_test select1-2.13.1 { 190 execsql {SELECT coalesce(max(a),'xyzzy') FROM t3} 191} {abc} 192do_test select1-2.13.2 { 193 execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3} 194} {xyzzy} 195do_test select1-2.14 { 196 set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] 197 lappend v $msg 198} {1 {wrong number of arguments to function SUM()}} 199do_test select1-2.15 { 200 set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg] 201 lappend v $msg 202} {0 44} 203do_test select1-2.16 { 204 set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg] 205 lappend v $msg 206} {1 {wrong number of arguments to function sum()}} 207do_test select1-2.17 { 208 set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] 209 lappend v $msg 210} {0 45} 211do_test select1-2.17.1 { 212 execsql {SELECT sum(a) FROM t3} 213} {44} 214do_test select1-2.18 { 215 set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg] 216 lappend v $msg 217} {1 {no such function: XYZZY}} 218do_test select1-2.19 { 219 set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] 220 lappend v $msg 221} {0 44} 222do_test select1-2.20 { 223 set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg] 224 lappend v $msg 225} {1 {misuse of aggregate function min()}} 226 227# WHERE clause expressions 228# 229do_test select1-3.1 { 230 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg] 231 lappend v $msg 232} {0 {}} 233do_test select1-3.2 { 234 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg] 235 lappend v $msg 236} {0 11} 237do_test select1-3.3 { 238 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg] 239 lappend v $msg 240} {0 11} 241do_test select1-3.4 { 242 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg] 243 lappend v [lsort $msg] 244} {0 {11 33}} 245do_test select1-3.5 { 246 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg] 247 lappend v [lsort $msg] 248} {0 33} 249do_test select1-3.6 { 250 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg] 251 lappend v [lsort $msg] 252} {0 33} 253do_test select1-3.7 { 254 set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg] 255 lappend v [lsort $msg] 256} {0 33} 257do_test select1-3.8 { 258 set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg] 259 lappend v [lsort $msg] 260} {0 {11 33}} 261do_test select1-3.9 { 262 set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg] 263 lappend v $msg 264} {1 {wrong number of arguments to function count()}} 265 266# ORDER BY expressions 267# 268do_test select1-4.1 { 269 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg] 270 lappend v $msg 271} {0 {11 33}} 272do_test select1-4.2 { 273 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg] 274 lappend v $msg 275} {0 {33 11}} 276do_test select1-4.3 { 277 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] 278 lappend v $msg 279} {0 {11 33}} 280do_test select1-4.4 { 281 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] 282 lappend v $msg 283} {1 {misuse of aggregate function min()}} 284do_test select1-4.5 { 285 catchsql { 286 SELECT f1 FROM test1 ORDER BY 8.4; 287 } 288} {1 {ORDER BY terms must not be non-integer constants}} 289do_test select1-4.6 { 290 catchsql { 291 SELECT f1 FROM test1 ORDER BY '8.4'; 292 } 293} {1 {ORDER BY terms must not be non-integer constants}} 294do_test select1-4.7 { 295 catchsql { 296 SELECT f1 FROM test1 ORDER BY 'xyz'; 297 } 298} {1 {ORDER BY terms must not be non-integer constants}} 299do_test select1-4.8 { 300 execsql { 301 CREATE TABLE t5(a,b); 302 INSERT INTO t5 VALUES(1,10); 303 INSERT INTO t5 VALUES(2,9); 304 SELECT * FROM t5 ORDER BY 1; 305 } 306} {1 10 2 9} 307do_test select1-4.9 { 308 execsql { 309 SELECT * FROM t5 ORDER BY 2; 310 } 311} {2 9 1 10} 312do_test select1-4.10 { 313 catchsql { 314 SELECT * FROM t5 ORDER BY 3; 315 } 316} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}} 317do_test select1-4.11 { 318 execsql { 319 INSERT INTO t5 VALUES(3,10); 320 SELECT * FROM t5 ORDER BY 2, 1 DESC; 321 } 322} {2 9 3 10 1 10} 323do_test select1-4.12 { 324 execsql { 325 SELECT * FROM t5 ORDER BY 1 DESC, b; 326 } 327} {3 10 2 9 1 10} 328do_test select1-4.13 { 329 execsql { 330 SELECT * FROM t5 ORDER BY b DESC, 1; 331 } 332} {1 10 3 10 2 9} 333 334 335# ORDER BY ignored on an aggregate query 336# 337do_test select1-5.1 { 338 set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg] 339 lappend v $msg 340} {0 33} 341 342execsql {CREATE TABLE test2(t1 test, t2 text)} 343execsql {INSERT INTO test2 VALUES('abc','xyz')} 344 345# Check for column naming 346# 347do_test select1-6.1 { 348 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 349 lappend v $msg 350} {0 {f1 11 f1 33}} 351do_test select1-6.1.1 { 352 execsql {PRAGMA full_column_names=on} 353 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 354 lappend v $msg 355} {0 {test1.f1 11 test1.f1 33}} 356do_test select1-6.1.2 { 357 set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg] 358 lappend v $msg 359} {0 {f1 11 f1 33}} 360do_test select1-6.1.3 { 361 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 362 lappend v $msg 363} {0 {test1.f1 11 test1.f2 22}} 364do_test select1-6.1.4 { 365 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 366 execsql {PRAGMA full_column_names=off} 367 lappend v $msg 368} {0 {test1.f1 11 test1.f2 22}} 369do_test select1-6.1.5 { 370 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 371 lappend v $msg 372} {0 {f1 11 f2 22}} 373do_test select1-6.1.6 { 374 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 375 lappend v $msg 376} {0 {f1 11 f2 22}} 377do_test select1-6.2 { 378 set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg] 379 lappend v $msg 380} {0 {xyzzy 11 xyzzy 33}} 381do_test select1-6.3 { 382 set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg] 383 lappend v $msg 384} {0 {xyzzy 11 xyzzy 33}} 385do_test select1-6.3.1 { 386 set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg] 387 lappend v $msg 388} {0 {{xyzzy } 11 {xyzzy } 33}} 389do_test select1-6.4 { 390 set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg] 391 lappend v $msg 392} {0 {xyzzy 33 xyzzy 77}} 393do_test select1-6.4a { 394 set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg] 395 lappend v $msg 396} {0 {f1+F2 33 f1+F2 77}} 397do_test select1-6.5 { 398 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 399 lappend v $msg 400} {0 {test1.f1+F2 33 test1.f1+F2 77}} 401do_test select1-6.5.1 { 402 execsql2 {PRAGMA full_column_names=on} 403 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 404 execsql2 {PRAGMA full_column_names=off} 405 lappend v $msg 406} {0 {test1.f1+F2 33 test1.f1+F2 77}} 407do_test select1-6.6 { 408 set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 409 ORDER BY f2}} msg] 410 lappend v $msg 411} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}} 412do_test select1-6.7 { 413 set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 414 ORDER BY f2}} msg] 415 lappend v $msg 416} {0 {A.f1 11 t1 abc A.f1 33 t1 abc}} 417do_test select1-6.8 { 418 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 419 ORDER BY f2}} msg] 420 lappend v $msg 421} {1 {ambiguous column name: f1}} 422do_test select1-6.8b { 423 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 424 ORDER BY f2}} msg] 425 lappend v $msg 426} {1 {ambiguous column name: f2}} 427do_test select1-6.8c { 428 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 429 ORDER BY f2}} msg] 430 lappend v $msg 431} {1 {ambiguous column name: A.f1}} 432do_test select1-6.9 { 433 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 434 ORDER BY A.f1, B.f1}} msg] 435 lappend v $msg 436} {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}} 437do_test select1-6.10 { 438 set v [catch {execsql2 { 439 SELECT f1 FROM test1 UNION SELECT f2 FROM test1 440 ORDER BY f2; 441 }} msg] 442 lappend v $msg 443} {0 {f2 11 f2 22 f2 33 f2 44}} 444do_test select1-6.11 { 445 set v [catch {execsql2 { 446 SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 447 ORDER BY f2+100; 448 }} msg] 449 lappend v $msg 450} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}} 451 452do_test select1-7.1 { 453 set v [catch {execsql { 454 SELECT f1 FROM test1 WHERE f2=; 455 }} msg] 456 lappend v $msg 457} {1 {near ";": syntax error}} 458do_test select1-7.2 { 459 set v [catch {execsql { 460 SELECT f1 FROM test1 UNION SELECT WHERE; 461 }} msg] 462 lappend v $msg 463} {1 {near "WHERE": syntax error}} 464do_test select1-7.3 { 465 set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg] 466 lappend v $msg 467} {1 {near "as": syntax error}} 468do_test select1-7.4 { 469 set v [catch {execsql { 470 SELECT f1 FROM test1 ORDER BY; 471 }} msg] 472 lappend v $msg 473} {1 {near ";": syntax error}} 474do_test select1-7.5 { 475 set v [catch {execsql { 476 SELECT f1 FROM test1 ORDER BY f1 desc, f2 where; 477 }} msg] 478 lappend v $msg 479} {1 {near "where": syntax error}} 480do_test select1-7.6 { 481 set v [catch {execsql { 482 SELECT count(f1,f2 FROM test1; 483 }} msg] 484 lappend v $msg 485} {1 {near "FROM": syntax error}} 486do_test select1-7.7 { 487 set v [catch {execsql { 488 SELECT count(f1,f2+) FROM test1; 489 }} msg] 490 lappend v $msg 491} {1 {near ")": syntax error}} 492do_test select1-7.8 { 493 set v [catch {execsql { 494 SELECT f1 FROM test1 ORDER BY f2, f1+; 495 }} msg] 496 lappend v $msg 497} {1 {near ";": syntax error}} 498 499do_test select1-8.1 { 500 execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1} 501} {11 33} 502do_test select1-8.2 { 503 execsql { 504 SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20' 505 ORDER BY f1 506 } 507} {11} 508do_test select1-8.3 { 509 execsql { 510 SELECT f1 FROM test1 WHERE 5-3==2 511 ORDER BY f1 512 } 513} {11 33} 514 515# TODO: This test is failing because f1 is now being loaded off the 516# disk as a vdbe integer, not a string. Hence the value of f1/(f1-11) 517# changes because of rounding. Disable the test for now. 518if 0 { 519do_test select1-8.4 { 520 execsql { 521 SELECT coalesce(f1/(f1-11),'x'), 522 coalesce(min(f1/(f1-11),5),'y'), 523 coalesce(max(f1/(f1-33),6),'z') 524 FROM test1 ORDER BY f1 525 } 526} {x y 6 1.5 1.5 z} 527} 528do_test select1-8.5 { 529 execsql { 530 SELECT min(1,2,3), -max(1,2,3) 531 FROM test1 ORDER BY f1 532 } 533} {1 -3 1 -3} 534 535 536# Check the behavior when the result set is empty 537# 538# SQLite v3 always sets r(*). 539# 540# do_test select1-9.1 { 541# catch {unset r} 542# set r(*) {} 543# db eval {SELECT * FROM test1 WHERE f1<0} r {} 544# set r(*) 545# } {} 546do_test select1-9.2 { 547 execsql {PRAGMA empty_result_callbacks=on} 548 catch {unset r} 549 set r(*) {} 550 db eval {SELECT * FROM test1 WHERE f1<0} r {} 551 set r(*) 552} {f1 f2} 553do_test select1-9.3 { 554 set r(*) {} 555 db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {} 556 set r(*) 557} {f1 f2} 558do_test select1-9.4 { 559 set r(*) {} 560 db eval {SELECT * FROM test1 ORDER BY f1} r {} 561 set r(*) 562} {f1 f2} 563do_test select1-9.5 { 564 set r(*) {} 565 db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} 566 set r(*) 567} {f1 f2} 568unset r 569 570# Check for ORDER BY clauses that refer to an AS name in the column list 571# 572do_test select1-10.1 { 573 execsql { 574 SELECT f1 AS x FROM test1 ORDER BY x 575 } 576} {11 33} 577do_test select1-10.2 { 578 execsql { 579 SELECT f1 AS x FROM test1 ORDER BY -x 580 } 581} {33 11} 582do_test select1-10.3 { 583 execsql { 584 SELECT f1-23 AS x FROM test1 ORDER BY abs(x) 585 } 586} {10 -12} 587do_test select1-10.4 { 588 execsql { 589 SELECT f1-23 AS x FROM test1 ORDER BY -abs(x) 590 } 591} {-12 10} 592do_test select1-10.5 { 593 execsql { 594 SELECT f1-22 AS x, f2-22 as y FROM test1 595 } 596} {-11 0 11 22} 597do_test select1-10.6 { 598 execsql { 599 SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50 600 } 601} {11 22} 602 603# Check the ability to specify "TABLE.*" in the result set of a SELECT 604# 605do_test select1-11.1 { 606 execsql { 607 DELETE FROM t3; 608 DELETE FROM t4; 609 INSERT INTO t3 VALUES(1,2); 610 INSERT INTO t4 VALUES(3,4); 611 SELECT * FROM t3, t4; 612 } 613} {1 2 3 4} 614do_test select1-11.2 { 615 execsql2 { 616 SELECT * FROM t3, t4; 617 } 618} {t3.a 1 t3.b 2 t4.a 3 t4.b 4} 619do_test select1-11.3 { 620 execsql2 { 621 SELECT * FROM t3 AS x, t4 AS y; 622 } 623} {x.a 1 x.b 2 y.a 3 y.b 4} 624do_test select1-11.4 { 625 execsql { 626 SELECT t3.*, t4.b FROM t3, t4; 627 } 628} {1 2 4} 629do_test select1-11.5 { 630 execsql2 { 631 SELECT t3.*, t4.b FROM t3, t4; 632 } 633} {t3.a 1 t3.b 2 t4.b 4} 634do_test select1-11.6 { 635 execsql2 { 636 SELECT x.*, y.b FROM t3 AS x, t4 AS y; 637 } 638} {x.a 1 x.b 2 y.b 4} 639do_test select1-11.7 { 640 execsql { 641 SELECT t3.b, t4.* FROM t3, t4; 642 } 643} {2 3 4} 644do_test select1-11.8 { 645 execsql2 { 646 SELECT t3.b, t4.* FROM t3, t4; 647 } 648} {t3.b 2 t4.a 3 t4.b 4} 649do_test select1-11.9 { 650 execsql2 { 651 SELECT x.b, y.* FROM t3 AS x, t4 AS y; 652 } 653} {x.b 2 y.a 3 y.b 4} 654do_test select1-11.10 { 655 catchsql { 656 SELECT t5.* FROM t3, t4; 657 } 658} {1 {no such table: t5}} 659do_test select1-11.11 { 660 catchsql { 661 SELECT t3.* FROM t3 AS x, t4; 662 } 663} {1 {no such table: t3}} 664do_test select1-11.12 { 665 execsql2 { 666 SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4) 667 } 668} {t3.a 1 t3.b 2} 669do_test select1-11.13 { 670 execsql2 { 671 SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3 672 } 673} {t3.a 1 t3.b 2} 674do_test select1-11.14 { 675 execsql2 { 676 SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx' 677 } 678} {t3.a 1 t3.b 2 tx.max(a) 3 tx.max(b) 4} 679do_test select1-11.15 { 680 execsql2 { 681 SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y 682 } 683} {y.max(a) 3 y.max(b) 4 t3.a 1 t3.b 2} 684do_test select1-11.16 { 685 execsql2 { 686 SELECT y.* FROM t3 as y, t4 as z 687 } 688} {y.a 1 y.b 2} 689 690# Tests of SELECT statements without a FROM clause. 691# 692do_test select1-12.1 { 693 execsql2 { 694 SELECT 1+2+3 695 } 696} {1+2+3 6} 697do_test select1-12.2 { 698 execsql2 { 699 SELECT 1,'hello',2 700 } 701} {1 1 'hello' hello 2 2} 702do_test select1-12.3 { 703 execsql2 { 704 SELECT 1 AS 'a','hello' AS 'b',2 AS 'c' 705 } 706} {a 1 b hello c 2} 707do_test select1-12.4 { 708 execsql { 709 DELETE FROM t3; 710 INSERT INTO t3 VALUES(1,2); 711 SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a; 712 } 713} {1 2 3 4} 714do_test select1-12.5 { 715 execsql { 716 SELECT 3, 4 UNION SELECT * FROM t3; 717 } 718} {1 2 3 4} 719do_test select1-12.6 { 720 execsql { 721 SELECT * FROM t3 WHERE a=(SELECT 1); 722 } 723} {1 2} 724do_test select1-12.7 { 725 execsql { 726 SELECT * FROM t3 WHERE a=(SELECT 2); 727 } 728} {} 729do_test select1-12.8 { 730 execsql2 { 731 SELECT x FROM ( 732 SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 733 ) ORDER BY x; 734 } 735} {x 1 x 3} 736do_test select1-12.9 { 737 execsql2 { 738 SELECT z.x FROM ( 739 SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 740 ) AS 'z' ORDER BY x; 741 } 742} {z.x 1 z.x 3} 743 744 745finish_test 746