/ Check-in [19c2e4b2]
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 minor problems on this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 19c2e4b2f164521eab84cb0a0e12984be9431eaedd001dd3671e9ea1a6212353
User & Date: dan 2018-05-19 14:15:29
Context
2018-05-21
19:45
Begin adding support for more esoteric window frames. check-in: bc4b81d6 user: dan tags: exp-window-functions
2018-05-19
14:15
Fix minor problems on this branch. check-in: 19c2e4b2 user: dan tags: exp-window-functions
2018-05-17
19:24
Evaluate multiple window functions in a single pass if they use the same window definition. Add xValue callbacks for other built-in aggregate functions. check-in: c9f0f140 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  3827   3827         zId = pExpr->u.zToken;
  3828   3828         pDef = sqlite3FindFunction(db, zId, nFarg, enc, 0);
  3829   3829   #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
  3830   3830         if( pDef==0 && pParse->explain ){
  3831   3831           pDef = sqlite3FindFunction(db, "unknown", nFarg, enc, 0);
  3832   3832         }
  3833   3833   #endif
  3834         -      if( pDef==0 /* || pDef->xFinalize!=0 */ ){
         3834  +      if( pDef==0 || pDef->xFinalize!=0 ){
  3835   3835           sqlite3ErrorMsg(pParse, "unknown function: %s()", zId);
  3836   3836           break;
  3837   3837         }
  3838   3838   
  3839   3839         /* Attempt a direct implementation of the built-in COALESCE() and
  3840   3840         ** IFNULL() functions.  This avoids unnecessary evaluation of
  3841   3841         ** arguments past the first non-NULL argument.

Changes to src/parse.y.

  1009   1009     }
  1010   1010     A = sqlite3ExprFunction(pParse, Y, &X);
  1011   1011     sqlite3WindowAttach(pParse, A, Z);
  1012   1012     if( D==SF_Distinct && A ){
  1013   1013       A->flags |= EP_Distinct;
  1014   1014     }
  1015   1015   }
  1016         -expr(A) ::= id(X) LP STAR RP. {
         1016  +expr(A) ::= id(X) LP STAR RP window(Z). {
  1017   1017     A = sqlite3ExprFunction(pParse, 0, &X);
         1018  +  sqlite3WindowAttach(pParse, A, Z);
  1018   1019   }
  1019   1020   term(A) ::= CTIME_KW(OP). {
  1020   1021     A = sqlite3ExprFunction(pParse, 0, &OP);
  1021   1022   }
  1022   1023   
  1023   1024   
  1024   1025   %type window {Window*}

Changes to src/select.c.

  4681   4681   #ifndef SQLITE_OMIT_SUBQUERY
  4682   4682         Select *pSel = pFrom->pSelect;
  4683   4683         /* A sub-query in the FROM clause of a SELECT */
  4684   4684         assert( pSel!=0 );
  4685   4685         assert( pFrom->pTab==0 );
  4686   4686         if( sqlite3WalkSelect(pWalker, pSel) ) return WRC_Abort;
  4687   4687         if( selectExpandSubquery(pParse, pFrom) ) return WRC_Abort;
  4688         -#if 0
  4689         -      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
  4690         -      if( pTab==0 ) return WRC_Abort;
  4691         -      pTab->nTabRef = 1;
  4692         -      if( pFrom->zAlias ){
  4693         -        pTab->zName = sqlite3DbStrDup(db, pFrom->zAlias);
  4694         -      }else{
  4695         -        pTab->zName = sqlite3MPrintf(db, "subquery_%p", (void*)pTab);
  4696         -      }
  4697         -      while( pSel->pPrior ){ pSel = pSel->pPrior; }
  4698         -      sqlite3ColumnsFromExprList(pParse, pSel->pEList,&pTab->nCol,&pTab->aCol);
  4699         -      pTab->iPKey = -1;
  4700         -      pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
  4701         -      pTab->tabFlags |= TF_Ephemeral;
  4702         -#endif
  4703   4688   #endif
  4704   4689       }else{
  4705   4690         /* An ordinary table or view name in the FROM clause */
  4706   4691         assert( pFrom->pTab==0 );
  4707   4692         pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom);
  4708   4693         if( pTab==0 ) return WRC_Abort;
  4709   4694         if( pTab->nTabRef>=0xffff ){

Changes to test/permutations.test.

   163    163   
   164    164   test_suite "veryquick" -prefix "" -description {
   165    165     "Very" quick test suite. Runs in minutes on a workstation.
   166    166     This test suite is the same as the "quick" tests, except that some files
   167    167     that test malloc and IO errors are omitted.
   168    168   } -files [
   169    169     test_set $allquicktests -exclude *malloc* *ioerr* *fault* *bigfile* *_err* \
   170         -      *fts5corrupt* *fts5big* *fts5aj* *expert* table.test
          170  +      *fts5corrupt* *fts5big* *fts5aj*
   171    171   ]
   172    172   
   173    173   test_suite "extraquick" -prefix "" -description {
   174    174     "Extra" quick test suite. Runs in a few minutes on a workstation.
   175    175     This test suite is the same as the "veryquick" tests, except that
   176    176     slower tests are omitted.
   177    177   } -files [

Changes to test/window1.test.

   147    147     2  3       1.0
   148    148     3  6       1.5
   149    149     4  10      2.0
   150    150     5  15      2.5
   151    151     6  21      3.0
   152    152   }
   153    153   
   154         -do_execsql_test 4.10 {
          154  +do_execsql_test 4.10.1 {
   155    155     SELECT a, 
   156    156       count() OVER (ORDER BY a DESC),
   157    157       group_concat(a, '.') OVER (ORDER BY a DESC) 
   158    158     FROM t2 ORDER BY a DESC
   159    159   } {
          160  +  6 1 6
          161  +  5 2 6.5
          162  +  4 3 6.5.4
          163  +  3 4 6.5.4.3
          164  +  2 5 6.5.4.3.2
          165  +  1 6 6.5.4.3.2.1
          166  +  0 7 6.5.4.3.2.1.0
          167  +}
          168  +
          169  +do_execsql_test 4.10.2 {
          170  +  SELECT a, 
          171  +    count(*) OVER (ORDER BY a DESC),
          172  +    group_concat(a, '.') OVER (ORDER BY a DESC) 
          173  +  FROM t2 ORDER BY a DESC
          174  +} {
   160    175     6 1 6
   161    176     5 2 6.5
   162    177     4 3 6.5.4
   163    178     3 4 6.5.4.3
   164    179     2 5 6.5.4.3.2
   165    180     1 6 6.5.4.3.2.1
   166    181     0 7 6.5.4.3.2.1.0
   167    182   }
   168    183   
   169    184   
   170    185   finish_test

Added test/window2.tcl.

            1  +# 2018 May 19
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +package require sqlite3
           14  +package require Pgtcl
           15  +
           16  +set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"]
           17  +sqlite3 sqlite ""
           18  +
           19  +proc execsql {sql} {
           20  +
           21  +  set lSql [list]
           22  +  set frag ""
           23  +  while {[string length $sql]>0} {
           24  +    set i [string first ";" $sql]
           25  +    if {$i>=0} {
           26  +      append frag [string range $sql 0 $i]
           27  +      set sql [string range $sql $i+1 end]
           28  +      if {[sqlite complete $frag]} {
           29  +        lappend lSql $frag
           30  +        set frag ""
           31  +      }
           32  +    } else {
           33  +      set frag $sql
           34  +      set sql ""
           35  +    }
           36  +  }
           37  +  if {$frag != ""} {
           38  +    lappend lSql $frag
           39  +  }
           40  +  #puts $lSql
           41  +
           42  +  set ret [list]
           43  +  foreach stmt $lSql {
           44  +    set res [pg_exec $::db $stmt]
           45  +    set err [pg_result $res -error]
           46  +    if {$err!=""} { error $err }
           47  +    for {set i 0} {$i < [pg_result $res -numTuples]} {incr i} {
           48  +      lappend ret {*}[pg_result $res -getTuple $i]
           49  +    }
           50  +    pg_result $res -clear
           51  +  }
           52  +
           53  +  set ret
           54  +}
           55  +
           56  +proc execsql_test {tn sql} {
           57  +  set res [execsql $sql]
           58  +  puts $::fd "do_execsql_test $tn {"
           59  +  puts $::fd "  [string trim $sql]"
           60  +  puts $::fd "} {$res}"
           61  +  puts $::fd ""
           62  +}
           63  +
           64  +proc start_test {name date} {
           65  +  set dir [file dirname $::argv0]
           66  +  set output [file join $dir $name.test]
           67  +  set ::fd [open $output w]
           68  +puts $::fd [string trimleft "
           69  +# $date
           70  +#
           71  +# The author disclaims copyright to this source code.  In place of
           72  +# a legal notice, here is a blessing:
           73  +#
           74  +#    May you do good and not evil.
           75  +#    May you find forgiveness for yourself and forgive others.
           76  +#    May you share freely, never taking more than you give.
           77  +#
           78  +#***********************************************************************
           79  +# This file implements regression tests for SQLite library.
           80  +#
           81  +
           82  +####################################################
           83  +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
           84  +####################################################
           85  +"]
           86  +  puts $::fd {set testdir [file dirname $argv0]}
           87  +  puts $::fd {source $testdir/tester.tcl}
           88  +  puts $::fd "set testprefix $name"
           89  +  puts $::fd ""
           90  +}
           91  +
           92  +proc finish_test {} {
           93  +  puts $::fd finish_test
           94  +  close $::fd
           95  +}
           96  +
           97  +#=========================================================================
           98  +
           99  +
          100  +start_test window2 "2018 May 19"
          101  +
          102  +execsql_test 1.0 {
          103  +  DROP TABLE IF EXISTS t1;
          104  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
          105  +  INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
          106  +  INSERT INTO t1 VALUES(2, 'even', 'two',   2);
          107  +  INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
          108  +  INSERT INTO t1 VALUES(4, 'even', 'four',  4);
          109  +  INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
          110  +  INSERT INTO t1 VALUES(6, 'even', 'six',   6);
          111  +}
          112  +
          113  +execsql_test 1.1 {
          114  +  SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1;
          115  +}
          116  +
          117  +execsql_test 1.2 {
          118  +  SELECT sum(d) OVER () FROM t1;
          119  +}
          120  +
          121  +execsql_test 1.3 {
          122  +  SELECT sum(d) OVER (PARTITION BY b) FROM t1;
          123  +}
          124  +
          125  +finish_test
          126  +
          127  +

Added test/window2.test.

            1  +# 2018 May 19
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +
           14  +####################################################
           15  +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
           16  +####################################################
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +set testprefix window2
           21  +
           22  +do_execsql_test 1.0 {
           23  +  DROP TABLE IF EXISTS t1;
           24  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
           25  +  INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
           26  +  INSERT INTO t1 VALUES(2, 'even', 'two',   2);
           27  +  INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
           28  +  INSERT INTO t1 VALUES(4, 'even', 'four',  4);
           29  +  INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
           30  +  INSERT INTO t1 VALUES(6, 'even', 'six',   6);
           31  +} {}
           32  +
           33  +do_execsql_test 1.1 {
           34  +  SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1;
           35  +} {four 4 six 10 two 12 five 5 one 6 three 9}
           36  +
           37  +do_execsql_test 1.2 {
           38  +  SELECT sum(d) OVER () FROM t1;
           39  +} {21 21 21 21 21 21}
           40  +
           41  +do_execsql_test 1.3 {
           42  +  SELECT sum(d) OVER (PARTITION BY b) FROM t1;
           43  +} {12 12 12 9 9 9}
           44  +
           45  +finish_test