1# 2# 2001 September 15 3# 4# The author disclaims copyright to this source code. In place of 5# a legal notice, here is a blessing: 6# 7# May you do good and not evil. 8# May you find forgiveness for yourself and forgive others. 9# May you share freely, never taking more than you give. 10# 11#*********************************************************************** 12# This file implements regression tests for SQLite library. The 13# focus of this file is testing the SELECT statement. 14# 15# $Id: select1.test,v 1.30.2.3 2004/07/20 01:45:49 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Try to select on a non-existant table. 21# 22do_test select1-1.1 { 23 set v [catch {execsql {SELECT * FROM test1}} msg] 24 lappend v $msg 25} {1 {no such table: test1}} 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} 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# WHERE clause expressions 229# 230do_test select1-3.1 { 231 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg] 232 lappend v $msg 233} {0 {}} 234do_test select1-3.2 { 235 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg] 236 lappend v $msg 237} {0 11} 238do_test select1-3.3 { 239 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg] 240 lappend v $msg 241} {0 11} 242do_test select1-3.4 { 243 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg] 244 lappend v [lsort $msg] 245} {0 {11 33}} 246do_test select1-3.5 { 247 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg] 248 lappend v [lsort $msg] 249} {0 33} 250do_test select1-3.6 { 251 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg] 252 lappend v [lsort $msg] 253} {0 33} 254do_test select1-3.7 { 255 set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg] 256 lappend v [lsort $msg] 257} {0 33} 258do_test select1-3.8 { 259 set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg] 260 lappend v [lsort $msg] 261} {0 {11 33}} 262do_test select1-3.9 { 263 set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg] 264 lappend v $msg 265} {1 {wrong number of arguments to function count()}} 266 267# ORDER BY expressions 268# 269do_test select1-4.1 { 270 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg] 271 lappend v $msg 272} {0 {11 33}} 273do_test select1-4.2 { 274 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg] 275 lappend v $msg 276} {0 {33 11}} 277do_test select1-4.3 { 278 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] 279 lappend v $msg 280} {0 {11 33}} 281do_test select1-4.4 { 282 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] 283 lappend v $msg 284} {1 {misuse of aggregate function min()}} 285do_test select1-4.5 { 286 catchsql { 287 SELECT f1 FROM test1 ORDER BY 8.4; 288 } 289} {1 {ORDER BY terms must not be non-integer constants}} 290do_test select1-4.6 { 291 catchsql { 292 SELECT f1 FROM test1 ORDER BY '8.4'; 293 } 294} {1 {ORDER BY terms must not be non-integer constants}} 295do_test select1-4.7 { 296 catchsql { 297 SELECT f1 FROM test1 ORDER BY 'xyz'; 298 } 299} {1 {ORDER BY terms must not be non-integer constants}} 300do_test select1-4.8 { 301 execsql { 302 CREATE TABLE t5(a,b); 303 INSERT INTO t5 VALUES(1,10); 304 INSERT INTO t5 VALUES(2,9); 305 SELECT * FROM t5 ORDER BY 1; 306 } 307} {1 10 2 9} 308do_test select1-4.9 { 309 execsql { 310 SELECT * FROM t5 ORDER BY 2; 311 } 312} {2 9 1 10} 313do_test select1-4.10 { 314 catchsql { 315 SELECT * FROM t5 ORDER BY 3; 316 } 317} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}} 318do_test select1-4.11 { 319 execsql { 320 INSERT INTO t5 VALUES(3,10); 321 SELECT * FROM t5 ORDER BY 2, 1 DESC; 322 } 323} {2 9 3 10 1 10} 324do_test select1-4.12 { 325 execsql { 326 SELECT * FROM t5 ORDER BY 1 DESC, b; 327 } 328} {3 10 2 9 1 10} 329do_test select1-4.13 { 330 execsql { 331 SELECT * FROM t5 ORDER BY b DESC, 1; 332 } 333} {1 10 3 10 2 9} 334 335 336# ORDER BY ignored on an aggregate query 337# 338do_test select1-5.1 { 339 set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg] 340 lappend v $msg 341} {0 33} 342 343execsql {CREATE TABLE test2(t1 test, t2 text)} 344execsql {INSERT INTO test2 VALUES('abc','xyz')} 345 346# Check for column naming 347# 348do_test select1-6.1 { 349 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 350 lappend v $msg 351} {0 {f1 11 f1 33}} 352do_test select1-6.1.1 { 353 execsql {PRAGMA full_column_names=on} 354 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 355 lappend v $msg 356} {0 {test1.f1 11 test1.f1 33}} 357do_test select1-6.1.2 { 358 set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg] 359 lappend v $msg 360} {0 {f1 11 f1 33}} 361do_test select1-6.1.3 { 362 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 363 lappend v $msg 364} {0 {test1.f1 11 test1.f2 22}} 365do_test select1-6.1.4 { 366 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 367 execsql {PRAGMA full_column_names=off} 368 lappend v $msg 369} {0 {test1.f1 11 test1.f2 22}} 370do_test select1-6.1.5 { 371 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 372 lappend v $msg 373} {0 {f1 11 f2 22}} 374do_test select1-6.1.6 { 375 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 376 lappend v $msg 377} {0 {f1 11 f2 22}} 378do_test select1-6.2 { 379 set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg] 380 lappend v $msg 381} {0 {xyzzy 11 xyzzy 33}} 382do_test select1-6.3 { 383 set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg] 384 lappend v $msg 385} {0 {xyzzy 11 xyzzy 33}} 386do_test select1-6.3.1 { 387 set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg] 388 lappend v $msg 389} {0 {{xyzzy } 11 {xyzzy } 33}} 390do_test select1-6.4 { 391 set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg] 392 lappend v $msg 393} {0 {xyzzy 33 xyzzy 77}} 394do_test select1-6.4a { 395 set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg] 396 lappend v $msg 397} {0 {f1+F2 33 f1+F2 77}} 398do_test select1-6.5 { 399 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 400 lappend v $msg 401} {0 {test1.f1+F2 33 test1.f1+F2 77}} 402do_test select1-6.5.1 { 403 execsql2 {PRAGMA full_column_names=on} 404 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 405 execsql2 {PRAGMA full_column_names=off} 406 lappend v $msg 407} {0 {test1.f1+F2 33 test1.f1+F2 77}} 408do_test select1-6.6 { 409 set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 410 ORDER BY f2}} msg] 411 lappend v $msg 412} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}} 413do_test select1-6.7 { 414 set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 415 ORDER BY f2}} msg] 416 lappend v $msg 417} {0 {A.f1 11 t1 abc A.f1 33 t1 abc}} 418do_test select1-6.8 { 419 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 420 ORDER BY f2}} msg] 421 lappend v $msg 422} {1 {ambiguous column name: f1}} 423do_test select1-6.8b { 424 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 425 ORDER BY f2}} msg] 426 lappend v $msg 427} {1 {ambiguous column name: f2}} 428do_test select1-6.8c { 429 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 430 ORDER BY f2}} msg] 431 lappend v $msg 432} {1 {ambiguous column name: A.f1}} 433do_test select1-6.9 { 434 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 435 ORDER BY A.f1, B.f1}} msg] 436 lappend v $msg 437} {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}} 438do_test select1-6.10 { 439 set v [catch {execsql2 { 440 SELECT f1 FROM test1 UNION SELECT f2 FROM test1 441 ORDER BY f2; 442 }} msg] 443 lappend v $msg 444} {0 {f2 11 f2 22 f2 33 f2 44}} 445do_test select1-6.11 { 446 set v [catch {execsql2 { 447 SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 448 ORDER BY f2+100; 449 }} msg] 450 lappend v $msg 451} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}} 452 453do_test select1-7.1 { 454 set v [catch {execsql { 455 SELECT f1 FROM test1 WHERE f2=; 456 }} msg] 457 lappend v $msg 458} {1 {near ";": syntax error}} 459do_test select1-7.2 { 460 set v [catch {execsql { 461 SELECT f1 FROM test1 UNION SELECT WHERE; 462 }} msg] 463 lappend v $msg 464} {1 {near "WHERE": syntax error}} 465do_test select1-7.3 { 466 set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg] 467 lappend v $msg 468} {1 {near "as": syntax error}} 469do_test select1-7.4 { 470 set v [catch {execsql { 471 SELECT f1 FROM test1 ORDER BY; 472 }} msg] 473 lappend v $msg 474} {1 {near ";": syntax error}} 475do_test select1-7.5 { 476 set v [catch {execsql { 477 SELECT f1 FROM test1 ORDER BY f1 desc, f2 where; 478 }} msg] 479 lappend v $msg 480} {1 {near "where": syntax error}} 481do_test select1-7.6 { 482 set v [catch {execsql { 483 SELECT count(f1,f2 FROM test1; 484 }} msg] 485 lappend v $msg 486} {1 {near "FROM": syntax error}} 487do_test select1-7.7 { 488 set v [catch {execsql { 489 SELECT count(f1,f2+) FROM test1; 490 }} msg] 491 lappend v $msg 492} {1 {near ")": syntax error}} 493do_test select1-7.8 { 494 set v [catch {execsql { 495 SELECT f1 FROM test1 ORDER BY f2, f1+; 496 }} msg] 497 lappend v $msg 498} {1 {near ";": syntax error}} 499 500do_test select1-8.1 { 501 execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1} 502} {11 33} 503do_test select1-8.2 { 504 execsql { 505 SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20' 506 ORDER BY f1 507 } 508} {11} 509do_test select1-8.3 { 510 execsql { 511 SELECT f1 FROM test1 WHERE 5-3==2 512 ORDER BY f1 513 } 514} {11 33} 515do_test select1-8.4 { 516 execsql { 517 SELECT coalesce(f1/(f1-11),'x'), 518 coalesce(min(f1/(f1-11),5),'y'), 519 coalesce(max(f1/(f1-33),6),'z') 520 FROM test1 ORDER BY f1 521 } 522} {x y 6 1.5 1.5 z} 523do_test select1-8.5 { 524 execsql { 525 SELECT min(1,2,3), -max(1,2,3) 526 FROM test1 ORDER BY f1 527 } 528} {1 -3 1 -3} 529 530 531# Check the behavior when the result set is empty 532# 533do_test select1-9.1 { 534 catch {unset r} 535 set r(*) {} 536 db eval {SELECT * FROM test1 WHERE f1<0} r {} 537 set r(*) 538} {} 539do_test select1-9.2 { 540 execsql {PRAGMA empty_result_callbacks=on} 541 set r(*) {} 542 db eval {SELECT * FROM test1 WHERE f1<0} r {} 543 set r(*) 544} {f1 f2} 545do_test select1-9.3 { 546 set r(*) {} 547 db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {} 548 set r(*) 549} {f1 f2} 550do_test select1-9.4 { 551 set r(*) {} 552 db eval {SELECT * FROM test1 ORDER BY f1} r {} 553 set r(*) 554} {f1 f2} 555do_test select1-9.5 { 556 set r(*) {} 557 db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} 558 set r(*) 559} {f1 f2} 560unset r 561 562# Check for ORDER BY clauses that refer to an AS name in the column list 563# 564do_test select1-10.1 { 565 execsql { 566 SELECT f1 AS x FROM test1 ORDER BY x 567 } 568} {11 33} 569do_test select1-10.2 { 570 execsql { 571 SELECT f1 AS x FROM test1 ORDER BY -x 572 } 573} {33 11} 574do_test select1-10.3 { 575 execsql { 576 SELECT f1-23 AS x FROM test1 ORDER BY abs(x) 577 } 578} {10 -12} 579do_test select1-10.4 { 580 execsql { 581 SELECT f1-23 AS x FROM test1 ORDER BY -abs(x) 582 } 583} {-12 10} 584do_test select1-10.5 { 585 execsql { 586 SELECT f1-22 AS x, f2-22 as y FROM test1 587 } 588} {-11 0 11 22} 589do_test select1-10.6 { 590 execsql { 591 SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50 592 } 593} {11 22} 594 595# Check the ability to specify "TABLE.*" in the result set of a SELECT 596# 597do_test select1-11.1 { 598 execsql { 599 DELETE FROM t3; 600 DELETE FROM t4; 601 INSERT INTO t3 VALUES(1,2); 602 INSERT INTO t4 VALUES(3,4); 603 SELECT * FROM t3, t4; 604 } 605} {1 2 3 4} 606do_test select1-11.2 { 607 execsql2 { 608 SELECT * FROM t3, t4; 609 } 610} {t3.a 1 t3.b 2 t4.a 3 t4.b 4} 611do_test select1-11.3 { 612 execsql2 { 613 SELECT * FROM t3 AS x, t4 AS y; 614 } 615} {x.a 1 x.b 2 y.a 3 y.b 4} 616do_test select1-11.4.1 { 617 execsql { 618 SELECT t3.*, t4.b FROM t3, t4; 619 } 620} {1 2 4} 621do_test select1-11.4.2 { 622 execsql { 623 SELECT "t3".*, t4.b FROM t3, t4; 624 } 625} {1 2 4} 626do_test select1-11.5 { 627 execsql2 { 628 SELECT t3.*, t4.b FROM t3, t4; 629 } 630} {t3.a 1 t3.b 2 t4.b 4} 631do_test select1-11.6 { 632 execsql2 { 633 SELECT x.*, y.b FROM t3 AS x, t4 AS y; 634 } 635} {x.a 1 x.b 2 y.b 4} 636do_test select1-11.7 { 637 execsql { 638 SELECT t3.b, t4.* FROM t3, t4; 639 } 640} {2 3 4} 641do_test select1-11.8 { 642 execsql2 { 643 SELECT t3.b, t4.* FROM t3, t4; 644 } 645} {t3.b 2 t4.a 3 t4.b 4} 646do_test select1-11.9 { 647 execsql2 { 648 SELECT x.b, y.* FROM t3 AS x, t4 AS y; 649 } 650} {x.b 2 y.a 3 y.b 4} 651do_test select1-11.10 { 652 catchsql { 653 SELECT t5.* FROM t3, t4; 654 } 655} {1 {no such table: t5}} 656do_test select1-11.11 { 657 catchsql { 658 SELECT t3.* FROM t3 AS x, t4; 659 } 660} {1 {no such table: t3}} 661do_test select1-11.12 { 662 execsql2 { 663 SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4) 664 } 665} {t3.a 1 t3.b 2} 666do_test select1-11.13 { 667 execsql2 { 668 SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3 669 } 670} {t3.a 1 t3.b 2} 671do_test select1-11.14 { 672 execsql2 { 673 SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx' 674 } 675} {t3.a 1 t3.b 2 tx.max(a) 3 tx.max(b) 4} 676do_test select1-11.15 { 677 execsql2 { 678 SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y 679 } 680} {y.max(a) 3 y.max(b) 4 t3.a 1 t3.b 2} 681do_test select1-11.16 { 682 execsql2 { 683 SELECT y.* FROM t3 as y, t4 as z 684 } 685} {y.a 1 y.b 2} 686 687# Tests of SELECT statements without a FROM clause. 688# 689do_test select1-12.1 { 690 execsql2 { 691 SELECT 1+2+3 692 } 693} {1+2+3 6} 694do_test select1-12.2 { 695 execsql2 { 696 SELECT 1,'hello',2 697 } 698} {1 1 'hello' hello 2 2} 699do_test select1-12.3 { 700 execsql2 { 701 SELECT 1 AS 'a','hello' AS 'b',2 AS 'c' 702 } 703} {a 1 b hello c 2} 704do_test select1-12.4 { 705 execsql { 706 DELETE FROM t3; 707 INSERT INTO t3 VALUES(1,2); 708 SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a; 709 } 710} {1 2 3 4} 711do_test select1-12.5 { 712 execsql { 713 SELECT 3, 4 UNION SELECT * FROM t3; 714 } 715} {1 2 3 4} 716do_test select1-12.6 { 717 execsql { 718 SELECT * FROM t3 WHERE a=(SELECT 1); 719 } 720} {1 2} 721do_test select1-12.7 { 722 execsql { 723 SELECT * FROM t3 WHERE a=(SELECT 2); 724 } 725} {} 726do_test select1-12.8 { 727 execsql2 { 728 SELECT x FROM ( 729 SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 730 ) ORDER BY x; 731 } 732} {x 1 x 3} 733do_test select1-12.9 { 734 execsql2 { 735 SELECT z.x FROM ( 736 SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 737 ) AS 'z' ORDER BY x; 738 } 739} {z.x 1 z.x 3} 740 741 742finish_test 743