/ Check-in [d03c7533a1]
Login

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

Overview
Comment:Parse EXCLUDE clauses in window frames. They do not yet work.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: d03c7533a1e993f1b12392a5193b2127484307b27072236a1255e7a30849a381
User & Date: dan 2019-03-14 20:53:21
Wiki:window-functions
Context
2019-03-15
20:46
Implement the EXCLUDE clause for window frames. check-in: 9b43c3ee2e user: dan tags: window-functions
2019-03-14
20:53
Parse EXCLUDE clauses in window frames. They do not yet work. check-in: d03c7533a1 user: dan tags: window-functions
16:36
Add things to this branch that will be required to support the EXCLUDE clause. check-in: 7d66cd2013 user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1684   1684     A = Z;
  1685   1685   }
  1686   1686   window(A) ::= nm(W) frame_opt(Z). {
  1687   1687     A = sqlite3WindowAssemble(pParse, Z, 0, 0, &W);
  1688   1688   }
  1689   1689   
  1690   1690   frame_opt(A) ::= .                             { 
  1691         -  A = sqlite3WindowAlloc(pParse, 0, TK_UNBOUNDED, 0, TK_CURRENT, 0);
         1691  +  A = sqlite3WindowAlloc(pParse, 0, TK_UNBOUNDED, 0, TK_CURRENT, 0, 0);
  1692   1692   }
  1693         -frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y). { 
  1694         -  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0);
         1693  +frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y) frame_exclude_opt(Z). { 
         1694  +  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0, Z);
  1695   1695   }
  1696         -frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z). { 
  1697         -  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
         1696  +frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z) frame_exclude_opt(W). { 
         1697  +  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr, W);
  1698   1698   }
  1699   1699   
  1700   1700   range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
  1701   1701   range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }
  1702   1702   range_or_rows(A) ::= GROUPS.  { A = TK_GROUPS;}
  1703   1703   
  1704   1704   
................................................................................
  1706   1706   frame_bound_s(A) ::= UNBOUNDED PRECEDING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
  1707   1707   frame_bound_e(A) ::= frame_bound(X). { A = X; }
  1708   1708   frame_bound_e(A) ::= UNBOUNDED FOLLOWING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
  1709   1709   
  1710   1710   frame_bound(A) ::= expr(X) PRECEDING.   { A.eType = TK_PRECEDING; A.pExpr = X; }
  1711   1711   frame_bound(A) ::= CURRENT ROW.         { A.eType = TK_CURRENT  ; A.pExpr = 0; }
  1712   1712   frame_bound(A) ::= expr(X) FOLLOWING.   { A.eType = TK_FOLLOWING; A.pExpr = X; }
         1713  +
         1714  +%type frame_exclude_opt {u8}
         1715  +frame_exclude_opt(A) ::= . { A = TK_NO; }
         1716  +frame_exclude_opt(A) ::= EXCLUDE frame_exclude(X). { A = X; }
         1717  +
         1718  +%type frame_exclude {u8}
         1719  +frame_exclude(A) ::= NO OTHERS.   { A = 0; }
         1720  +frame_exclude(A) ::= CURRENT ROW. { A = TK_CURRENT; }
         1721  +frame_exclude(A) ::= GROUP.       { A = TK_GROUP; }
         1722  +frame_exclude(A) ::= TIES.        { A = TK_TIES; }
         1723  +
  1713   1724   
  1714   1725   %type window_clause {Window*}
  1715   1726   %destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);}
  1716   1727   window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; }
  1717   1728   
  1718   1729   %type over_clause {Window*}
  1719   1730   %destructor over_clause {sqlite3WindowDelete(pParse->db, $$);}

Changes to src/sqliteInt.h.

  3581   3581     int regStartRowid;
  3582   3582     int regEndRowid;
  3583   3583   };
  3584   3584   
  3585   3585   #ifndef SQLITE_OMIT_WINDOWFUNC
  3586   3586   void sqlite3WindowDelete(sqlite3*, Window*);
  3587   3587   void sqlite3WindowListDelete(sqlite3 *db, Window *p);
  3588         -Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
         3588  +Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8);
  3589   3589   void sqlite3WindowAttach(Parse*, Expr*, Window*);
  3590   3590   int sqlite3WindowCompare(Parse*, Window*, Window*);
  3591   3591   void sqlite3WindowCodeInit(Parse*, Window*);
  3592   3592   void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
  3593   3593   int sqlite3WindowRewrite(Parse*, Select*);
  3594   3594   int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
  3595   3595   void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);

