Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add extra tests for the optimization on this branch. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | having-where-optimization |
| Files: | files | file ages | folders |
| SHA3-256: |
4921cd9520080f9baff70e548f64a56e |
| User & Date: | dan 2017-05-01 14:09:32 |
Context
|
2017-05-01
| ||
| 19:53 | Remove an unnecessary branch. (check-in: a3317959 user: drh tags: having-where-optimization) | |
| 14:09 | Add extra tests for the optimization on this branch. (check-in: 4921cd95 user: dan tags: having-where-optimization) | |
|
2017-04-29
| ||
| 20:53 | Automatically transfer terms from the HAVING clause to the WHERE clause of an aggregate query in cases where the result of evaluating the term depends only one one or more of the GROUP BY expressions (and on no other inputs). (check-in: 5375a3ce user: dan tags: having-where-optimization) | |
Changes
Changes to test/having.test.
13 14 15 16 17 18 19 20 21 22 23 24 25 26 .. 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 .. 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix having
do_execsql_test 1.0 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
INSERT INTO t1 VALUES(1, 3);
INSERT INTO t1 VALUES(2, 4);
INSERT INTO t1 VALUES(1, 5);
INSERT INTO t1 VALUES(2, 6);
................................................................................
1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" {2 12}
2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2 AND sum(b)>10" {2 12}
3 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>12" {}
} {
do_execsql_test 1.$tn $sql $res
}
proc compare_vdbe {sql1 sql2} {
set r1 [list]
set r2 [list]
db eval "explain $sql1" { lappend r1 $opcode $p1 $p2 $p3}
db eval "explain $sql2" { lappend r2 $opcode $p1 $p2 $p3}
return [expr {$r1==$r2}]
}
proc do_compare_vdbe_test {tn sql1 sql2 res} {
uplevel [list do_test $tn [list compare_vdbe $sql1 $sql2] $res]
}
do_compare_vdbe_test 2.1 {
SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2
} {
SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a
} 1
do_compare_vdbe_test 2.2 {
SELECT a, sum(b) FROM t1 GROUP BY a+1 HAVING a=2
} {
SELECT a, sum(b) FROM t1 GROUP BY a+1 HAVING a=2
} 1
foreach {tn sql1 sql2} {
1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a"
2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5"
................................................................................
} {
SELECT x,y FROM (
SELECT a AS x, sum(b) AS y FROM t1
WHERE x BETWEEN 8888 AND 9999
GROUP BY a
)
}
} {
do_compare_vdbe_test 3.$tn $sql1 $sql2 1
}
foreach {tn sql1 sql2} {
1 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE nocase HAVING a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE nocase"
} {
do_compare_vdbe_test 4.$tn $sql1 $sql2 0
}
finish_test
|
> > > > > | | < > | | < < < < < < < | > > > > > > > > > > | > > > > > > > | > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 .. 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 .. 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix having
do_execsql_test 1.0 {
CREATE TABLE t2(c, d);
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
INSERT INTO t1 VALUES(1, 3);
INSERT INTO t1 VALUES(2, 4);
INSERT INTO t1 VALUES(1, 5);
INSERT INTO t1 VALUES(2, 6);
................................................................................
1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" {2 12}
2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2 AND sum(b)>10" {2 12}
3 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>12" {}
} {
do_execsql_test 1.$tn $sql $res
}
# Run an EXPLAIN command for both SQL statements. Return true if
# the outputs are identical, or false otherwise.
#
proc compare_vdbe {sql1 sql2} {
set r1 [list]
set r2 [list]
db eval "explain $sql1" { lappend r1 $opcode $p1 $p2 $p3 $p4 $p5}
db eval "explain $sql2" { lappend r2 $opcode $p1 $p2 $p3 $p4 $p5}
return [expr {$r1==$r2}]
}
proc do_compare_vdbe_test {tn sql1 sql2 res} {
uplevel [list do_test $tn [list compare_vdbe $sql1 $sql2] $res]
}
#-------------------------------------------------------------------------
# Test that various statements that are eligible for the optimization
# produce the same VDBE code as optimizing by hand does.
#
foreach {tn sql1 sql2} {
1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a"
2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5"
................................................................................
} {
SELECT x,y FROM (
SELECT a AS x, sum(b) AS y FROM t1
WHERE x BETWEEN 8888 AND 9999
GROUP BY a
)
}
5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 0"
"SELECT a, sum(b) FROM t1 WHERE 0 GROUP BY a COLLATE binary"
6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d"
"SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d"
7 {
SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d
HAVING b=d COLLATE nocase
} {
SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase
GROUP BY b, d
}
8 "SELECT a, sum(b) FROM t1 GROUP BY a||b HAVING substr(a||b, 1, 1)='a'"
"SELECT a, sum(b) FROM t1 WHERE substr(a||b, 1, 1)='a' GROUP BY a||b"
} {
do_compare_vdbe_test 2.$tn $sql1 $sql2 1
}
#-------------------------------------------------------------------------
# 1: Test that the optimization is only applied if the GROUP BY term
# uses BINARY collation.
#
# 2: Not applied if there is a non-deterministic function in the HAVING
# term.
#
foreach {tn sql1 sql2} {
1 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE nocase HAVING a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE nocase"
2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING randomblob(a)<X'88'"
"SELECT a, sum(b) FROM t1 WHERE randomblob(a)<X'88' GROUP BY a"
} {
do_compare_vdbe_test 3.$tn $sql1 $sql2 0
}
#-------------------------------------------------------------------------
# Test that non-deterministic functions disqualify a term from being
# moved from the HAVING to WHERE clause.
#
do_execsql_test 4.1 {
CREATE TABLE t3(a, b);
INSERT INTO t3 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 2);
INSERT INTO t3 VALUES(1, 3);
INSERT INTO t3 VALUES(2, 1);
INSERT INTO t3 VALUES(2, 2);
INSERT INTO t3 VALUES(2, 3);
}
proc nondeter {args} {
incr ::nondeter_ret
expr {$::nondeter_ret % 2}
}
db func nondeter nondeter
set ::nondeter_ret 0
do_execsql_test 4.2 {
SELECT a, sum(b) FROM t3 GROUP BY a HAVING nondeter(a)
} {1 6}
# If the term where moved, the query above would return the same
# result as the following. But it does not.
#
set ::nondeter_ret 0
do_execsql_test 4.3 {
SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a
} {1 4 2 2}
finish_test
|