/ Check-in [11d73339]
Login

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

Overview
Comment:Fix a problem with using a window-function SELECT as a FROM clause sub-query in some circumstances.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 11d733396f75ef1f206cd6f35630ff176484279861772918b9ea69412c13c62d
User & Date: dan 2018-06-23 07:59:39
Context
2018-06-23
16:26
Fix a problem with using LIMIT in window-function queries. check-in: c1abd2dd user: dan tags: exp-window-functions
07:59
Fix a problem with using a window-function SELECT as a FROM clause sub-query in some circumstances. check-in: 11d73339 user: dan tags: exp-window-functions
2018-06-22
20:51
Omit all window-function related code when building with SQLITE_OMIT_WINDOWFUNC. check-in: 5f04b016 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  4114   4114   **           a GROUP BY clause.  But such a HAVING clause is also harmless
  4115   4115   **           so there does not appear to be any reason to add extra logic
  4116   4116   **           to suppress it. **)
  4117   4117   **
  4118   4118   **   (2) The inner query is the recursive part of a common table expression.
  4119   4119   **
  4120   4120   **   (3) The inner query has a LIMIT clause (since the changes to the WHERE
  4121         -**       close would change the meaning of the LIMIT).
         4121  +**       clause would change the meaning of the LIMIT).
  4122   4122   **
  4123   4123   **   (4) The inner query is the right operand of a LEFT JOIN and the
  4124   4124   **       expression to be pushed down does not come from the ON clause
  4125   4125   **       on that LEFT JOIN.
  4126   4126   **
  4127   4127   **   (5) The WHERE clause expression originates in the ON or USING clause
  4128   4128   **       of a LEFT JOIN where iCursor is not the right-hand table of that
................................................................................
  4132   4132   **           FROM (SELECT 1 AS a1 UNION ALL SELECT 2) AS aa
  4133   4133   **           JOIN (SELECT 1 AS b2 UNION ALL SELECT 2) AS bb ON (a1=b2)
  4134   4134   **           LEFT JOIN (SELECT 8 AS c3 UNION ALL SELECT 9) AS cc ON (b2=2);
  4135   4135   **
  4136   4136   **       The correct answer is three rows:  (1,1,NULL),(2,2,8),(2,2,9).
  4137   4137   **       But if the (b2=2) term were to be pushed down into the bb subquery,
  4138   4138   **       then the (1,1,NULL) row would be suppressed.
         4139  +**
         4140  +**   (6) The inner query features one or more window-functions (since 
         4141  +**       changes to the WHERE clause of the inner query could change the 
         4142  +**       window over which window functions are calculated).
  4139   4143   **
  4140   4144   ** Return 0 if no changes are made and non-zero if one or more WHERE clause
  4141   4145   ** terms are duplicated into the subquery.
  4142   4146   */
  4143   4147   static int pushDownWhereTerms(
  4144   4148     Parse *pParse,        /* Parse context (for malloc() and error reporting) */
  4145   4149     Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
................................................................................
  4147   4151     int iCursor,          /* Cursor number of the subquery */
  4148   4152     int isLeftJoin        /* True if pSubq is the right term of a LEFT JOIN */
  4149   4153   ){
  4150   4154     Expr *pNew;
  4151   4155     int nChng = 0;
  4152   4156     if( pWhere==0 ) return 0;
  4153   4157     if( pSubq->selFlags & SF_Recursive ) return 0;  /* restriction (2) */
         4158  +
         4159  +#ifndef SQLITE_OMIT_WINDOWFUNC
         4160  +  if( pSubq->pWin ) return 0;
         4161  +#endif
  4154   4162   
  4155   4163   #ifdef SQLITE_DEBUG
  4156   4164     /* Only the first term of a compound can have a WITH clause.  But make
  4157   4165     ** sure no other terms are marked SF_Recursive in case something changes
  4158   4166     ** in the future.
  4159   4167     */
  4160   4168     {

Changes to test/window1.test.

   366    366   do_execsql_test 9.3 {
   367    367     WITH aaa(x, y, z) AS (
   368    368       SELECT x, y, max(y) OVER xyz FROM t4
   369    369       WINDOW xyz AS (ORDER BY x)
   370    370     )
   371    371     SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
   372    372   } {1 g g g   2 i i g   3 l l g   4 g l g   5 a l g   6 m m g}
          373  +
          374  +#-------------------------------------------------------------------------
          375  +#
          376  +do_execsql_test 10.0 {
          377  +  CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total);
          378  +  INSERT INTO sales VALUES
          379  +      ('Alice',     'North', 34),
          380  +      ('Frank',     'South', 22),
          381  +      ('Charles',   'North', 45),
          382  +      ('Darrell',   'South', 8),
          383  +      ('Grant',     'South', 23),
          384  +      ('Brad' ,     'North', 22),
          385  +      ('Elizabeth', 'South', 99),
          386  +      ('Horace',    'East',   1);
          387  +}
          388  +
          389  +# Best two salespeople from each region
          390  +#
          391  +do_execsql_test 10.1 {
          392  +  SELECT emp, region, total FROM (
          393  +    SELECT 
          394  +      emp, region, total,
          395  +      row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank
          396  +    FROM sales
          397  +  ) WHERE rank<=2 ORDER BY region, total DESC
          398  +} {
          399  +  Horace      East     1
          400  +  Charles     North   45
          401  +  Alice       North   34
          402  +  Elizabeth   South   99
          403  +  Grant       South   23
          404  +}
   373    405   
   374    406   finish_test
   375    407