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: |
19c2e4b2f164521eab84cb0a0e12984b |
| 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
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