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 IN and BETWEEN operator.

    13 #

    14 # $Id: in.test,v 1.22 2008/08/04 03:51:24 danielk1977 Exp $

    15 

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    18 

    19 # Generate the test data we will need for the first squences of tests.

    20 #

    21 do_test in-1.0 {

    22   execsql {

    23     BEGIN;

    24     CREATE TABLE t1(a int, b int);

    25   }

    26   for {set i 1} {$i<=10} {incr i} {

    27     execsql "INSERT INTO t1 VALUES($i,[expr {1<<$i}])"

    28   }

    29   execsql {

    30     COMMIT;

    31     SELECT count(*) FROM t1;

    32   }

    33 } {10}

    34 

    35 # Do basic testing of BETWEEN.

    36 #

    37 do_test in-1.1 {

    38   execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}

    39 } {4 5}

    40 do_test in-1.2 {

    41   execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}

    42 } {1 2 3 6 7 8 9 10}

    43 do_test in-1.3 {

    44   execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}

    45 } {1 2 3 4}

    46 do_test in-1.4 {

    47   execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}

    48 } {5 6 7 8 9 10}

    49 do_test in-1.6 {

    50   execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}

    51 } {1 2 3 4 9}

    52 do_test in-1.7 {

    53   execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}

    54 } {101 102 103 4 5 6 7 8 9 10}

    55 

    56 # The rest of this file concentrates on testing the IN operator.

    57 # Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY 

    58 # (because the IN operator is unavailable).

    59 #

    60 ifcapable !subquery {

    61   finish_test

    62   return

    63 }

    64 

    65 # Testing of the IN operator using static lists on the right-hand side.

    66 #

    67 do_test in-2.1 {

    68   execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}

    69 } {3 4 5}

    70 do_test in-2.2 {

    71   execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}

    72 } {1 2 6 7 8 9 10}

    73 do_test in-2.3 {

    74   execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}

    75 } {3 4 5 9}

    76 do_test in-2.4 {

    77   execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}

    78 } {1 2 6 7 8 9 10}

    79 do_test in-2.5 {

    80   execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}

    81 } {1 2 103 104 5 6 7 8 9 10}

    82 

    83 do_test in-2.6 {

    84   execsql {SELECT a FROM t1 WHERE b IN (b+8,64)}

    85 } {6}

    86 do_test in-2.7 {

    87   execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}

    88 } {4 5 6 7 8 9 10}

    89 do_test in-2.8 {

    90   execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}

    91 } {4 5}

    92 do_test in-2.9 {

    93   execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}

    94 } {}

    95 do_test in-2.10 {

    96   execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}

    97 } {}

    98 do_test in-2.11 {

    99   set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]

   100   lappend v $msg

   101 } {1 {no such column: c}}

   102 

   103 # Testing the IN operator where the right-hand side is a SELECT

   104 #

   105 do_test in-3.1 {

   106   execsql {

   107     SELECT a FROM t1

   108     WHERE b IN (SELECT b FROM t1 WHERE a<5)

   109     ORDER BY a

   110   }

   111 } {1 2 3 4}

   112 do_test in-3.2 {

   113   execsql {

   114     SELECT a FROM t1

   115     WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512

   116     ORDER BY a

   117   }

   118 } {1 2 3 4 9}

   119 do_test in-3.3 {

   120   execsql {

   121     SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b

   122   }

   123 } {101 102 103 104 5 6 7 8 9 10}

   124 

   125 # Make sure the UPDATE and DELETE commands work with IN-SELECT

   126 #

   127 do_test in-4.1 {

   128   execsql {

   129     UPDATE t1 SET b=b*2 

   130     WHERE b IN (SELECT b FROM t1 WHERE a>8)

   131   }

   132   execsql {SELECT b FROM t1 ORDER BY b}

   133 } {2 4 8 16 32 64 128 256 1024 2048}

   134 do_test in-4.2 {

   135   execsql {

   136     DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)

   137   }

   138   execsql {SELECT a FROM t1 ORDER BY a}

   139 } {1 2 3 4 5 6 7 8}

   140 do_test in-4.3 {

   141   execsql {

   142     DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)

   143   }

   144   execsql {SELECT a FROM t1 ORDER BY a}

   145 } {5 6 7 8}

   146 

   147 # Do an IN with a constant RHS but where the RHS has many, many

   148 # elements.  We need to test that collisions in the hash table

   149 # are resolved properly.

   150 #

   151 do_test in-5.1 {

   152   execsql {

   153     INSERT INTO t1 VALUES('hello', 'world');

   154     SELECT * FROM t1

   155     WHERE a IN (

   156        'Do','an','IN','with','a','constant','RHS','but','where','the',

   157        'has','many','elements','We','need','to','test','that',

   158        'collisions','hash','table','are','resolved','properly',

   159        'This','in-set','contains','thirty','one','entries','hello');

   160   }

   161 } {hello world}

   162 

   163 # Make sure the IN operator works with INTEGER PRIMARY KEY fields.

   164 #

   165 do_test in-6.1 {

   166   execsql {

   167     CREATE TABLE ta(a INTEGER PRIMARY KEY, b);

   168     INSERT INTO ta VALUES(1,1);

   169     INSERT INTO ta VALUES(2,2);

   170     INSERT INTO ta VALUES(3,3);

   171     INSERT INTO ta VALUES(4,4);

   172     INSERT INTO ta VALUES(6,6);

   173     INSERT INTO ta VALUES(8,8);

   174     INSERT INTO ta VALUES(10,

   175        'This is a key that is long enough to require a malloc in the VDBE');

   176     SELECT * FROM ta WHERE a<10;

   177   }

   178 } {1 1 2 2 3 3 4 4 6 6 8 8}

   179 do_test in-6.2 {

   180   execsql {

   181     CREATE TABLE tb(a INTEGER PRIMARY KEY, b);

   182     INSERT INTO tb VALUES(1,1);

   183     INSERT INTO tb VALUES(2,2);

   184     INSERT INTO tb VALUES(3,3);

   185     INSERT INTO tb VALUES(5,5);

   186     INSERT INTO tb VALUES(7,7);

   187     INSERT INTO tb VALUES(9,9);

   188     INSERT INTO tb VALUES(11,

   189        'This is a key that is long enough to require a malloc in the VDBE');

   190     SELECT * FROM tb WHERE a<10;

   191   }

   192 } {1 1 2 2 3 3 5 5 7 7 9 9}

   193 do_test in-6.3 {

   194   execsql {

   195     SELECT a FROM ta WHERE b IN (SELECT a FROM tb);

   196   }

   197 } {1 2 3}

   198 do_test in-6.4 {

   199   execsql {

   200     SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);

   201   }

   202 } {4 6 8 10}

   203 do_test in-6.5 {

   204   execsql {

   205     SELECT a FROM ta WHERE b IN (SELECT b FROM tb);

   206   }

   207 } {1 2 3 10}

   208 do_test in-6.6 {

   209   execsql {

   210     SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);

   211   }

   212 } {4 6 8}

   213 do_test in-6.7 {

   214   execsql {

   215     SELECT a FROM ta WHERE a IN (SELECT a FROM tb);

   216   }

   217 } {1 2 3}

   218 do_test in-6.8 {

   219   execsql {

   220     SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);

   221   }

   222 } {4 6 8 10}

   223 do_test in-6.9 {

   224   execsql {

   225     SELECT a FROM ta WHERE a IN (SELECT b FROM tb);

   226   }

   227 } {1 2 3}

   228 do_test in-6.10 {

   229   execsql {

   230     SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);

   231   }

   232 } {4 6 8 10}

   233 

   234 # Tests of IN operator against empty sets.  (Ticket #185)

   235 #

   236 do_test in-7.1 {

   237   execsql {

   238     SELECT a FROM t1 WHERE a IN ();

   239   }

   240 } {}

   241 do_test in-7.2 {

   242   execsql {

   243     SELECT a FROM t1 WHERE a IN (5);

   244   }

   245 } {5}

   246 do_test in-7.3 {

   247   execsql {

   248     SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;

   249   }

   250 } {5 6 7 8 hello}

   251 do_test in-7.4 {

   252   execsql {

   253     SELECT a FROM t1 WHERE a IN (5) AND b IN ();

   254   }

   255 } {}

   256 do_test in-7.5 {

   257   execsql {

   258     SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();

   259   }

   260 } {5}

   261 do_test in-7.6 {

   262   execsql {

   263     SELECT a FROM ta WHERE a IN ();

   264   }

   265 } {}

   266 do_test in-7.7 {

   267   execsql {

   268     SELECT a FROM ta WHERE a NOT IN ();

   269   }

   270 } {1 2 3 4 6 8 10}

   271 

   272 do_test in-8.1 {

   273   execsql {

   274     SELECT b FROM t1 WHERE a IN ('hello','there')

   275   }

   276 } {world}

   277 do_test in-8.2 {

   278   execsql {

   279     SELECT b FROM t1 WHERE a IN ("hello",'there')

   280   }

   281 } {world}

   282 

   283 # Test constructs of the form:  expr IN tablename

   284 #

   285 do_test in-9.1 {

   286   execsql {

   287     CREATE TABLE t4 AS SELECT a FROM tb;

   288     SELECT * FROM t4;    

   289   }

   290 } {1 2 3 5 7 9 11}

   291 do_test in-9.2 {

   292   execsql {

   293     SELECT b FROM t1 WHERE a IN t4;

   294   }

   295 } {32 128}

   296 do_test in-9.3 {

   297   execsql {

   298     SELECT b FROM t1 WHERE a NOT IN t4;

   299   }

   300 } {64 256 world}

   301 do_test in-9.4 {

   302   catchsql {

   303     SELECT b FROM t1 WHERE a NOT IN tb;

   304   }

   305 } {1 {only a single result allowed for a SELECT that is part of an expression}}

   306 

   307 # IN clauses in CHECK constraints.  Ticket #1645

   308 #

   309 do_test in-10.1 {

   310   execsql {

   311     CREATE TABLE t5(

   312       a INTEGER,

   313       CHECK( a IN (111,222,333) )

   314     );

   315     INSERT INTO t5 VALUES(111);

   316     SELECT * FROM t5;

   317   }

   318 } {111}

   319 do_test in-10.2 {

   320   catchsql {

   321     INSERT INTO t5 VALUES(4);

   322   }

   323 } {1 {constraint failed}}

   324 

   325 # Ticket #1821

   326 #

   327 # Type affinity applied to the right-hand side of an IN operator.

   328 #

   329 do_test in-11.1 {

   330   execsql {

   331     CREATE TABLE t6(a,b NUMERIC);

   332     INSERT INTO t6 VALUES(1,2);

   333     INSERT INTO t6 VALUES(2,3);

   334     SELECT * FROM t6 WHERE b IN (2);

   335   }

   336 } {1 2}

   337 do_test in-11.2 {

   338   # The '2' should be coerced into 2 because t6.b is NUMERIC

   339   execsql {

   340     SELECT * FROM t6 WHERE b IN ('2');

   341   }

   342 } {1 2}

   343 do_test in-11.3 {

   344   # No coercion should occur here because of the unary + before b.

   345   execsql {

   346     SELECT * FROM t6 WHERE +b IN ('2');

   347   }

   348 } {}

   349 do_test in-11.4 {

   350   # No coercion because column a as affinity NONE

   351   execsql {

   352     SELECT * FROM t6 WHERE a IN ('2');

   353   }

   354 } {}

   355 do_test in-11.5 {

   356   execsql {

   357     SELECT * FROM t6 WHERE a IN (2);

   358   }

   359 } {2 3}

   360 do_test in-11.6 {

   361   # No coercion because column a as affinity NONE

   362   execsql {

   363     SELECT * FROM t6 WHERE +a IN ('2');

   364   }

   365 } {}

   366 

   367 # Test error conditions with expressions of the form IN(<compound select>).

   368 #

   369 ifcapable compound {

   370 do_test in-12.1 {

   371   execsql {

   372     CREATE TABLE t2(a, b, c);

   373     CREATE TABLE t3(a, b, c);

   374   }

   375 } {}

   376 do_test in-12.2 {

   377   catchsql {

   378     SELECT * FROM t2 WHERE a IN (

   379       SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2

   380     );

   381   }

   382 } {1 {only a single result allowed for a SELECT that is part of an expression}}

   383 do_test in-12.3 {

   384   catchsql {

   385     SELECT * FROM t2 WHERE a IN (

   386       SELECT a, b FROM t3 UNION SELECT a, b FROM t2

   387     );

   388   }

   389 } {1 {only a single result allowed for a SELECT that is part of an expression}}

   390 do_test in-12.4 {

   391   catchsql {

   392     SELECT * FROM t2 WHERE a IN (

   393       SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2

   394     );

   395   }

   396 } {1 {only a single result allowed for a SELECT that is part of an expression}}

   397 do_test in-12.5 {

   398   catchsql {

   399     SELECT * FROM t2 WHERE a IN (

   400       SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2

   401     );

   402   }

   403 } {1 {only a single result allowed for a SELECT that is part of an expression}}

   404 do_test in-12.6 {

   405   catchsql {

   406     SELECT * FROM t2 WHERE a IN (

   407       SELECT a FROM t3 UNION ALL SELECT a, b FROM t2

   408     );

   409   }

   410 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

   411 do_test in-12.7 {

   412   catchsql {

   413     SELECT * FROM t2 WHERE a IN (

   414       SELECT a FROM t3 UNION SELECT a, b FROM t2

   415     );

   416   }

   417 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

   418 do_test in-12.8 {

   419   catchsql {

   420     SELECT * FROM t2 WHERE a IN (

   421       SELECT a FROM t3 EXCEPT SELECT a, b FROM t2

   422     );

   423   }

   424 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}

   425 do_test in-12.9 {

   426   catchsql {

   427     SELECT * FROM t2 WHERE a IN (

   428       SELECT a FROM t3 INTERSECT SELECT a, b FROM t2

   429     );

   430   }

   431 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}

   432 }

   433 

   434 

   435 #------------------------------------------------------------------------

   436 # The following tests check that NULL is handled correctly when it 

   437 # appears as part of a set of values on the right-hand side of an

   438 # IN or NOT IN operator.

   439 #

   440 # When it appears in such a set, NULL is handled as an "unknown value".

   441 # If, because of the unknown value in the set, the result of the expression 

   442 # cannot be determined, then it itself evaluates to NULL.

   443 #

   444 

   445 # Warm body test to demonstrate the principles being tested:

   446 #

   447 do_test in-13.1 {

   448   db nullvalue "null"

   449   execsql { SELECT 

   450     1 IN (NULL, 1, 2),     -- The value 1 is a member of the set, return true.

   451     3 IN (NULL, 1, 2),     -- Ambiguous, return NULL.

   452     1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.

   453     3 NOT IN (NULL, 1, 2)  -- Ambiguous, return NULL.

   454   }

   455 } {1 null 0 null}

   456 

   457 do_test in-13.2 {

   458   execsql { 

   459     CREATE TABLE t7(a, b, c NOT NULL);

   460     INSERT INTO t7 VALUES(1,    1, 1);

   461     INSERT INTO t7 VALUES(2,    2, 2);

   462     INSERT INTO t7 VALUES(3,    3, 3);

   463     INSERT INTO t7 VALUES(NULL, 4, 4);

   464     INSERT INTO t7 VALUES(NULL, 5, 5);

   465   }

   466 } {}

   467 

   468 do_test in-13.3 {

   469   execsql { SELECT 2 IN (SELECT a FROM t7) }

   470 } {1}

   471 do_test in-13.4 {

   472   execsql { SELECT 6 IN (SELECT a FROM t7) }

   473 } {null}

   474 

   475 do_test in-13.5 {

   476   execsql { SELECT 2 IN (SELECT b FROM t7) }

   477 } {1}

   478 do_test in-13.6 {

   479   execsql { SELECT 6 IN (SELECT b FROM t7) }

   480 } {0}

   481 

   482 do_test in-13.7 {

   483   execsql { SELECT 2 IN (SELECT c FROM t7) }

   484 } {1}

   485 do_test in-13.8 {

   486   execsql { SELECT 6 IN (SELECT c FROM t7) }

   487 } {0}

   488 

   489 do_test in-13.9 {

   490   execsql {

   491     SELECT

   492       2 NOT IN (SELECT a FROM t7),

   493       6 NOT IN (SELECT a FROM t7),

   494       2 NOT IN (SELECT b FROM t7),

   495       6 NOT IN (SELECT b FROM t7),

   496       2 NOT IN (SELECT c FROM t7),

   497       6 NOT IN (SELECT c FROM t7)

   498   } 

   499 } {0 null 0 1 0 1}

   500 

   501 do_test in-13.10 {

   502   execsql { 

   503     SELECT b IN (

   504       SELECT inside.a 

   505       FROM t7 AS inside 

   506       WHERE inside.b BETWEEN outside.b+1 AND outside.b+2

   507     )

   508     FROM t7 AS outside ORDER BY b;

   509   }

   510 } {0 null null null 0}

   511 

   512 do_test in-13.11 {

   513   execsql {

   514     SELECT b NOT IN (

   515       SELECT inside.a 

   516       FROM t7 AS inside 

   517       WHERE inside.b BETWEEN outside.b+1 AND outside.b+2

   518     )

   519     FROM t7 AS outside ORDER BY b;

   520   }

   521 } {1 null null null 1}

   522 

   523 do_test in-13.12 {

   524   execsql {

   525     CREATE INDEX i1 ON t7(a);

   526     CREATE INDEX i2 ON t7(b);

   527     CREATE INDEX i3 ON t7(c);

   528   }

   529   execsql {

   530     SELECT

   531       2 IN (SELECT a FROM t7),

   532       6 IN (SELECT a FROM t7),

   533       2 IN (SELECT b FROM t7),

   534       6 IN (SELECT b FROM t7),

   535       2 IN (SELECT c FROM t7),

   536       6 IN (SELECT c FROM t7)

   537   } 

   538 } {1 null 1 0 1 0}

   539 

   540 do_test in-13.13 {

   541   execsql {

   542     SELECT

   543       2 NOT IN (SELECT a FROM t7),

   544       6 NOT IN (SELECT a FROM t7),

   545       2 NOT IN (SELECT b FROM t7),

   546       6 NOT IN (SELECT b FROM t7),

   547       2 NOT IN (SELECT c FROM t7),

   548       6 NOT IN (SELECT c FROM t7)

   549   } 

   550 } {0 null 0 1 0 1}

   551 

   552 do_test in-13.14 {

   553   execsql {

   554     BEGIN TRANSACTION;

   555     CREATE TABLE a(id INTEGER);

   556     INSERT INTO a VALUES(1);

   557     INSERT INTO a VALUES(2);

   558     INSERT INTO a VALUES(3);

   559     CREATE TABLE b(id INTEGER);

   560     INSERT INTO b VALUES(NULL);

   561     INSERT INTO b VALUES(3);

   562     INSERT INTO b VALUES(4);

   563     INSERT INTO b VALUES(5);

   564     COMMIT;

   565     SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);

   566   }

   567 } {}

   568 do_test in-13.14 {

   569   execsql {

   570     CREATE INDEX i5 ON b(id);

   571     SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);

   572   }

   573 } {}

   574 

   575 

   576 do_test in-13.X {

   577   db nullvalue ""

   578 } {}

   579 

   580 finish_test