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.42 2005/01/21 04:25:47 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.0} 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.0} 211do_test select1-2.17.1 { 212 execsql {SELECT sum(a) FROM t3} 213} {44.0} 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.0} 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.1 { 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.7.2 { 300 catchsql { 301 SELECT f1 FROM test1 ORDER BY -8.4; 302 } 303} {1 {ORDER BY terms must not be non-integer constants}} 304do_test select1-4.7.3 { 305 catchsql { 306 SELECT f1 FROM test1 ORDER BY +8.4; 307 } 308} {1 {ORDER BY terms must not be non-integer constants}} 309do_test select1-4.7.4 { 310 catchsql { 311 SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits 312 } 313} {1 {ORDER BY terms must not be non-integer constants}} 314do_test select1-4.8 { 315 execsql { 316 CREATE TABLE t5(a,b); 317 INSERT INTO t5 VALUES(1,10); 318 INSERT INTO t5 VALUES(2,9); 319 SELECT * FROM t5 ORDER BY 1; 320 } 321} {1 10 2 9} 322do_test select1-4.9.1 { 323 execsql { 324 SELECT * FROM t5 ORDER BY 2; 325 } 326} {2 9 1 10} 327do_test select1-4.9.2 { 328 execsql { 329 SELECT * FROM t5 ORDER BY +2; 330 } 331} {2 9 1 10} 332do_test select1-4.10.1 { 333 catchsql { 334 SELECT * FROM t5 ORDER BY 3; 335 } 336} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}} 337do_test select1-4.10.2 { 338 catchsql { 339 SELECT * FROM t5 ORDER BY -1; 340 } 341} {1 {ORDER BY column number -1 out of range - should be between 1 and 2}} 342do_test select1-4.11 { 343 execsql { 344 INSERT INTO t5 VALUES(3,10); 345 SELECT * FROM t5 ORDER BY 2, 1 DESC; 346 } 347} {2 9 3 10 1 10} 348do_test select1-4.12 { 349 execsql { 350 SELECT * FROM t5 ORDER BY 1 DESC, b; 351 } 352} {3 10 2 9 1 10} 353do_test select1-4.13 { 354 execsql { 355 SELECT * FROM t5 ORDER BY b DESC, 1; 356 } 357} {1 10 3 10 2 9} 358 359 360# ORDER BY ignored on an aggregate query 361# 362do_test select1-5.1 { 363 set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg] 364 lappend v $msg 365} {0 33} 366 367execsql {CREATE TABLE test2(t1 test, t2 text)} 368execsql {INSERT INTO test2 VALUES('abc','xyz')} 369 370# Check for column naming 371# 372do_test select1-6.1 { 373 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 374 lappend v $msg 375} {0 {f1 11 f1 33}} 376do_test select1-6.1.1 { 377 execsql {PRAGMA full_column_names=on} 378 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 379 lappend v $msg 380} {0 {test1.f1 11 test1.f1 33}} 381do_test select1-6.1.2 { 382 set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg] 383 lappend v $msg 384} {0 {f1 11 f1 33}} 385do_test select1-6.1.3 { 386 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 387 lappend v $msg 388} {0 {f1 11 f2 22}} 389do_test select1-6.1.4 { 390 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 391 execsql {PRAGMA full_column_names=off} 392 lappend v $msg 393} {0 {f1 11 f2 22}} 394do_test select1-6.1.5 { 395 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 396 lappend v $msg 397} {0 {f1 11 f2 22}} 398do_test select1-6.1.6 { 399 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 400 lappend v $msg 401} {0 {f1 11 f2 22}} 402do_test select1-6.2 { 403 set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg] 404 lappend v $msg 405} {0 {xyzzy 11 xyzzy 33}} 406do_test select1-6.3 { 407 set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg] 408 lappend v $msg 409} {0 {xyzzy 11 xyzzy 33}} 410do_test select1-6.3.1 { 411 set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg] 412 lappend v $msg 413} {0 {{xyzzy } 11 {xyzzy } 33}} 414do_test select1-6.4 { 415 set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg] 416 lappend v $msg 417} {0 {xyzzy 33 xyzzy 77}} 418do_test select1-6.4a { 419 set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg] 420 lappend v $msg 421} {0 {f1+F2 33 f1+F2 77}} 422do_test select1-6.5 { 423 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 424 lappend v $msg 425} {0 {test1.f1+F2 33 test1.f1+F2 77}} 426do_test select1-6.5.1 { 427 execsql2 {PRAGMA full_column_names=on} 428 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 429 execsql2 {PRAGMA full_column_names=off} 430 lappend v $msg 431} {0 {test1.f1+F2 33 test1.f1+F2 77}} 432do_test select1-6.6 { 433 set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 434 ORDER BY f2}} msg] 435 lappend v $msg 436} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}} 437do_test select1-6.7 { 438 set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 439 ORDER BY f2}} msg] 440 lappend v $msg 441} {0 {f1 11 t1 abc f1 33 t1 abc}} 442do_test select1-6.8 { 443 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 444 ORDER BY f2}} msg] 445 lappend v $msg 446} {1 {ambiguous column name: f1}} 447do_test select1-6.8b { 448 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 449 ORDER BY f2}} msg] 450 lappend v $msg 451} {1 {ambiguous column name: f2}} 452do_test select1-6.8c { 453 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 454 ORDER BY f2}} msg] 455 lappend v $msg 456} {1 {ambiguous column name: A.f1}} 457do_test select1-6.9.1 { 458 set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 459 ORDER BY A.f1, B.f1}} msg] 460 lappend v $msg 461} {0 {11 11 11 33 33 11 33 33}} 462do_test select1-6.9.2 { 463 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 464 ORDER BY A.f1, B.f1}} msg] 465 lappend v $msg 466} {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}} 467 468ifcapable compound { 469do_test select1-6.10 { 470 set v [catch {execsql2 { 471 SELECT f1 FROM test1 UNION SELECT f2 FROM test1 472 ORDER BY f2; 473 }} msg] 474 lappend v $msg 475} {0 {f2 11 f2 22 f2 33 f2 44}} 476do_test select1-6.11 { 477 set v [catch {execsql2 { 478 SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 479 ORDER BY f2+100; 480 }} msg] 481 lappend v $msg 482} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}} 483} ;#ifcapable compound 484 485do_test select1-7.1 { 486 set v [catch {execsql { 487 SELECT f1 FROM test1 WHERE f2=; 488 }} msg] 489 lappend v $msg 490} {1 {near ";": syntax error}} 491ifcapable compound { 492do_test select1-7.2 { 493 set v [catch {execsql { 494 SELECT f1 FROM test1 UNION SELECT WHERE; 495 }} msg] 496 lappend v $msg 497} {1 {near "WHERE": syntax error}} 498} ;# ifcapable compound 499do_test select1-7.3 { 500 set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg] 501 lappend v $msg 502} {1 {near "as": syntax error}} 503do_test select1-7.4 { 504 set v [catch {execsql { 505 SELECT f1 FROM test1 ORDER BY; 506 }} msg] 507 lappend v $msg 508} {1 {near ";": syntax error}} 509do_test select1-7.5 { 510 set v [catch {execsql { 511 SELECT f1 FROM test1 ORDER BY f1 desc, f2 where; 512 }} msg] 513 lappend v $msg 514} {1 {near "where": syntax error}} 515do_test select1-7.6 { 516 set v [catch {execsql { 517 SELECT count(f1,f2 FROM test1; 518 }} msg] 519 lappend v $msg 520} {1 {near "FROM": syntax error}} 521do_test select1-7.7 { 522 set v [catch {execsql { 523 SELECT count(f1,f2+) FROM test1; 524 }} msg] 525 lappend v $msg 526} {1 {near ")": syntax error}} 527do_test select1-7.8 { 528 set v [catch {execsql { 529 SELECT f1 FROM test1 ORDER BY f2, f1+; 530 }} msg] 531 lappend v $msg 532} {1 {near ";": syntax error}} 533 534do_test select1-8.1 { 535 execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1} 536} {11 33} 537do_test select1-8.2 { 538 execsql { 539 SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20' 540 ORDER BY f1 541 } 542} {11} 543do_test select1-8.3 { 544 execsql { 545 SELECT f1 FROM test1 WHERE 5-3==2 546 ORDER BY f1 547 } 548} {11 33} 549 550# TODO: This test is failing because f1 is now being loaded off the 551# disk as a vdbe integer, not a string. Hence the value of f1/(f1-11) 552# changes because of rounding. Disable the test for now. 553if 0 { 554do_test select1-8.4 { 555 execsql { 556 SELECT coalesce(f1/(f1-11),'x'), 557 coalesce(min(f1/(f1-11),5),'y'), 558 coalesce(max(f1/(f1-33),6),'z') 559 FROM test1 ORDER BY f1 560 } 561} {x y 6 1.5 1.5 z} 562} 563do_test select1-8.5 { 564 execsql { 565 SELECT min(1,2,3), -max(1,2,3) 566 FROM test1 ORDER BY f1 567 } 568} {1 -3 1 -3} 569 570 571# Check the behavior when the result set is empty 572# 573# SQLite v3 always sets r(*). 574# 575# do_test select1-9.1 { 576# catch {unset r} 577# set r(*) {} 578# db eval {SELECT * FROM test1 WHERE f1<0} r {} 579# set r(*) 580# } {} 581do_test select1-9.2 { 582 execsql {PRAGMA empty_result_callbacks=on} 583 catch {unset r} 584 set r(*) {} 585 db eval {SELECT * FROM test1 WHERE f1<0} r {} 586 set r(*) 587} {f1 f2} 588ifcapable subquery { 589 do_test select1-9.3 { 590 set r(*) {} 591 db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {} 592 set r(*) 593 } {f1 f2} 594} 595do_test select1-9.4 { 596 set r(*) {} 597 db eval {SELECT * FROM test1 ORDER BY f1} r {} 598 set r(*) 599} {f1 f2} 600do_test select1-9.5 { 601 set r(*) {} 602 db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} 603 set r(*) 604} {f1 f2} 605unset r 606 607# Check for ORDER BY clauses that refer to an AS name in the column list 608# 609do_test select1-10.1 { 610 execsql { 611 SELECT f1 AS x FROM test1 ORDER BY x 612 } 613} {11 33} 614do_test select1-10.2 { 615 execsql { 616 SELECT f1 AS x FROM test1 ORDER BY -x 617 } 618} {33 11} 619do_test select1-10.3 { 620 execsql { 621 SELECT f1-23 AS x FROM test1 ORDER BY abs(x) 622 } 623} {10 -12} 624do_test select1-10.4 { 625 execsql { 626 SELECT f1-23 AS x FROM test1 ORDER BY -abs(x) 627 } 628} {-12 10} 629do_test select1-10.5 { 630 execsql { 631 SELECT f1-22 AS x, f2-22 as y FROM test1 632 } 633} {-11 0 11 22} 634do_test select1-10.6 { 635 execsql { 636 SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50 637 } 638} {11 22} 639 640# Check the ability to specify "TABLE.*" in the result set of a SELECT 641# 642do_test select1-11.1 { 643 execsql { 644 DELETE FROM t3; 645 DELETE FROM t4; 646 INSERT INTO t3 VALUES(1,2); 647 INSERT INTO t4 VALUES(3,4); 648 SELECT * FROM t3, t4; 649 } 650} {1 2 3 4} 651do_test select1-11.2.1 { 652 execsql { 653 SELECT * FROM t3, t4; 654 } 655} {1 2 3 4} 656do_test select1-11.2.2 { 657 execsql2 { 658 SELECT * FROM t3, t4; 659 } 660} {a 3 b 4 a 3 b 4} 661do_test select1-11.4.1 { 662 execsql { 663 SELECT t3.*, t4.b FROM t3, t4; 664 } 665} {1 2 4} 666do_test select1-11.4.2 { 667 execsql { 668 SELECT "t3".*, t4.b FROM t3, t4; 669 } 670} {1 2 4} 671do_test select1-11.5.1 { 672 execsql2 { 673 SELECT t3.*, t4.b FROM t3, t4; 674 } 675} {a 1 b 4 b 4} 676do_test select1-11.6 { 677 execsql2 { 678 SELECT x.*, y.b FROM t3 AS x, t4 AS y; 679 } 680} {a 1 b 4 b 4} 681do_test select1-11.7 { 682 execsql { 683 SELECT t3.b, t4.* FROM t3, t4; 684 } 685} {2 3 4} 686do_test select1-11.8 { 687 execsql2 { 688 SELECT t3.b, t4.* FROM t3, t4; 689 } 690} {b 4 a 3 b 4} 691do_test select1-11.9 { 692 execsql2 { 693 SELECT x.b, y.* FROM t3 AS x, t4 AS y; 694 } 695} {b 4 a 3 b 4} 696do_test select1-11.10 { 697 catchsql { 698 SELECT t5.* FROM t3, t4; 699 } 700} {1 {no such table: t5}} 701do_test select1-11.11 { 702 catchsql { 703 SELECT t3.* FROM t3 AS x, t4; 704 } 705} {1 {no such table: t3}} 706ifcapable subquery { 707 do_test select1-11.12 { 708 execsql2 { 709 SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4) 710 } 711 } {a 1 b 2} 712 do_test select1-11.13 { 713 execsql2 { 714 SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3 715 } 716 } {a 1 b 2} 717 do_test select1-11.14 { 718 execsql2 { 719 SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx' 720 } 721 } {a 1 b 2 max(a) 3 max(b) 4} 722 do_test select1-11.15 { 723 execsql2 { 724 SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y 725 } 726 } {max(a) 3 max(b) 4 a 1 b 2} 727} 728do_test select1-11.16 { 729 execsql2 { 730 SELECT y.* FROM t3 as y, t4 as z 731 } 732} {a 1 b 2} 733 734# Tests of SELECT statements without a FROM clause. 735# 736do_test select1-12.1 { 737 execsql2 { 738 SELECT 1+2+3 739 } 740} {1+2+3 6} 741do_test select1-12.2 { 742 execsql2 { 743 SELECT 1,'hello',2 744 } 745} {1 1 'hello' hello 2 2} 746do_test select1-12.3 { 747 execsql2 { 748 SELECT 1 AS 'a','hello' AS 'b',2 AS 'c' 749 } 750} {a 1 b hello c 2} 751do_test select1-12.4 { 752 execsql { 753 DELETE FROM t3; 754 INSERT INTO t3 VALUES(1,2); 755 } 756} {} 757 758ifcapable compound { 759do_test select1-12.5 { 760 execsql { 761 SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a; 762 } 763} {1 2 3 4} 764 765do_test select1-12.6 { 766 execsql { 767 SELECT 3, 4 UNION SELECT * FROM t3; 768 } 769} {1 2 3 4} 770} ;# ifcapable compound 771 772ifcapable subquery { 773 do_test select1-12.7 { 774 execsql { 775 SELECT * FROM t3 WHERE a=(SELECT 1); 776 } 777 } {1 2} 778 do_test select1-12.8 { 779 execsql { 780 SELECT * FROM t3 WHERE a=(SELECT 2); 781 } 782 } {} 783} 784 785ifcapable {compound && subquery} { 786 do_test select1-12.9 { 787 execsql2 { 788 SELECT x FROM ( 789 SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 790 ) ORDER BY x; 791 } 792 } {x 1 x 3} 793 do_test select1-12.10 { 794 execsql2 { 795 SELECT z.x FROM ( 796 SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 797 ) AS 'z' ORDER BY x; 798 } 799 } {x 1 x 3} 800} ;# ifcapable compound 801 802 803finish_test 804