SQLite

Check-in [57070c68]
Login

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

Overview
Comment:Avoid evaluating PARTITION BY or ORDER BY expressions multiple times for window function queries that use multiple window functions with the same window-definition.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 57070c68bbe15fc1d19a765182432e844c082909bdbc63b58fd86b96e2c521dd
User & Date: dan 2019-11-23 15:10:28
Context
2019-11-23
16:34
Abort the window function tree rewrite early following an OOM. (check-in: d66f95a5 user: drh tags: trunk)
15:10
Avoid evaluating PARTITION BY or ORDER BY expressions multiple times for window function queries that use multiple window functions with the same window-definition. (check-in: 57070c68 user: dan tags: trunk)
08:51
Enhance the path arguments in JSON functions to access "#-N" array indexes. (check-in: ffeec62c user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

︙︙
783
784
785
786
787
788
789











790
791

792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807

          }
        }
      }
      /* Fall through.  */

    case TK_AGG_FUNCTION:
    case TK_COLUMN: {











      Expr *pDup = sqlite3ExprDup(pParse->db, pExpr, 0);
      p->pSub = sqlite3ExprListAppend(pParse, p->pSub, pDup);

      if( p->pSub ){
        assert( ExprHasProperty(pExpr, EP_Static)==0 );
        ExprSetProperty(pExpr, EP_Static);
        sqlite3ExprDelete(pParse->db, pExpr);
        ExprClearProperty(pExpr, EP_Static);
        memset(pExpr, 0, sizeof(Expr));

        pExpr->op = TK_COLUMN;
        pExpr->iColumn = p->pSub->nExpr-1;
        pExpr->iTable = p->pWin->iEphCsr;
        pExpr->y.pTab = p->pTab;
      }

      break;
    }









>
>
>
>
>
>
>
>
>
>
>
|
|
>








|








783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819

          }
        }
      }
      /* Fall through.  */

    case TK_AGG_FUNCTION:
    case TK_COLUMN: {
      int iCol = -1;
      if( p->pSub ){
        int i;
        for(i=0; i<p->pSub->nExpr; i++){
          if( 0==sqlite3ExprCompare(0, p->pSub->a[i].pExpr, pExpr, -1) ){
            iCol = i;
            break;
          }
        }
      }
      if( iCol<0 ){
        Expr *pDup = sqlite3ExprDup(pParse->db, pExpr, 0);
        p->pSub = sqlite3ExprListAppend(pParse, p->pSub, pDup);
      }
      if( p->pSub ){
        assert( ExprHasProperty(pExpr, EP_Static)==0 );
        ExprSetProperty(pExpr, EP_Static);
        sqlite3ExprDelete(pParse->db, pExpr);
        ExprClearProperty(pExpr, EP_Static);
        memset(pExpr, 0, sizeof(Expr));

        pExpr->op = TK_COLUMN;
        pExpr->iColumn = (iCol<0 ? p->pSub->nExpr-1: iCol);
        pExpr->iTable = p->pWin->iEphCsr;
        pExpr->y.pTab = p->pTab;
      }

      break;
    }
︙︙

Changes to test/window1.test.

︙︙
1251
1252
1253
1254
1255
1256
1257
















1258
1259

  CREATE TABLE t2(x);
  INSERT INTO t2 VALUES(1);
}
do_execsql_test 33.2 {
  SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2 
  ORDER BY 1;
} {6 1}

















finish_test








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



1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275

  CREATE TABLE t2(x);
  INSERT INTO t2 VALUES(1);
}
do_execsql_test 33.2 {
  SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2 
  ORDER BY 1;
} {6 1}

reset_db
do_execsql_test 34.1 {
  CREATE TABLE t1(a,b,c);
}
do_execsql_test 34.2 {
  SELECT avg(a) OVER (
      ORDER BY (SELECT sum(b) OVER ()
        FROM t1 ORDER BY (
          SELECT total(d) OVER (ORDER BY c)
          FROM (SELECT 1 AS d) ORDER BY 1
          )
        )
      )
  FROM t1;
}

finish_test