SQLite

Changes On Branch ticket-f09fcd17810f
Login

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

Changes In Branch ticket-f09fcd17810f Excluding Merge-Ins

This is equivalent to a diff from 53d3b169 to 2b9258b8

2018-12-07
01:56
Fix the sqlite3ExprDup() function so that it correctly duplicates the Window object list on a Select that contains window functions. Fix for ticket [f09fcd17810f65f717]. (check-in: db5ed226 user: drh tags: trunk)
2018-12-06
22:12
Performance improvement: Avoid using sqlite3WalkerSelectExpr() and sqlite3WalkerSelectFrom() twice, so that the compiler will in-line their implementation. (Closed-Leaf check-in: 2b9258b8 user: drh tags: ticket-f09fcd17810f)
22:04
Fix the sqlite3ExprDup() routine so that it makes complete duplications of subqueries containing window functions. (check-in: 94017454 user: drh tags: ticket-f09fcd17810f)
20:18
Experimental changes to (optionally) allow double-quoted strings to be checked against known identifiers. (Leaf check-in: 73a6b8c1 user: mistachkin tags: normalize_v4)
19:15
Simplify the query flattener so that it does not duplicate the WHERE clause of subquery that is being incorporated into the outer query - copies it directly. This is more efficient. And it also fixes the specific test case show for ticket [f09fcd17810f65f71789525] but it does not resolve the more general problem that sqlite3ExprDup() does not correctly duplicate expressions that contain subqueries with window functions. (check-in: f1b18d44 user: drh tags: ticket-f09fcd17810f)
17:06
When masking bits off of sqlite3.flags, make sure the mask is 64 bits in size so as not to accidentally mask of high-order bits. (check-in: 53d3b169 user: drh tags: trunk)
16:50
When saving off the value of sqlite3.flags, take care to preserve all 64 bits. (check-in: 9c6dbcfa user: drh tags: trunk)

Changes to src/expr.c.

1325
1326
1327
1328
1329
1330
1331





























1332
1333
1334
1335
1336
1337
1338
    }
  }
  return pRet;
}
#else
# define withDup(x,y) 0
#endif






























/*
** The following group of routines make deep copies of expressions,
** expression lists, ID lists, and select statements.  The copies can
** be deleted (by being passed to their respective ...Delete() routines)
** without effecting the originals.
**







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
    }
  }
  return pRet;
}
#else
# define withDup(x,y) 0
#endif

#ifndef SQLITE_OMIT_WINDOWFUNC
/*
** The gatherSelectWindows() procedure and its helper routine
** gatherSelectWindowsCallback() are used to scan all the expressions
** an a newly duplicated SELECT statement and gather all of the Window
** objects found there, assembling them onto the linked list at Select->pWin.
*/
static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_FUNCTION && pExpr->y.pWin!=0 ){
    assert( ExprHasProperty(pExpr, EP_WinFunc) );
    pExpr->y.pWin->pNextWin = pWalker->u.pSelect->pWin;
    pWalker->u.pSelect->pWin = pExpr->y.pWin;
  }
  return WRC_Continue;
}
static int gatherSelectWindowsSelectCallback(Walker *pWalker, Select *p){
  return p==pWalker->u.pSelect ? WRC_Continue : WRC_Prune;
}
static void gatherSelectWindows(Select *p){
  Walker w;
  w.xExprCallback = gatherSelectWindowsCallback;
  w.xSelectCallback = gatherSelectWindowsSelectCallback;
  w.xSelectCallback2 = 0;
  w.u.pSelect = p;
  sqlite3WalkSelect(&w, p);
}
#endif


