/ Check-in [11d73339]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

4114
4115
4116
4117
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
....
4132
4133
4134
4135
4136
4137
4138




4139
4140
4141
4142
4143
4144
4145
....
4147
4148
4149
4150
4151
4152
4153




4154
4155
4156
4157
4158
4159
4160
**           a GROUP BY clause.  But such a HAVING clause is also harmless
**           so there does not appear to be any reason to add extra logic
**           to suppress it. **)
**
**   (2) The inner query is the recursive part of a common table expression.
**
**   (3) The inner query has a LIMIT clause (since the changes to the WHERE
**       close would change the meaning of the LIMIT).
**
**   (4) The inner query is the right operand of a LEFT JOIN and the
**       expression to be pushed down does not come from the ON clause
**       on that LEFT JOIN.
**
**   (5) The WHERE clause expression originates in the ON or USING clause
**       of a LEFT JOIN where iCursor is not the right-hand table of that
................................................................................
**           FROM (SELECT 1 AS a1 UNION ALL SELECT 2) AS aa
**           JOIN (SELECT 1 AS b2 UNION ALL SELECT 2) AS bb ON (a1=b2)
**           LEFT JOIN (SELECT 8 AS c3 UNION ALL SELECT 9) AS cc ON (b2=2);
**
**       The correct answer is three rows:  (1,1,NULL),(2,2,8),(2,2,9).
**       But if the (b2=2) term were to be pushed down into the bb subquery,
**       then the (1,1,NULL) row would be suppressed.




**
** Return 0 if no changes are made and non-zero if one or more WHERE clause
** terms are duplicated into the subquery.
*/
static int pushDownWhereTerms(
  Parse *pParse,        /* Parse context (for malloc() and error reporting) */
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
................................................................................
  int iCursor,          /* Cursor number of the subquery */
  int isLeftJoin        /* True if pSubq is the right term of a LEFT JOIN */
){
  Expr *pNew;
  int nChng = 0;
  if( pWhere==0 ) return 0;
  if( pSubq->selFlags & SF_Recursive ) return 0;  /* restriction (2) */





#ifdef SQLITE_DEBUG
  /* Only the first term of a compound can have a WITH clause.  But make
  ** sure no other terms are marked SF_Recursive in case something changes
  ** in the future.
  */
  {







|







 







>
>
>
>







 







>
>
>
>







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

#ifndef SQLITE_OMIT_WINDOWFUNC
  if( pSubq->pWin ) return 0;
#endif

#ifdef SQLITE_DEBUG
  /* Only the first term of a compound can have a WITH clause.  But make
  ** sure no other terms are marked SF_Recursive in case something changes
  ** in the future.
  */
  {

Changes to test/window1.test.

366
367
368
369
370
371
372
373
































374
375
do_execsql_test 9.3 {
  WITH aaa(x, y, z) AS (
    SELECT x, y, max(y) OVER xyz FROM t4
    WINDOW xyz AS (ORDER BY x)
  )
  SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
} {1 g g g   2 i i g   3 l l g   4 g l g   5 a l g   6 m m g}

































finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
do_execsql_test 9.3 {
  WITH aaa(x, y, z) AS (
    SELECT x, y, max(y) OVER xyz FROM t4
    WINDOW xyz AS (ORDER BY x)
  )
  SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
} {1 g g g   2 i i g   3 l l g   4 g l g   5 a l g   6 m m g}

#-------------------------------------------------------------------------
#
do_execsql_test 10.0 {
  CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total);
  INSERT INTO sales VALUES
      ('Alice',     'North', 34),
      ('Frank',     'South', 22),
      ('Charles',   'North', 45),
      ('Darrell',   'South', 8),
      ('Grant',     'South', 23),
      ('Brad' ,     'North', 22),
      ('Elizabeth', 'South', 99),
      ('Horace',    'East',   1);
}

# Best two salespeople from each region
#
do_execsql_test 10.1 {
  SELECT emp, region, total FROM (
    SELECT 
      emp, region, total,
      row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank
    FROM sales
  ) WHERE rank<=2 ORDER BY region, total DESC
} {
  Horace      East     1
  Charles     North   45
  Alice       North   34
  Elizabeth   South   99
  Grant       South   23
}

finish_test