/ Check-in [00ac73a0]
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 | SQL archive
Timelines: family | ancestors | tabfunc-in-left-join
Files: files | file ages | folders
SHA1: 00ac73a01caf578c70b7114f84f35fdba9a2097c
User & Date: drh 2016-04-08 21:35:34
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: 00ac73a0 user: drh tags: tabfunc-in-left-join
19:44
Update documentation for sqlite3_snapshot_open(). No code changes. check-in: 1dfa5234 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

  1352   1352       pColRef = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
  1353   1353       if( pColRef==0 ) return;
  1354   1354       pColRef->iTable = pItem->iCursor;
  1355   1355       pColRef->iColumn = k++;
  1356   1356       pColRef->pTab = pTab;
  1357   1357       pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef,
  1358   1358                            sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
         1359  +    if( pTerm && (pItem->fg.jointype & JT_OUTER)!=0 ){
         1360  +      ExprSetProperty(pTerm, EP_FromJoin);
         1361  +    }
  1359   1362       whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
  1360   1363     }
  1361   1364   }

Changes to test/tabfunc01.test.

    65     65   do_catchsql_test tabfunc01-1.21.2 {
    66     66     SELECT * FROM v1();
    67     67   } {1 {'v1' is not a function}}
    68     68   do_execsql_test tabfunc01-1.22 {
    69     69     CREATE VIEW v2(x) AS SELECT value FROM generate_series(1,5);
    70     70     SELECT * FROM v2;
    71     71   } {1 2 3 4 5}
           72  +do_execsql_test tabfunc01-1.22.2 {
           73  +  SELECT * FROM generate_series(1,5) AS A
           74  +    LEFT JOIN generate_series(4,8) AS B ON A.value=B.value;
           75  +} {1 {} 2 {} 3 {} 4 4 5 5}
           76  +do_execsql_test tabfunc01-1.22.3 {
           77  +  CREATE TEMP TABLE t22 AS SELECT value AS x FROM generate_series(1,5);
           78  +  SELECT * FROM t22 AS A LEFT JOIN generate_series(4,8) AS B ON A.x=B.value;
           79  +} {1 {} 2 {} 3 {} 4 4 5 5}
           80  +do_execsql_test tabfunc01-1.22.4 {
           81  +  WITH x1(x) AS (SELECT value FROM generate_series(1,5))
           82  +  SELECT * FROM x1 AS A LEFT JOIN generate_series(4,8) AS B ON A.x=B.value;
           83  +} {1 {} 2 {} 3 {} 4 4 5 5}
           84  +do_execsql_test tabfunc01-1.22.5 {
           85  +  SELECT * FROM (SELECT value AS x FROM generate_series(1,5)) AS A
           86  +    LEFT JOIN generate_series(4,8) AS B ON A.x=B.value;
           87  +} {1 {} 2 {} 3 {} 4 4 5 5}
           88  +do_execsql_test tabfunc01-1.22.6 {
           89  +  SELECT * FROM v2 LEFT JOIN generate_series(4,8) ON value=x;
           90  +} {1 {} 2 {} 3 {} 4 4 5 5}
           91  +do_execsql_test tabfunc01-1.22.7 {
           92  +  SELECT * FROM generate_series(1,21,10) AS a LEFT JOIN v2 ON a.value=v2.x;
           93  +} {1 1 11 {} 21 {}}
    72     94   do_catchsql_test tabfunc01-1.23.1 {
    73     95     SELECT * FROM v2(55);
    74     96   } {1 {'v2' is not a function}}
    75     97   do_catchsql_test tabfunc01-1.23.2 {
    76     98     SELECT * FROM v2();
    77     99   } {1 {'v2' is not a function}}
    78    100   do_execsql_test tabfunc01-1.24 {
................................................................................
   130    152   # each step of output.  At one point, the IN operator could not be used
   131    153   # by virtual tables unless omit was set.
   132    154   #
   133    155   do_execsql_test tabfunc01-500 {
   134    156     SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
   135    157     ORDER BY +1;
   136    158   } {1 7 11 17}
          159  +
          160  +# When a table-valued function appears as the right table in a LEFT JOIN,
          161  +# the function arguments are understood as if they appear in the ON clause,
          162  +# not in the WHERE clause.
          163  +#
          164  +do_execsql_test tabfunc01-600 {
          165  +  DROP TABLE IF EXISTS t1;
          166  +  CREATE TABLE t1(x);
          167  +  INSERT INTO t1 VALUES(4),(11),(17);
          168  +  SELECT * FROM t1 LEFT JOIN generate_series(9,13) ON x=value ORDER BY +x;
          169  +} {4 {} 11 11 17 {}}
          170  +do_execsql_test tabfunc01-601 {
          171  +  SELECT * FROM t1 LEFT JOIN generate_series ON x=value
          172  +   WHERE start=9 AND stop=13
          173  +   ORDER BY +x;
          174  +} {11 11}
          175  +do_execsql_test tabfunc01-602 {
          176  +  SELECT * FROM t1 LEFT JOIN generate_series ON x=value AND start=9 AND stop=13
          177  +   ORDER BY +x;
          178  +} {4 {} 11 11 17 {}}
   137    179   
   138    180   finish_test