SQLite

Check-in [2c35d3f67b]
Login

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

Overview
Comment:Fix table-valued functions so that they will work as the right table in a LEFT JOIN. Ticket [2ae0c599b735d59e]
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2c35d3f67b67a53ead08b1c395e7ca8e5bf65f94a5a962a0562994a3c66c95d9
User & Date: drh 2019-11-15 00:52:13.066
Context
2024-04-02
18:31
Fix table-valued functions so that they will work as the right table in a LEFT JOIN. Ticket [2ae0c599b735d59e] (check-in: 1f97086d62 user: drh tags: branch-3.28)
2019-11-15
21:16
Modify three test cases so that they work even with unusual versions of the library printf(). (check-in: 8f4a3750b7 user: drh tags: trunk)
00:52
Fix table-valued functions so that they will work as the right table in a LEFT JOIN. Ticket [2ae0c599b735d59e] (check-in: 2c35d3f67b user: drh tags: trunk)
2019-11-14
18:07
Add support for the sqlite3_hard_heap_limit64() interface and the hard_heap_limit pragma. (check-in: 6399c47ea8 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
** The where clause needs to defer the handling of the t1.x=5
** term until after the t2 loop of the join.  In that way, a
** NULL t2 row will be inserted whenever t1.x!=5.  If we do not
** defer the handling of t1.x=5, it will be processed immediately
** after the t1 loop and rows with t1.x!=5 will never appear in
** the output, which is incorrect.
*/
static void setJoinExpr(Expr *p, int iTable){
  while( p ){
    ExprSetProperty(p, EP_FromJoin);
    assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
    ExprSetVVAProperty(p, EP_NoReduce);
    p->iRightJoinTable = (i16)iTable;
    if( p->op==TK_FUNCTION && p->x.pList ){
      int i;
      for(i=0; i<p->x.pList->nExpr; i++){
        setJoinExpr(p->x.pList->a[i].pExpr, iTable);
      }
    }
    setJoinExpr(p->pLeft, iTable);
    p = p->pRight;
  } 
}

/* Undo the work of setJoinExpr().  In the expression tree p, convert every
** term that is marked with EP_FromJoin and iRightJoinTable==iTable into
** an ordinary term that omits the EP_FromJoin mark.
**
** This happens when a LEFT JOIN is simplified into an ordinary JOIN.
*/
static void unsetJoinExpr(Expr *p, int iTable){
  while( p ){







|








|


|




|







381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
** The where clause needs to defer the handling of the t1.x=5
** term until after the t2 loop of the join.  In that way, a
** NULL t2 row will be inserted whenever t1.x!=5.  If we do not
** defer the handling of t1.x=5, it will be processed immediately
** after the t1 loop and rows with t1.x!=5 will never appear in
** the output, which is incorrect.
*/
void sqlite3SetJoinExpr(Expr *p, int iTable){
  while( p ){
    ExprSetProperty(p, EP_FromJoin);
    assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
    ExprSetVVAProperty(p, EP_NoReduce);
    p->iRightJoinTable = (i16)iTable;
    if( p->op==TK_FUNCTION && p->x.pList ){
      int i;
      for(i=0; i<p->x.pList->nExpr; i++){
        sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable);
      }
    }
    sqlite3SetJoinExpr(p->pLeft, iTable);
    p = p->pRight;
  } 
}

/* Undo the work of sqlite3SetJoinExpr(). In the expression p, convert every
** term that is marked with EP_FromJoin and iRightJoinTable==iTable into
** an ordinary term that omits the EP_FromJoin mark.
**
** This happens when a LEFT JOIN is simplified into an ordinary JOIN.
*/
static void unsetJoinExpr(Expr *p, int iTable){
  while( p ){
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
      return 1;
    }

    /* Add the ON clause to the end of the WHERE clause, connected by
    ** an AND operator.
    */
    if( pRight->pOn ){
      if( isOuter ) setJoinExpr(pRight->pOn, pRight->iCursor);
      p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pRight->pOn);
      pRight->pOn = 0;
    }

    /* Create extra terms on the WHERE clause for each column named
    ** in the USING clause.  Example: If the two tables to be joined are 
    ** A and B and the USING clause names X, Y, and Z, then add this







|







485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
      return 1;
    }

    /* Add the ON clause to the end of the WHERE clause, connected by
    ** an AND operator.
    */
    if( pRight->pOn ){
      if( isOuter ) sqlite3SetJoinExpr(pRight->pOn, pRight->iCursor);
      p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pRight->pOn);
      pRight->pOn = 0;
    }

    /* Create extra terms on the WHERE clause for each column named
    ** in the USING clause.  Example: If the two tables to be joined are 
    ** A and B and the USING clause names X, Y, and Z, then add this
4051
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
      assert( pParent->pOrderBy==0 );
      pParent->pOrderBy = pOrderBy;
      pSub->pOrderBy = 0;
    }
    pWhere = pSub->pWhere;
    pSub->pWhere = 0;
    if( isLeftJoin>0 ){
      setJoinExpr(pWhere, iNewParent);
    }
    pParent->pWhere = sqlite3ExprAnd(pParse, pWhere, pParent->pWhere);
    if( db->mallocFailed==0 ){
      SubstContext x;
      x.pParse = pParse;
      x.iTable = iParent;
      x.iNewTable = iNewParent;







|







4051
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
      assert( pParent->pOrderBy==0 );
      pParent->pOrderBy = pOrderBy;
      pSub->pOrderBy = 0;
    }
    pWhere = pSub->pWhere;
    pSub->pWhere = 0;
    if( isLeftJoin>0 ){
      sqlite3SetJoinExpr(pWhere, iNewParent);
    }
    pParent->pWhere = sqlite3ExprAnd(pParse, pWhere, pParent->pWhere);
    if( db->mallocFailed==0 ){
      SubstContext x;
      x.pParse = pParse;
      x.iTable = iParent;
      x.iNewTable = iNewParent;
Changes to src/sqliteInt.h.
4244
4245
4246
4247
4248
4249
4250

4251
4252
4253
4254
4255
4256
4257
# define sqlite3TriggerList(X, Y) 0
# define sqlite3ParseToplevel(p) p
# define sqlite3IsToplevel(p) 1
# define sqlite3TriggerColmask(A,B,C,D,E,F,G) 0
#endif

int sqlite3JoinType(Parse*, Token*, Token*, Token*);

void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*);
  int sqlite3AuthCheck(Parse*,int, const char*, const char*, const char*);
  void sqlite3AuthContextPush(Parse*, AuthContext*, const char*);
  void sqlite3AuthContextPop(AuthContext*);







>







4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
# define sqlite3TriggerList(X, Y) 0
# define sqlite3ParseToplevel(p) p
# define sqlite3IsToplevel(p) 1
# define sqlite3TriggerColmask(A,B,C,D,E,F,G) 0
#endif

int sqlite3JoinType(Parse*, Token*, Token*, Token*);
void sqlite3SetJoinExpr(Expr*,int);
void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*);
  int sqlite3AuthCheck(Parse*,int, const char*, const char*, const char*);
  void sqlite3AuthContextPush(Parse*, AuthContext*, const char*);
  void sqlite3AuthContextPop(AuthContext*);
Changes to src/whereexpr.c.
1590
1591
1592
1593
1594
1595
1596



1597
1598
1599
    if( pColRef==0 ) return;
    pColRef->iTable = pItem->iCursor;
    pColRef->iColumn = k++;
    pColRef->y.pTab = pTab;
    pRhs = sqlite3PExpr(pParse, TK_UPLUS, 
        sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
    pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef, pRhs);



    whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
  }
}







>
>
>



1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
    if( pColRef==0 ) return;
    pColRef->iTable = pItem->iCursor;
    pColRef->iColumn = k++;
    pColRef->y.pTab = pTab;
    pRhs = sqlite3PExpr(pParse, TK_UPLUS, 
        sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
    pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef, pRhs);
    if( pItem->fg.jointype & JT_LEFT ){
      sqlite3SetJoinExpr(pTerm, pItem->iCursor);
    }
    whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
  }
}
Changes to test/tabfunc01.test.
213
214
215
216
217
218
219









220
221
222
223
224
225
226
do_test tabfunc01-750 {
  db eval {
    SELECT aa.value, bb.value, '|'
      FROM carray(inttoptr($PTR4),5,'double') AS aa
      JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
  }
} {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}










# Free up memory allocations
intarray_addr
int64array_addr
doublearray_addr
textarray_addr








>
>
>
>
>
>
>
>
>







213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
do_test tabfunc01-750 {
  db eval {
    SELECT aa.value, bb.value, '|'
      FROM carray(inttoptr($PTR4),5,'double') AS aa
      JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
  }
} {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}

# ticket https://www.sqlite.org/src/info/2ae0c599b735d59e
do_test tabfunc01-751 {
  db eval {
    SELECT aa.value, bb.value, '|'
      FROM carray(inttoptr($PTR4),5,'double') AS aa
      LEFT JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
  }
} {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}

# Free up memory allocations
intarray_addr
int64array_addr
doublearray_addr
textarray_addr