SQLite

Check-in [e195948a68]
Login

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

Overview
Comment:Add tests to ensure that the window functions implementation is not generating code for unnecessary sorts.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: e195948a6876efe01b5cf2ed67bc9015a781fda39dca668099cb7edc1d331818
User & Date: dan 2019-03-19 17:45:31.515
Context
2019-03-19
19:19
Fix a problem with EXCLUDE clauses on window frames with no ORDER BY. (check-in: e025506379 user: dan tags: window-functions)
17:45
Add tests to ensure that the window functions implementation is not generating code for unnecessary sorts. (check-in: e195948a68 user: dan tags: window-functions)
16:49
Add missing VdbeCoverage() macros to new code in window.c. (check-in: 4f9b93e6cf user: dan tags: window-functions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/window1.test.
965
966
967
968
969
970
971

























































972
973
974
975
  do_catchsql_test 22.$tn.2 "
    WITH a(x, y) AS ( VALUES(1, 2) )
    SELECT sum(x) OVER (
      ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING
    ) FROM a
  " $res
}


























































finish_test









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




965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
  do_catchsql_test 22.$tn.2 "
    WITH a(x, y) AS ( VALUES(1, 2) )
    SELECT sum(x) OVER (
      ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING
    ) FROM a
  " $res
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 23.0 {
  CREATE TABLE t5(a, b, c);
  CREATE INDEX t5ab ON t5(a, b);
}

proc do_ordercount_test {tn sql nOrderBy} {
  set plan [execsql "EXPLAIN QUERY PLAN $sql"]
  uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy]
}

do_ordercount_test 23.1 {
  SELECT 
    sum(c) OVER (ORDER BY a, b),
    sum(c) OVER (PARTITION BY a ORDER BY b)
  FROM t5
} 0

do_ordercount_test 23.2 {
  SELECT 
    sum(c) OVER (ORDER BY b, a),
    sum(c) OVER (PARTITION BY b ORDER BY a)
  FROM t5
} 1

do_ordercount_test 23.3 {
  SELECT 
    sum(c) OVER (ORDER BY b, a),
    sum(c) OVER (ORDER BY c, b)
  FROM t5
} 2

do_ordercount_test 23.4 {
  SELECT 
    sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  FROM t5
} 1

do_ordercount_test 23.5 {
  SELECT 
    sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING)
  FROM t5
} 1

do_ordercount_test 23.6 {
  SELECT 
    sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING)
  FROM t5
} 3

finish_test


Changes to test/window8.tcl.
161
162
163
164
165
166
167

168
169
170
171
172
173
174

  7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
  8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
  9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
  10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
  11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
  12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }

} {
  execsql_test 3.$tn "
    SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
  "
}

==========







>







161
162
163
164
165
166
167
168
169
170
171
172
173
174
175

  7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
  8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
  9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
  10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
  11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
  12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
  13  { ORDER BY a RANGE 5.1 PRECEDING }
} {
  execsql_test 3.$tn "
    SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
  "
}

==========
Changes to test/window8.test.
3490
3491
3492
3493
3494
3495
3496




3497
3498
3499
3500
3501
3502
3503
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
} {30 {}   22 90   20 90   15 170   13 210   13 210   10 210   5 292}

do_execsql_test 3.12 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
} {30 232   22 112   20 112   15 30   13 30   13 30   10 10   5 {}}





#==========================================================================

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);







>
>
>
>







3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
} {30 {}   22 90   20 90   15 170   13 210   13 210   10 210   5 292}

do_execsql_test 3.12 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
} {30 232   22 112   20 112   15 30   13 30   13 30   10 10   5 {}}

do_execsql_test 3.13 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE 5.1 PRECEDING )
} {5 10   10 30   13 72   13 72   15 102   20 70   22 120   30 90}

#==========================================================================

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);