/*
** The following group of routines make deep copies of expressions,
** expression lists, ID lists, and select statements.  The copies can
** be deleted (by being passed to their respective ...Delete() routines)
** without effecting the originals.
**
1493
1494
1495
1496
1497
1498
1499

1500
1501
1502
1503
1504
1505
1506
    pNew->addrOpenEphm[0] = -1;
    pNew->addrOpenEphm[1] = -1;
    pNew->nSelectRow = p->nSelectRow;
    pNew->pWith = withDup(db, p->pWith);
#ifndef SQLITE_OMIT_WINDOWFUNC
    pNew->pWin = 0;
    pNew->pWinDefn = sqlite3WindowListDup(db, p->pWinDefn);

#endif
    pNew->selId = p->selId;
    *pp = pNew;
    pp = &pNew->pPrior;
    pNext = pNew;
  }








>







1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
    pNew->addrOpenEphm[0] = -1;
    pNew->addrOpenEphm[1] = -1;
    pNew->nSelectRow = p->nSelectRow;
    pNew->pWith = withDup(db, p->pWith);
#ifndef SQLITE_OMIT_WINDOWFUNC
    pNew->pWin = 0;
    pNew->pWinDefn = sqlite3WindowListDup(db, p->pWinDefn);
    if( p->pWin ) gatherSelectWindows(pNew);
#endif
    pNew->selId = p->selId;
    *pp = pNew;
    pp = &pNew->pPrior;
    pNext = pNew;
  }

Changes to src/select.c.

3457
3458
3459
3460
3461
3462
3463

3464
3465
3466
3467
3468
3469
3470
        if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){
          memset(&ifNullRow, 0, sizeof(ifNullRow));
          ifNullRow.op = TK_IF_NULL_ROW;
          ifNullRow.pLeft = pCopy;
          ifNullRow.iTable = pSubst->iNewTable;
          pCopy = &ifNullRow;
        }

        pNew = sqlite3ExprDup(db, pCopy, 0);
        if( pNew && pSubst->isLeftJoin ){
          ExprSetProperty(pNew, EP_CanBeNull);
        }
        if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
          pNew->iRightJoinTable = pExpr->iRightJoinTable;
          ExprSetProperty(pNew, EP_FromJoin);







>







3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
        if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){
          memset(&ifNullRow, 0, sizeof(ifNullRow));
          ifNullRow.op = TK_IF_NULL_ROW;
          ifNullRow.pLeft = pCopy;
          ifNullRow.iTable = pSubst->iNewTable;
          pCopy = &ifNullRow;
        }
        testcase( ExprHasProperty(pCopy, EP_Subquery) );
        pNew = sqlite3ExprDup(db, pCopy, 0);
        if( pNew && pSubst->isLeftJoin ){
          ExprSetProperty(pNew, EP_CanBeNull);
        }
        if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
          pNew->iRightJoinTable = pExpr->iRightJoinTable;
          ExprSetProperty(pNew, EP_FromJoin);
4021
4022
4023
4024
4025
4026
4027

4028
4029
4030
4031
4032
4033
4034
4035
      for(i=0; i<pOrderBy->nExpr; i++){
        pOrderBy->a[i].u.x.iOrderByCol = 0;
      }
      assert( pParent->pOrderBy==0 );
      pParent->pOrderBy = pOrderBy;
      pSub->pOrderBy = 0;
    }

    pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
    if( isLeftJoin>0 ){
      setJoinExpr(pWhere, iNewParent);
    }
    pParent->pWhere = sqlite3ExprAnd(db, pWhere, pParent->pWhere);
    if( db->mallocFailed==0 ){
      SubstContext x;
      x.pParse = pParse;







>
|







4022
4023
4024
4025
4026
4027
4028
4029
4030
4031
4032
4033
4034
4035
4036
4037
      for(i=0; i<pOrderBy->nExpr; i++){
        pOrderBy->a[i].u.x.iOrderByCol = 0;
      }
      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(db, pWhere, pParent->pWhere);
    if( db->mallocFailed==0 ){
      SubstContext x;
      x.pParse = pParse;

Changes to src/window.c.

2129
2130
2131
2132
2133
2134
2135

2136
2137
2138
2139
2140
2141
2142
Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p){
  Window *pNew = 0;
  if( ALWAYS(p) ){
    pNew = sqlite3DbMallocZero(db, sizeof(Window));
    if( pNew ){
      pNew->zName = sqlite3DbStrDup(db, p->zName);
      pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0);

      pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0);
      pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0);
      pNew->eType = p->eType;
      pNew->eEnd = p->eEnd;
      pNew->eStart = p->eStart;
      pNew->pStart = sqlite3ExprDup(db, p->pStart, 0);
      pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0);







>







2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p){
  Window *pNew = 0;
  if( ALWAYS(p) ){
    pNew = sqlite3DbMallocZero(db, sizeof(Window));
    if( pNew ){
      pNew->zName = sqlite3DbStrDup(db, p->zName);
      pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0);
      pNew->pFunc = p->pFunc;
      pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0);
      pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0);
      pNew->eType = p->eType;
      pNew->eEnd = p->eEnd;
      pNew->eStart = p->eStart;
      pNew->pStart = sqlite3ExprDup(db, p->pStart, 0);
      pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0);

Changes to test/window1.test.

589
590
591
592
593
594
595



























596
597

do_execsql_test 13.5 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    INTERSECT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
}




























finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624

do_execsql_test 13.5 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    INTERSECT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
}

# 2018-12-06
# https://www.sqlite.org/src/info/f09fcd17810f65f7
# Assertion fault when window functions are used.
#
# Root cause is the query flattener invoking sqlite3ExprDup() on
# expressions that contain subqueries with window functions.  The
# sqlite3ExprDup() routine is not making correctly initializing
# Select.pWin field of the subqueries.
#
sqlite3 db :memory:
do_execsql_test 14.0 {
  SELECT * FROM(
    SELECT * FROM (SELECT 1 AS c) WHERE c IN (
        SELECT (row_number() OVER()) FROM (VALUES (0))
    )
  );
} {1}
do_execsql_test 14.1 {
  CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345);
  CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1);
  SELECT y, y+1, y+2 FROM (
    SELECT c IN (
      SELECT (row_number() OVER()) FROM t1
    ) AS y FROM t2
  );
} {1 2 3}

finish_test