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.65 2008/08/04 03:51:24 danielk1977 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(f1)}}

   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 db close

  1026 file delete -force test.db

  1027 sqlite3 db test.db

  1028 do_test select1-14.1 {

  1029   execsql { 

  1030     SELECT * FROM sqlite_master WHERE rowid>10; 

  1031     SELECT * FROM sqlite_master WHERE rowid=10;

  1032     SELECT * FROM sqlite_master WHERE rowid<10;

  1033     SELECT * FROM sqlite_master WHERE rowid<=10;

  1034     SELECT * FROM sqlite_master WHERE rowid>=10;

  1035     SELECT * FROM sqlite_master;

  1036   }

  1037 } {}

  1038 do_test select1-14.2 {

  1039   execsql { 

  1040     SELECT 10 IN (SELECT rowid FROM sqlite_master);

  1041   }

  1042 } {0}

  1043 

  1044 finish_test