/ Check-in [507fdbfb]
Login

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

Overview
Comment:Merge the alternative table-valued function RHS of IN operator implementation from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | prototype-int-array
Files: files | file ages | folders
SHA1: 507fdbfb54ce377f0d870260b07d71b797843fcf
User & Date: drh 2016-07-02 20:51:31
Context
2016-07-02
20:57
Fix an off-by-one comparison in the intarray() virtual table. Get the intarray() virtual table tests working using the legacy makefile. check-in: 7c3d441f user: drh tags: prototype-int-array
20:51
Merge the alternative table-valued function RHS of IN operator implementation from trunk. check-in: 507fdbfb user: drh tags: prototype-int-array
12:33
Fix a problem in table-valued functions on the RHS of an IN operator that occurs following an OOM error. check-in: bead151e user: drh tags: trunk
2016-06-29
06:19
Add the ability to have a table-valued function on the RHS of an IN operator. check-in: ba1b441b user: drh tags: prototype-int-array
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1130   1130     }
  1131   1131     expr(A) ::= expr(A) in_op(N) LP select(Y) RP(E).  [IN] {
  1132   1132       A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
  1133   1133       sqlite3PExprAddSelect(pParse, A.pExpr, Y);
  1134   1134       exprNot(pParse, N, &A);
  1135   1135       A.zEnd = &E.z[E.n];
  1136   1136     }
  1137         -  expr(A) ::= expr(A) in_op(N) nm(Y) dbnm(Z). [IN] {
         1137  +  expr(A) ::= expr(A) in_op(N) nm(Y) dbnm(Z) paren_exprlist(E). [IN] {
  1138   1138       SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z);
  1139   1139       Select *pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0);
  1140         -    A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
  1141         -    sqlite3PExprAddSelect(pParse, A.pExpr, pSelect);
  1142         -    exprNot(pParse, N, &A);
  1143         -    A.zEnd = Z.z ? &Z.z[Z.n] : &Y.z[Y.n];
  1144         -  }
  1145         -  expr(A) ::= expr(A) in_op(N) nm(Y) dbnm(Z) LP exprlist(E) RP. [IN] {
  1146         -    SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z);
  1147         -    Select *pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0);
  1148         -    sqlite3SrcListFuncArgs(pParse, pSrc, E);
         1140  +    if( E )  sqlite3SrcListFuncArgs(pParse, pSelect ? pSrc : 0, E);
  1149   1141       A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
  1150   1142       sqlite3PExprAddSelect(pParse, A.pExpr, pSelect);
  1151   1143       exprNot(pParse, N, &A);
  1152   1144       A.zEnd = Z.z ? &Z.z[Z.n] : &Y.z[Y.n];
  1153   1145     }
  1154   1146     expr(A) ::= EXISTS(B) LP select(Y) RP(E). {
  1155   1147       Expr *p;
................................................................................
  1197   1189   
  1198   1190   exprlist(A) ::= nexprlist(A).
  1199   1191   exprlist(A) ::= .                            {A = 0;}
  1200   1192   nexprlist(A) ::= nexprlist(A) COMMA expr(Y).
  1201   1193       {A = sqlite3ExprListAppend(pParse,A,Y.pExpr);}
  1202   1194   nexprlist(A) ::= expr(Y).
  1203   1195       {A = sqlite3ExprListAppend(pParse,0,Y.pExpr); /*A-overwrites-Y*/}
         1196  +
         1197  +/* A paren_exprlist is an optional expression list contained inside
         1198  +** of parenthesis */
         1199  +%type paren_exprlist {ExprList*}
         1200  +%destructor paren_exprlist {sqlite3ExprListDelete(pParse->db, $$);}
         1201  +paren_exprlist(A) ::= .   {A = 0;}
         1202  +paren_exprlist(A) ::= LP exprlist(X) RP.  {A = X;}
         1203  +
  1204   1204   
  1205   1205   
  1206   1206   ///////////////////////////// The CREATE INDEX command ///////////////////////
  1207   1207   //
  1208   1208   cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
  1209   1209           ON nm(Y) LP sortlist(Z) RP where_opt(W). {
  1210   1210     sqlite3CreateIndex(pParse, &X, &D, 

Changes to src/whereexpr.c.

   781    781      && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
   782    782     ){
   783    783       return 0;
   784    784     }
   785    785     pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
   786    786     if( pColl==0 || sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
   787    787     pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
   788         -  /* Since pLeft and pRight are both a column references, their collating
   789         -  ** sequence should always be defined. */
   790         -  zColl1 = ALWAYS(pColl) ? pColl->zName : 0;
          788  +  zColl1 = pColl ? pColl->zName : 0;
   791    789     pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
   792         -  zColl2 = ALWAYS(pColl) ? pColl->zName : 0;
   793         -  return sqlite3StrICmp(zColl1, zColl2)==0;
          790  +  zColl2 = pColl ? pColl->zName : 0;
          791  +  return sqlite3_stricmp(zColl1, zColl2)==0;
   794    792   }
   795    793   
   796    794   /*
   797    795   ** Recursively walk the expressions of a SELECT statement and generate
   798    796   ** a bitmask indicating which tables are used in that expression
   799    797   ** tree.
   800    798   */

Added test/collateB.test.

            1  +# 2016-07-01
            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  +# Test cases for a crash bug.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +
           17  +do_execsql_test collateB-1.1 {
           18  +  CREATE TABLE t1(a INTEGER PRIMARY KEY);
           19  +  CREATE TABLE t2(b INTEGER PRIMARY KEY, x1 INT COLLATE NOCASE);
           20  +  CREATE TABLE t3(x2 INT);
           21  +  SELECT * FROM t3, t2, t1 WHERE x2=b AND x1=a AND a=1;
           22  +} {}
           23  +do_execsql_test collateB-1.2 {
           24  +  INSERT INTO t1(a) VALUES(1),(2),(3);
           25  +  INSERT INTO t2(b,x1) VALUES(11,1),(22,2),(33,3);
           26  +  INSERT INTO t3(x2) VALUES(11),(22),(33);
           27  +  SELECT *,'|' FROM t3, t2, t1 WHERE x2=b AND x1=a AND a=1;
           28  +} {11 11 1 1 |}
           29  +do_execsql_test collateB-1.3 {
           30  +  SELECT *,'|' FROM t3, t1, t2 WHERE x2=b AND x1=a AND a=1;
           31  +} {11 1 11 1 |}
           32  +do_execsql_test collateB-1.4 {
           33  +  SELECT *,'|' FROM t2, t3, t1 WHERE x2=b AND x1=a AND a=1;
           34  +} {11 1 11 1 |}
           35  +do_execsql_test collateB-1.5 {
           36  +  SELECT *,'|' FROM t2, t1, t3 WHERE x2=b AND x1=a AND a=1;
           37  +} {11 1 1 11 |}
           38  +do_execsql_test collateB-1.6 {
           39  +  SELECT *,'|' FROM t1, t2, t3 WHERE x2=b AND x1=a AND a=1;
           40  +} {1 11 1 11 |}
           41  +do_execsql_test collateB-1.7 {
           42  +  SELECT *,'|' FROM t1, t2, t3 WHERE x2=b AND x1=a AND a=1;
           43  +} {1 11 1 11 |}
           44  +do_execsql_test collateB-1.12 {
           45  +  SELECT *,'|' FROM t3, t2, t1 WHERE b=x2 AND a=x1 AND 1=a;
           46  +} {11 11 1 1 |}
           47  +do_execsql_test collateB-1.13 {
           48  +  SELECT *,'|' FROM t3, t1, t2 WHERE b=x2 AND a=x1 AND 1=a;
           49  +} {11 1 11 1 |}
           50  +do_execsql_test collateB-1.14 {
           51  +  SELECT *,'|' FROM t2, t3, t1 WHERE b=x2 AND a=x1 AND 1=a;
           52  +} {11 1 11 1 |}
           53  +do_execsql_test collateB-1.15 {
           54  +  SELECT *,'|' FROM t2, t1, t3 WHERE b=x2 AND a=x1 AND 1=a;
           55  +} {11 1 1 11 |}
           56  +do_execsql_test collateB-1.16 {
           57  +  SELECT *,'|' FROM t1, t2, t3 WHERE b=x2 AND a=x1 AND 1=a;
           58  +} {1 11 1 11 |}
           59  +do_execsql_test collateB-1.17 {
           60  +  SELECT *,'|' FROM t1, t2, t3 WHERE b=x2 AND a=x1 AND 1=a;
           61  +} {1 11 1 11 |}
           62  +
           63  +finish_test

Changes to test/tabfunc01.test.

   131    131   # each step of output.  At one point, the IN operator could not be used
   132    132   # by virtual tables unless omit was set.
   133    133   #
   134    134   do_execsql_test tabfunc01-500 {
   135    135     SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
   136    136     ORDER BY +1;
   137    137   } {1 7 11 17}
          138  +
          139  +# Table-valued functions on the RHS of an IN operator
          140  +#
          141  +do_execsql_test tabfunc01-600 {
          142  +  CREATE TABLE t600(a INTEGER PRIMARY KEY, b TEXT);
          143  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
          144  +    INSERT INTO t600(a,b) SELECT x, printf('(%03d)',x) FROM c;
          145  +  SELECT b FROM t600 WHERE a IN generate_series(2,52,10);
          146  +} {(002) (012) (022) (032) (042) (052)}
   138    147   
   139    148   
   140    149   do_test tabfunc01-600 {
   141    150     set TAIL {}
   142    151     set VM [sqlite3_prepare db {SELECT * FROM intarray(?2,?3)} -1 TAIL]
   143    152     set TAIL
   144    153   } {}