AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the SELECT statement.13#14# $Id: select1.test,v 1.70 2009/05/28 01:00:56 drh Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 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 $msg24} {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 $msg32} {1 {no such table: test2}}33do_test select1-1.3 {34 set v [catch {execsql {SELECT * FROM test2, test1}} msg]35 lappend v $msg36} {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 sqliteExprCheck119#120do_test select1-2.1 {121 set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg]122 lappend v $msg123} {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 $msg127} {0 2}128do_test select1-2.3 {129 set v [catch {execsql {SELECT Count() FROM test1}} msg]130 lappend v $msg131} {0 2}132do_test select1-2.4 {133 set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg]134 lappend v $msg135} {0 2}136do_test select1-2.5 {137 set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]138 lappend v $msg139} {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 $msg152} {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 $msg156} {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 $msg173} {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 $msg177} {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 $msg189} {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 $msg199} {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 $msg203} {0 44}204do_test select1-2.16 {205 set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg]206 lappend v $msg207} {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 $msg211} {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 $msg218} {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 $msg222} {0 44}223do_test select1-2.20 {224 set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg]225 lappend v $msg226} {1 {misuse of aggregate function min()}}227 228# Ticket #2526229#230do_test select1-2.21 {231 catchsql {232 SELECT min(f1) AS m FROM test1 GROUP BY f1 HAVING max(m+5)<10233 }234} {1 {misuse of aliased aggregate m}}235do_test select1-2.22 {236 catchsql {237 SELECT coalesce(min(f1)+5,11) AS m FROM test1238 GROUP BY f1239 HAVING max(m+5)<10240 }241} {1 {misuse of aliased aggregate m}}242do_test select1-2.23 {243 execsql {244 CREATE TABLE tkt2526(a,b,c PRIMARY KEY);245 INSERT INTO tkt2526 VALUES('x','y',NULL);246 INSERT INTO tkt2526 VALUES('x','z',NULL);247 }248 catchsql {249 SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)250 }251} {1 {misuse of aliased aggregate cn}}252 253# WHERE clause expressions254#255do_test select1-3.1 {256 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg]257 lappend v $msg258} {0 {}}259do_test select1-3.2 {260 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg]261 lappend v $msg262} {0 11}263do_test select1-3.3 {264 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg]265 lappend v $msg266} {0 11}267do_test select1-3.4 {268 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg]269 lappend v [lsort $msg]270} {0 {11 33}}271do_test select1-3.5 {272 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg]273 lappend v [lsort $msg]274} {0 33}275do_test select1-3.6 {276 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg]277 lappend v [lsort $msg]278} {0 33}279do_test select1-3.7 {280 set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg]281 lappend v [lsort $msg]282} {0 33}283do_test select1-3.8 {284 set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg]285 lappend v [lsort $msg]286} {0 {11 33}}287do_test select1-3.9 {288 set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg]289 lappend v $msg290} {1 {wrong number of arguments to function count()}}291 292# ORDER BY expressions293#294do_test select1-4.1 {295 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg]296 lappend v $msg297} {0 {11 33}}298do_test select1-4.2 {299 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg]300 lappend v $msg301} {0 {33 11}}302do_test select1-4.3 {303 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]304 lappend v $msg305} {0 {11 33}}306do_test select1-4.4 {307 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]308 lappend v $msg309} {1 {misuse of aggregate: min()}}310do_catchsql_test select1-4.5 {311 INSERT INTO test1(f1) SELECT f1 FROM test1 ORDER BY min(f1);312} {1 {misuse of aggregate: min()}}313 314# The restriction not allowing constants in the ORDER BY clause315# has been removed. See ticket #1768316#do_test select1-4.5 {317# catchsql {318# SELECT f1 FROM test1 ORDER BY 8.4;319# }320#} {1 {ORDER BY terms must not be non-integer constants}}321#do_test select1-4.6 {322# catchsql {323# SELECT f1 FROM test1 ORDER BY '8.4';324# }325#} {1 {ORDER BY terms must not be non-integer constants}}326#do_test select1-4.7.1 {327# catchsql {328# SELECT f1 FROM test1 ORDER BY 'xyz';329# }330#} {1 {ORDER BY terms must not be non-integer constants}}331#do_test select1-4.7.2 {332# catchsql {333# SELECT f1 FROM test1 ORDER BY -8.4;334# }335#} {1 {ORDER BY terms must not be non-integer constants}}336#do_test select1-4.7.3 {337# catchsql {338# SELECT f1 FROM test1 ORDER BY +8.4;339# }340#} {1 {ORDER BY terms must not be non-integer constants}}341#do_test select1-4.7.4 {342# catchsql {343# SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits344# }345#} {1 {ORDER BY terms must not be non-integer constants}}346 347do_test select1-4.5 {348 execsql {349 SELECT f1 FROM test1 ORDER BY 8.4350 }351} {11 33}352do_test select1-4.6 {353 execsql {354 SELECT f1 FROM test1 ORDER BY '8.4'355 }356} {11 33}357 358do_test select1-4.8 {359 execsql {360 CREATE TABLE t5(a,b);361 INSERT INTO t5 VALUES(1,10);362 INSERT INTO t5 VALUES(2,9);363 SELECT * FROM t5 ORDER BY 1;364 }365} {1 10 2 9}366do_test select1-4.9.1 {367 execsql {368 SELECT * FROM t5 ORDER BY 2;369 }370} {2 9 1 10}371do_test select1-4.9.2 {372 execsql {373 SELECT * FROM t5 ORDER BY +2;374 }375} {2 9 1 10}376do_test select1-4.10.1 {377 catchsql {378 SELECT * FROM t5 ORDER BY 3;379 }380} {1 {1st ORDER BY term out of range - should be between 1 and 2}}381do_test select1-4.10.2 {382 catchsql {383 SELECT * FROM t5 ORDER BY -1;384 }385} {1 {1st ORDER BY term out of range - should be between 1 and 2}}386do_test select1-4.11 {387 execsql {388 INSERT INTO t5 VALUES(3,10);389 SELECT * FROM t5 ORDER BY 2, 1 DESC;390 }391} {2 9 3 10 1 10}392do_test select1-4.12 {393 execsql {394 SELECT * FROM t5 ORDER BY 1 DESC, b;395 }396} {3 10 2 9 1 10}397do_test select1-4.13 {398 execsql {399 SELECT * FROM t5 ORDER BY b DESC, 1;400 }401} {1 10 3 10 2 9}402 403 404# ORDER BY ignored on an aggregate query405#406do_test select1-5.1 {407 set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]408 lappend v $msg409} {0 33}410 411execsql {CREATE TABLE test2(t1 text, t2 text)}412execsql {INSERT INTO test2 VALUES('abc','xyz')}413 414# Check for column naming415#416do_test select1-6.1 {417 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]418 lappend v $msg419} {0 {f1 11 f1 33}}420do_test select1-6.1.1 {421 db eval {PRAGMA full_column_names=on}422 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]423 lappend v $msg424} {0 {test1.f1 11 test1.f1 33}}425do_test select1-6.1.2 {426 set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]427 lappend v $msg428} {0 {f1 11 f1 33}}429do_test select1-6.1.3 {430 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]431 lappend v $msg432} {0 {f1 11 f2 22}}433do_test select1-6.1.4 {434 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]435 db eval {PRAGMA full_column_names=off}436 lappend v $msg437} {0 {f1 11 f2 22}}438do_test select1-6.1.5 {439 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]440 lappend v $msg441} {0 {f1 11 f2 22}}442do_test select1-6.1.6 {443 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]444 lappend v $msg445} {0 {f1 11 f2 22}}446do_test select1-6.2 {447 set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]448 lappend v $msg449} {0 {xyzzy 11 xyzzy 33}}450do_test select1-6.3 {451 set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]452 lappend v $msg453} {0 {xyzzy 11 xyzzy 33}}454do_test select1-6.3.1 {455 set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg]456 lappend v $msg457} {0 {{xyzzy } 11 {xyzzy } 33}}458do_test select1-6.4 {459 set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]460 lappend v $msg461} {0 {xyzzy 33 xyzzy 77}}462do_test select1-6.4a {463 set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]464 lappend v $msg465} {0 {f1+F2 33 f1+F2 77}}466do_test select1-6.5 {467 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]468 lappend v $msg469} {0 {test1.f1+F2 33 test1.f1+F2 77}}470do_test select1-6.5.1 {471 execsql2 {PRAGMA full_column_names=on}472 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]473 execsql2 {PRAGMA full_column_names=off}474 lappend v $msg475} {0 {test1.f1+F2 33 test1.f1+F2 77}}476do_test select1-6.6 {477 set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 478 ORDER BY f2}} msg]479 lappend v $msg480} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}}481do_test select1-6.7 {482 set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 483 ORDER BY f2}} msg]484 lappend v $msg485} {0 {f1 11 t1 abc f1 33 t1 abc}}486do_test select1-6.8 {487 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 488 ORDER BY f2}} msg]489 lappend v $msg490} {1 {ambiguous column name: f1}}491do_test select1-6.8b {492 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 493 ORDER BY f2}} msg]494 lappend v $msg495} {1 {ambiguous column name: f2}}496do_test select1-6.8c {497 set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 498 ORDER BY f2}} msg]499 lappend v $msg500} {1 {ambiguous column name: A.f1}}501do_test select1-6.9.1 {502 set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 503 ORDER BY A.f1, B.f1}} msg]504 lappend v $msg505} {0 {11 11 11 33 33 11 33 33}}506do_test select1-6.9.2 {507 set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 508 ORDER BY A.f1, B.f1}} msg]509 lappend v $msg510} {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}}511 512do_test select1-6.9.3 {513 db eval {514 PRAGMA short_column_names=OFF;515 PRAGMA full_column_names=OFF;516 }517 execsql2 {518 SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1519 }520} {{test1 . f1} 11 {test1 . f2} 22}521do_test select1-6.9.4 {522 db eval {523 PRAGMA short_column_names=OFF;524 PRAGMA full_column_names=ON;525 }526 execsql2 {527 SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1528 }529} {test1.f1 11 test1.f2 22}530do_test select1-6.9.5 {531 db eval {532 PRAGMA short_column_names=OFF;533 PRAGMA full_column_names=ON;534 }535 execsql2 {536 SELECT 123.45;537 }538} {123.45 123.45}539do_test select1-6.9.6 {540 execsql2 {541 SELECT * FROM test1 a, test1 b LIMIT 1542 }543} {a.f1 11 a.f2 22 b.f1 11 b.f2 22}544do_test select1-6.9.7 {545 set x [execsql2 {546 SELECT * FROM test1 a, (select 5, 6) LIMIT 1547 }]548 regsub -all {subquery-\d+} $x {subquery-0} x549 set x550} {a.f1 11 a.f2 22 (subquery-0).5 5 (subquery-0).6 6}551do_test select1-6.9.8 {552 set x [execsql2 {553 SELECT * FROM test1 a, (select 5 AS x, 6 AS y) AS b LIMIT 1554 }]555 regsub -all {subquery-\d+} $x {subquery-0} x556 set x557} {a.f1 11 a.f2 22 b.x 5 b.y 6}558do_test select1-6.9.9 {559 execsql2 {560 SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1561 }562} {test1.f1 11 test1.f2 22}563do_test select1-6.9.10 {564 execsql2 {565 SELECT f1, t1 FROM test1, test2 LIMIT 1566 }567} {test1.f1 11 test2.t1 abc}568do_test select1-6.9.11 {569 db eval {570 PRAGMA short_column_names=ON;571 PRAGMA full_column_names=ON;572 }573 execsql2 {574 SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1575 }576} {test1.f1 11 test1.f2 22}577do_test select1-6.9.12 {578 execsql2 {579 SELECT f1, t1 FROM test1, test2 LIMIT 1580 }581} {test1.f1 11 test2.t1 abc}582do_test select1-6.9.13 {583 db eval {584 PRAGMA short_column_names=ON;585 PRAGMA full_column_names=OFF;586 }587 execsql2 {588 SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1589 }590} {f1 11 f1 11}591do_test select1-6.9.14 {592 execsql2 {593 SELECT f1, t1 FROM test1, test2 LIMIT 1594 }595} {f1 11 t1 abc}596do_test select1-6.9.15 {597 db eval {598 PRAGMA short_column_names=OFF;599 PRAGMA full_column_names=ON;600 }601 execsql2 {602 SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1603 }604} {test1.f1 11 test1.f1 11}605do_test select1-6.9.16 {606 execsql2 {607 SELECT f1, t1 FROM test1, test2 LIMIT 1608 }609} {test1.f1 11 test2.t1 abc}610 611 612db eval {613 PRAGMA short_column_names=ON;614 PRAGMA full_column_names=OFF;615}616 617ifcapable compound {618do_test select1-6.10 {619 set v [catch {execsql2 {620 SELECT f1 FROM test1 UNION SELECT f2 FROM test1621 ORDER BY f2;622 }} msg]623 lappend v $msg624} {0 {f1 11 f1 22 f1 33 f1 44}}625do_test select1-6.11 {626 set v [catch {execsql2 {627 SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1628 ORDER BY f2+101;629 }} msg]630 lappend v $msg631} {1 {1st ORDER BY term does not match any column in the result set}}632 633# Ticket #2296634ifcapable subquery&&compound {635do_test select1-6.20 {636 execsql {637 CREATE TABLE t6(a TEXT, b TEXT);638 INSERT INTO t6 VALUES('a','0');639 INSERT INTO t6 VALUES('b','1');640 INSERT INTO t6 VALUES('c','2');641 INSERT INTO t6 VALUES('d','3');642 SELECT a FROM t6 WHERE b IN 643 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x644 ORDER BY 1 LIMIT 1)645 }646} {a}647do_test select1-6.21 {648 execsql {649 SELECT a FROM t6 WHERE b IN 650 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x651 ORDER BY 1 DESC LIMIT 1)652 }653} {d}654do_test select1-6.22 {655 execsql {656 SELECT a FROM t6 WHERE b IN 657 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x658 ORDER BY b LIMIT 2)659 ORDER BY a;660 }661} {a b}662do_test select1-6.23 {663 execsql {664 SELECT a FROM t6 WHERE b IN 665 (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x666 ORDER BY x DESC LIMIT 2)667 ORDER BY a;668 }669} {b d}670}671 672} ;#ifcapable compound673 674do_test select1-7.1 {675 set v [catch {execsql {676 SELECT f1 FROM test1 WHERE f2=;677 }} msg]678 lappend v $msg679} {1 {near ";": syntax error}}680ifcapable compound {681do_test select1-7.2 {682 set v [catch {execsql {683 SELECT f1 FROM test1 UNION SELECT WHERE;684 }} msg]685 lappend v $msg686} {1 {near "WHERE": syntax error}}687} ;# ifcapable compound688do_test select1-7.3 {689 set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]690 lappend v $msg691} {1 {incomplete input}}692do_test select1-7.4 {693 set v [catch {execsql {694 SELECT f1 FROM test1 ORDER BY;695 }} msg]696 lappend v $msg697} {1 {near ";": syntax error}}698do_test select1-7.5 {699 set v [catch {execsql {700 SELECT f1 FROM test1 ORDER BY f1 desc, f2 where;701 }} msg]702 lappend v $msg703} {1 {near "where": syntax error}}704do_test select1-7.6 {705 set v [catch {execsql {706 SELECT count(f1,f2 FROM test1;707 }} msg]708 lappend v $msg709} {1 {near "FROM": syntax error}}710do_test select1-7.7 {711 set v [catch {execsql {712 SELECT count(f1,f2+) FROM test1;713 }} msg]714 lappend v $msg715} {1 {near ")": syntax error}}716do_test select1-7.8 {717 set v [catch {execsql {718 SELECT f1 FROM test1 ORDER BY f2, f1+;719 }} msg]720 lappend v $msg721} {1 {near ";": syntax error}}722do_test select1-7.9 {723 catchsql {724 SELECT f1 FROM test1 LIMIT 5+3 OFFSET 11 ORDER BY f2;725 }726} {1 {near "ORDER": syntax error}}727 728do_test select1-8.1 {729 execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1}730} {11 33}731do_test select1-8.2 {732 execsql {733 SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20'734 ORDER BY f1735 }736} {11}737do_test select1-8.3 {738 execsql {739 SELECT f1 FROM test1 WHERE 5-3==2740 ORDER BY f1741 }742} {11 33}743 744# TODO: This test is failing because f1 is now being loaded off the745# disk as a vdbe integer, not a string. Hence the value of f1/(f1-11)746# changes because of rounding. Disable the test for now.747if 0 {748do_test select1-8.4 {749 execsql {750 SELECT coalesce(f1/(f1-11),'x'),751 coalesce(min(f1/(f1-11),5),'y'),752 coalesce(max(f1/(f1-33),6),'z')753 FROM test1 ORDER BY f1754 }755} {x y 6 1.5 1.5 z}756}757do_test select1-8.5 {758 execsql {759 SELECT min(1,2,3), -max(1,2,3)760 FROM test1 ORDER BY f1761 }762} {1 -3 1 -3}763 764 765# Check the behavior when the result set is empty766#767# SQLite v3 always sets r(*).768#769# do_test select1-9.1 {770# catch {unset r}771# set r(*) {}772# db eval {SELECT * FROM test1 WHERE f1<0} r {}773# set r(*)774# } {}775do_test select1-9.2 {776 execsql {PRAGMA empty_result_callbacks=on}777 catch {unset r}778 set r(*) {}779 db eval {SELECT * FROM test1 WHERE f1<0} r {}780 set r(*)781} {f1 f2}782ifcapable subquery {783 do_test select1-9.3 {784 set r(*) {}785 db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}786 set r(*)787 } {f1 f2}788}789do_test select1-9.4 {790 set r(*) {}791 db eval {SELECT * FROM test1 ORDER BY f1} r {}792 set r(*)793} {f1 f2}794do_test select1-9.5 {795 set r(*) {}796 db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}797 set r(*)798} {f1 f2}799unset r800 801# Check for ORDER BY clauses that refer to an AS name in the column list802#803do_test select1-10.1 {804 execsql {805 SELECT f1 AS x FROM test1 ORDER BY x806 }807} {11 33}808do_test select1-10.2 {809 execsql {810 SELECT f1 AS x FROM test1 ORDER BY -x811 }812} {33 11}813do_test select1-10.3 {814 execsql {815 SELECT f1-23 AS x FROM test1 ORDER BY abs(x)816 }817} {10 -12}818do_test select1-10.4 {819 execsql {820 SELECT f1-23 AS x FROM test1 ORDER BY -abs(x)821 }822} {-12 10}823do_test select1-10.5 {824 execsql {825 SELECT f1-22 AS x, f2-22 as y FROM test1826 }827} {-11 0 11 22}828do_test select1-10.6 {829 execsql {830 SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50831 }832} {11 22}833do_test select1-10.7 {834 execsql {835 SELECT f1 COLLATE nocase AS x FROM test1 ORDER BY x836 }837} {11 33}838 839# Check the ability to specify "TABLE.*" in the result set of a SELECT840#841do_test select1-11.1 {842 execsql {843 DELETE FROM t3;844 DELETE FROM t4;845 INSERT INTO t3 VALUES(1,2);846 INSERT INTO t4 VALUES(3,4);847 SELECT * FROM t3, t4;848 }849} {1 2 3 4}850do_test select1-11.2.1 {851 execsql {852 SELECT * FROM t3, t4;853 }854} {1 2 3 4}855do_test select1-11.2.2 {856 execsql2 {857 SELECT * FROM t3, t4;858 }859} {a 3 b 4 a 3 b 4}860do_test select1-11.4.1 {861 execsql {862 SELECT t3.*, t4.b FROM t3, t4;863 }864} {1 2 4}865do_test select1-11.4.2 {866 execsql {867 SELECT "t3".*, t4.b FROM t3, t4;868 }869} {1 2 4}870do_test select1-11.5.1 {871 execsql2 {872 SELECT t3.*, t4.b FROM t3, t4;873 }874} {a 1 b 4 b 4}875do_test select1-11.6 {876 execsql2 {877 SELECT x.*, y.b FROM t3 AS x, t4 AS y;878 }879} {a 1 b 4 b 4}880do_test select1-11.7 {881 execsql {882 SELECT t3.b, t4.* FROM t3, t4;883 }884} {2 3 4}885do_test select1-11.8 {886 execsql2 {887 SELECT t3.b, t4.* FROM t3, t4;888 }889} {b 4 a 3 b 4}890do_test select1-11.9 {891 execsql2 {892 SELECT x.b, y.* FROM t3 AS x, t4 AS y;893 }894} {b 4 a 3 b 4}895do_test select1-11.10 {896 catchsql {897 SELECT t5.* FROM t3, t4;898 }899} {1 {no such table: t5}}900do_test select1-11.11 {901 catchsql {902 SELECT t3.* FROM t3 AS x, t4;903 }904} {1 {no such table: t3}}905ifcapable subquery {906 do_test select1-11.12 {907 execsql2 {908 SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)909 }910 } {a 1 b 2}911 do_test select1-11.13 {912 execsql2 {913 SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3914 }915 } {a 1 b 2}916 do_test select1-11.14 {917 execsql2 {918 SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'919 }920 } {a 1 b 2 max(a) 3 max(b) 4}921 do_test select1-11.15 {922 execsql2 {923 SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y924 }925 } {max(a) 3 max(b) 4 a 1 b 2}926}927do_test select1-11.16 {928 execsql2 {929 SELECT y.* FROM t3 as y, t4 as z930 }931} {a 1 b 2}932 933# Tests of SELECT statements without a FROM clause.934#935do_test select1-12.1 {936 execsql2 {937 SELECT 1+2+3938 }939} {1+2+3 6}940do_test select1-12.2 {941 execsql2 {942 SELECT 1,'hello',2943 }944} {1 1 'hello' hello 2 2}945do_test select1-12.3 {946 execsql2 {947 SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'948 }949} {a 1 b hello c 2}950do_test select1-12.4 {951 execsql {952 DELETE FROM t3;953 INSERT INTO t3 VALUES(1,2);954 }955} {}956 957ifcapable compound {958do_test select1-12.5 {959 execsql {960 SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;961 }962} {1 2 3 4}963 964do_test select1-12.6 {965 execsql {966 SELECT 3, 4 UNION SELECT * FROM t3;967 }968} {1 2 3 4}969} ;# ifcapable compound970 971ifcapable subquery {972 do_test select1-12.7 {973 execsql {974 SELECT * FROM t3 WHERE a=(SELECT 1);975 }976 } {1 2}977 do_test select1-12.8 {978 execsql {979 SELECT * FROM t3 WHERE a=(SELECT 2);980 }981 } {}982}983 984ifcapable {compound && subquery} {985 do_test select1-12.9 {986 execsql2 {987 SELECT x FROM (988 SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b989 ) ORDER BY x;990 }991 } {x 1 x 3}992 do_test select1-12.10 {993 execsql2 {994 SELECT z.x FROM (995 SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b996 ) AS 'z' ORDER BY x;997 }998 } {x 1 x 3}999} ;# ifcapable compound1000 1001 1002# Check for a VDBE stack growth problem that existed at one point.1003#1004ifcapable subquery {1005 do_test select1-13.1 {1006 execsql {1007 BEGIN;1008 create TABLE abc(a, b, c, PRIMARY KEY(a, b));1009 INSERT INTO abc VALUES(1, 1, 1);1010 }1011 for {set i 0} {$i<10} {incr i} {1012 execsql {1013 INSERT INTO abc SELECT a+(select max(a) FROM abc), 1014 b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;1015 }1016 }1017 execsql {COMMIT}1018 1019 # This used to seg-fault when the problem existed.1020 execsql {1021 SELECT count(1022 (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) 1023 ) FROM abc AS upper;1024 }1025 } {0}1026}1027 1028foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] {1029 db eval "DROP TABLE $tab"1030}1031db close1032sqlite3 db test.db1033 1034do_test select1-14.1 {1035 execsql { 1036 SELECT * FROM sqlite_master WHERE rowid>10; 1037 SELECT * FROM sqlite_master WHERE rowid=10;1038 SELECT * FROM sqlite_master WHERE rowid<10;1039 SELECT * FROM sqlite_master WHERE rowid<=10;1040 SELECT * FROM sqlite_master WHERE rowid>=10;1041 SELECT * FROM sqlite_master;1042 }1043} {}1044do_test select1-14.2 {1045 execsql { 1046 SELECT 10 IN (SELECT rowid FROM sqlite_master);1047 }1048} {0}1049 1050if {[db one {PRAGMA locking_mode}]=="normal"} {1051 # Check that ticket #3771 has been fixed. This test does not1052 # work with locking_mode=EXCLUSIVE so disable in that case.1053 #1054 do_test select1-15.1 {1055 execsql {1056 CREATE TABLE t1(a);1057 CREATE INDEX i1 ON t1(a);1058 INSERT INTO t1 VALUES(1);1059 INSERT INTO t1 VALUES(2);1060 INSERT INTO t1 VALUES(3);1061 }1062 } {}1063 do_test select1-15.2 {1064 sqlite3 db2 test.db1065 execsql { DROP INDEX i1 } db21066 db2 close1067 } {}1068 do_test select1-15.3 {1069 execsql { SELECT 2 IN (SELECT a FROM t1) }1070 } {1}1071}1072 1073# Crash bug reported on the mailing list on 2012-02-231074#1075do_test select1-16.1 {1076 catchsql {SELECT 1 FROM (SELECT *)}1077} {1 {no tables specified}}1078 1079# 2015-04-17: assertion fix.1080do_catchsql_test select1-16.2 {1081 SELECT 1 FROM sqlite_master LIMIT 1,#1;1082} {1 {near "#1": syntax error}}1083 1084# 2019-01-16 Chromium bug 9223121085# Sorting with a LIMIT clause using SRT_EphemTab and SRT_Table1086#1087do_execsql_test select1-17.1 {1088 DROP TABLE IF EXISTS t1;1089 DROP TABLE IF EXISTS t2;1090 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1);1091 CREATE TABLE t2(y,z); INSERT INTO t2 VALUES(2,3);1092 CREATE INDEX t2y ON t2(y);1093 SELECT * FROM t1,(SELECT * FROM t2 WHERE y=2 ORDER BY y,z);1094} {1 2 3}1095do_execsql_test select1-17.2 {1096 SELECT * FROM t1,(SELECT * FROM t2 WHERE y=2 ORDER BY y,z LIMIT 4);1097} {1 2 3}1098do_execsql_test select1-17.3 {1099 SELECT * FROM t1,(SELECT * FROM t2 WHERE y=21100 UNION ALL SELECT * FROM t2 WHERE y=3 ORDER BY y,z LIMIT 4);1101} {1 2 3}1102 1103# 2019-07-24 Ticket https://sqlite.org/src/tktview/c52b09c7f38903b13111104#1105do_execsql_test select1-18.1 {1106 DROP TABLE IF EXISTS t1;1107 DROP TABLE IF EXISTS t2;1108 CREATE TABLE t1(c);1109 CREATE TABLE t2(x PRIMARY KEY, y);1110 INSERT INTO t1(c) VALUES(123);1111 INSERT INTO t2(x) VALUES(123);1112 SELECT x FROM t2, t1 WHERE x BETWEEN c AND null OR x AND1113 x IN ((SELECT x FROM (SELECT x FROM t2, t1 1114 WHERE x BETWEEN (SELECT x FROM (SELECT x COLLATE rtrim 1115 FROM t2, t1 WHERE x BETWEEN c AND null1116 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null1117 OR x AND x IN (c)) AND null1118 OR NOT EXISTS(SELECT -4.81 FROM t1, t2 WHERE x BETWEEN c AND null1119 OR x AND x IN ((SELECT x FROM (SELECT x FROM t2, t11120 WHERE x BETWEEN (SELECT x FROM (SELECT x BETWEEN c AND null1121 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null1122 OR x AND x IN (c)) AND null1123 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null1124 OR x AND x IN (c)))) AND x IN (c)1125 ), t1 WHERE x BETWEEN c AND null1126 OR x AND x IN (c)));1127} {}1128do_execsql_test select1-18.2 {1129 DROP TABLE IF EXISTS t1;1130 DROP TABLE IF EXISTS t2;1131 CREATE TABLE t1(c);1132 CREATE TABLE t2(x PRIMARY KEY, y);1133 INSERT INTO t1(c) VALUES(123);1134 INSERT INTO t2(x) VALUES(123);1135 SELECT x FROM t2, t1 WHERE x BETWEEN c AND (c+1) OR x AND1136 x IN ((SELECT x FROM (SELECT x FROM t2, t1 1137 WHERE x BETWEEN (SELECT x FROM (SELECT x COLLATE rtrim 1138 FROM t2, t1 WHERE x BETWEEN c AND (c+1)1139 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND (c+1)1140 OR x AND x IN (c)) AND (c+1)1141 OR NOT EXISTS(SELECT -4.81 FROM t1, t2 WHERE x BETWEEN c AND (c+1)1142 OR x AND x IN ((SELECT x FROM (SELECT x FROM t2, t11143 WHERE x BETWEEN (SELECT x FROM (SELECT x BETWEEN c AND (c+1)1144 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND (c+1)1145 OR x AND x IN (c)) AND (c+1)1146 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND (c+1)1147 OR x AND x IN (c)))) AND x IN (c)1148 ), t1 WHERE x BETWEEN c AND (c+1)1149 OR x AND x IN (c)));1150} {123}1151do_execsql_test select1-18.3 {1152 SELECT 1 FROM t1 WHERE (1153 SELECT 2 FROM t2 WHERE (1154 SELECT 3 FROM (1155 SELECT x FROM t2 WHERE x=c OR x=(SELECT x FROM (VALUES(0)))1156 ) WHERE x>c OR x=c1157 )1158 );1159} {1}1160do_execsql_test select1-18.4 {1161 SELECT 1 FROM t1, t2 WHERE (1162 SELECT 3 FROM (1163 SELECT x FROM t2 WHERE x=c OR x=(SELECT x FROM (VALUES(0)))1164 ) WHERE x>c OR x=c1165 );1166} {1}1167 1168# 2019-12-17 gramfuzz find1169#1170do_execsql_test select1-19.10 {1171 DROP TABLE IF EXISTS t1;1172 CREATE TABLE t1(x);1173} {}1174do_catchsql_test select1-19.20 {1175 INSERT INTO t11176 SELECT 1,2,3,4,5,6,71177 UNION ALL SELECT 1,2,3,4,5,6,71178 ORDER BY 1;1179} {1 {table t1 has 1 columns but 7 values were supplied}}1180do_catchsql_test select1-19.21 {1181 INSERT INTO t11182 SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,151183 UNION ALL SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,151184 ORDER BY 1;1185} {1 {table t1 has 1 columns but 15 values were supplied}}1186 1187# 2020-01-01 Found by Yongheng's fuzzer1188#1189reset_db1190do_execsql_test select1-20.10 {1191 CREATE TABLE t1 (1192 a INTEGER PRIMARY KEY,1193 b AS('Y') UNIQUE1194 );1195 INSERT INTO t1(a) VALUES (10);1196 SELECT * FROM t1 JOIN t1 USING(a,b)1197 WHERE ((SELECT t1.a FROM t1 AS x GROUP BY b) AND b=0)1198 OR a = 10;1199} {10 Y}1200do_execsql_test select1-20.20 {