test/select1.test

   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_[0-9a-fA-F_]+} $x {subquery} x
 549   set x
 550 } {a.f1 11 a.f2 22 subquery.5 5 subquery.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_[0-9a-fA-F]+_} $x {subquery} 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 finish_test