Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -4116,11 +4116,11 @@ ** 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). +** 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. ** @@ -4134,10 +4134,14 @@ ** 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( @@ -4149,10 +4153,14 @@ ){ 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. Index: test/window1.test ================================================================== --- test/window1.test +++ test/window1.test @@ -368,8 +368,40 @@ 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