Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fixes for RANGE windows and NULL values. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
723c84be3ec5ae941b7abd2442cdb76c |
| User & Date: | dan 2019-03-18 16:51:24 |
| Wiki: | window-functions |
Context
|
2019-03-18
| ||
| 18:55 | Always evaluate window functions using the alternative path usually only used by EXCLUDE frames if the SQLITE_QueryFlattener test flag is set. check-in: 2879a691 user: dan tags: window-functions | |
| 16:51 | Fixes for RANGE windows and NULL values. check-in: 723c84be user: dan tags: window-functions | |
|
2019-03-16
| ||
| 20:29 | Fix problems with RANGE windows and string, blob and NULL values. check-in: cebe09e1 user: dan tags: window-functions | |
Changes
Changes to src/vdbe.c.
1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 |
if( (flags1 | flags3)&MEM_Null ){
/* One or both operands are NULL */
if( pOp->p5 & SQLITE_NULLEQ ){
/* If SQLITE_NULLEQ is set (which will only happen if the operator is
** OP_Eq or OP_Ne) then take the jump or not depending on whether
** or not both operands are null.
*/
assert( pOp->opcode==OP_Eq || pOp->opcode==OP_Ne );
assert( (flags1 & MEM_Cleared)==0 );
assert( (pOp->p5 & SQLITE_JUMPIFNULL)==0 || CORRUPT_DB );
testcase( (pOp->p5 & SQLITE_JUMPIFNULL)!=0 );
if( (flags1&flags3&MEM_Null)!=0
&& (flags3&MEM_Cleared)==0
){
res = 0; /* Operands are equal */
}else{
res = 1; /* Operands are not equal */
}
}else{
/* SQLITE_NULLEQ is clear and at least one operand is NULL,
** then the result is always NULL.
** The jump is taken if the SQLITE_JUMPIFNULL bit is set.
*/
if( pOp->p5 & SQLITE_STOREP2 ){
|
< | |
1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 |
if( (flags1 | flags3)&MEM_Null ){
/* One or both operands are NULL */
if( pOp->p5 & SQLITE_NULLEQ ){
/* If SQLITE_NULLEQ is set (which will only happen if the operator is
** OP_Eq or OP_Ne) then take the jump or not depending on whether
** or not both operands are null.
*/
assert( (flags1 & MEM_Cleared)==0 );
assert( (pOp->p5 & SQLITE_JUMPIFNULL)==0 || CORRUPT_DB );
testcase( (pOp->p5 & SQLITE_JUMPIFNULL)!=0 );
if( (flags1&flags3&MEM_Null)!=0
&& (flags3&MEM_Cleared)==0
){
res = 0; /* Operands are equal */
}else{
res = ((flags3 & MEM_Null) ? -1 : +1); /* Operands are not equal */
}
}else{
/* SQLITE_NULLEQ is clear and at least one operand is NULL,
** then the result is always NULL.
** The jump is taken if the SQLITE_JUMPIFNULL bit is set.
*/
if( pOp->p5 & SQLITE_STOREP2 ){
|
Changes to src/window.c.
1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 .... 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 .... 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 |
arith = OP_Subtract;
}
windowReadPeerValues(p, csr1, reg1);
windowReadPeerValues(p, csr2, reg2);
/* Check if the peer value for csr1 value is a text or blob by comparing
** it to the smallest possible string - ''. */
sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC);
addrGe = sqlite3VdbeAddOp3(v, OP_Ge, regString, 0, reg1);
if( op==OP_Le ){
sqlite3VdbeAddOp2(v, OP_IsNull, reg1, lbl);
}
if( op==OP_Ge ){
sqlite3VdbeAddOp2(v, OP_NotNull, reg1, sqlite3VdbeCurrentAddr(v)+2);
sqlite3VdbeAddOp2(v, OP_IsNull, reg2, lbl);
}
sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
sqlite3VdbeJumpHere(v, addrGe);
sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
sqlite3ReleaseTempReg(pParse, reg1);
sqlite3ReleaseTempReg(pParse, reg2);
}
static int windowCodeOp(
WindowCodeArg *p,
................................................................................
** are four options - they may never be deleted (eDelete==0), they may
** be deleted as soon as they are no longer part of the window frame
** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row
** has been returned to the caller (WINDOW_RETURN_ROW), or they may
** be deleted after they enter the frame (WINDOW_AGGSTEP). */
switch( pMWin->eStart ){
case TK_FOLLOWING: {
sqlite3 *db = pParse->db;
sqlite3_value *pVal = 0;
sqlite3ValueFromExpr(db, pMWin->pStart, db->enc,SQLITE_AFF_NUMERIC,&pVal);
if( pVal && sqlite3_value_int(pVal)>0 ){
s.eDelete = WINDOW_RETURN_ROW;
}
sqlite3ValueFree(pVal);
break;
}
case TK_UNBOUNDED:
if( windowCacheFrame(pMWin)==0 ){
if( pMWin->eEnd==TK_PRECEDING ){
s.eDelete = WINDOW_AGGSTEP;
}else{
................................................................................
}
}
break;
default:
s.eDelete = WINDOW_AGGINVERSE;
break;
}
/* Allocate registers for the array of values from the sub-query, the
** samve values in record form, and the rowid used to insert said record
** into the ephemeral table. */
regNew = pParse->nMem+1;
pParse->nMem += nInput;
regRecord = ++pParse->nMem;
|
| > < < < < < < < < < > > | | | | | | | > > > |
1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 .... 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 .... 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 |
arith = OP_Subtract;
}
windowReadPeerValues(p, csr1, reg1);
windowReadPeerValues(p, csr2, reg2);
/* Check if the peer value for csr1 value is a text or blob by comparing
** it to the smallest possible string - ''. If it is, jump over the
** OP_Add or OP_Subtract operation and proceed directly to the comparison. */
sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC);
addrGe = sqlite3VdbeAddOp3(v, OP_Ge, regString, 0, reg1);
sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
sqlite3VdbeJumpHere(v, addrGe);
sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
sqlite3ReleaseTempReg(pParse, reg1);
sqlite3ReleaseTempReg(pParse, reg2);
}
static int windowCodeOp(
WindowCodeArg *p,
................................................................................
** are four options - they may never be deleted (eDelete==0), they may
** be deleted as soon as they are no longer part of the window frame
** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row
** has been returned to the caller (WINDOW_RETURN_ROW), or they may
** be deleted after they enter the frame (WINDOW_AGGSTEP). */
switch( pMWin->eStart ){
case TK_FOLLOWING: {
if( pMWin->eType!=TK_RANGE ){
sqlite3 *db = pParse->db;
sqlite3_value *pVal = 0;
sqlite3ValueFromExpr(db,pMWin->pStart,db->enc,SQLITE_AFF_NUMERIC,&pVal);
if( pVal && sqlite3_value_int(pVal)>0 ){
s.eDelete = WINDOW_RETURN_ROW;
}
sqlite3ValueFree(pVal);
}
break;
}
case TK_UNBOUNDED:
if( windowCacheFrame(pMWin)==0 ){
if( pMWin->eEnd==TK_PRECEDING ){
s.eDelete = WINDOW_AGGSTEP;
}else{
................................................................................
}
}
break;
default:
s.eDelete = WINDOW_AGGINVERSE;
break;
}
s.eDelete = 0;
/* Allocate registers for the array of values from the sub-query, the
** samve values in record form, and the rowid used to insert said record
** into the ephemeral table. */
regNew = pParse->nMem+1;
pParse->nMem += nInput;
regRecord = ++pParse->nMem;
|
Changes to test/pg_common.tcl.
66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
set ret
}
proc execsql_test {tn sql} {
set res [execsql $sql]
set sql [string map {string_agg group_concat} $sql]
puts $::fd "do_execsql_test $tn {"
puts $::fd " [string trim $sql]"
puts $::fd "} {$res}"
puts $::fd ""
}
proc errorsql_test {tn sql} {
|
> > |
66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
set ret
}
proc execsql_test {tn sql} {
set res [execsql $sql]
set sql [string map {string_agg group_concat} $sql]
set sql [string map [list {NULLS FIRST} {}] $sql]
set sql [string map [list {NULLS LAST} {}] $sql]
puts $::fd "do_execsql_test $tn {"
puts $::fd " [string trim $sql]"
puts $::fd "} {$res}"
puts $::fd ""
}
proc errorsql_test {tn sql} {
|
Changes to test/window8.tcl.
167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
execsql_test 4.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
}
execsql_test 4.1 {
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
}
finish_test
|
| > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 |
execsql_test 4.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
}
execsql_test 4.1.1 {
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
}
execsql_test 4.1.2 {
SELECT sum(b) OVER (
ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
}
execsql_test 4.2.1 {
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
}
execsql_test 4.2.2 {
SELECT sum(b) OVER (
ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
}
execsql_test 4.3.1 {
SELECT sum(b) OVER (
ORDER BY a NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
}
execsql_test 4.4.1 {
SELECT sum(b) OVER (
ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
}
execsql_test 4.4.2 {
SELECT sum(b) OVER (
ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
}
finish_test
|
Changes to test/window8.test.
2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 |
do_execsql_test 4.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
} {}
do_execsql_test 4.1 {
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
} {6 6 6 9 9}
finish_test
|
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 |
do_execsql_test 4.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
} {}
do_execsql_test 4.1.1 {
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
} {6 6 6 9 9}
do_execsql_test 4.1.2 {
SELECT sum(b) OVER (
ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
} {6 6 6 9 9}
do_execsql_test 4.2.1 {
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 ;
} {{} {} 6 6 6}
do_execsql_test 4.2.2 {
SELECT sum(b) OVER (
ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 ;
} {{} {} 6 6 6}
do_execsql_test 4.3.1 {
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 ;
} {6 6 6 15 15}
do_execsql_test 4.4.1 {
SELECT sum(b) OVER (
ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 ;
} {3 6 9 9 12}
do_execsql_test 4.4.2 {
SELECT sum(b) OVER (
ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 ;
} {5 6 8 9 10}
finish_test
|