1 # 2001 September 15

     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 # This file implements regression tests for SQLite library.  The

    12 # focus of this file is testing aggregate functions and the

    13 # GROUP BY and HAVING clauses of SELECT statements.

    14 #

    15 # $Id: select3.test,v 1.23 2008/01/16 18:20:42 danielk1977 Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Build some test data

    21 #

    22 do_test select3-1.0 {

    23   execsql {

    24     CREATE TABLE t1(n int, log int);

    25     BEGIN;

    26   }

    27   for {set i 1} {$i<32} {incr i} {

    28     for {set j 0} {(1<<$j)<$i} {incr j} {}

    29     execsql "INSERT INTO t1 VALUES($i,$j)"

    30   }

    31   execsql {

    32     COMMIT

    33   }

    34   execsql {SELECT DISTINCT log FROM t1 ORDER BY log}

    35 } {0 1 2 3 4 5}

    36 

    37 # Basic aggregate functions.

    38 #

    39 do_test select3-1.1 {

    40   execsql {SELECT count(*) FROM t1}

    41 } {31}

    42 do_test select3-1.2 {

    43   execsql {

    44     SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log)

    45     FROM t1

    46   }

    47 } {1 0 31 5 496 124 16.0 4.0}

    48 do_test select3-1.3 {

    49   execsql {SELECT max(n)/avg(n), max(log)/avg(log) FROM t1}

    50 } {1.9375 1.25}

    51 

    52 # Try some basic GROUP BY clauses

    53 #

    54 do_test select3-2.1 {

    55   execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log}

    56 } {0 1 1 1 2 2 3 4 4 8 5 15}

    57 do_test select3-2.2 {

    58   execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}

    59 } {0 1 1 2 2 3 3 5 4 9 5 17}

    60 do_test select3-2.3.1 {

    61   execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}

    62 } {0 1.0 1 2.0 2 3.5 3 6.5 4 12.5 5 24.0}

    63 do_test select3-2.3.2 {

    64   execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}

    65 } {0 2.0 1 3.0 2 4.5 3 7.5 4 13.5 5 25.0}

    66 do_test select3-2.4 {

    67   execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}

    68 } {0 0.0 1 0.0 2 0.5 3 1.5 4 3.5 5 7.0}

    69 do_test select3-2.5 {

    70   execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}

    71 } {1 0.0 3 0.0 5 0.5 7 1.5 9 3.5 11 7.0}

    72 do_test select3-2.6 {

    73   execsql {

    74     SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x

    75   }

    76 } {1 1 3 1 5 2 7 4 9 8 11 15}

    77 do_test select3-2.7 {

    78   execsql {

    79     SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y, x

    80   }

    81 } {1 1 3 1 5 2 7 4 9 8 11 15}

    82 do_test select3-2.8 {

    83   execsql {

    84     SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)

    85   }

    86 } {11 15 9 8 7 4 5 2 3 1 1 1}

    87 #do_test select3-2.9 {

    88 #  catchsql {

    89 #    SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;

    90 #  }

    91 #} {1 {GROUP BY terms must not be non-integer constants}}

    92 do_test select3-2.10 {

    93   catchsql {

    94     SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;

    95   }

    96 } {1 {1st GROUP BY term out of range - should be between 1 and 2}}

    97 do_test select3-2.11 {

    98   catchsql {

    99     SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;

   100   }

   101 } {1 {1st GROUP BY term out of range - should be between 1 and 2}}

   102 do_test select3-2.12 {

   103   catchsql {

   104     SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;

   105   }

   106 } {0 {0 1 1 1 2 2 3 4 4 8 5 15}}

   107 

   108 # Cannot have an empty GROUP BY

   109 do_test select3-2.13 {

   110   catchsql {

   111     SELECT log, count(*) FROM t1 GROUP BY ORDER BY log;

   112   }

   113 } {1 {near "ORDER": syntax error}}

   114 do_test select3-2.14 {

   115   catchsql {

   116     SELECT log, count(*) FROM t1 GROUP BY;

   117   }

   118 } {1 {near ";": syntax error}}

   119 

   120 # Cannot have a HAVING without a GROUP BY

   121 #

   122 do_test select3-3.1 {

   123   set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]

   124   lappend v $msg

   125 } {1 {a GROUP BY clause is required before HAVING}}

   126 

   127 # Toss in some HAVING clauses

   128 #

   129 do_test select3-4.1 {

   130   execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log}

   131 } {4 8 5 15}

   132 do_test select3-4.2 {

   133   execsql {

   134     SELECT log, count(*) FROM t1 

   135     GROUP BY log 

   136     HAVING count(*)>=4 

   137     ORDER BY log

   138   }

   139 } {3 4 4 8 5 15}

   140 do_test select3-4.3 {

   141   execsql {

   142     SELECT log, count(*) FROM t1 

   143     GROUP BY log 

   144     HAVING count(*)>=4 

   145     ORDER BY max(n)+0

   146   }

   147 } {3 4 4 8 5 15}

   148 do_test select3-4.4 {

   149   execsql {

   150     SELECT log AS x, count(*) AS y FROM t1 

   151     GROUP BY x

   152     HAVING y>=4 

   153     ORDER BY max(n)+0

   154   }

   155 } {3 4 4 8 5 15}

   156 do_test select3-4.5 {

   157   execsql {

   158     SELECT log AS x FROM t1 

   159     GROUP BY x

   160     HAVING count(*)>=4 

   161     ORDER BY max(n)+0

   162   }

   163 } {3 4 5}

   164 

   165 do_test select3-5.1 {

   166   execsql {

   167     SELECT log, count(*), avg(n), max(n+log*2) FROM t1 

   168     GROUP BY log 

   169     ORDER BY max(n+log*2)+0, avg(n)+0

   170   }

   171 } {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}

   172 do_test select3-5.2 {

   173   execsql {

   174     SELECT log, count(*), avg(n), max(n+log*2) FROM t1 

   175     GROUP BY log 

   176     ORDER BY max(n+log*2)+0, min(log,avg(n))+0

   177   }

   178 } {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}

   179 

   180 # Test sorting of GROUP BY results in the presence of an index

   181 # on the GROUP BY column.

   182 #

   183 do_test select3-6.1 {

   184   execsql {

   185     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;

   186   }

   187 } {0 1 1 2 2 3 3 5 4 9 5 17}

   188 do_test select3-6.2 {

   189   execsql {

   190     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;

   191   }

   192 } {5 17 4 9 3 5 2 3 1 2 0 1}

   193 do_test select3-6.3 {

   194   execsql {

   195     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;

   196   }

   197 } {0 1 1 2 2 3 3 5 4 9 5 17}

   198 do_test select3-6.4 {

   199   execsql {

   200     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;

   201   }

   202 } {5 17 4 9 3 5 2 3 1 2 0 1}

   203 do_test select3-6.5 {

   204   execsql {

   205     CREATE INDEX i1 ON t1(log);

   206     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;

   207   }

   208 } {0 1 1 2 2 3 3 5 4 9 5 17}

   209 do_test select3-6.6 {

   210   execsql {

   211     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;

   212   }

   213 } {5 17 4 9 3 5 2 3 1 2 0 1}

   214 do_test select3-6.7 {

   215   execsql {

   216     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;

   217   }

   218 } {0 1 1 2 2 3 3 5 4 9 5 17}

   219 do_test select3-6.8 {

   220   execsql {

   221     SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;

   222   }

   223 } {5 17 4 9 3 5 2 3 1 2 0 1}

   224 

   225 # Sometimes an aggregate query can return no rows at all.

   226 #

   227 do_test select3-7.1 {

   228   execsql {

   229     CREATE TABLE t2(a,b);

   230     INSERT INTO t2 VALUES(1,2);

   231     SELECT a, sum(b) FROM t2 WHERE b=5 GROUP BY a;

   232   }

   233 } {}

   234 do_test select3-7.2 {

   235   execsql {

   236     SELECT a, sum(b) FROM t2 WHERE b=5;

   237   }

   238 } {{} {}}

   239 

   240 # If a table column is of type REAL but we are storing integer values

   241 # in it, the values are stored as integers to take up less space.  The

   242 # values are converted by to REAL as they are read out of the table.

   243 # Make sure the GROUP BY clause does this conversion correctly.

   244 # Ticket #2251.

   245 #

   246 do_test select3-8.1 {

   247   execsql {

   248     CREATE TABLE A (

   249       A1 DOUBLE,

   250       A2 VARCHAR COLLATE NOCASE,

   251       A3 DOUBLE

   252     );

   253     INSERT INTO A VALUES(39136,'ABC',1201900000);

   254     INSERT INTO A VALUES(39136,'ABC',1207000000);

   255     SELECT typeof(sum(a3)) FROM a;

   256   }

   257 } {real}

   258 do_test select3-8.2 {

   259   execsql {

   260     SELECT typeof(sum(a3)) FROM a GROUP BY a1;

   261   }

   262 } {real}

   263 

   264 finish_test