SQLite

Check-in [059ff53a46]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 059ff53a46c7f1e4bf3e7dc558312beef67826c2753e2ab7e4e7df498b37b617
User & Date: dan 2018-10-23 13:48:19.879
Context
2018-10-24
23:55
Add the ENABLE_GEOPOLY case to the compile_options pragma. (check-in: de940296d2 user: drh tags: trunk)
2018-10-23
13:48
Fix a problem with using window functions in compound (UNION, INTERSECT etc.) queries. (check-in: 059ff53a46 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: cbedcb9aae user: dan tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/window.c.
︙
742
743
744
745
746
747
748
749

750
751
752
753
754
755
756

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 ){
  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

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