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