SQLite

Check-in [00ac73a01c]
Login

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

Overview
Comment:Attempt to treat the arguments to a table-valued function as if they occur in the ON clause of a LEFT JOIN rather than in the WHERE clause. But this causes undesirable behavior with generate_series, as demonstrated by test cases. This is an incremental check-in pending further work.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | tabfunc-in-left-join
Files: files | file ages | folders
SHA1: 00ac73a01caf578c70b7114f84f35fdba9a2097c
User & Date: drh 2016-04-08 21:35:34.942
Context
2016-04-08
21:35
Attempt to treat the arguments to a table-valued function as if they occur in the ON clause of a LEFT JOIN rather than in the WHERE clause. But this causes undesirable behavior with generate_series, as demonstrated by test cases. This is an incremental check-in pending further work. (Leaf check-in: 00ac73a01c user: drh tags: tabfunc-in-left-join)
19:44
Update documentation for sqlite3_snapshot_open(). No code changes. (check-in: 1dfa5234d3 user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/whereexpr.c.
︙
1352
1353
1354
1355
1356
1357
1358



1359
1360
1361

1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364








+
+
+




    pColRef = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
    if( pColRef==0 ) return;
    pColRef->iTable = pItem->iCursor;
    pColRef->iColumn = k++;
    pColRef->pTab = pTab;
    pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef,
                         sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
    if( pTerm && (pItem->fg.jointype & JT_OUTER)!=0 ){
      ExprSetProperty(pTerm, EP_FromJoin);
    }
    whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
  }
}
Changes to test/tabfunc01.test.
︙
65
66
67
68
69
70
71






















72
73
74
75
76
77
78

65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+








do_catchsql_test tabfunc01-1.21.2 {
  SELECT * FROM v1();
} {1 {'v1' is not a function}}
do_execsql_test tabfunc01-1.22 {
  CREATE VIEW v2(x) AS SELECT value FROM generate_series(1,5);
  SELECT * FROM v2;
} {1 2 3 4 5}
do_execsql_test tabfunc01-1.22.2 {
  SELECT * FROM generate_series(1,5) AS A
    LEFT JOIN generate_series(4,8) AS B ON A.value=B.value;
} {1 {} 2 {} 3 {} 4 4 5 5}
do_execsql_test tabfunc01-1.22.3 {
  CREATE TEMP TABLE t22 AS SELECT value AS x FROM generate_series(1,5);
  SELECT * FROM t22 AS A LEFT JOIN generate_series(4,8) AS B ON A.x=B.value;
} {1 {} 2 {} 3 {} 4 4 5 5}
do_execsql_test tabfunc01-1.22.4 {
  WITH x1(x) AS (SELECT value FROM generate_series(1,5))
  SELECT * FROM x1 AS A LEFT JOIN generate_series(4,8) AS B ON A.x=B.value;
} {1 {} 2 {} 3 {} 4 4 5 5}
do_execsql_test tabfunc01-1.22.5 {
  SELECT * FROM (SELECT value AS x FROM generate_series(1,5)) AS A
    LEFT JOIN generate_series(4,8) AS B ON A.x=B.value;
} {1 {} 2 {} 3 {} 4 4 5 5}
do_execsql_test tabfunc01-1.22.6 {
  SELECT * FROM v2 LEFT JOIN generate_series(4,8) ON value=x;
} {1 {} 2 {} 3 {} 4 4 5 5}
do_execsql_test tabfunc01-1.22.7 {
  SELECT * FROM generate_series(1,21,10) AS a LEFT JOIN v2 ON a.value=v2.x;
} {1 1 11 {} 21 {}}
do_catchsql_test tabfunc01-1.23.1 {
  SELECT * FROM v2(55);
} {1 {'v2' is not a function}}
do_catchsql_test tabfunc01-1.23.2 {
  SELECT * FROM v2();
} {1 {'v2' is not a function}}
do_execsql_test tabfunc01-1.24 {
︙
130
131
132
133
134
135
136
137




















138

152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180









+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+


# 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}

# When a table-valued function appears as the right table in a LEFT JOIN,
# the function arguments are understood as if they appear in the ON clause,
# not in the WHERE clause.
#
do_execsql_test tabfunc01-600 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(4),(11),(17);
  SELECT * FROM t1 LEFT JOIN generate_series(9,13) ON x=value ORDER BY +x;
} {4 {} 11 11 17 {}}
do_execsql_test tabfunc01-601 {
  SELECT * FROM t1 LEFT JOIN generate_series ON x=value
   WHERE start=9 AND stop=13
   ORDER BY +x;
} {11 11}
do_execsql_test tabfunc01-602 {
  SELECT * FROM t1 LEFT JOIN generate_series ON x=value AND start=9 AND stop=13
   ORDER BY +x;
} {4 {} 11 11 17 {}}

finish_test