usr/src/lib/libsqlite/test/minmax.test

   1
   2 #pragma ident   "%Z%%M% %I%     %E% SMI"
   3
   4 # 2001 September 15
   5 #
   6 # The author disclaims copyright to this source code.  In place of
   7 # a legal notice, here is a blessing:
   8 #
   9 #    May you do good and not evil.
  10 #    May you find forgiveness for yourself and forgive others.
  11 #    May you share freely, never taking more than you give.
  12 #
  13 #***********************************************************************
  14 # This file implements regression tests for SQLite library.  The
  15 # focus of this file is testing SELECT statements that contain
  16 # aggregate min() and max() functions and which are handled as
  17 # as a special case.
  18 #
  19 # $Id: minmax.test,v 1.9.2.2 2004/07/18 21:14:05 drh Exp $
  20
  21 set testdir [file dirname $argv0]
  22 source $testdir/tester.tcl
  23
  24 do_test minmax-1.0 {
  25   execsql {
  26     BEGIN;
  27     CREATE TABLE t1(x, y);
  28     INSERT INTO t1 VALUES(1,1);
  29     INSERT INTO t1 VALUES(2,2);
  30     INSERT INTO t1 VALUES(3,2);
  31     INSERT INTO t1 VALUES(4,3);
  32     INSERT INTO t1 VALUES(5,3);
  33     INSERT INTO t1 VALUES(6,3);
  34     INSERT INTO t1 VALUES(7,3);
  35     INSERT INTO t1 VALUES(8,4);
  36     INSERT INTO t1 VALUES(9,4);
  37     INSERT INTO t1 VALUES(10,4);
  38     INSERT INTO t1 VALUES(11,4);
  39     INSERT INTO t1 VALUES(12,4);
  40     INSERT INTO t1 VALUES(13,4);
  41     INSERT INTO t1 VALUES(14,4);
  42     INSERT INTO t1 VALUES(15,4);
  43     INSERT INTO t1 VALUES(16,5);
  44     INSERT INTO t1 VALUES(17,5);
  45     INSERT INTO t1 VALUES(18,5);
  46     INSERT INTO t1 VALUES(19,5);
  47     INSERT INTO t1 VALUES(20,5);
  48     COMMIT;
  49     SELECT DISTINCT y FROM t1 ORDER BY y;
  50   }
  51 } {1 2 3 4 5}
  52
  53 do_test minmax-1.1 {
  54   set sqlite_search_count 0
  55   execsql {SELECT min(x) FROM t1}
  56 } {1}
  57 do_test minmax-1.2 {
  58   set sqlite_search_count
  59 } {19}
  60 do_test minmax-1.3 {
  61   set sqlite_search_count 0
  62   execsql {SELECT max(x) FROM t1}
  63 } {20}
  64 do_test minmax-1.4 {
  65   set sqlite_search_count
  66 } {19}
  67 do_test minmax-1.5 {
  68   execsql {CREATE INDEX t1i1 ON t1(x)}
  69   set sqlite_search_count 0
  70   execsql {SELECT min(x) FROM t1}
  71 } {1}
  72 do_test minmax-1.6 {
  73   set sqlite_search_count
  74 } {2}
  75 do_test minmax-1.7 {
  76   set sqlite_search_count 0
  77   execsql {SELECT max(x) FROM t1}
  78 } {20}
  79 do_test minmax-1.8 {
  80   set sqlite_search_count
  81 } {1}
  82 do_test minmax-1.9 {
  83   set sqlite_search_count 0
  84   execsql {SELECT max(y) FROM t1}
  85 } {5}
  86 do_test minmax-1.10 {
  87   set sqlite_search_count
  88 } {19}
  89
  90 do_test minmax-2.0 {
  91   execsql {
  92     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
  93     INSERT INTO t2 SELECT * FROM t1;
  94   }
  95   set sqlite_search_count 0
  96   execsql {SELECT min(a) FROM t2}
  97 } {1}
  98 do_test minmax-2.1 {
  99   set sqlite_search_count
 100 } {0}
 101 do_test minmax-2.2 {
 102   set sqlite_search_count 0
 103   execsql {SELECT max(a) FROM t2}
 104 } {20}
 105 do_test minmax-2.3 {
 106   set sqlite_search_count
 107 } {0}
 108
 109 do_test minmax-3.0 {
 110   execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
 111   set sqlite_search_count 0
 112   execsql {SELECT max(a) FROM t2}
 113 } {21}
 114 do_test minmax-3.1 {
 115   set sqlite_search_count
 116 } {0}
 117 do_test minmax-3.2 {
 118   execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
 119   set sqlite_search_count 0
 120   execsql {
 121     SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2)
 122   }
 123 } {999}
 124 do_test minmax-3.3 {
 125   set sqlite_search_count
 126 } {0}
 127
 128 do_test minmax-4.1 {
 129   execsql {
 130     SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
 131       (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
 132   }
 133 } {1 20}
 134 do_test minmax-4.2 {
 135   execsql {
 136     SELECT y, sum(x) FROM
 137       (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
 138     GROUP BY y ORDER BY y;
 139   }
 140 } {1 1 2 5 3 22 4 92 5 90 6 0}
 141 do_test minmax-4.3 {
 142   execsql {
 143     SELECT y, count(x), count(*) FROM
 144       (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
 145     GROUP BY y ORDER BY y;
 146   }
 147 } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
 148
 149 # Make sure the min(x) and max(x) optimizations work on empty tables
 150 # including empty tables with indices. Ticket #296.
 151 #
 152 do_test minmax-5.1 {
 153   execsql {
 154     CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
 155     SELECT coalesce(min(x),999) FROM t3;
 156   }
 157 } {999}
 158 do_test minmax-5.2 {
 159   execsql {
 160     SELECT coalesce(min(rowid),999) FROM t3;
 161   }
 162 } {999}
 163 do_test minmax-5.3 {
 164   execsql {
 165     SELECT coalesce(max(x),999) FROM t3;
 166   }
 167 } {999}
 168 do_test minmax-5.4 {
 169   execsql {
 170     SELECT coalesce(max(rowid),999) FROM t3;
 171   }
 172 } {999}
 173 do_test minmax-5.5 {
 174   execsql {
 175     SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
 176   }
 177 } {999}
 178
 179 # Make sure the min(x) and max(x) optimizations work when there
 180 # is a LIMIT clause.  Ticket #396.
 181 #
 182 do_test minmax-6.1 {
 183   execsql {
 184     SELECT min(a) FROM t2 LIMIT 1
 185   }
 186 } {1}
 187 do_test minmax-6.2 {
 188   execsql {
 189     SELECT max(a) FROM t2 LIMIT 3
 190   }
 191 } {22}
 192 do_test minmax-6.3 {
 193   execsql {
 194     SELECT min(a) FROM t2 LIMIT 0,100
 195   }
 196 } {1}
 197 do_test minmax-6.4 {
 198   execsql {
 199     SELECT max(a) FROM t2 LIMIT 1,100
 200   }
 201 } {}
 202 do_test minmax-6.5 {
 203   execsql {
 204     SELECT min(x) FROM t3 LIMIT 1
 205   }
 206 } {{}}
 207 do_test minmax-6.6 {
 208   execsql {
 209     SELECT max(x) FROM t3 LIMIT 0
 210   }
 211 } {}
 212 do_test minmax-6.7 {
 213   execsql {
 214     SELECT max(a) FROM t2 LIMIT 0
 215   }
 216 } {}
 217
 218 # Make sure the max(x) and min(x) optimizations work for nested
 219 # queries.  Ticket #587.
 220 #
 221 do_test minmax-7.1 {
 222   execsql {
 223     SELECT max(x) FROM t1;
 224   }
 225 } 20
 226 do_test minmax-7.2 {
 227   execsql {
 228     SELECT * FROM (SELECT max(x) FROM t1);
 229   }
 230 } 20
 231 do_test minmax-7.3 {
 232   execsql {
 233     SELECT min(x) FROM t1;
 234   }
 235 } 1
 236 do_test minmax-7.4 {
 237   execsql {
 238     SELECT * FROM (SELECT min(x) FROM t1);
 239   }
 240 } 1
 241
 242 # Make sure min(x) and max(x) work correctly when the datatype is
 243 # TEXT instead of NUMERIC.  Ticket #623.
 244 #
 245 do_test minmax-8.1 {
 246   execsql {
 247     CREATE TABLE t4(a TEXT);
 248     INSERT INTO t4 VALUES('1234');
 249     INSERT INTO t4 VALUES('234');
 250     INSERT INTO t4 VALUES('34');
 251     SELECT min(a), max(a) FROM t4;
 252   }
 253 } {1234 34}
 254 do_test minmax-8.2 {
 255   execsql {
 256     CREATE TABLE t5(a INTEGER);
 257     INSERT INTO t5 VALUES('1234');
 258     INSERT INTO t5 VALUES('234');
 259     INSERT INTO t5 VALUES('34');
 260     SELECT min(a), max(a) FROM t5;
 261   }
 262 } {34 1234}
 263
 264 # Ticket #658:  Test the min()/max() optimization when the FROM clause
 265 # is a subquery.
 266 #
 267 do_test minmax-9.1 {
 268   execsql {
 269     SELECT max(rowid) FROM (
 270       SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
 271     )
 272   }
 273 } {1}
 274 do_test minmax-9.2 {
 275   execsql {
 276     SELECT max(rowid) FROM (
 277       SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
 278     )
 279   }
 280 } {{}}
 281
 282 # If there is a NULL in an aggregate max() or min(), ignore it.  An
 283 # aggregate min() or max() will only return NULL if all values are NULL.
 284 #
 285 do_test minmax-10.1 {
 286   execsql {
 287     CREATE TABLE t6(x);
 288     INSERT INTO t6 VALUES(1);
 289     INSERT INTO t6 VALUES(2);
 290     INSERT INTO t6 VALUES(NULL);
 291     SELECT coalesce(min(x),-1) FROM t6;
 292   }
 293 } {1}
 294 do_test minmax-10.2 {
 295   execsql {
 296     SELECT max(x) FROM t6;
 297   }
 298 } {2}
 299 do_test minmax-10.3 {
 300   execsql {
 301     CREATE INDEX i6 ON t6(x);
 302     SELECT coalesce(min(x),-1) FROM t6;
 303   }
 304 } {1}
 305 do_test minmax-10.4 {
 306   execsql {
 307     SELECT max(x) FROM t6;
 308   }
 309 } {2}
 310 do_test minmax-10.5 {
 311   execsql {
 312     DELETE FROM t6 WHERE x NOT NULL;
 313     SELECT count(*) FROM t6;
 314   }
 315 } 1
 316 do_test minmax-10.6 {
 317   execsql {
 318     SELECT count(x) FROM t6;
 319   }
 320 } 0
 321 do_test minmax-10.7 {
 322   execsql {
 323     SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
 324   }
 325 } {{} {}}
 326 do_test minmax-10.8 {
 327   execsql {
 328     SELECT min(x), max(x) FROM t6;
 329   }
 330 } {{} {}}
 331 do_test minmax-10.9 {
 332   execsql {
 333     INSERT INTO t6 SELECT * FROM t6;
 334     INSERT INTO t6 SELECT * FROM t6;
 335     INSERT INTO t6 SELECT * FROM t6;
 336     INSERT INTO t6 SELECT * FROM t6;
 337     INSERT INTO t6 SELECT * FROM t6;
 338     INSERT INTO t6 SELECT * FROM t6;
 339     INSERT INTO t6 SELECT * FROM t6;
 340     INSERT INTO t6 SELECT * FROM t6;
 341     INSERT INTO t6 SELECT * FROM t6;
 342     INSERT INTO t6 SELECT * FROM t6;
 343     SELECT count(*) FROM t6;
 344   }
 345 } 1024
 346 do_test minmax-10.10 {
 347   execsql {
 348     SELECT count(x) FROM t6;
 349   }
 350 } 0
 351 do_test minmax-10.11 {
 352   execsql {
 353     SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
 354   }
 355 } {{} {}}
 356 do_test minmax-10.12 {
 357   execsql {
 358     SELECT min(x), max(x) FROM t6;
 359   }
 360 } {{} {}}
 361
 362 finish_test