SQLite

Check-in [507fdbfb54]
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
Timelines: family | ancestors | descendants | both | prototype-int-array
Files: files | file ages | folders
SHA1: 507fdbfb54ce377f0d870260b07d71b797843fcf
User & Date: drh 2016-07-02 20:51:31.298
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: 7c3d441f2a user: drh tags: prototype-int-array)
20:51
Merge the alternative table-valued function RHS of IN operator implementation from trunk. (check-in: 507fdbfb54 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: bead151e72 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: ba1b441b60 user: drh tags: prototype-int-array)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/parse.y.
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
  }
  expr(A) ::= expr(A) in_op(N) LP select(Y) RP(E).  [IN] {
    A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
    sqlite3PExprAddSelect(pParse, A.pExpr, Y);
    exprNot(pParse, N, &A);
    A.zEnd = &E.z[E.n];
  }
  expr(A) ::= expr(A) in_op(N) nm(Y) dbnm(Z). [IN] {
    SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z);
    Select *pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0);
    A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
    sqlite3PExprAddSelect(pParse, A.pExpr, pSelect);
    exprNot(pParse, N, &A);
    A.zEnd = Z.z ? &Z.z[Z.n] : &Y.z[Y.n];
  }
  expr(A) ::= expr(A) in_op(N) nm(Y) dbnm(Z) LP exprlist(E) RP. [IN] {
    SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z);
    Select *pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0);
    sqlite3SrcListFuncArgs(pParse, pSrc, E);
    A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
    sqlite3PExprAddSelect(pParse, A.pExpr, pSelect);
    exprNot(pParse, N, &A);
    A.zEnd = Z.z ? &Z.z[Z.n] : &Y.z[Y.n];
  }
  expr(A) ::= EXISTS(B) LP select(Y) RP(E). {
    Expr *p;







|


<
<
<
<
<
<
<
<
|







1130
1131
1132
1133
1134
1135
1136
1137
1138
1139








1140
1141
1142
1143
1144
1145
1146
1147
  }
  expr(A) ::= expr(A) in_op(N) LP select(Y) RP(E).  [IN] {
    A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
    sqlite3PExprAddSelect(pParse, A.pExpr, Y);
    exprNot(pParse, N, &A);
    A.zEnd = &E.z[E.n];
  }
  expr(A) ::= expr(A) in_op(N) nm(Y) dbnm(Z) paren_exprlist(E). [IN] {
    SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z);
    Select *pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0);








    if( E )  sqlite3SrcListFuncArgs(pParse, pSelect ? pSrc : 0, E);
    A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0);
    sqlite3PExprAddSelect(pParse, A.pExpr, pSelect);
    exprNot(pParse, N, &A);
    A.zEnd = Z.z ? &Z.z[Z.n] : &Y.z[Y.n];
  }
  expr(A) ::= EXISTS(B) LP select(Y) RP(E). {
    Expr *p;
1197
1198
1199
1200
1201
1202
1203








1204
1205
1206
1207
1208
1209
1210

exprlist(A) ::= nexprlist(A).
exprlist(A) ::= .                            {A = 0;}
nexprlist(A) ::= nexprlist(A) COMMA expr(Y).
    {A = sqlite3ExprListAppend(pParse,A,Y.pExpr);}
nexprlist(A) ::= expr(Y).
    {A = sqlite3ExprListAppend(pParse,0,Y.pExpr); /*A-overwrites-Y*/}










///////////////////////////// The CREATE INDEX command ///////////////////////
//
cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
        ON nm(Y) LP sortlist(Z) RP where_opt(W). {
  sqlite3CreateIndex(pParse, &X, &D, 







>
>
>
>
>
>
>
>







1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210

exprlist(A) ::= nexprlist(A).
exprlist(A) ::= .                            {A = 0;}
nexprlist(A) ::= nexprlist(A) COMMA expr(Y).
    {A = sqlite3ExprListAppend(pParse,A,Y.pExpr);}
nexprlist(A) ::= expr(Y).
    {A = sqlite3ExprListAppend(pParse,0,Y.pExpr); /*A-overwrites-Y*/}

/* A paren_exprlist is an optional expression list contained inside
** of parenthesis */
%type paren_exprlist {ExprList*}
%destructor paren_exprlist {sqlite3ExprListDelete(pParse->db, $$);}
paren_exprlist(A) ::= .   {A = 0;}
paren_exprlist(A) ::= LP exprlist(X) RP.  {A = X;}



///////////////////////////// The CREATE INDEX command ///////////////////////
//
cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
        ON nm(Y) LP sortlist(Z) RP where_opt(W). {
  sqlite3CreateIndex(pParse, &X, &D, 
Changes to src/whereexpr.c.
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
   && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
  ){
    return 0;
  }
  pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
  if( pColl==0 || sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
  pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  /* Since pLeft and pRight are both a column references, their collating
  ** sequence should always be defined. */
  zColl1 = ALWAYS(pColl) ? pColl->zName : 0;
  pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
  zColl2 = ALWAYS(pColl) ? pColl->zName : 0;
  return sqlite3StrICmp(zColl1, zColl2)==0;
}

/*
** Recursively walk the expressions of a SELECT statement and generate
** a bitmask indicating which tables are used in that expression
** tree.
*/







<
<
|

|
|







781
782
783
784
785
786
787


788
789
790
791
792
793
794
795
796
797
798
   && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
  ){
    return 0;
  }
  pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
  if( pColl==0 || sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
  pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);


  zColl1 = pColl ? pColl->zName : 0;
  pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
  zColl2 = pColl ? pColl->zName : 0;
  return sqlite3_stricmp(zColl1, zColl2)==0;
}

/*
** Recursively walk the expressions of a SELECT statement and generate
** a bitmask indicating which tables are used in that expression
** tree.
*/
Added test/collateB.test.






























































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
# 2016-07-01
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# Test cases for a crash bug.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test collateB-1.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY);
  CREATE TABLE t2(b INTEGER PRIMARY KEY, x1 INT COLLATE NOCASE);
  CREATE TABLE t3(x2 INT);
  SELECT * FROM t3, t2, t1 WHERE x2=b AND x1=a AND a=1;
} {}
do_execsql_test collateB-1.2 {
  INSERT INTO t1(a) VALUES(1),(2),(3);
  INSERT INTO t2(b,x1) VALUES(11,1),(22,2),(33,3);
  INSERT INTO t3(x2) VALUES(11),(22),(33);
  SELECT *,'|' FROM t3, t2, t1 WHERE x2=b AND x1=a AND a=1;
} {11 11 1 1 |}
do_execsql_test collateB-1.3 {
  SELECT *,'|' FROM t3, t1, t2 WHERE x2=b AND x1=a AND a=1;
} {11 1 11 1 |}
do_execsql_test collateB-1.4 {
  SELECT *,'|' FROM t2, t3, t1 WHERE x2=b AND x1=a AND a=1;
} {11 1 11 1 |}
do_execsql_test collateB-1.5 {
  SELECT *,'|' FROM t2, t1, t3 WHERE x2=b AND x1=a AND a=1;
} {11 1 1 11 |}
do_execsql_test collateB-1.6 {
  SELECT *,'|' FROM t1, t2, t3 WHERE x2=b AND x1=a AND a=1;
} {1 11 1 11 |}
do_execsql_test collateB-1.7 {
  SELECT *,'|' FROM t1, t2, t3 WHERE x2=b AND x1=a AND a=1;
} {1 11 1 11 |}
do_execsql_test collateB-1.12 {
  SELECT *,'|' FROM t3, t2, t1 WHERE b=x2 AND a=x1 AND 1=a;
} {11 11 1 1 |}
do_execsql_test collateB-1.13 {
  SELECT *,'|' FROM t3, t1, t2 WHERE b=x2 AND a=x1 AND 1=a;
} {11 1 11 1 |}
do_execsql_test collateB-1.14 {
  SELECT *,'|' FROM t2, t3, t1 WHERE b=x2 AND a=x1 AND 1=a;
} {11 1 11 1 |}
do_execsql_test collateB-1.15 {
  SELECT *,'|' FROM t2, t1, t3 WHERE b=x2 AND a=x1 AND 1=a;
} {11 1 1 11 |}
do_execsql_test collateB-1.16 {
  SELECT *,'|' FROM t1, t2, t3 WHERE b=x2 AND a=x1 AND 1=a;
} {1 11 1 11 |}
do_execsql_test collateB-1.17 {
  SELECT *,'|' FROM t1, t2, t3 WHERE b=x2 AND a=x1 AND 1=a;
} {1 11 1 11 |}

finish_test
Changes to test/tabfunc01.test.
131
132
133
134
135
136
137









138
139
140
141
142
143
144
# each step of output.  At one point, the IN operator could not be used
# by virtual tables unless omit was set.
#
do_execsql_test tabfunc01-500 {
  SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
  ORDER BY +1;
} {1 7 11 17}











do_test tabfunc01-600 {
  set TAIL {}
  set VM [sqlite3_prepare db {SELECT * FROM intarray(?2,?3)} -1 TAIL]
  set TAIL
} {}







>
>
>
>
>
>
>
>
>







131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
# each step of output.  At one point, the IN operator could not be used
# by virtual tables unless omit was set.
#
do_execsql_test tabfunc01-500 {
  SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
  ORDER BY +1;
} {1 7 11 17}

# Table-valued functions on the RHS of an IN operator
#
do_execsql_test tabfunc01-600 {
  CREATE TABLE t600(a INTEGER PRIMARY KEY, b TEXT);
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
    INSERT INTO t600(a,b) SELECT x, printf('(%03d)',x) FROM c;
  SELECT b FROM t600 WHERE a IN generate_series(2,52,10);
} {(002) (012) (022) (032) (042) (052)}


do_test tabfunc01-600 {
  set TAIL {}
  set VM [sqlite3_prepare db {SELECT * FROM intarray(?2,?3)} -1 TAIL]
  set TAIL
} {}