3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
12 # Test the HAVING->WHERE optimization.
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
20 CREATE TABLE t2(c, d);
22 CREATE TABLE t1(a, b);
23 INSERT INTO t1 VALUES(1, 1);
24 INSERT INTO t1 VALUES(2, 2);
25 INSERT INTO t1 VALUES(1, 3);
26 INSERT INTO t1 VALUES(2, 4);
27 INSERT INTO t1 VALUES(1, 5);
28 INSERT INTO t1 VALUES(2, 6);
31 foreach {tn sql res} {
32 1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" {2 12}
33 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2 AND sum(b)>10" {2 12}
34 3 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>12" {}
36 do_execsql_test 1.$tn $sql $res
39 # Run an EXPLAIN command for both SQL statements. Return true if
40 # the outputs are identical, or false otherwise.
42 proc compare_vdbe {sql1 sql2} {
45 db eval "explain $sql1" { lappend r1 $opcode $p1 $p2 $p3 $p4 $p5}
46 db eval "explain $sql2" { lappend r2 $opcode $p1 $p2 $p3 $p4 $p5}
47 return [expr {$r1==$r2}]
50 proc do_compare_vdbe_test {tn sql1 sql2 res} {
51 uplevel [list do_test $tn [list compare_vdbe $sql1 $sql2] $res]
54 #-------------------------------------------------------------------------
55 # Test that various statements that are eligible for the optimization
56 # produce the same VDBE code as optimizing by hand does.
58 foreach {tn sql1 sql2} {
59 1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2"
60 "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a"
62 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2"
63 "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5"
65 3 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING a=2"
66 "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE binary"
70 SELECT a AS x, sum(b) AS y FROM t1
72 ) WHERE x BETWEEN 8888 AND 9999
75 SELECT a AS x, sum(b) AS y FROM t1
76 WHERE x BETWEEN 8888 AND 9999
81 5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 0"
82 "SELECT a, sum(b) FROM t1 WHERE 0 GROUP BY a COLLATE binary"
84 6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d"
85 "SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d"
88 SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d
89 HAVING b=d COLLATE nocase
91 SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase
95 8 "SELECT a, sum(b) FROM t1 GROUP BY a||b HAVING substr(a||b, 1, 1)='a'"
96 "SELECT a, sum(b) FROM t1 WHERE substr(a||b, 1, 1)='a' GROUP BY a||b"
98 do_compare_vdbe_test 2.$tn $sql1 $sql2 1
101 #-------------------------------------------------------------------------
102 # 1: Test that the optimization is only applied if the GROUP BY term
103 # uses BINARY collation.
105 # 2: Not applied if there is a non-deterministic function in the HAVING
108 foreach {tn sql1 sql2} {
109 1 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE nocase HAVING a=2"
110 "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE nocase"
112 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING randomblob(a)<X'88'"
113 "SELECT a, sum(b) FROM t1 WHERE randomblob(a)<X'88' GROUP BY a"
115 do_compare_vdbe_test 3.$tn $sql1 $sql2 0
119 #-------------------------------------------------------------------------
120 # Test that non-deterministic functions disqualify a term from being
121 # moved from the HAVING to WHERE clause.
123 do_execsql_test 4.1 {
124 CREATE TABLE t3(a, b);
125 INSERT INTO t3 VALUES(1, 1);
126 INSERT INTO t3 VALUES(1, 2);
127 INSERT INTO t3 VALUES(1, 3);
128 INSERT INTO t3 VALUES(2, 1);
129 INSERT INTO t3 VALUES(2, 2);
130 INSERT INTO t3 VALUES(2, 3);
133 proc nondeter {args} {
135 expr {$::nondeter_ret % 2}
137 db func nondeter nondeter
140 do_execsql_test 4.2 {
141 SELECT a, sum(b) FROM t3 GROUP BY a HAVING nondeter(a)
144 # If the term where moved, the query above would return the same
145 # result as the following. But it does not.
148 do_execsql_test 4.3 {
149 SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a