Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with using scalar sub-selects in window function queries. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
687fe532c274265ca77451f97829743f |
| User & Date: | dan 2018-07-10 17:26:12 |
Context
|
2018-07-10
| ||
| 18:50 | Modify tests in window3.test to be tolerant of rounding errors when comparing floating point values. (check-in: 7c26c443 user: dan tags: trunk) | |
| 17:26 | Fix a problem with using scalar sub-selects in window function queries. (check-in: 687fe532 user: dan tags: trunk) | |
| 17:10 | Further improvements to bytecode branch testing. Fix cases where the macros said a branch could not be taken when in fact it could be. Alter some window function branch coverage macros to indicate that comparison operands cannot be NULL. (check-in: 76e42b70 user: drh tags: trunk) | |
Changes
Changes to src/window.c.
585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 ... 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 ... 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 |
/*
** Context object passed through sqlite3WalkExprList() to
** selectWindowRewriteExprCb() by selectWindowRewriteEList().
*/
typedef struct WindowRewrite WindowRewrite;
struct WindowRewrite {
Window *pWin;
ExprList *pSub;
};
/*
** Callback function used by selectWindowRewriteEList(). If necessary,
** this function appends to the output expression-list and updates
** expression (*ppExpr) in place.
*/
static int selectWindowRewriteExprCb(Walker *pWalker, Expr *pExpr){
struct WindowRewrite *p = pWalker->u.pRewrite;
Parse *pParse = pWalker->pParse;
switch( pExpr->op ){
case TK_FUNCTION:
if( pExpr->pWin==0 ){
break;
}else{
................................................................................
default: /* no-op */
break;
}
return WRC_Continue;
}
static int selectWindowRewriteSelectCb(Walker *pWalker, Select *pSelect){
UNUSED_PARAMETER(pWalker);
UNUSED_PARAMETER(pSelect);
return WRC_Prune;
}
/*
** Iterate through each expression in expression-list pEList. For each:
**
** * TK_COLUMN,
** * aggregate function, or
** * window function with a Window object that is not a member of the
** linked list passed as the second argument (pWin)
**
** Append the node to output expression-list (*ppSub). And replace it
** with a TK_COLUMN that reads the (N-1)th element of table
** pWin->iEphCsr, where N is the number of elements in (*ppSub) after
** appending the new one.
*/
static void selectWindowRewriteEList(
Parse *pParse,
Window *pWin,
ExprList *pEList, /* Rewrite expressions in this list */
ExprList **ppSub /* IN/OUT: Sub-select expression-list */
){
Walker sWalker;
WindowRewrite sRewrite;
memset(&sWalker, 0, sizeof(Walker));
memset(&sRewrite, 0, sizeof(WindowRewrite));
sRewrite.pSub = *ppSub;
sRewrite.pWin = pWin;
sWalker.pParse = pParse;
sWalker.xExprCallback = selectWindowRewriteExprCb;
sWalker.xSelectCallback = selectWindowRewriteSelectCb;
sWalker.u.pRewrite = &sRewrite;
(void)sqlite3WalkExprList(&sWalker, pEList);
................................................................................
}
/* Assign a cursor number for the ephemeral table used to buffer rows.
** The OpenEphemeral instruction is coded later, after it is known how
** many columns the table will have. */
pMWin->iEphCsr = pParse->nTab++;
selectWindowRewriteEList(pParse, pMWin, p->pEList, &pSublist);
selectWindowRewriteEList(pParse, pMWin, p->pOrderBy, &pSublist);
pMWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);
/* Append the PARTITION BY and ORDER BY expressions to the to the
** sub-select expression list. They are required to figure out where
** boundaries for partitions and sets of peer rows lie. */
pSublist = exprListAppendList(pParse, pSublist, pMWin->pPartition);
pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy);
|
> > > > > > > > > > > > > > > > > > > > | | > > > > > > > | > > | | |
585 586 587 588 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 625 626 627 628 629 ... 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 ... 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 |
/*
** Context object passed through sqlite3WalkExprList() to
** selectWindowRewriteExprCb() by selectWindowRewriteEList().
*/
typedef struct WindowRewrite WindowRewrite;
struct WindowRewrite {
Window *pWin;
SrcList *pSrc;
ExprList *pSub;
Select *pSubSelect; /* Current sub-select, if any */
};
/*
** Callback function used by selectWindowRewriteEList(). If necessary,
** this function appends to the output expression-list and updates
** expression (*ppExpr) in place.
*/
static int selectWindowRewriteExprCb(Walker *pWalker, Expr *pExpr){
struct WindowRewrite *p = pWalker->u.pRewrite;
Parse *pParse = pWalker->pParse;
/* If this function is being called from within a scalar sub-select
** that used by the SELECT statement being processed, only process
** TK_COLUMN expressions that refer to it (the outer SELECT). Do
** not process aggregates or window functions at all, as they belong
** to the scalar sub-select. */
if( p->pSubSelect ){
if( pExpr->op!=TK_COLUMN ){
return WRC_Continue;
}else{
int nSrc = p->pSrc->nSrc;
int i;
for(i=0; i<nSrc; i++){
if( pExpr->iTable==p->pSrc->a[i].iCursor ) break;
}
if( i==nSrc ) return WRC_Continue;
}
}
switch( pExpr->op ){
case TK_FUNCTION:
if( pExpr->pWin==0 ){
break;
}else{
................................................................................
default: /* no-op */
break;
}
return WRC_Continue;
}
static int selectWindowRewriteSelectCb(Walker *pWalker, Select *pSelect){
struct WindowRewrite *p = pWalker->u.pRewrite;
Select *pSave = p->pSubSelect;
if( pSave==pSelect ){
return WRC_Continue;
}else{
p->pSubSelect = pSelect;
sqlite3WalkSelect(pWalker, pSelect);
p->pSubSelect = pSave;
}
return WRC_Prune;
}
/*
** Iterate through each expression in expression-list pEList. For each:
**
** * TK_COLUMN,
** * aggregate function, or
** * window function with a Window object that is not a member of the
** Window list passed as the second argument (pWin).
**
** Append the node to output expression-list (*ppSub). And replace it
** with a TK_COLUMN that reads the (N-1)th element of table
** pWin->iEphCsr, where N is the number of elements in (*ppSub) after
** appending the new one.
*/
static void selectWindowRewriteEList(
Parse *pParse,
Window *pWin,
SrcList *pSrc,
ExprList *pEList, /* Rewrite expressions in this list */
ExprList **ppSub /* IN/OUT: Sub-select expression-list */
){
Walker sWalker;
WindowRewrite sRewrite;
memset(&sWalker, 0, sizeof(Walker));
memset(&sRewrite, 0, sizeof(WindowRewrite));
sRewrite.pSub = *ppSub;
sRewrite.pWin = pWin;
sRewrite.pSrc = pSrc;
sWalker.pParse = pParse;
sWalker.xExprCallback = selectWindowRewriteExprCb;
sWalker.xSelectCallback = selectWindowRewriteSelectCb;
sWalker.u.pRewrite = &sRewrite;
(void)sqlite3WalkExprList(&sWalker, pEList);
................................................................................
}
/* Assign a cursor number for the ephemeral table used to buffer rows.
** The OpenEphemeral instruction is coded later, after it is known how
** many columns the table will have. */
pMWin->iEphCsr = pParse->nTab++;
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, &pSublist);
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, &pSublist);
pMWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);
/* Append the PARTITION BY and ORDER BY expressions to the to the
** sub-select expression list. They are required to figure out where
** boundaries for partitions and sets of peer rows lie. */
pSublist = exprListAppendList(pParse, pSublist, pMWin->pPartition);
pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy);
|
Changes to test/window6.test.
306 307 308 309 310 311 312 313 |
} {
do_execsql_test 10.2.$tn "
WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
$stmt
" $res
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > |
306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 |
} {
do_execsql_test 10.2.$tn "
WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
$stmt
" $res
}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 11.0 {
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(10),(15),(20),(20),(25),(30),(30),(50);
CREATE TABLE t3(x INT, y VARCHAR);
INSERT INTO t3(x,y) VALUES(10,'ten'),('15','fifteen'),(30,'thirty');
}
do_execsql_test 11.1 {
SELECT a, (SELECT y FROM t3 WHERE x=a) FROM t1 ORDER BY a;
} {
10 ten 15 fifteen 20 {} 20 {} 25 {} 30 thirty 30 thirty 50 {}
}
do_execsql_test 11.2 {
SELECT a, (SELECT y FROM t3 WHERE x=a), sum(a) OVER (ORDER BY a)
FROM t1 ORDER BY a;
} {
10 ten 10 15 fifteen 25 20 {} 65 20 {} 65
25 {} 90 30 thirty 150 30 thirty 150 50 {} 200
}
finish_test
|