SQLite: Check-in [cb3e2be6]

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

Overview

Comment:	Fix handling of NULL, text and blob values in window queries that use "RANGE BETWEEN A FOLLOWING AND B FOLLOWING", or "B PRECEDING AND A PRECEDING", where A>B.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA3-256:	cb3e2be674316e1d39968eb6567f1fe1b72f9d89af49640a9e83f944979c4cf0
User & Date:	dan 2019-09-04 06:56:43

Context

2019-09-09
20:17		Ensure the columns of views and sub-selects in the FROM clause of a select are always assigned implicit collation sequences, just as table columns are. Fix for [a7debbe0]. (check-in: b9ec7220 user: dan tags: trunk)
19:49		Ensure the columns of views and sub-selects in the FROM clause of a select are always assigned implicit collation sequences, just as table columns are. Possible fix for [a7debbe0]. (Closed-Leaf check-in: 1863b7bf user: dan tags: tkt-a7debbe0.)
2019-09-07
18:20		Add the SQLITE_SUBTYPE flag, which can be passed to sqlite3_create_function() and similar to indicate to the core that a user function is likely to use sqlite3_result_subtype(). (check-in: 6aa438ce user: dan tags: window-functions-subtype-fix)
2019-09-04
06:56		Fix handling of NULL, text and blob values in window queries that use "RANGE BETWEEN A FOLLOWING AND B FOLLOWING", or "B PRECEDING AND A PRECEDING", where A>B. (check-in: cb3e2be6 user: dan tags: trunk)
2019-09-03
16:23		Updates to the default settings in Makefile.linux-gcc. (check-in: 3044cf69 user: drh tags: trunk)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

    windowCheckValue(pParse, regStart, 0 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
  }
  if( regEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckValue(pParse, regEnd, 1 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
  }

  if( pMWin->eStart==pMWin->eEnd && regStart ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
    VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound <expr> */
    VdbeCoverageNeverNullIf(v, op==OP_Le); /*   values previously checked */
    windowAggFinal(&s, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
    VdbeCoverageNeverTaken(v);

    windowCheckValue(pParse, regStart, 0 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
  }
  if( regEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckValue(pParse, regEnd, 1 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
  }

  if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && regStart ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
    VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound <expr> */
    VdbeCoverageNeverNullIf(v, op==OP_Le); /*   values previously checked */
    windowAggFinal(&s, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
    VdbeCoverageNeverTaken(v);

Changes to test/window8.tcl.

  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a INTEGER, b INTEGER);

  INSERT INTO t2 VALUES(1, 65);
  INSERT INTO t2 VALUES(2, NULL);
  INSERT INTO t2 VALUES(3, NULL);
  INSERT INTO t2 VALUES(4, NULL);


}







execsql_test 7.1 {
  SELECT sum(a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
  );

























}




























finish_test








>
>


>
>
>
>
>
>
|
|



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

  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a INTEGER, b INTEGER);

  INSERT INTO t2 VALUES(1, 65);
  INSERT INTO t2 VALUES(2, NULL);
  INSERT INTO t2 VALUES(3, NULL);
  INSERT INTO t2 VALUES(4, NULL);
  INSERT INTO t2 VALUES(5, 66);
  INSERT INTO t2 VALUES(6, 67);
}

foreach {tn f ex} {
  1 sum ""
  2 min ""
  3 sum "EXCLUDE CURRENT ROW"
  4 max "EXCLUDE CURRENT ROW"
} {
execsql_test 7.$tn.1 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
  );
"
execsql_test 7.$tn.2 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
"
execsql_test 7.$tn.3 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
"
execsql_test 7.$tn.4 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
"
execsql_test 7.$tn.5 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
"

execsql_test 7.$tn.6 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
  );
"
execsql_test 7.$tn.7 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
"
execsql_test 7.$tn.8 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
  );
"
execsql_test 7.$tn.9 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
"
}



finish_test

Changes to test/window8.test.

  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a INTEGER, b INTEGER);

  INSERT INTO t2 VALUES(1, 65);
  INSERT INTO t2 VALUES(2, NULL);
  INSERT INTO t2 VALUES(3, NULL);
  INSERT INTO t2 VALUES(4, NULL);


} {}

do_execsql_test 7.1 {
  SELECT sum(a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
  );
} {9   9   9   9}






















































































































































































































































finish_test








>
>


|
|



|

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

  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a INTEGER, b INTEGER);

  INSERT INTO t2 VALUES(1, 65);
  INSERT INTO t2 VALUES(2, NULL);
  INSERT INTO t2 VALUES(3, NULL);
  INSERT INTO t2 VALUES(4, NULL);
  INSERT INTO t2 VALUES(5, 66);
  INSERT INTO t2 VALUES(6, 67);
} {}

do_execsql_test 7.1.1 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
  );
} {9   9   9   9   9   9}

do_execsql_test 7.1.2 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   {}   9   9   9}

do_execsql_test 7.1.3 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {{}   {}   {}   9   9   9}

do_execsql_test 7.1.4 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.1.5 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.1.6 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   1   9   9   9}

do_execsql_test 7.1.7 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {{}   {}   {}   9   9   9}

do_execsql_test 7.1.8 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.1.9 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.2.1 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
  );
} {2   2   2   2   2   2}

do_execsql_test 7.2.2 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   {}   2   2   2}

do_execsql_test 7.2.3 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {{}   {}   {}   2   2   2}

do_execsql_test 7.2.4 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {2   2   2   {}   {}   {}}

do_execsql_test 7.2.5 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {2   2   2   {}   {}   {}}

do_execsql_test 7.2.6 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   1   2   2   2}

do_execsql_test 7.2.7 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {{}   {}   {}   2   2   2}

do_execsql_test 7.2.8 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
  );
} {2   2   2   {}   {}   {}}

do_execsql_test 7.2.9 {
  SELECT min (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {2   2   2   {}   {}   {}}

do_execsql_test 7.3.1 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
  );
} {9   9   9   9   9   9}

do_execsql_test 7.3.2 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   {}   9   9   9}

do_execsql_test 7.3.3 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {{}   {}   {}   9   9   9}

do_execsql_test 7.3.4 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.3.5 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.3.6 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   1   9   9   9}

do_execsql_test 7.3.7 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {{}   {}   {}   9   9   9}

do_execsql_test 7.3.8 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.3.9 {
  SELECT sum (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {9   9   9   {}   {}   {}}

do_execsql_test 7.4.1 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
  );
} {4   4   4   4   4   4}

do_execsql_test 7.4.2 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   {}   4   4   4}

do_execsql_test 7.4.3 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {{}   {}   {}   4   4   4}

do_execsql_test 7.4.4 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  );
} {4   4   4   {}   {}   {}}

do_execsql_test 7.4.5 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
  );
} {4   4   4   {}   {}   {}}

do_execsql_test 7.4.6 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
  );
} {{}   {}   1   4   4   4}

do_execsql_test 7.4.7 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {{}   {}   {}   4   4   4}

do_execsql_test 7.4.8 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
  );
} {4   4   4   {}   {}   {}}

do_execsql_test 7.4.9 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {4   4   4   {}   {}   {}}

finish_test