lang/sql/sqlite/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
 311 # The restriction not allowing constants in the ORDER BY clause
 312 # has been removed.  See ticket #1768
 313 #do_test select1-4.5 {
 314 #  catchsql {
 315 #    SELECT f1 FROM test1 ORDER BY 8.4;
 316 #  }
 317 #} {1 {ORDER BY terms must not be non-integer constants}}
 318 #do_test select1-4.6 {
 319 #  catchsql {
 320 #    SELECT f1 FROM test1 ORDER BY '8.4';
 321 #  }
 322 #} {1 {ORDER BY terms must not be non-integer constants}}
 323 #do_test select1-4.7.1 {
 324 #  catchsql {
 325 #    SELECT f1 FROM test1 ORDER BY 'xyz';
 326 #  }
 327 #} {1 {ORDER BY terms must not be non-integer constants}}
 328 #do_test select1-4.7.2 {
 329 #  catchsql {
 330 #    SELECT f1 FROM test1 ORDER BY -8.4;
 331 #  }
 332 #} {1 {ORDER BY terms must not be non-integer constants}}
 333 #do_test select1-4.7.3 {
 334 #  catchsql {
 335 #    SELECT f1 FROM test1 ORDER BY +8.4;
 336 #  }
 337 #} {1 {ORDER BY terms must not be non-integer constants}}
 338 #do_test select1-4.7.4 {
 339 #  catchsql {
 340 #    SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits
 341 #  }
 342 #} {1 {ORDER BY terms must not be non-integer constants}}
 343
 344 do_test select1-4.5 {
 345   execsql {
 346     SELECT f1 FROM test1 ORDER BY 8.4
 347   }
 348 } {11 33}
 349 do_test select1-4.6 {
 350   execsql {
 351     SELECT f1 FROM test1 ORDER BY '8.4'
 352   }
 353 } {11 33}
 354
 355 do_test select1-4.8 {
 356   execsql {
 357     CREATE TABLE t5(a,b);
 358     INSERT INTO t5 VALUES(1,10);
 359     INSERT INTO t5 VALUES(2,9);
 360     SELECT * FROM t5 ORDER BY 1;
 361   }
 362 } {1 10 2 9}
 363 do_test select1-4.9.1 {
 364   execsql {
 365     SELECT * FROM t5 ORDER BY 2;
 366   }
 367 } {2 9 1 10}
 368 do_test select1-4.9.2 {
 369   execsql {
 370     SELECT * FROM t5 ORDER BY +2;
 371   }
 372 } {2 9 1 10}
 373 do_test select1-4.10.1 {
 374   catchsql {
 375     SELECT * FROM t5 ORDER BY 3;
 376   }
 377 } {1 {1st ORDER BY term out of range - should be between 1 and 2}}
 378 do_test select1-4.10.2 {
 379   catchsql {
 380     SELECT * FROM t5 ORDER BY -1;
 381   }
 382 } {1 {1st ORDER BY term out of range - should be between 1 and 2}}
 383 do_test select1-4.11 {
 384   execsql {
 385     INSERT INTO t5 VALUES(3,10);
 386     SELECT * FROM t5 ORDER BY 2, 1 DESC;
 387   }
 388 } {2 9 3 10 1 10}
 389 do_test select1-4.12 {
 390   execsql {
 391     SELECT * FROM t5 ORDER BY 1 DESC, b;
 392   }
 393 } {3 10 2 9 1 10}
 394 do_test select1-4.13 {
 395   execsql {
 396     SELECT * FROM t5 ORDER BY b DESC, 1;
 397   }
 398 } {1 10 3 10 2 9}
 399
 400
 401 # ORDER BY ignored on an aggregate query
 402 #
 403 do_test select1-5.1 {
 404   set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
 405   lappend v $msg
 406 } {0 33}
 407
 408 execsql {CREATE TABLE test2(t1 text, t2 text)}
 409 execsql {INSERT INTO test2 VALUES('abc','xyz')}
 410
 411 # Check for column naming
 412 #
 413 do_test select1-6.1 {
 414   set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
 415   lappend v $msg
 416 } {0 {f1 11 f1 33}}
 417 do_test select1-6.1.1 {
 418   db eval {PRAGMA full_column_names=on}
 419   set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
 420   lappend v $msg
 421 } {0 {test1.f1 11 test1.f1 33}}
 422 do_test select1-6.1.2 {
 423   set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
 424   lappend v $msg
 425 } {0 {f1 11 f1 33}}
 426 do_test select1-6.1.3 {
 427   set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
 428   lappend v $msg
 429 } {0 {f1 11 f2 22}}
 430 do_test select1-6.1.4 {
 431   set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
 432   db eval {PRAGMA full_column_names=off}
 433   lappend v $msg
 434 } {0 {f1 11 f2 22}}
 435 do_test select1-6.1.5 {
 436   set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
 437   lappend v $msg
 438 } {0 {f1 11 f2 22}}
 439 do_test select1-6.1.6 {
 440   set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
 441   lappend v $msg
 442 } {0 {f1 11 f2 22}}
 443 do_test select1-6.2 {
 444   set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
 445   lappend v $msg
 446 } {0 {xyzzy 11 xyzzy 33}}
 447 do_test select1-6.3 {
 448   set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
 449   lappend v $msg
 450 } {0 {xyzzy 11 xyzzy 33}}
 451 do_test select1-6.3.1 {
 452   set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg]
 453   lappend v $msg
 454 } {0 {{xyzzy } 11 {xyzzy } 33}}
 455 do_test select1-6.4 {
 456   set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
 457   lappend v $msg
 458 } {0 {xyzzy 33 xyzzy 77}}
 459 do_test select1-6.4a {
 460   set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]
 461   lappend v $msg
 462 } {0 {f1+F2 33 f1+F2 77}}
 463 do_test select1-6.5 {
 464   set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
 465   lappend v $msg
 466 } {0 {test1.f1+F2 33 test1.f1+F2 77}}
 467 do_test select1-6.5.1 {
 468   execsql2 {PRAGMA full_column_names=on}
 469   set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
 470   execsql2 {PRAGMA full_column_names=off}
 471   lappend v $msg
 472 } {0 {test1.f1+F2 33 test1.f1+F2 77}}
 473 do_test select1-6.6 {
 474   set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2
 475          ORDER BY f2}} msg]
 476   lappend v $msg
 477 } {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}}
 478 do_test select1-6.7 {
 479   set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2
 480          ORDER BY f2}} msg]
 481   lappend v $msg
 482 } {0 {f1 11 t1 abc f1 33 t1 abc}}
 483 do_test select1-6.8 {
 484   set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B
 485          ORDER BY f2}} msg]
 486   lappend v $msg
 487 } {1 {ambiguous column name: f1}}
 488 do_test select1-6.8b {
 489   set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
 490          ORDER BY f2}} msg]
 491   lappend v $msg
 492 } {1 {ambiguous column name: f2}}
 493 do_test select1-6.8c {
 494   set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A
 495          ORDER BY f2}} msg]
 496   lappend v $msg
 497 } {1 {ambiguous column name: A.f1}}
 498 do_test select1-6.9.1 {
 499   set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
 500          ORDER BY A.f1, B.f1}} msg]
 501   lappend v $msg
 502 } {0 {11 11 11 33 33 11 33 33}}
 503 do_test select1-6.9.2 {
 504   set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
 505          ORDER BY A.f1, B.f1}} msg]
 506   lappend v $msg
 507 } {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}}
 508
 509 do_test select1-6.9.3 {
 510   db eval {
 511      PRAGMA short_column_names=OFF;
 512      PRAGMA full_column_names=OFF;
 513   }
 514   execsql2 {
 515      SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
 516   }
 517 } {{test1 . f1} 11 {test1 . f2} 22}
 518 do_test select1-6.9.4 {
 519   db eval {
 520      PRAGMA short_column_names=OFF;
 521      PRAGMA full_column_names=ON;
 522   }
 523   execsql2 {
 524      SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
 525   }
 526 } {test1.f1 11 test1.f2 22}
 527 do_test select1-6.9.5 {
 528   db eval {
 529      PRAGMA short_column_names=OFF;
 530      PRAGMA full_column_names=ON;
 531   }
 532   execsql2 {
 533      SELECT 123.45;
 534   }
 535 } {123.45 123.45}
 536 do_test select1-6.9.6 {
 537   execsql2 {
 538      SELECT * FROM test1 a, test1 b LIMIT 1
 539   }
 540 } {a.f1 11 a.f2 22 b.f1 11 b.f2 22}
 541 do_test select1-6.9.7 {
 542   set x [execsql2 {
 543      SELECT * FROM test1 a, (select 5, 6) LIMIT 1
 544   }]
 545   regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
 546   set x
 547 } {a.f1 11 a.f2 22 sqlite_subquery.5 5 sqlite_subquery.6 6}
 548 do_test select1-6.9.8 {
 549   set x [execsql2 {
 550      SELECT * FROM test1 a, (select 5 AS x, 6 AS y) AS b LIMIT 1
 551   }]
 552   regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
 553   set x
 554 } {a.f1 11 a.f2 22 b.x 5 b.y 6}
 555 do_test select1-6.9.9 {
 556   execsql2 {
 557      SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
 558   }
 559 } {test1.f1 11 test1.f2 22}
 560 do_test select1-6.9.10 {
 561   execsql2 {
 562      SELECT f1, t1 FROM test1, test2 LIMIT 1
 563   }
 564 } {test1.f1 11 test2.t1 abc}
 565 do_test select1-6.9.11 {
 566   db eval {
 567      PRAGMA short_column_names=ON;
 568      PRAGMA full_column_names=ON;
 569   }
 570   execsql2 {
 571      SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
 572   }
 573 } {test1.f1 11 test1.f2 22}
 574 do_test select1-6.9.12 {
 575   execsql2 {
 576      SELECT f1, t1 FROM test1, test2 LIMIT 1
 577   }
 578 } {test1.f1 11 test2.t1 abc}
 579 do_test select1-6.9.13 {
 580   db eval {
 581      PRAGMA short_column_names=ON;
 582      PRAGMA full_column_names=OFF;
 583   }
 584   execsql2 {
 585      SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
 586   }
 587 } {f1 11 f1 11}
 588 do_test select1-6.9.14 {
 589   execsql2 {
 590      SELECT f1, t1 FROM test1, test2 LIMIT 1
 591   }
 592 } {f1 11 t1 abc}
 593 do_test select1-6.9.15 {
 594   db eval {
 595      PRAGMA short_column_names=OFF;
 596      PRAGMA full_column_names=ON;
 597   }
 598   execsql2 {
 599      SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
 600   }
 601 } {test1.f1 11 test1.f1 11}
 602 do_test select1-6.9.16 {
 603   execsql2 {
 604      SELECT f1, t1 FROM test1, test2 LIMIT 1
 605   }
 606 } {test1.f1 11 test2.t1 abc}
 607
 608
 609 db eval {
 610   PRAGMA short_column_names=ON;
 611   PRAGMA full_column_names=OFF;
 612 }
 613
 614 ifcapable compound {
 615 do_test select1-6.10 {
 616   set v [catch {execsql2 {
 617     SELECT f1 FROM test1 UNION SELECT f2 FROM test1
 618     ORDER BY f2;
 619   }} msg]
 620   lappend v $msg
 621 } {0 {f1 11 f1 22 f1 33 f1 44}}
 622 do_test select1-6.11 {
 623   set v [catch {execsql2 {
 624     SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
 625     ORDER BY f2+101;
 626   }} msg]
 627   lappend v $msg
 628 } {1 {1st ORDER BY term does not match any column in the result set}}
 629
 630 # Ticket #2296
 631 ifcapable subquery&&compound {
 632 do_test select1-6.20 {
 633    execsql {
 634      CREATE TABLE t6(a TEXT, b TEXT);
 635      INSERT INTO t6 VALUES('a','0');
 636      INSERT INTO t6 VALUES('b','1');
 637      INSERT INTO t6 VALUES('c','2');
 638      INSERT INTO t6 VALUES('d','3');
 639      SELECT a FROM t6 WHERE b IN
 640         (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
 641                  ORDER BY 1 LIMIT 1)
 642    }
 643 } {a}
 644 do_test select1-6.21 {
 645    execsql {
 646      SELECT a FROM t6 WHERE b IN
 647         (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
 648                  ORDER BY 1 DESC LIMIT 1)
 649    }
 650 } {d}
 651 do_test select1-6.22 {
 652    execsql {
 653      SELECT a FROM t6 WHERE b IN
 654         (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
 655                  ORDER BY b LIMIT 2)
 656      ORDER BY a;
 657    }
 658 } {a b}
 659 do_test select1-6.23 {
 660    execsql {
 661      SELECT a FROM t6 WHERE b IN
 662         (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
 663                  ORDER BY x DESC LIMIT 2)
 664      ORDER BY a;
 665    }
 666 } {b d}
 667 }
 668
 669 } ;#ifcapable compound
 670
 671 do_test select1-7.1 {
 672   set v [catch {execsql {
 673      SELECT f1 FROM test1 WHERE f2=;
 674   }} msg]
 675   lappend v $msg
 676 } {1 {near ";": syntax error}}
 677 ifcapable compound {
 678 do_test select1-7.2 {
 679   set v [catch {execsql {
 680      SELECT f1 FROM test1 UNION SELECT WHERE;
 681   }} msg]
 682   lappend v $msg
 683 } {1 {near "WHERE": syntax error}}
 684 } ;# ifcapable compound
 685 do_test select1-7.3 {
 686   set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]
 687   lappend v $msg
 688 } {1 {near "as": syntax error}}
 689 do_test select1-7.4 {
 690   set v [catch {execsql {
 691      SELECT f1 FROM test1 ORDER BY;
 692   }} msg]
 693   lappend v $msg
 694 } {1 {near ";": syntax error}}
 695 do_test select1-7.5 {
 696   set v [catch {execsql {
 697      SELECT f1 FROM test1 ORDER BY f1 desc, f2 where;
 698   }} msg]
 699   lappend v $msg
 700 } {1 {near "where": syntax error}}
 701 do_test select1-7.6 {
 702   set v [catch {execsql {
 703      SELECT count(f1,f2 FROM test1;
 704   }} msg]
 705   lappend v $msg
 706 } {1 {near "FROM": syntax error}}
 707 do_test select1-7.7 {
 708   set v [catch {execsql {
 709      SELECT count(f1,f2+) FROM test1;
 710   }} msg]
 711   lappend v $msg
 712 } {1 {near ")": syntax error}}
 713 do_test select1-7.8 {
 714   set v [catch {execsql {
 715      SELECT f1 FROM test1 ORDER BY f2, f1+;
 716   }} msg]
 717   lappend v $msg
 718 } {1 {near ";": syntax error}}
 719 do_test select1-7.9 {
 720   catchsql {
 721      SELECT f1 FROM test1 LIMIT 5+3 OFFSET 11 ORDER BY f2;
 722   }
 723 } {1 {near "ORDER": syntax error}}
 724
 725 do_test select1-8.1 {
 726   execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1}
 727 } {11 33}
 728 do_test select1-8.2 {
 729   execsql {
 730     SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20'
 731     ORDER BY f1
 732   }
 733 } {11}
 734 do_test select1-8.3 {
 735   execsql {
 736     SELECT f1 FROM test1 WHERE 5-3==2
 737     ORDER BY f1
 738   }
 739 } {11 33}
 740
 741 # TODO: This test is failing because f1 is now being loaded off the
 742 # disk as a vdbe integer, not a string. Hence the value of f1/(f1-11)
 743 # changes because of rounding. Disable the test for now.
 744 if 0 {
 745 do_test select1-8.4 {
 746   execsql {
 747     SELECT coalesce(f1/(f1-11),'x'),
 748            coalesce(min(f1/(f1-11),5),'y'),
 749            coalesce(max(f1/(f1-33),6),'z')
 750     FROM test1 ORDER BY f1
 751   }
 752 } {x y 6 1.5 1.5 z}
 753 }
 754 do_test select1-8.5 {
 755   execsql {
 756     SELECT min(1,2,3), -max(1,2,3)
 757     FROM test1 ORDER BY f1
 758   }
 759 } {1 -3 1 -3}
 760
 761
 762 # Check the behavior when the result set is empty
 763 #
 764 # SQLite v3 always sets r(*).
 765 #
 766 # do_test select1-9.1 {
 767 #   catch {unset r}
 768 #   set r(*) {}
 769 #   db eval {SELECT * FROM test1 WHERE f1<0} r {}
 770 #   set r(*)
 771 # } {}
 772 do_test select1-9.2 {
 773   execsql {PRAGMA empty_result_callbacks=on}
 774   catch {unset r}
 775   set r(*) {}
 776   db eval {SELECT * FROM test1 WHERE f1<0} r {}
 777   set r(*)
 778 } {f1 f2}
 779 ifcapable subquery {
 780   do_test select1-9.3 {
 781     set r(*) {}
 782     db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
 783     set r(*)
 784   } {f1 f2}
 785 }
 786 do_test select1-9.4 {
 787   set r(*) {}
 788   db eval {SELECT * FROM test1 ORDER BY f1} r {}
 789   set r(*)
 790 } {f1 f2}
 791 do_test select1-9.5 {
 792   set r(*) {}
 793   db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}
 794   set r(*)
 795 } {f1 f2}
 796 unset r
 797
 798 # Check for ORDER BY clauses that refer to an AS name in the column list
 799 #
 800 do_test select1-10.1 {
 801   execsql {
 802     SELECT f1 AS x FROM test1 ORDER BY x
 803   }
 804 } {11 33}
 805 do_test select1-10.2 {
 806   execsql {
 807     SELECT f1 AS x FROM test1 ORDER BY -x
 808   }
 809 } {33 11}
 810 do_test select1-10.3 {
 811   execsql {
 812     SELECT f1-23 AS x FROM test1 ORDER BY abs(x)
 813   }
 814 } {10 -12}
 815 do_test select1-10.4 {
 816   execsql {
 817     SELECT f1-23 AS x FROM test1 ORDER BY -abs(x)
 818   }
 819 } {-12 10}
 820 do_test select1-10.5 {
 821   execsql {
 822     SELECT f1-22 AS x, f2-22 as y FROM test1
 823   }
 824 } {-11 0 11 22}
 825 do_test select1-10.6 {
 826   execsql {
 827     SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50
 828   }
 829 } {11 22}
 830 do_test select1-10.7 {
 831   execsql {
 832     SELECT f1 COLLATE nocase AS x FROM test1 ORDER BY x
 833   }
 834 } {11 33}
 835
 836 # Check the ability to specify "TABLE.*" in the result set of a SELECT
 837 #
 838 do_test select1-11.1 {
 839   execsql {
 840     DELETE FROM t3;
 841     DELETE FROM t4;
 842     INSERT INTO t3 VALUES(1,2);
 843     INSERT INTO t4 VALUES(3,4);
 844     SELECT * FROM t3, t4;
 845   }
 846 } {1 2 3 4}
 847 do_test select1-11.2.1 {
 848   execsql {
 849     SELECT * FROM t3, t4;
 850   }
 851 } {1 2 3 4}
 852 do_test select1-11.2.2 {
 853   execsql2 {
 854     SELECT * FROM t3, t4;
 855   }
 856 } {a 3 b 4 a 3 b 4}
 857 do_test select1-11.4.1 {
 858   execsql {
 859     SELECT t3.*, t4.b FROM t3, t4;
 860   }
 861 } {1 2 4}
 862 do_test select1-11.4.2 {
 863   execsql {
 864     SELECT "t3".*, t4.b FROM t3, t4;
 865   }
 866 } {1 2 4}
 867 do_test select1-11.5.1 {
 868   execsql2 {
 869     SELECT t3.*, t4.b FROM t3, t4;
 870   }
 871 } {a 1 b 4 b 4}
 872 do_test select1-11.6 {
 873   execsql2 {
 874     SELECT x.*, y.b FROM t3 AS x, t4 AS y;
 875   }
 876 } {a 1 b 4 b 4}
 877 do_test select1-11.7 {
 878   execsql {
 879     SELECT t3.b, t4.* FROM t3, t4;
 880   }
 881 } {2 3 4}
 882 do_test select1-11.8 {
 883   execsql2 {
 884     SELECT t3.b, t4.* FROM t3, t4;
 885   }
 886 } {b 4 a 3 b 4}
 887 do_test select1-11.9 {
 888   execsql2 {
 889     SELECT x.b, y.* FROM t3 AS x, t4 AS y;
 890   }
 891 } {b 4 a 3 b 4}
 892 do_test select1-11.10 {
 893   catchsql {
 894     SELECT t5.* FROM t3, t4;
 895   }
 896 } {1 {no such table: t5}}
 897 do_test select1-11.11 {
 898   catchsql {
 899     SELECT t3.* FROM t3 AS x, t4;
 900   }
 901 } {1 {no such table: t3}}
 902 ifcapable subquery {
 903   do_test select1-11.12 {
 904     execsql2 {
 905       SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
 906     }
 907   } {a 1 b 2}
 908   do_test select1-11.13 {
 909     execsql2 {
 910       SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
 911     }
 912   } {a 1 b 2}
 913   do_test select1-11.14 {
 914     execsql2 {
 915       SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
 916     }
 917   } {a 1 b 2 max(a) 3 max(b) 4}
 918   do_test select1-11.15 {
 919     execsql2 {
 920       SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
 921     }
 922   } {max(a) 3 max(b) 4 a 1 b 2}
 923 }
 924 do_test select1-11.16 {
 925   execsql2 {
 926     SELECT y.* FROM t3 as y, t4 as z
 927   }
 928 } {a 1 b 2}
 929
 930 # Tests of SELECT statements without a FROM clause.
 931 #
 932 do_test select1-12.1 {
 933   execsql2 {
 934     SELECT 1+2+3
 935   }
 936 } {1+2+3 6}
 937 do_test select1-12.2 {
 938   execsql2 {
 939     SELECT 1,'hello',2
 940   }
 941 } {1 1 'hello' hello 2 2}
 942 do_test select1-12.3 {
 943   execsql2 {
 944     SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
 945   }
 946 } {a 1 b hello c 2}
 947 do_test select1-12.4 {
 948   execsql {
 949     DELETE FROM t3;
 950     INSERT INTO t3 VALUES(1,2);
 951   }
 952 } {}
 953
 954 ifcapable compound {
 955 do_test select1-12.5 {
 956   execsql {
 957     SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
 958   }
 959 } {1 2 3 4}
 960
 961 do_test select1-12.6 {
 962   execsql {
 963     SELECT 3, 4 UNION SELECT * FROM t3;
 964   }
 965 } {1 2 3 4}
 966 } ;# ifcapable compound
 967
 968 ifcapable subquery {
 969   do_test select1-12.7 {
 970     execsql {
 971       SELECT * FROM t3 WHERE a=(SELECT 1);
 972     }
 973   } {1 2}
 974   do_test select1-12.8 {
 975     execsql {
 976       SELECT * FROM t3 WHERE a=(SELECT 2);
 977     }
 978   } {}
 979 }
 980
 981 ifcapable {compound && subquery} {
 982   do_test select1-12.9 {
 983     execsql2 {
 984       SELECT x FROM (
 985         SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b
 986       ) ORDER BY x;
 987     }
 988   } {x 1 x 3}
 989   do_test select1-12.10 {
 990     execsql2 {
 991       SELECT z.x FROM (
 992         SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b
 993       ) AS 'z' ORDER BY x;
 994     }
 995   } {x 1 x 3}
 996 } ;# ifcapable compound
 997
 998
 999 # Check for a VDBE stack growth problem that existed at one point.
