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