/ Check-in [01874d25]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow "expr IN table" as a shorthand for "expr IN (SELECT * FROM table)" (CVS 1180)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:01874d252ac44861e927dea3f5534f67e19b1fa8
User & Date: drh 2004-01-15 03:30:25
Original Comment: Allow "<expr> IN " as a shorthand for "<expr> IN (SELECT * FROM
)" (CVS 1180)
Context
2004-01-15
13:29
More aggressive retry schedule in sqlite_busy_timeout(). (CVS 1181) check-in: 5e85025b user: drh tags: trunk
03:30
Allow "expr IN table" as a shorthand for "expr IN (SELECT * FROM table)" (CVS 1180) check-in: 01874d25 user: drh tags: trunk
02:44
Reinsert the experimental sqlite_commit_hook() API. (CVS 1179) check-in: 72bc84f2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains SQLite's grammar for SQL.  Process this file
    13     13   ** using the lemon parser generator to generate C code that runs
    14     14   ** the parser.  Lemon will also generate a header file containing
    15     15   ** numeric codes for all of the tokens.
    16     16   **
    17         -** @(#) $Id: parse.y,v 1.105 2003/12/06 21:43:56 drh Exp $
           17  +** @(#) $Id: parse.y,v 1.106 2004/01/15 03:30:25 drh Exp $
    18     18   */
    19     19   %token_prefix TK_
    20     20   %token_type {Token}
    21     21   %default_type {Token}
    22     22   %extra_argument {Parse *pParse}
    23     23   %syntax_error {
    24     24     if( pParse->zErrMsg==0 ){
................................................................................
   660    660   }
   661    661   expr(A) ::= expr(X) NOT IN LP select(Y) RP(E).  {
   662    662     A = sqliteExpr(TK_IN, X, 0, 0);
   663    663     if( A ) A->pSelect = Y;
   664    664     A = sqliteExpr(TK_NOT, A, 0, 0);
   665    665     sqliteExprSpan(A,&X->span,&E);
   666    666   }
          667  +expr(A) ::= expr(X) IN nm(Y) dbnm(D). {
          668  +  SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);
          669  +  ExprList *pList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
          670  +  A = sqliteExpr(TK_IN, X, 0, 0);
          671  +  if( A ) A->pSelect = sqliteSelectNew(pList,pSrc,0,0,0,0,0,-1,0);
          672  +  sqliteExprSpan(A,&X->span,D.z?&D:&Y);
          673  +}
          674  +expr(A) ::= expr(X) NOT IN nm(Y) dbnm(D). {
          675  +  SrcList *pSrc = sqliteSrcListAppend(0, &Y, &D);
          676  +  ExprList *pList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
          677  +  A = sqliteExpr(TK_IN, X, 0, 0);
          678  +  if( A ) A->pSelect = sqliteSelectNew(pList,pSrc,0,0,0,0,0,-1,0);
          679  +  A = sqliteExpr(TK_NOT, A, 0, 0);
          680  +  sqliteExprSpan(A,&X->span,D.z?&D:&Y);
          681  +}
          682  +
   667    683   
   668    684   /* CASE expressions */
   669    685   expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). {
   670    686     A = sqliteExpr(TK_CASE, X, Z, 0);
   671    687     if( A ) A->pList = Y;
   672    688     sqliteExprSpan(A, &C, &E);
   673    689   }

Changes to test/in.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the IN and BETWEEN operator.
    13     13   #
    14         -# $Id: in.test,v 1.10 2003/04/19 17:27:25 drh Exp $
           14  +# $Id: in.test,v 1.11 2004/01/15 03:30:25 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Generate the test data we will need for the first squences of tests.
    20     20   #
    21     21   do_test in-1.0 {
................................................................................
   272    272   } {world}
   273    273   do_test in-8.2 {
   274    274     execsql {
   275    275       SELECT b FROM t1 WHERE a IN ("hello",'there')
   276    276     }
   277    277   } {world}
   278    278   
   279         -
          279  +# Test constructs of the form:  expr IN tablename
          280  +#
          281  +do_test in-9.1 {
          282  +  execsql {
          283  +    CREATE TABLE t4 AS SELECT a FROM tb;
          284  +    SELECT * FROM t4;    
          285  +  }
          286  +} {1 2 3 5 7 9 11}
          287  +do_test in-9.2 {
          288  +  execsql {
          289  +    SELECT b FROM t1 WHERE a IN t4;
          290  +  }
          291  +} {32 128}
          292  +do_test in-9.3 {
          293  +  execsql {
          294  +    SELECT b FROM t1 WHERE a NOT IN t4;
          295  +  }
          296  +} {64 256 world}
          297  +do_test in-9.4 {
          298  +  catchsql {
          299  +    SELECT b FROM t1 WHERE a NOT IN tb;
          300  +  }
          301  +} {1 {only a single result allowed for a SELECT that is part of an expression}}
   280    302   
   281    303   finish_test