/ Check-in [059ff53a]
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 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 Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

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







|







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

Changes to test/window1.test.

544
545
546
547
548
549
550
551













































552
do_execsql_test 12.110 {
  INSERT INTO t1 VALUES(6, 'F', 'three');
  INSERT INTO t1 VALUES(7, 'G', 'one');
  SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
    FROM t1 WHERE id>1
   ORDER BY b LIMIT 2;
} {2 B two 3 C three}














































finish_test








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

544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
do_execsql_test 12.110 {
  INSERT INTO t1 VALUES(6, 'F', 'three');
  INSERT INTO t1 VALUES(7, 'G', 'one');
  SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
    FROM t1 WHERE id>1
   ORDER BY b LIMIT 2;
} {2 B two 3 C three}

#-------------------------------------------------------------------------

do_execsql_test 13.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a int, b int);
  INSERT INTO t1 VALUES(1,11);
  INSERT INTO t1 VALUES(2,12);
}

do_execsql_test 13.2.1 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1;
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2   2 1   1 2
}
do_execsql_test 13.2.2 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    UNION ALL
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2   2 1   1 2
}
do_execsql_test 13.3 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    UNION 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   1 2   2 1   2 2  
}

do_execsql_test 13.4 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    EXCEPT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2 
}

do_execsql_test 13.5 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    INTERSECT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
}

finish_test