/ Check-in [d0bc7db6]
Login

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

Overview
Comment:Ensure that the same subquery does not go through the window-function rewrite more than once, even when that subquery is part of a virtual table constraint that lacks the omit flag.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d0bc7db6b0a53edc04815622c46250d26f526f56e59f14875f4e18c75b49000d
User & Date: drh 2019-11-14 13:24:04
Context
2019-11-14
13:57
New test cases added to fuzzdata8.db. check-in: 5baffcda user: drh tags: trunk
13:24
Ensure that the same subquery does not go through the window-function rewrite more than once, even when that subquery is part of a virtual table constraint that lacks the omit flag. check-in: d0bc7db6 user: drh tags: trunk
2019-11-13
18:50
Add header guard to the expert extension. check-in: 7e315185 user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957

2958
2959
2960
2961
2962
2963
2964
** "Select Flag".
**
** Value constraints (all checked via assert())
**     SF_HasAgg     == NC_HasAgg
**     SF_MinMaxAgg  == NC_MinMaxAgg     == SQLITE_FUNC_MINMAX
**     SF_FixedLimit == WHERE_USE_LIMIT
*/
#define SF_Distinct       0x00001  /* Output should be DISTINCT */
#define SF_All            0x00002  /* Includes the ALL keyword */
#define SF_Resolved       0x00004  /* Identifiers have been resolved */
#define SF_Aggregate      0x00008  /* Contains agg functions or a GROUP BY */
#define SF_HasAgg         0x00010  /* Contains aggregate functions */
#define SF_UsesEphemeral  0x00020  /* Uses the OpenEphemeral opcode */
#define SF_Expanded       0x00040  /* sqlite3SelectExpand() called on this */
#define SF_HasTypeInfo    0x00080  /* FROM subqueries have Table metadata */
#define SF_Compound       0x00100  /* Part of a compound query */
#define SF_Values         0x00200  /* Synthesized from VALUES clause */
#define SF_MultiValue     0x00400  /* Single VALUES term with multiple rows */
#define SF_NestedFrom     0x00800  /* Part of a parenthesized FROM clause */
#define SF_MinMaxAgg      0x01000  /* Aggregate containing min() or max() */
#define SF_Recursive      0x02000  /* The recursive part of a recursive CTE */
#define SF_FixedLimit     0x04000  /* nSelectRow set by a constant LIMIT */
#define SF_MaybeConvert   0x08000  /* Need convertCompoundSelectToSubquery() */
#define SF_Converted      0x10000  /* By convertCompoundSelectToSubquery() */
#define SF_IncludeHidden  0x20000  /* Include hidden columns in output */
#define SF_ComplexResult  0x40000  /* Result contains subquery or function */
#define SF_WhereBegin     0x80000  /* Really a WhereBegin() call.  Debug Only */


/*
** The results of a SELECT can be distributed in several ways, as defined
** by one of the following macros.  The "SRT" prefix means "SELECT Result
** Type".
**
**     SRT_Union       Store results as a key in a temporary index







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
** "Select Flag".
**
** Value constraints (all checked via assert())
**     SF_HasAgg     == NC_HasAgg
**     SF_MinMaxAgg  == NC_MinMaxAgg     == SQLITE_FUNC_MINMAX
**     SF_FixedLimit == WHERE_USE_LIMIT
*/
#define SF_Distinct      0x0000001 /* Output should be DISTINCT */
#define SF_All           0x0000002 /* Includes the ALL keyword */
#define SF_Resolved      0x0000004 /* Identifiers have been resolved */
#define SF_Aggregate     0x0000008 /* Contains agg functions or a GROUP BY */
#define SF_HasAgg        0x0000010 /* Contains aggregate functions */
#define SF_UsesEphemeral 0x0000020 /* Uses the OpenEphemeral opcode */
#define SF_Expanded      0x0000040 /* sqlite3SelectExpand() called on this */
#define SF_HasTypeInfo   0x0000080 /* FROM subqueries have Table metadata */
#define SF_Compound      0x0000100 /* Part of a compound query */
#define SF_Values        0x0000200 /* Synthesized from VALUES clause */
#define SF_MultiValue    0x0000400 /* Single VALUES term with multiple rows */
#define SF_NestedFrom    0x0000800 /* Part of a parenthesized FROM clause */
#define SF_MinMaxAgg     0x0001000 /* Aggregate containing min() or max() */
#define SF_Recursive     0x0002000 /* The recursive part of a recursive CTE */
#define SF_FixedLimit    0x0004000 /* nSelectRow set by a constant LIMIT */
#define SF_MaybeConvert  0x0008000 /* Need convertCompoundSelectToSubquery() */
#define SF_Converted     0x0010000 /* By convertCompoundSelectToSubquery() */
#define SF_IncludeHidden 0x0020000 /* Include hidden columns in output */
#define SF_ComplexResult 0x0040000 /* Result contains subquery or function */
#define SF_WhereBegin    0x0080000 /* Really a WhereBegin() call.  Debug Only */
#define SF_WinRewrite    0x0100000 /* Window function rewrite accomplished */

/*
** The results of a SELECT can be distributed in several ways, as defined
** by one of the following macros.  The "SRT" prefix means "SELECT Result
** Type".
**
**     SRT_Union       Store results as a key in a temporary index

Changes to src/window.c.

899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
...
924
925
926
927
928
929
930

931
932
933
934
935
936
937
** any SQL window functions, this function is a no-op. Otherwise, it 
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
*/
int sqlite3WindowRewrite(Parse *pParse, Select *p){
  int rc = SQLITE_OK;
  if( p->pWin && p->pPrior==0 ){
    Vdbe *v = sqlite3GetVdbe(pParse);
    sqlite3 *db = pParse->db;
    Select *pSub = 0;             /* The subquery */
    SrcList *pSrc = p->pSrc;
    Expr *pWhere = p->pWhere;
    ExprList *pGroupBy = p->pGroupBy;
    Expr *pHaving = p->pHaving;
................................................................................
    }

    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 && p->pOrderBy->nExpr<=pSort->nExpr ){







|







 







>







899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
...
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
** any SQL window functions, this function is a no-op. Otherwise, it 
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
*/
int sqlite3WindowRewrite(Parse *pParse, Select *p){
  int rc = SQLITE_OK;
  if( p->pWin && p->pPrior==0 && (p->selFlags & SF_WinRewrite)==0 ){
    Vdbe *v = sqlite3GetVdbe(pParse);
    sqlite3 *db = pParse->db;
    Select *pSub = 0;             /* The subquery */
    SrcList *pSrc = p->pSrc;
    Expr *pWhere = p->pWhere;
    ExprList *pGroupBy = p->pGroupBy;
    Expr *pHaving = p->pHaving;
................................................................................
    }

    p->pSrc = 0;
    p->pWhere = 0;
    p->pGroupBy = 0;
    p->pHaving = 0;
    p->selFlags &= ~SF_Aggregate;
    p->selFlags |= SF_WinRewrite;

    /* 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 && p->pOrderBy->nExpr<=pSort->nExpr ){