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