Changes to src/window.c.

   990    990   */
   991    991   Window *sqlite3WindowAlloc(
   992    992     Parse *pParse,    /* Parsing context */
   993    993     int eType,        /* Frame type. TK_RANGE or TK_ROWS */
   994    994     int eStart,       /* Start type: CURRENT, PRECEDING, FOLLOWING, UNBOUNDED */
   995    995     Expr *pStart,     /* Start window size if TK_PRECEDING or FOLLOWING */
   996    996     int eEnd,         /* End type: CURRENT, FOLLOWING, TK_UNBOUNDED, PRECEDING */
   997         -  Expr *pEnd        /* End window size if TK_FOLLOWING or PRECEDING */
          997  +  Expr *pEnd,       /* End window size if TK_FOLLOWING or PRECEDING */
          998  +  u8 eExclude       /* EXCLUDE clause */
   998    999   ){
   999   1000     Window *pWin = 0;
  1000   1001     int bImplicitFrame = 0;
  1001   1002   
  1002   1003     /* Parser assures the following: */
  1003   1004     assert( eType==0 || eType==TK_RANGE || eType==TK_ROWS || eType==TK_GROUPS );
  1004   1005     assert( eStart==TK_CURRENT || eStart==TK_PRECEDING
................................................................................
  1035   1036     }
  1036   1037   
  1037   1038     pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  1038   1039     if( pWin==0 ) goto windowAllocErr;
  1039   1040     pWin->eType = eType;
  1040   1041     pWin->eStart = eStart;
  1041   1042     pWin->eEnd = eEnd;
         1043  +  pWin->eExclude = eExclude;
  1042   1044     pWin->bImplicitFrame = bImplicitFrame;
  1043   1045     pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  1044   1046     pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  1045   1047     /* pWin->eExclude = 1; */
  1046   1048     return pWin;
  1047   1049   
  1048   1050   windowAllocErr:

Changes to tool/mkkeywordhash.c.

   201    201     { "DROP",             "TK_DROP",         ALWAYS                 },
   202    202     { "END",              "TK_END",          ALWAYS                 },
   203    203     { "EACH",             "TK_EACH",         TRIGGER                },
   204    204     { "ELSE",             "TK_ELSE",         ALWAYS                 },
   205    205     { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
   206    206     { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
   207    207     { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
          208  +  { "EXCLUDE",          "TK_EXCLUDE",      WINDOWFUNC             },
   208    209     { "EXISTS",           "TK_EXISTS",       ALWAYS                 },
   209    210     { "EXPLAIN",          "TK_EXPLAIN",      EXPLAIN                },
   210    211     { "FAIL",             "TK_FAIL",         CONFLICT|TRIGGER       },
   211    212     { "FILTER",           "TK_FILTER",       WINDOWFUNC             },
   212    213     { "FOLLOWING",        "TK_FOLLOWING",    WINDOWFUNC             },
   213    214     { "FOR",              "TK_FOR",          TRIGGER                },
   214    215     { "FOREIGN",          "TK_FOREIGN",      FKEY                   },
................................................................................
   235    236     { "JOIN",             "TK_JOIN",         ALWAYS                 },
   236    237     { "KEY",              "TK_KEY",          ALWAYS                 },
   237    238     { "LEFT",             "TK_JOIN_KW",      ALWAYS                 },
   238    239     { "LIKE",             "TK_LIKE_KW",      ALWAYS                 },
   239    240     { "LIMIT",            "TK_LIMIT",        ALWAYS                 },
   240    241     { "MATCH",            "TK_MATCH",        ALWAYS                 },
   241    242     { "NATURAL",          "TK_JOIN_KW",      ALWAYS                 },
   242         -  { "NO",               "TK_NO",           FKEY                   },
          243  +  { "NO",               "TK_NO",           FKEY|WINDOWFUNC        },
   243    244     { "NOT",              "TK_NOT",          ALWAYS                 },
   244    245     { "NOTHING",          "TK_NOTHING",      UPSERT                 },
   245    246     { "NOTNULL",          "TK_NOTNULL",      ALWAYS                 },
   246    247     { "NULL",             "TK_NULL",         ALWAYS                 },
   247    248     { "OF",               "TK_OF",           ALWAYS                 },
   248    249     { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
   249    250     { "ON",               "TK_ON",           ALWAYS                 },
   250    251     { "OR",               "TK_OR",           ALWAYS                 },
   251    252     { "ORDER",            "TK_ORDER",        ALWAYS                 },
          253  +  { "OTHERS",           "TK_OTHERS",       WINDOWFUNC             },
   252    254     { "OUTER",            "TK_JOIN_KW",      ALWAYS                 },
   253    255     { "OVER",             "TK_OVER",         WINDOWFUNC             },
   254    256     { "PARTITION",        "TK_PARTITION",    WINDOWFUNC             },
   255    257     { "PLAN",             "TK_PLAN",         EXPLAIN                },
   256    258     { "PRAGMA",           "TK_PRAGMA",       PRAGMA                 },
   257    259     { "PRECEDING",        "TK_PRECEDING",    WINDOWFUNC             },
   258    260     { "PRIMARY",          "TK_PRIMARY",      ALWAYS                 },
................................................................................
   274    276     { "SAVEPOINT",        "TK_SAVEPOINT",    ALWAYS                 },
   275    277     { "SELECT",           "TK_SELECT",       ALWAYS                 },
   276    278     { "SET",              "TK_SET",          ALWAYS                 },
   277    279     { "TABLE",            "TK_TABLE",        ALWAYS                 },
   278    280     { "TEMP",             "TK_TEMP",         ALWAYS                 },
   279    281     { "TEMPORARY",        "TK_TEMP",         ALWAYS                 },
   280    282     { "THEN",             "TK_THEN",         ALWAYS                 },
          283  +  { "TIES",             "TK_TIES",         WINDOWFUNC             },
   281    284     { "TO",               "TK_TO",           ALWAYS                 },
   282    285     { "TRANSACTION",      "TK_TRANSACTION",  ALWAYS                 },
   283    286     { "TRIGGER",          "TK_TRIGGER",      TRIGGER                },
   284    287     { "UNBOUNDED",        "TK_UNBOUNDED",    WINDOWFUNC             },
   285    288     { "UNION",            "TK_UNION",        COMPOUND               },
   286    289     { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
   287    290     { "UPDATE",           "TK_UPDATE",       ALWAYS                 },