modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/having.test (about)

     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    5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 0"
    69      "SELECT a, sum(b) FROM t1 WHERE 0 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 nocase
    77      } {
    78        SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase 
    79        GROUP BY b, d
    80      }
    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 1
    86  }
    87  
    88  # The (4) test in the above set used to generate identical bytecode, but
    89  # that is no longer the case.  The byte code is equivalent, though.
    90  #
    91  do_execsql_test 2.4a {
    92    SELECT x,y FROM (
    93      SELECT a AS x, sum(b) AS y FROM t1 
    94      GROUP BY a
    95    ) WHERE x BETWEEN 2 AND 9999
    96  } {2 12}
    97  do_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 a
   102    )
   103  } {2 12}
   104  
   105  
   106  #-------------------------------------------------------------------------
   107  # 1: Test that the optimization is only applied if the GROUP BY term
   108  #    uses BINARY collation.
   109  #
   110  # 2: Not applied if there is a non-deterministic function in the HAVING
   111  #    term.
   112  #
   113  foreach {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 0
   121  }
   122  
   123  
   124  #-------------------------------------------------------------------------
   125  # Test that non-deterministic functions disqualify a term from being
   126  # moved from the HAVING to WHERE clause.
   127  #
   128  do_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  
   138  proc nondeter {args} {
   139    incr ::nondeter_ret
   140    expr {$::nondeter_ret % 2}
   141  }
   142  db func nondeter nondeter
   143  
   144  set ::nondeter_ret 0
   145  do_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 same
   150  # result as the following. But it does not.
   151  #
   152  set ::nondeter_ret 0
   153  do_execsql_test 4.3 {
   154    SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a
   155  } {1 4 2 2}
   156  
   157  
   158  finish_test