Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -3829,11 +3829,11 @@ #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION if( pDef==0 && pParse->explain ){ pDef = sqlite3FindFunction(db, "unknown", nFarg, enc, 0); } #endif - if( pDef==0 /* || pDef->xFinalize!=0 */ ){ + if( pDef==0 || pDef->xFinalize!=0 ){ sqlite3ErrorMsg(pParse, "unknown function: %s()", zId); break; } /* Attempt a direct implementation of the built-in COALESCE() and Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -1011,12 +1011,13 @@ sqlite3WindowAttach(pParse, A, Z); if( D==SF_Distinct && A ){ A->flags |= EP_Distinct; } } -expr(A) ::= id(X) LP STAR RP. { +expr(A) ::= id(X) LP STAR RP window(Z). { A = sqlite3ExprFunction(pParse, 0, &X); + sqlite3WindowAttach(pParse, A, Z); } term(A) ::= CTIME_KW(OP). { A = sqlite3ExprFunction(pParse, 0, &OP); } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -4683,25 +4683,10 @@ /* A sub-query in the FROM clause of a SELECT */ assert( pSel!=0 ); assert( pFrom->pTab==0 ); if( sqlite3WalkSelect(pWalker, pSel) ) return WRC_Abort; if( selectExpandSubquery(pParse, pFrom) ) return WRC_Abort; -#if 0 - pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); - if( pTab==0 ) return WRC_Abort; - pTab->nTabRef = 1; - if( pFrom->zAlias ){ - pTab->zName = sqlite3DbStrDup(db, pFrom->zAlias); - }else{ - pTab->zName = sqlite3MPrintf(db, "subquery_%p", (void*)pTab); - } - while( pSel->pPrior ){ pSel = pSel->pPrior; } - sqlite3ColumnsFromExprList(pParse, pSel->pEList,&pTab->nCol,&pTab->aCol); - pTab->iPKey = -1; - pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); - pTab->tabFlags |= TF_Ephemeral; -#endif #endif }else{ /* An ordinary table or view name in the FROM clause */ assert( pFrom->pTab==0 ); pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom); Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -165,11 +165,11 @@ "Very" quick test suite. Runs in minutes on a workstation. This test suite is the same as the "quick" tests, except that some files that test malloc and IO errors are omitted. } -files [ test_set $allquicktests -exclude *malloc* *ioerr* *fault* *bigfile* *_err* \ - *fts5corrupt* *fts5big* *fts5aj* *expert* table.test + *fts5corrupt* *fts5big* *fts5aj* ] test_suite "extraquick" -prefix "" -description { "Extra" quick test suite. Runs in a few minutes on a workstation. This test suite is the same as the "veryquick" tests, except that Index: test/window1.test ================================================================== --- test/window1.test +++ test/window1.test @@ -149,16 +149,31 @@ 4 10 2.0 5 15 2.5 6 21 3.0 } -do_execsql_test 4.10 { +do_execsql_test 4.10.1 { SELECT a, count() OVER (ORDER BY a DESC), group_concat(a, '.') OVER (ORDER BY a DESC) FROM t2 ORDER BY a DESC } { + 6 1 6 + 5 2 6.5 + 4 3 6.5.4 + 3 4 6.5.4.3 + 2 5 6.5.4.3.2 + 1 6 6.5.4.3.2.1 + 0 7 6.5.4.3.2.1.0 +} + +do_execsql_test 4.10.2 { + SELECT a, + count(*) OVER (ORDER BY a DESC), + group_concat(a, '.') OVER (ORDER BY a DESC) + FROM t2 ORDER BY a DESC +} { 6 1 6 5 2 6.5 4 3 6.5.4 3 4 6.5.4.3 2 5 6.5.4.3.2 ADDED test/window2.tcl Index: test/window2.tcl ================================================================== --- /dev/null +++ test/window2.tcl @@ -0,0 +1,127 @@ +# 2018 May 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +package require sqlite3 +package require Pgtcl + +set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"] +sqlite3 sqlite "" + +proc execsql {sql} { + + set lSql [list] + set frag "" + while {[string length $sql]>0} { + set i [string first ";" $sql] + if {$i>=0} { + append frag [string range $sql 0 $i] + set sql [string range $sql $i+1 end] + if {[sqlite complete $frag]} { + lappend lSql $frag + set frag "" + } + } else { + set frag $sql + set sql "" + } + } + if {$frag != ""} { + lappend lSql $frag + } + #puts $lSql + + set ret [list] + foreach stmt $lSql { + set res [pg_exec $::db $stmt] + set err [pg_result $res -error] + if {$err!=""} { error $err } + for {set i 0} {$i < [pg_result $res -numTuples]} {incr i} { + lappend ret {*}[pg_result $res -getTuple $i] + } + pg_result $res -clear + } + + set ret +} + +proc execsql_test {tn sql} { + set res [execsql $sql] + puts $::fd "do_execsql_test $tn {" + puts $::fd " [string trim $sql]" + puts $::fd "} {$res}" + puts $::fd "" +} + +proc start_test {name date} { + set dir [file dirname $::argv0] + set output [file join $dir $name.test] + set ::fd [open $output w] +puts $::fd [string trimleft " +# $date +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### +"] + puts $::fd {set testdir [file dirname $argv0]} + puts $::fd {source $testdir/tester.tcl} + puts $::fd "set testprefix $name" + puts $::fd "" +} + +proc finish_test {} { + puts $::fd finish_test + close $::fd +} + +#========================================================================= + + +start_test window2 "2018 May 19" + +execsql_test 1.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); + INSERT INTO t1 VALUES(1, 'odd', 'one', 1); + INSERT INTO t1 VALUES(2, 'even', 'two', 2); + INSERT INTO t1 VALUES(3, 'odd', 'three', 3); + INSERT INTO t1 VALUES(4, 'even', 'four', 4); + INSERT INTO t1 VALUES(5, 'odd', 'five', 5); + INSERT INTO t1 VALUES(6, 'even', 'six', 6); +} + +execsql_test 1.1 { + SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1; +} + +execsql_test 1.2 { + SELECT sum(d) OVER () FROM t1; +} + +execsql_test 1.3 { + SELECT sum(d) OVER (PARTITION BY b) FROM t1; +} + +finish_test + + ADDED test/window2.test Index: test/window2.test ================================================================== --- /dev/null +++ test/window2.test @@ -0,0 +1,45 @@ +# 2018 May 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix window2 + +do_execsql_test 1.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); + INSERT INTO t1 VALUES(1, 'odd', 'one', 1); + INSERT INTO t1 VALUES(2, 'even', 'two', 2); + INSERT INTO t1 VALUES(3, 'odd', 'three', 3); + INSERT INTO t1 VALUES(4, 'even', 'four', 4); + INSERT INTO t1 VALUES(5, 'odd', 'five', 5); + INSERT INTO t1 VALUES(6, 'even', 'six', 6); +} {} + +do_execsql_test 1.1 { + SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1; +} {four 4 six 10 two 12 five 5 one 6 three 9} + +do_execsql_test 1.2 { + SELECT sum(d) OVER () FROM t1; +} {21 21 21 21 21 21} + +do_execsql_test 1.3 { + SELECT sum(d) OVER (PARTITION BY b) FROM t1; +} {12 12 12 9 9 9} + +finish_test