gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/select1.test (about) 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.70 2009/05/28 01:00:56 drh Exp $ 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # Try to select on a non-existant table. 20 # 21 do_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 27 execsql {CREATE TABLE test1(f1 int, f2 int)} 28 29 do_test select1-1.2 { 30 set v [catch {execsql {SELECT * FROM test1, test2}} msg] 31 lappend v $msg 32 } {1 {no such table: test2}} 33 do_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 38 execsql {INSERT INTO test1(f1,f2) VALUES(11,22)} 39 40 41 # Make sure the columns are extracted correctly. 42 # 43 do_test select1-1.4 { 44 execsql {SELECT f1 FROM test1} 45 } {11} 46 do_test select1-1.5 { 47 execsql {SELECT f2 FROM test1} 48 } {22} 49 do_test select1-1.6 { 50 execsql {SELECT f2, f1 FROM test1} 51 } {22 11} 52 do_test select1-1.7 { 53 execsql {SELECT f1, f2 FROM test1} 54 } {11 22} 55 do_test select1-1.8 { 56 execsql {SELECT * FROM test1} 57 } {11 22} 58 do_test select1-1.8.1 { 59 execsql {SELECT *, * FROM test1} 60 } {11 22 11 22} 61 do_test select1-1.8.2 { 62 execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1} 63 } {11 22 11 22} 64 do_test select1-1.8.3 { 65 execsql {SELECT 'one', *, 'two', * FROM test1} 66 } {one 11 22 two 11 22} 67 68 execsql {CREATE TABLE test2(r1 real, r2 real)} 69 execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)} 70 71 do_test select1-1.9 { 72 execsql {SELECT * FROM test1, test2} 73 } {11 22 1.1 2.2} 74 do_test select1-1.9.1 { 75 execsql {SELECT *, 'hi' FROM test1, test2} 76 } {11 22 1.1 2.2 hi} 77 do_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} 80 do_test select1-1.10 { 81 execsql {SELECT test1.f1, test2.r1 FROM test1, test2} 82 } {11 1.1} 83 do_test select1-1.11 { 84 execsql {SELECT test1.f1, test2.r1 FROM test2, test1} 85 } {11 1.1} 86 do_test select1-1.11.1 { 87 execsql {SELECT * FROM test2, test1} 88 } {1.1 2.2 11 22} 89 do_test select1-1.11.2 { 90 execsql {SELECT * FROM test1 AS a, test1 AS b} 91 } {11 22 11 22} 92 do_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} 96 do_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 101 set long {This is a string that is too big to fit inside a NBFS buffer} 102 do_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 # 120 do_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()}} 124 do_test select1-2.2 { 125 set v [catch {execsql {SELECT count(f1) FROM test1}} msg] 126 lappend v $msg 127 } {0 2} 128 do_test select1-2.3 { 129 set v [catch {execsql {SELECT Count() FROM test1}} msg] 130 lappend v $msg 131 } {0 2} 132 do_test select1-2.4 { 133 set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg] 134 lappend v $msg 135 } {0 2} 136 do_test select1-2.5 { 137 set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg] 138 lappend v $msg 139 } {0 3} 140 do_test select1-2.5.1 { 141 execsql {SELECT count(*),count(a),count(b) FROM t3} 142 } {4 3 3} 143 do_test select1-2.5.2 { 144 execsql {SELECT count(*),count(a),count(b) FROM t4} 145 } {1 0 1} 146 do_test select1-2.5.3 { 147 execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5} 148 } {0 0 0} 149 do_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()}} 153 do_test select1-2.7 { 154 set v [catch {execsql {SELECT Min(f1) FROM test1}} msg] 155 lappend v $msg 156 } {0 11} 157 do_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}} 161 do_test select1-2.8.1 { 162 execsql {SELECT coalesce(min(a),'xyzzy') FROM t3} 163 } {11} 164 do_test select1-2.8.2 { 165 execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3} 166 } {11} 167 do_test select1-2.8.3 { 168 execsql {SELECT min(b), min(b) FROM t4} 169 } [list $long $long] 170 do_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()}} 174 do_test select1-2.10 { 175 set v [catch {execsql {SELECT Max(f1) FROM test1}} msg] 176 lappend v $msg 177 } {0 33} 178 do_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}} 182 do_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}} 186 do_test select1-2.13 { 187 set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] 188 lappend v $msg 189 } {0 34} 190 do_test select1-2.13.1 { 191 execsql {SELECT coalesce(max(a),'xyzzy') FROM t3} 192 } {abc} 193 do_test select1-2.13.2 { 194 execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3} 195 } {xyzzy} 196 do_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()}} 200 do_test select1-2.15 { 201 set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg] 202 lappend v $msg 203 } {0 44} 204 do_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()}} 208 do_test select1-2.17 { 209 set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] 210 lappend v $msg 211 } {0 45} 212 do_test select1-2.17.1 { 213 execsql {SELECT sum(a) FROM t3} 214 } {44.0} 215 do_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}} 219 do_test select1-2.19 { 220 set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] 221 lappend v $msg 222 } {0 44} 223 do_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 # Ticket #2526 229 # 230 do_test select1-2.21 { 231 catchsql { 232 SELECT min(f1) AS m FROM test1 GROUP BY f1 HAVING max(m+5)<10 233 } 234 } {1 {misuse of aliased aggregate m}} 235 do_test select1-2.22 { 236 catchsql { 237 SELECT coalesce(min(f1)+5,11) AS m FROM test1 238 GROUP BY f1 239 HAVING max(m+5)<10 240 } 241 } {1 {misuse of aliased aggregate m}} 242 do_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 expressions 254 # 255 do_test select1-3.1 { 256 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg] 257 lappend v $msg 258 } {0 {}} 259 do_test select1-3.2 { 260 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg] 261 lappend v $msg 262 } {0 11} 263 do_test select1-3.3 { 264 set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg] 265 lappend v $msg 266 } {0 11} 267 do_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}} 271 do_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} 275 do_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} 279 do_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} 283 do_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}} 287 do_test select1-3.9 { 288 set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg] 289 lappend v $msg 290 } {1 {wrong number of arguments to function count()}} 291 292 # ORDER BY expressions 293 # 294 do_test select1-4.1 { 295 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg] 296 lappend v $msg 297 } {0 {11 33}} 298 do_test select1-4.2 { 299 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg] 300 lappend v $msg 301 } {0 {33 11}} 302 do_test select1-4.3 { 303 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] 304 lappend v $msg 305 } {0 {11 33}} 306 do_test select1-4.4 { 307 set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] 308 lappend v $msg 309 } {1 {misuse of aggregate: min()}} 310 do_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 clause 315 # has been removed. See ticket #1768 316 #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 bits 344 # } 345 #} {1 {ORDER BY terms must not be non-integer constants}} 346 347 do_test select1-4.5 { 348 execsql { 349 SELECT f1 FROM test1 ORDER BY 8.4 350 } 351 } {11 33} 352 do_test select1-4.6 { 353 execsql { 354 SELECT f1 FROM test1 ORDER BY '8.4' 355 } 356 } {11 33} 357 358 do_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} 366 do_test select1-4.9.1 { 367 execsql { 368 SELECT * FROM t5 ORDER BY 2; 369 } 370 } {2 9 1 10} 371 do_test select1-4.9.2 { 372 execsql { 373 SELECT * FROM t5 ORDER BY +2; 374 } 375 } {2 9 1 10} 376 do_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}} 381 do_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}} 386 do_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} 392 do_test select1-4.12 { 393 execsql { 394 SELECT * FROM t5 ORDER BY 1 DESC, b; 395 } 396 } {3 10 2 9 1 10} 397 do_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 query 405 # 406 do_test select1-5.1 { 407 set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg] 408 lappend v $msg 409 } {0 33} 410 411 execsql {CREATE TABLE test2(t1 text, t2 text)} 412 execsql {INSERT INTO test2 VALUES('abc','xyz')} 413 414 # Check for column naming 415 # 416 do_test select1-6.1 { 417 set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 418 lappend v $msg 419 } {0 {f1 11 f1 33}} 420 do_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 $msg 424 } {0 {test1.f1 11 test1.f1 33}} 425 do_test select1-6.1.2 { 426 set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg] 427 lappend v $msg 428 } {0 {f1 11 f1 33}} 429 do_test select1-6.1.3 { 430 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 431 lappend v $msg 432 } {0 {f1 11 f2 22}} 433 do_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 $msg 437 } {0 {f1 11 f2 22}} 438 do_test select1-6.1.5 { 439 set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 440 lappend v $msg 441 } {0 {f1 11 f2 22}} 442 do_test select1-6.1.6 { 443 set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 444 lappend v $msg 445 } {0 {f1 11 f2 22}} 446 do_test select1-6.2 { 447 set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg] 448 lappend v $msg 449 } {0 {xyzzy 11 xyzzy 33}} 450 do_test select1-6.3 { 451 set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg] 452 lappend v $msg 453 } {0 {xyzzy 11 xyzzy 33}} 454 do_test select1-6.3.1 { 455 set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg] 456 lappend v $msg 457 } {0 {{xyzzy } 11 {xyzzy } 33}} 458 do_test select1-6.4 { 459 set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg] 460 lappend v $msg 461 } {0 {xyzzy 33 xyzzy 77}} 462 do_test select1-6.4a { 463 set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg] 464 lappend v $msg 465 } {0 {f1+F2 33 f1+F2 77}} 466 do_test select1-6.5 { 467 set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 468 lappend v $msg 469 } {0 {test1.f1+F2 33 test1.f1+F2 77}} 470 do_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 $msg 475 } {0 {test1.f1+F2 33 test1.f1+F2 77}} 476 do_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 $msg 480 } {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}} 481 do_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 $msg 485 } {0 {f1 11 t1 abc f1 33 t1 abc}} 486 do_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 $msg 490 } {1 {ambiguous column name: f1}} 491 do_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 $msg 495 } {1 {ambiguous column name: f2}} 496 do_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 $msg 500 } {1 {ambiguous column name: A.f1}} 501 do_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 $msg 505 } {0 {11 11 11 33 33 11 33 33}} 506 do_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 $msg 510 } {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}} 511 512 do_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 1 519 } 520 } {{test1 . f1} 11 {test1 . f2} 22} 521 do_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 1 528 } 529 } {test1.f1 11 test1.f2 22} 530 do_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} 539 do_test select1-6.9.6 { 540 execsql2 { 541 SELECT * FROM test1 a, test1 b LIMIT 1 542 } 543 } {a.f1 11 a.f2 22 b.f1 11 b.f2 22} 544 do_test select1-6.9.7 { 545 set x [execsql2 { 546 SELECT * FROM test1 a, (select 5, 6) LIMIT 1 547 }] 548 regsub -all {subquery-\d+} $x {subquery-0} x 549 set x 550 } {a.f1 11 a.f2 22 (subquery-0).5 5 (subquery-0).6 6} 551 do_test select1-6.9.8 { 552 set x [execsql2 { 553 SELECT * FROM test1 a, (select 5 AS x, 6 AS y) AS b LIMIT 1 554 }] 555 regsub -all {subquery-\d+} $x {subquery-0} x 556 set x 557 } {a.f1 11 a.f2 22 b.x 5 b.y 6} 558 do_test select1-6.9.9 { 559 execsql2 { 560 SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1 561 } 562 } {test1.f1 11 test1.f2 22} 563 do_test select1-6.9.10 { 564 execsql2 { 565 SELECT f1, t1 FROM test1, test2 LIMIT 1 566 } 567 } {test1.f1 11 test2.t1 abc} 568 do_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 1 575 } 576 } {test1.f1 11 test1.f2 22} 577 do_test select1-6.9.12 { 578 execsql2 { 579 SELECT f1, t1 FROM test1, test2 LIMIT 1 580 } 581 } {test1.f1 11 test2.t1 abc} 582 do_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 1 589 } 590 } {f1 11 f1 11} 591 do_test select1-6.9.14 { 592 execsql2 { 593 SELECT f1, t1 FROM test1, test2 LIMIT 1 594 } 595 } {f1 11 t1 abc} 596 do_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 1 603 } 604 } {test1.f1 11 test1.f1 11} 605 do_test select1-6.9.16 { 606 execsql2 { 607 SELECT f1, t1 FROM test1, test2 LIMIT 1 608 } 609 } {test1.f1 11 test2.t1 abc} 610 611 612 db eval { 613 PRAGMA short_column_names=ON; 614 PRAGMA full_column_names=OFF; 615 } 616 617 ifcapable compound { 618 do_test select1-6.10 { 619 set v [catch {execsql2 { 620 SELECT f1 FROM test1 UNION SELECT f2 FROM test1 621 ORDER BY f2; 622 }} msg] 623 lappend v $msg 624 } {0 {f1 11 f1 22 f1 33 f1 44}} 625 do_test select1-6.11 { 626 set v [catch {execsql2 { 627 SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 628 ORDER BY f2+101; 629 }} msg] 630 lappend v $msg 631 } {1 {1st ORDER BY term does not match any column in the result set}} 632 633 # Ticket #2296 634 ifcapable subquery&&compound { 635 do_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 x 644 ORDER BY 1 LIMIT 1) 645 } 646 } {a} 647 do_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 x 651 ORDER BY 1 DESC LIMIT 1) 652 } 653 } {d} 654 do_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 x 658 ORDER BY b LIMIT 2) 659 ORDER BY a; 660 } 661 } {a b} 662 do_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 x 666 ORDER BY x DESC LIMIT 2) 667 ORDER BY a; 668 } 669 } {b d} 670 } 671 672 } ;#ifcapable compound 673 674 do_test select1-7.1 { 675 set v [catch {execsql { 676 SELECT f1 FROM test1 WHERE f2=; 677 }} msg] 678 lappend v $msg 679 } {1 {near ";": syntax error}} 680 ifcapable compound { 681 do_test select1-7.2 { 682 set v [catch {execsql { 683 SELECT f1 FROM test1 UNION SELECT WHERE; 684 }} msg] 685 lappend v $msg 686 } {1 {near "WHERE": syntax error}} 687 } ;# ifcapable compound 688 do_test select1-7.3 { 689 set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg] 690 lappend v $msg 691 } {1 {incomplete input}} 692 do_test select1-7.4 { 693 set v [catch {execsql { 694 SELECT f1 FROM test1 ORDER BY; 695 }} msg] 696 lappend v $msg 697 } {1 {near ";": syntax error}} 698 do_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 $msg 703 } {1 {near "where": syntax error}} 704 do_test select1-7.6 { 705 set v [catch {execsql { 706 SELECT count(f1,f2 FROM test1; 707 }} msg] 708 lappend v $msg 709 } {1 {near "FROM": syntax error}} 710 do_test select1-7.7 { 711 set v [catch {execsql { 712 SELECT count(f1,f2+) FROM test1; 713 }} msg] 714 lappend v $msg 715 } {1 {near ")": syntax error}} 716 do_test select1-7.8 { 717 set v [catch {execsql { 718 SELECT f1 FROM test1 ORDER BY f2, f1+; 719 }} msg] 720 lappend v $msg 721 } {1 {near ";": syntax error}} 722 do_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 728 do_test select1-8.1 { 729 execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1} 730 } {11 33} 731 do_test select1-8.2 { 732 execsql { 733 SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20' 734 ORDER BY f1 735 } 736 } {11} 737 do_test select1-8.3 { 738 execsql { 739 SELECT f1 FROM test1 WHERE 5-3==2 740 ORDER BY f1 741 } 742 } {11 33} 743 744 # TODO: This test is failing because f1 is now being loaded off the 745 # 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. 747 if 0 { 748 do_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 f1 754 } 755 } {x y 6 1.5 1.5 z} 756 } 757 do_test select1-8.5 { 758 execsql { 759 SELECT min(1,2,3), -max(1,2,3) 760 FROM test1 ORDER BY f1 761 } 762 } {1 -3 1 -3} 763 764 765 # Check the behavior when the result set is empty 766 # 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 # } {} 775 do_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} 782 ifcapable 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 } 789 do_test select1-9.4 { 790 set r(*) {} 791 db eval {SELECT * FROM test1 ORDER BY f1} r {} 792 set r(*) 793 } {f1 f2} 794 do_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} 799 unset r 800 801 # Check for ORDER BY clauses that refer to an AS name in the column list 802 # 803 do_test select1-10.1 { 804 execsql { 805 SELECT f1 AS x FROM test1 ORDER BY x 806 } 807 } {11 33} 808 do_test select1-10.2 { 809 execsql { 810 SELECT f1 AS x FROM test1 ORDER BY -x 811 } 812 } {33 11} 813 do_test select1-10.3 { 814 execsql { 815 SELECT f1-23 AS x FROM test1 ORDER BY abs(x) 816 } 817 } {10 -12} 818 do_test select1-10.4 { 819 execsql { 820 SELECT f1-23 AS x FROM test1 ORDER BY -abs(x) 821 } 822 } {-12 10} 823 do_test select1-10.5 { 824 execsql { 825 SELECT f1-22 AS x, f2-22 as y FROM test1 826 } 827 } {-11 0 11 22} 828 do_test select1-10.6 { 829 execsql { 830 SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50 831 } 832 } {11 22} 833 do_test select1-10.7 { 834 execsql { 835 SELECT f1 COLLATE nocase AS x FROM test1 ORDER BY x 836 } 837 } {11 33} 838 839 # Check the ability to specify "TABLE.*" in the result set of a SELECT 840 # 841 do_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} 850 do_test select1-11.2.1 { 851 execsql { 852 SELECT * FROM t3, t4; 853 } 854 } {1 2 3 4} 855 do_test select1-11.2.2 { 856 execsql2 { 857 SELECT * FROM t3, t4; 858 } 859 } {a 3 b 4 a 3 b 4} 860 do_test select1-11.4.1 { 861 execsql { 862 SELECT t3.*, t4.b FROM t3, t4; 863 } 864 } {1 2 4} 865 do_test select1-11.4.2 { 866 execsql { 867 SELECT "t3".*, t4.b FROM t3, t4; 868 } 869 } {1 2 4} 870 do_test select1-11.5.1 { 871 execsql2 { 872 SELECT t3.*, t4.b FROM t3, t4; 873 } 874 } {a 1 b 4 b 4} 875 do_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} 880 do_test select1-11.7 { 881 execsql { 882 SELECT t3.b, t4.* FROM t3, t4; 883 } 884 } {2 3 4} 885 do_test select1-11.8 { 886 execsql2 { 887 SELECT t3.b, t4.* FROM t3, t4; 888 } 889 } {b 4 a 3 b 4} 890 do_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} 895 do_test select1-11.10 { 896 catchsql { 897 SELECT t5.* FROM t3, t4; 898 } 899 } {1 {no such table: t5}} 900 do_test select1-11.11 { 901 catchsql { 902 SELECT t3.* FROM t3 AS x, t4; 903 } 904 } {1 {no such table: t3}} 905 ifcapable 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), t3 914 } 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 y 924 } 925 } {max(a) 3 max(b) 4 a 1 b 2} 926 } 927 do_test select1-11.16 { 928 execsql2 { 929 SELECT y.* FROM t3 as y, t4 as z 930 } 931 } {a 1 b 2} 932 933 # Tests of SELECT statements without a FROM clause. 934 # 935 do_test select1-12.1 { 936 execsql2 { 937 SELECT 1+2+3 938 } 939 } {1+2+3 6} 940 do_test select1-12.2 { 941 execsql2 { 942 SELECT 1,'hello',2 943 } 944 } {1 1 'hello' hello 2 2} 945 do_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} 950 do_test select1-12.4 { 951 execsql { 952 DELETE FROM t3; 953 INSERT INTO t3 VALUES(1,2); 954 } 955 } {} 956 957 ifcapable compound { 958 do_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 964 do_test select1-12.6 { 965 execsql { 966 SELECT 3, 4 UNION SELECT * FROM t3; 967 } 968 } {1 2 3 4} 969 } ;# ifcapable compound 970 971 ifcapable 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 984 ifcapable {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,b 989 ) 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,b 996 ) AS 'z' ORDER BY x; 997 } 998 } {x 1 x 3} 999 } ;# ifcapable compound 1000 1001 1002 # Check for a VDBE stack growth problem that existed at one point. 1003 # 1004 ifcapable 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 1028 foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] { 1029 db eval "DROP TABLE $tab" 1030 } 1031 db close 1032 sqlite3 db test.db 1033 1034 do_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 } {} 1044 do_test select1-14.2 { 1045 execsql { 1046 SELECT 10 IN (SELECT rowid FROM sqlite_master); 1047 } 1048 } {0} 1049 1050 if {[db one {PRAGMA locking_mode}]=="normal"} { 1051 # Check that ticket #3771 has been fixed. This test does not 1052 # 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.db 1065 execsql { DROP INDEX i1 } db2 1066 db2 close 1067 } {} 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-23 1074 # 1075 do_test select1-16.1 { 1076 catchsql {SELECT 1 FROM (SELECT *)} 1077 } {1 {no tables specified}} 1078 1079 # 2015-04-17: assertion fix. 1080 do_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 922312 1085 # Sorting with a LIMIT clause using SRT_EphemTab and SRT_Table 1086 # 1087 do_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} 1095 do_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} 1098 do_execsql_test select1-17.3 { 1099 SELECT * FROM t1,(SELECT * FROM t2 WHERE y=2 1100 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/c52b09c7f38903b1311 1104 # 1105 do_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 AND 1113 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 null 1116 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null 1117 OR x AND x IN (c)) AND null 1118 OR NOT EXISTS(SELECT -4.81 FROM t1, t2 WHERE x BETWEEN c AND null 1119 OR x AND x IN ((SELECT x FROM (SELECT x FROM t2, t1 1120 WHERE x BETWEEN (SELECT x FROM (SELECT x BETWEEN c AND null 1121 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null 1122 OR x AND x IN (c)) AND null 1123 OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null 1124 OR x AND x IN (c)))) AND x IN (c) 1125 ), t1 WHERE x BETWEEN c AND null 1126 OR x AND x IN (c))); 1127 } {} 1128 do_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 AND 1136 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, t1 1143 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} 1151 do_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=c 1157 ) 1158 ); 1159 } {1} 1160 do_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=c 1165 ); 1166 } {1} 1167 1168 # 2019-12-17 gramfuzz find 1169 # 1170 do_execsql_test select1-19.10 { 1171 DROP TABLE IF EXISTS t1; 1172 CREATE TABLE t1(x); 1173 } {} 1174 do_catchsql_test select1-19.20 { 1175 INSERT INTO t1 1176 SELECT 1,2,3,4,5,6,7 1177 UNION ALL SELECT 1,2,3,4,5,6,7 1178 ORDER BY 1; 1179 } {1 {table t1 has 1 columns but 7 values were supplied}} 1180 do_catchsql_test select1-19.21 { 1181 INSERT INTO t1 1182 SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 1183 UNION ALL SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 1184 ORDER BY 1; 1185 } {1 {table t1 has 1 columns but 15 values were supplied}} 1186 1187 # 2020-01-01 Found by Yongheng's fuzzer 1188 # 1189 reset_db 1190 do_execsql_test select1-20.10 { 1191 CREATE TABLE t1 ( 1192 a INTEGER PRIMARY KEY, 1193 b AS('Y') UNIQUE 1194 ); 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} 1200 do_execsql_test select1-20.20 { 1201 SELECT ifnull(a, max((SELECT 123))), count(a) FROM t1 ; 1202 } {10 1} 1203 1204 # 2020-10-02 dbsqlfuzz find 1205 reset_db 1206 do_execsql_test select1-21.1 { 1207 CREATE TABLE t1(a IMTEGES PRIMARY KEY,R); 1208 CREATE TABLE t2(x UNIQUE); 1209 CREATE VIEW v1a(z,y) AS SELECT x IS NULL, x FROM t2; 1210 SELECT a,(+a)b,(+a)b,(+a)b,NOT EXISTS(SELECT null FROM t2),CASE z WHEN 487 THEN 992 WHEN 391 THEN 203 WHEN 10 THEN '?k<D Q' END,'' FROM t1 LEFT JOIN v1a ON z=b; 1211 } {} 1212 1213 finish_test