1000 #
1001 ifcapable subquery {
1002   do_test select1-13.1 {
1003     execsql {
1004       BEGIN;
1005       create TABLE abc(a, b, c, PRIMARY KEY(a, b));
1006       INSERT INTO abc VALUES(1, 1, 1);
1007     }
1008     for {set i 0} {$i<10} {incr i} {
1009       execsql {
1010         INSERT INTO abc SELECT a+(select max(a) FROM abc),
1011             b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;
1012       }
1013     }
1014     execsql {COMMIT}
1015
1016     # This used to seg-fault when the problem existed.
1017     execsql {
1018       SELECT count(
1019         (SELECT a FROM abc WHERE a = NULL AND b >= upper.c)
1020       ) FROM abc AS upper;
1021     }
1022   } {0}
1023 }
1024
1025 foreach tab [db eval {SELECT name FROM sqlite_master WHERE type = 'table'}] {
1026   db eval "DROP TABLE $tab"
1027 }
1028 db close
1029 sqlite3 db test.db
1030
1031 do_test select1-14.1 {
1032   execsql {
1033     SELECT * FROM sqlite_master WHERE rowid>10;
1034     SELECT * FROM sqlite_master WHERE rowid=10;
1035     SELECT * FROM sqlite_master WHERE rowid<10;
1036     SELECT * FROM sqlite_master WHERE rowid<=10;
1037     SELECT * FROM sqlite_master WHERE rowid>=10;
1038     SELECT * FROM sqlite_master;
1039   }
1040 } {}
1041 do_test select1-14.2 {
1042   execsql {
1043     SELECT 10 IN (SELECT rowid FROM sqlite_master);
1044   }
1045 } {0}
1046
1047 if {[db one {PRAGMA locking_mode}]=="normal"} {
1048   # Check that ticket #3771 has been fixed.  This test does not
1049   # work with locking_mode=EXCLUSIVE so disable in that case.
1050   #
1051   do_test select1-15.1 {
1052     execsql {
1053       CREATE TABLE t1(a);
1054       CREATE INDEX i1 ON t1(a);
1055       INSERT INTO t1 VALUES(1);
1056       INSERT INTO t1 VALUES(2);
1057       INSERT INTO t1 VALUES(3);
1058     }
1059   } {}
1060   do_test select1-15.2 {
1061     sqlite3 db2 test.db
1062     execsql { DROP INDEX i1 } db2
1063     db2 close
1064   } {}
1065   do_test select1-15.3 {
1066     execsql { SELECT 2 IN (SELECT a FROM t1) }
1067   } {1}
1068 }
1069
1070 finish_test