test/having.test

   1 # 2017 April 30
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # 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
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set testprefix having
  18
  19 do_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
  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" {}
  35 } {
  36   do_execsql_test 1.$tn $sql $res
  37 }
  38
  39 # Run an EXPLAIN command for both SQL statements. Return true if
  40 # the outputs are identical, or false otherwise.
  41 #
  42 proc 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
  50 proc 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 optimization
  56 # produce the same VDBE code as optimizing by hand does.
  57 #
  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"
  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   4 {
  69       SELECT x,y FROM (
  70         SELECT a AS x, sum(b) AS y FROM t1
  71         GROUP BY a
  72       ) WHERE x BETWEEN 8888 AND 9999
  73     } {
  74       SELECT x,y FROM (
  75         SELECT a AS x, sum(b) AS y FROM t1
  76         WHERE x BETWEEN 8888 AND 9999
  77         GROUP BY a
  78       )
  79     }
  80
  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"
  83
  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"
  86
  87   7 {
  88       SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d
  89       HAVING b=d COLLATE nocase
  90     } {
  91       SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase
  92       GROUP BY b, d
  93     }
  94
  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"
  97 } {
  98   do_compare_vdbe_test 2.$tn $sql1 $sql2 1
  99 }
 100
 101 #-------------------------------------------------------------------------
 102 # 1: Test that the optimization is only applied if the GROUP BY term
 103 #    uses BINARY collation.
 104 #
 105 # 2: Not applied if there is a non-deterministic function in the HAVING
 106 #    term.
 107 #
 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"
 111
 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"
 114 } {
 115   do_compare_vdbe_test 3.$tn $sql1 $sql2 0
 116 }
 117
 118
 119 #-------------------------------------------------------------------------
 120 # Test that non-deterministic functions disqualify a term from being
 121 # moved from the HAVING to WHERE clause.
 122 #
 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);
 131 }
 132
 133 proc nondeter {args} {
 134   incr ::nondeter_ret
 135   expr {$::nondeter_ret % 2}
 136 }
 137 db func nondeter nondeter
 138
 139 set ::nondeter_ret 0
 140 do_execsql_test 4.2 {
 141   SELECT a, sum(b) FROM t3 GROUP BY a HAVING nondeter(a)
 142 } {1 6}
 143
 144 # If the term where moved, the query above would return the same
 145 # result as the following. But it does not.
 146 #
 147 set ::nondeter_ret 0
 148 do_execsql_test 4.3 {
 149   SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a
 150 } {1 4 2 2}
 151
 152
 153 finish_test