AryaWu/sqlite

1# 2017 April 302#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#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.9#10#***********************************************************************11#12# Test the HAVING->WHERE optimization.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix having18 19do_execsql_test 1.0 {20  CREATE TABLE t2(c, d);21 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);29} {}30 31foreach {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" {}35} {36  do_execsql_test 1.$tn $sql $res37}38 39# Run an EXPLAIN command for both SQL statements. Return true if 40# the outputs are identical, or false otherwise.41#42proc compare_vdbe {sql1 sql2} {43  set r1 [list]44  set r2 [list]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}]48}49 50proc do_compare_vdbe_test {tn sql1 sql2 res} {51  uplevel [list do_test $tn [list compare_vdbe $sql1 $sql2] $res]52}53 54#-------------------------------------------------------------------------55# Test that various statements that are eligible for the optimization56# produce the same VDBE code as optimizing by hand does.57#58foreach {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"61 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"64 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"67 68  5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 1"69    "SELECT a, sum(b) FROM t1 WHERE 1 GROUP BY a COLLATE binary"70 71  6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d"72    "SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d"73 74  7 {75      SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d 76      HAVING b=d COLLATE nocase77    } {78      SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase 79      GROUP BY b, d80    }81 82  8 "SELECT a, sum(b) FROM t1 GROUP BY a||b HAVING substr(a||b, 1, 1)='a'"83    "SELECT a, sum(b) FROM t1 WHERE substr(a||b, 1, 1)='a' GROUP BY a||b"84} {85  do_compare_vdbe_test 2.$tn $sql1 $sql2 186}87 88# The (4) test in the above set used to generate identical bytecode, but89# that is no longer the case.  The byte code is equivalent, though.90#91do_execsql_test 2.4a {92  SELECT x,y FROM (93    SELECT a AS x, sum(b) AS y FROM t1 94    GROUP BY a95  ) WHERE x BETWEEN 2 AND 999996} {2 12}97do_execsql_test 2.4b {98  SELECT x,y FROM (99    SELECT a AS x, sum(b) AS y FROM t1 100    WHERE x BETWEEN 2 AND 9999 101    GROUP BY a102  )103} {2 12}104 105 106#-------------------------------------------------------------------------107# 1: Test that the optimization is only applied if the GROUP BY term108#    uses BINARY collation.109#110# 2: Not applied if there is a non-deterministic function in the HAVING111#    term.112#113foreach {tn sql1 sql2} {114  1 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE nocase HAVING a=2"115    "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE nocase"116 117  2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING randomblob(a)<X'88'"118    "SELECT a, sum(b) FROM t1 WHERE randomblob(a)<X'88' GROUP BY a"119} {120  do_compare_vdbe_test 3.$tn $sql1 $sql2 0121}122 123 124#-------------------------------------------------------------------------125# Test that non-deterministic functions disqualify a term from being126# moved from the HAVING to WHERE clause.127#128do_execsql_test 4.1 {129  CREATE TABLE t3(a, b);130  INSERT INTO t3 VALUES(1, 1);131  INSERT INTO t3 VALUES(1, 2);132  INSERT INTO t3 VALUES(1, 3);133  INSERT INTO t3 VALUES(2, 1);134  INSERT INTO t3 VALUES(2, 2);135  INSERT INTO t3 VALUES(2, 3);136}137 138proc nondeter {args} {139  incr ::nondeter_ret140  expr {$::nondeter_ret % 2}141}142db func nondeter nondeter143 144set ::nondeter_ret 0145do_execsql_test 4.2 {146  SELECT a, sum(b) FROM t3 GROUP BY a HAVING nondeter(a)147} {1 6}148 149# If the term where moved, the query above would return the same150# result as the following. But it does not.151#152set ::nondeter_ret 0153do_execsql_test 4.3 {154  SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a155} {1 4 2 2}156 157#-------------------------------------------------------------------------158reset_db159do_execsql_test 5.0 {160  CREATE TABLE t1(a, b);161  CREATE TABLE t2(x, y);162  INSERT INTO t1 VALUES('a', 'b');163}164 165# The WHERE clause (a=2), uses an aggregate column from the outer query.166# If the HAVING term (0) is moved into the WHERE clause in this case,167# SQLite would at one point optimize (a=2 AND 0) to simply (0). Which168# is logically correct, but happened to cause problems in aggregate169# processing for the outer query. This test case verifies that those 170# problems are no longer present.171do_execsql_test 5.1 {172  SELECT min(b), (173    SELECT x FROM t2 WHERE a=2 GROUP BY y HAVING 0174  ) FROM t1;175} {b {}}176 177# From chromium178# https://bugs.chromium.org/p/chromium/issues/detail?id=1161869179#180do_execsql_test 5.2 {181  SELECT EXISTS (182    SELECT * FROM (183      SELECT * FROM (184        SELECT 1185      ) WHERE Col0 = 1   GROUP BY 1186    )   WHERE 0187  )188  FROM (SELECT 1 Col0)   GROUP BY 1189} {0}190 191finish_test192