/ Check-in [059ff53a]
Login

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

Overview
Comment:Fix a problem with using window functions in compound (UNION, INTERSECT etc.) queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 059ff53a46c7f1e4bf3e7dc558312beef67826c2753e2ab7e4e7df498b37b617
User & Date: dan 2018-10-23 13:48:19
Context
2018-10-24
23:55
Add the ENABLE_GEOPOLY case to the compile_options pragma. check-in: de940296 user: drh tags: trunk
2018-10-23
13:48
Fix a problem with using window functions in compound (UNION, INTERSECT etc.) queries. check-in: 059ff53a user: dan tags: trunk
2018-10-20
13:48
Add the sqlite3changeset_start_v2() - a new version of _start() that accepts a flags parameter - and a streaming equivalent to the sessions module. Also add the SQLITE_CHANGESETSTART_INVERT flag, used with start_v2() to invert a changeset while iterating through it. check-in: cbedcb9a user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

   742    742   ** any SQL window functions, this function is a no-op. Otherwise, it 
   743    743   ** rewrites the SELECT statement so that window function xStep functions
   744    744   ** are invoked in the correct order as described under "SELECT REWRITING"
   745    745   ** at the top of this file.
   746    746   */
   747    747   int sqlite3WindowRewrite(Parse *pParse, Select *p){
   748    748     int rc = SQLITE_OK;
   749         -  if( p->pWin ){
          749  +  if( p->pWin && p->pPrior==0 ){
   750    750       Vdbe *v = sqlite3GetVdbe(pParse);
   751    751       sqlite3 *db = pParse->db;
   752    752       Select *pSub = 0;             /* The subquery */
   753    753       SrcList *pSrc = p->pSrc;
   754    754       Expr *pWhere = p->pWhere;
   755    755       ExprList *pGroupBy = p->pGroupBy;
   756    756       Expr *pHaving = p->pHaving;

Changes to test/window1.test.

   544    544   do_execsql_test 12.110 {
   545    545     INSERT INTO t1 VALUES(6, 'F', 'three');
   546    546     INSERT INTO t1 VALUES(7, 'G', 'one');
   547    547     SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
   548    548       FROM t1 WHERE id>1
   549    549      ORDER BY b LIMIT 2;
   550    550   } {2 B two 3 C three}
          551  +
          552  +#-------------------------------------------------------------------------
          553  +
          554  +do_execsql_test 13.1 {
          555  +  DROP TABLE IF EXISTS t1;
          556  +  CREATE TABLE t1(a int, b int);
          557  +  INSERT INTO t1 VALUES(1,11);
          558  +  INSERT INTO t1 VALUES(2,12);
          559  +}
          560  +
          561  +do_execsql_test 13.2.1 {
          562  +  SELECT a, rank() OVER(ORDER BY b) FROM t1;
          563  +  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
          564  +} {
          565  +  1 1   2 2   2 1   1 2
          566  +}
          567  +do_execsql_test 13.2.2 {
          568  +  SELECT a, rank() OVER(ORDER BY b) FROM t1
          569  +    UNION ALL
          570  +  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
          571  +} {
          572  +  1 1   2 2   2 1   1 2
          573  +}
          574  +do_execsql_test 13.3 {
          575  +  SELECT a, rank() OVER(ORDER BY b) FROM t1
          576  +    UNION 
          577  +  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
          578  +} {
          579  +  1 1   1 2   2 1   2 2  
          580  +}
          581  +
          582  +do_execsql_test 13.4 {
          583  +  SELECT a, rank() OVER(ORDER BY b) FROM t1
          584  +    EXCEPT 
          585  +  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
          586  +} {
          587  +  1 1   2 2 
          588  +}
          589  +
          590  +do_execsql_test 13.5 {
          591  +  SELECT a, rank() OVER(ORDER BY b) FROM t1
          592  +    INTERSECT 
          593  +  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
          594  +} {
          595  +}
   551    596   
   552    597   finish_test