Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Ensure collation sequences and affinities work in window function queries. Fix for [9ece23d2]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
28196d894ac9fad9d8f877c7bf17ec9d |
| User & Date: | dan 2019-07-08 12:01:39 |
References
|
2019-07-08
| ||
| 13:45 | Fix an assert() that [28196d89] caused to fail. check-in: 8fb0c6d5 user: dan tags: trunk | |
| 12:03 | • Ticket [9ece23d2] Default collation sequences and affinities lost when window function added to query status still Open with 3 other changes artifact: f5d6cde2 user: dan | |
Context
|
2019-07-08
| ||
| 13:45 | Fix an assert() that [28196d89] caused to fail. check-in: 8fb0c6d5 user: dan tags: trunk | |
| 12:01 | Ensure collation sequences and affinities work in window function queries. Fix for [9ece23d2]. check-in: 28196d89 user: dan tags: trunk | |
|
2019-07-05
| ||
| 15:16 | Fix a valgrind problem in fts3corrupt4.test. check-in: cb3dec42 user: dan tags: trunk | |
Changes
Changes to src/select.c.
2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 |
db->flags = savedFlags;
if( pParse->nErr ) return 0;
while( pSelect->pPrior ) pSelect = pSelect->pPrior;
pTab = sqlite3DbMallocZero(db, sizeof(Table) );
if( pTab==0 ){
return 0;
}
/* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
** is disabled */
assert( db->lookaside.bDisable );
pTab->nTabRef = 1;
pTab->zName = 0;
pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
sqlite3ColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect);
pTab->iPKey = -1;
if( db->mallocFailed ){
|
< < < |
2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 |
db->flags = savedFlags;
if( pParse->nErr ) return 0;
while( pSelect->pPrior ) pSelect = pSelect->pPrior;
pTab = sqlite3DbMallocZero(db, sizeof(Table) );
if( pTab==0 ){
return 0;
}
pTab->nTabRef = 1;
pTab->zName = 0;
pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
sqlite3ColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect);
pTab->iPKey = -1;
if( db->mallocFailed ){
|
Changes to src/window.c.
732 733 734 735 736 737 738 739 740 741 742 743 744 745 ... 792 793 794 795 796 797 798 799 800 801 802 803 804 805 ... 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 ... 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 ... 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 ... 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 |
** 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.
................................................................................
sqlite3ExprDelete(pParse->db, pExpr);
ExprClearProperty(pExpr, EP_Static);
memset(pExpr, 0, sizeof(Expr));
pExpr->op = TK_COLUMN;
pExpr->iColumn = p->pSub->nExpr-1;
pExpr->iTable = p->pWin->iEphCsr;
}
break;
}
default: /* no-op */
break;
................................................................................
** 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);
................................................................................
ExprList *pGroupBy = p->pGroupBy;
Expr *pHaving = p->pHaving;
ExprList *pSort = 0;
ExprList *pSublist = 0; /* Expression list for sub-query */
Window *pMWin = p->pWin; /* Master window object */
Window *pWin; /* Window object iterator */
p->pSrc = 0;
p->pWhere = 0;
p->pGroupBy = 0;
p->pHaving = 0;
/* Create the ORDER BY clause for the sub-select. This is the concatenation
** of the window PARTITION and ORDER BY clauses. Then, if this makes it
** redundant, remove the ORDER BY from the parent SELECT. */
pSort = sqlite3ExprListDup(db, pMWin->pPartition, 0);
pSort = exprListAppendList(pParse, pSort, pMWin->pOrderBy, 1);
if( pSort && p->pOrderBy ){
................................................................................
/* 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++;
pParse->nTab += 3;
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, 0);
pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy, 0);
................................................................................
}
pSub = sqlite3SelectNew(
pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
);
p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
if( p->pSrc ){
p->pSrc->a[0].pSelect = pSub;
sqlite3SrcListAssignCursors(pParse, p->pSrc);
if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
rc = SQLITE_NOMEM;
}else{
pSub->selFlags |= SF_Expanded;
p->selFlags &= ~SF_Aggregate;
sqlite3SelectPrep(pParse, pSub, 0);
}
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+1, pMWin->iEphCsr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+2, pMWin->iEphCsr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+3, pMWin->iEphCsr);
}else{
sqlite3SelectDelete(db, pSub);
}
if( db->mallocFailed ) rc = SQLITE_NOMEM;
}
return rc;
}
/*
** Free the Window object passed as the second argument.
|
> > > > > > > > > > > | | > < > > > < < < > > > > < > |
732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 ... 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 ... 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 ... 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 ... 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 ... 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 |
** selectWindowRewriteExprCb() by selectWindowRewriteEList().
*/
typedef struct WindowRewrite WindowRewrite;
struct WindowRewrite {
Window *pWin;
SrcList *pSrc;
ExprList *pSub;
Table *pTab;
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.
................................................................................
sqlite3ExprDelete(pParse->db, pExpr);
ExprClearProperty(pExpr, EP_Static);
memset(pExpr, 0, sizeof(Expr));
pExpr->op = TK_COLUMN;
pExpr->iColumn = p->pSub->nExpr-1;
pExpr->iTable = p->pWin->iEphCsr;
pExpr->y.pTab = p->pTab;
}
break;
}
default: /* no-op */
break;
................................................................................
** appending the new one.
*/
static void selectWindowRewriteEList(
Parse *pParse,
Window *pWin,
SrcList *pSrc,
ExprList *pEList, /* Rewrite expressions in this list */
Table *pTab,
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;
sRewrite.pTab = pTab;
sWalker.pParse = pParse;
sWalker.xExprCallback = selectWindowRewriteExprCb;
sWalker.xSelectCallback = selectWindowRewriteSelectCb;
sWalker.u.pRewrite = &sRewrite;
(void)sqlite3WalkExprList(&sWalker, pEList);
................................................................................
ExprList *pGroupBy = p->pGroupBy;
Expr *pHaving = p->pHaving;
ExprList *pSort = 0;
ExprList *pSublist = 0; /* Expression list for sub-query */
Window *pMWin = p->pWin; /* Master window object */
Window *pWin; /* Window object iterator */
Table *pTab;
pTab = sqlite3DbMallocZero(db, sizeof(Table));
if( pTab==0 ){
return SQLITE_NOMEM;
}
p->pSrc = 0;
p->pWhere = 0;
p->pGroupBy = 0;
p->pHaving = 0;
p->selFlags &= ~SF_Aggregate;
/* Create the ORDER BY clause for the sub-select. This is the concatenation
** of the window PARTITION and ORDER BY clauses. Then, if this makes it
** redundant, remove the ORDER BY from the parent SELECT. */
pSort = sqlite3ExprListDup(db, pMWin->pPartition, 0);
pSort = exprListAppendList(pParse, pSort, pMWin->pOrderBy, 1);
if( pSort && p->pOrderBy ){
................................................................................
/* 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++;
pParse->nTab += 3;
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, pTab, &pSublist);
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, pTab, &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, 0);
pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy, 0);
................................................................................
}
pSub = sqlite3SelectNew(
pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
);
p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
if( p->pSrc ){
Table *pTab2;
p->pSrc->a[0].pSelect = pSub;
sqlite3SrcListAssignCursors(pParse, p->pSrc);
pSub->selFlags |= SF_Expanded;
pTab2 = sqlite3ResultSetOfSelect(pParse, pSub);
if( pTab2==0 ){
rc = SQLITE_NOMEM;
}else{
memcpy(pTab, pTab2, sizeof(Table));
pTab->tabFlags |= TF_Ephemeral;
p->pSrc->a[0].pTab = pTab;
pTab = pTab2;
}
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+1, pMWin->iEphCsr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+2, pMWin->iEphCsr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+3, pMWin->iEphCsr);
}else{
sqlite3SelectDelete(db, pSub);
}
if( db->mallocFailed ) rc = SQLITE_NOMEM;
sqlite3DbFree(db, pTab);
}
return rc;
}
/*
** Free the Window object passed as the second argument.
|
Added test/window9.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 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 101 102 |
# 2019 June 8 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix window9 ifcapable !windowfunc { finish_test return } do_execsql_test 1.0 { CREATE TABLE fruits( name TEXT COLLATE NOCASE, color TEXT COLLATE NOCASE ); } do_execsql_test 1.1 { INSERT INTO fruits (name, color) VALUES ('apple', 'RED'); INSERT INTO fruits (name, color) VALUES ('APPLE', 'yellow'); INSERT INTO fruits (name, color) VALUES ('pear', 'YELLOW'); INSERT INTO fruits (name, color) VALUES ('PEAR', 'green'); } do_execsql_test 1.2 { SELECT name, color, dense_rank() OVER (ORDER BY name) FROM fruits; } { apple RED 1 APPLE yellow 1 pear YELLOW 2 PEAR green 2 } do_execsql_test 1.3 { SELECT name, color, dense_rank() OVER (PARTITION BY name ORDER BY color) FROM fruits; } { apple RED 1 APPLE yellow 2 PEAR green 1 pear YELLOW 2 } do_execsql_test 1.4 { SELECT name, color, dense_rank() OVER (ORDER BY name), dense_rank() OVER (PARTITION BY name ORDER BY color) FROM fruits; } { apple RED 1 1 APPLE yellow 1 2 PEAR green 2 1 pear YELLOW 2 2 } do_execsql_test 1.5 { SELECT name, color, dense_rank() OVER (ORDER BY name), dense_rank() OVER (PARTITION BY name ORDER BY color) FROM fruits ORDER BY color; } { PEAR green 2 1 apple RED 1 1 APPLE yellow 1 2 pear YELLOW 2 2 } do_execsql_test 2.0 { CREATE TABLE t1(a BLOB, b INTEGER, c COLLATE nocase); INSERT INTO t1 VALUES(1, 2, 'abc'); INSERT INTO t1 VALUES(3, 4, 'ABC'); } do_execsql_test 2.1.1 { SELECT c=='Abc' FROM t1 } {1 1} do_execsql_test 2.1.2 { SELECT c=='Abc', rank() OVER (ORDER BY b) FROM t1 } {1 1 1 2} do_execsql_test 2.2.1 { SELECT b=='2' FROM t1 } {1 0} do_execsql_test 2.2.2 { SELECT b=='2', rank() OVER (ORDER BY a) FROM t1 } {1 1 0 2} finish_test |