| # 2017 April 30 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # |
| # Test the HAVING->WHERE optimization. |
| # |
| |
| 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); |
| } {} |
| |
| foreach {tn sql res} { |
| 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" |
| |
| 3 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING a=2" |
| "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE binary" |
| |
| 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 |
| } |
| |
| # The (4) test in the above set used to generate identical bytecode, but |
| # that is no longer the case. The byte code is equivalent, though. |
| # |
| do_execsql_test 2.4a { |
| SELECT x,y FROM ( |
| SELECT a AS x, sum(b) AS y FROM t1 |
| GROUP BY a |
| ) WHERE x BETWEEN 2 AND 9999 |
| } {2 12} |
| do_execsql_test 2.4b { |
| SELECT x,y FROM ( |
| SELECT a AS x, sum(b) AS y FROM t1 |
| WHERE x BETWEEN 2 AND 9999 |
| GROUP BY a |
| ) |
| } {2 12} |
| |
| |
| #------------------------------------------------------------------------- |
| # 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 |