test/minmax2.test

   1 # 2007 July 17
   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 SELECT statements that contain
  13 # aggregate min() and max() functions and which are handled as
  14 # as a special case.  This file makes sure that the min/max
  15 # optimization works right in the presence of descending
  16 # indices.  Ticket #2514.
  17 #
  18 # $Id: minmax2.test,v 1.2 2008/01/05 17:39:30 danielk1977 Exp $
  19
  20 set testdir [file dirname $argv0]
  21 source $testdir/tester.tcl
  22
  23 do_test minmax2-1.0 {
  24   execsql {
  25     PRAGMA legacy_file_format=0;
  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 minmax2-1.1 {
  54   set sqlite_search_count 0
  55   execsql {SELECT min(x) FROM t1}
  56 } {1}
  57 do_test minmax2-1.2 {
  58   set sqlite_search_count
  59 } {19}
  60 do_test minmax2-1.3 {
  61   set sqlite_search_count 0
  62   execsql {SELECT max(x) FROM t1}
  63 } {20}
  64 do_test minmax2-1.4 {
  65   set sqlite_search_count
  66 } {19}
  67 do_test minmax2-1.5 {
  68   execsql {CREATE INDEX t1i1 ON t1(x DESC)}
  69   set sqlite_search_count 0
  70   execsql {SELECT min(x) FROM t1}
  71 } {1}
  72 do_test minmax2-1.6 {
  73   set sqlite_search_count
  74 } {1}
  75 do_test minmax2-1.7 {
  76   set sqlite_search_count 0
  77   execsql {SELECT max(x) FROM t1}
  78 } {20}
  79 do_test minmax2-1.8 {
  80   set sqlite_search_count
  81 } {0}
  82 do_test minmax2-1.9 {
  83   set sqlite_search_count 0
  84   execsql {SELECT max(y) FROM t1}
  85 } {5}
  86 do_test minmax2-1.10 {
  87   set sqlite_search_count
  88 } {19}
  89
  90 do_test minmax2-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 minmax2-2.1 {
  99   set sqlite_search_count
 100 } {0}
 101 do_test minmax2-2.2 {
 102   set sqlite_search_count 0
 103   execsql {SELECT max(a) FROM t2}
 104 } {20}
 105 do_test minmax2-2.3 {
 106   set sqlite_search_count
 107 } {0}
 108
 109 do_test minmax2-3.0 {
 110   ifcapable subquery {
 111     execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
 112   } else {
 113     db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
 114     execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
 115   }
 116   set sqlite_search_count 0
 117   execsql {SELECT max(a) FROM t2}
 118 } {21}
 119 do_test minmax2-3.1 {
 120   set sqlite_search_count
 121 } {0}
 122 do_test minmax2-3.2 {
 123   ifcapable subquery {
 124     execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
 125   } else {
 126     db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
 127     execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
 128   }
 129   set sqlite_search_count 0
 130   ifcapable subquery {
 131     execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
 132   } else {
 133     execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
 134   }
 135 } {999}
 136 do_test minmax2-3.3 {
 137   set sqlite_search_count
 138 } {0}
 139
 140 ifcapable {compound && subquery} {
 141   do_test minmax2-4.1 {
 142     execsql {
 143       SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
 144         (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
 145     }
 146   } {1 20}
 147   do_test minmax2-4.2 {
 148     execsql {
 149       SELECT y, coalesce(sum(x),0) FROM
 150         (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
 151       GROUP BY y ORDER BY y;
 152     }
 153   } {1 1 2 5 3 22 4 92 5 90 6 0}
 154   do_test minmax2-4.3 {
 155     execsql {
 156       SELECT y, count(x), count(*) FROM
 157         (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
 158       GROUP BY y ORDER BY y;
 159     }
 160   } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
 161 } ;# ifcapable compound
 162
 163 # Make sure the min(x) and max(x) optimizations work on empty tables
 164 # including empty tables with indices. Ticket #296.
 165 #
 166 do_test minmax2-5.1 {
 167   execsql {
 168     CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
 169     SELECT coalesce(min(x),999) FROM t3;
 170   }
 171 } {999}
 172 do_test minmax2-5.2 {
 173   execsql {
 174     SELECT coalesce(min(rowid),999) FROM t3;
 175   }
 176 } {999}
 177 do_test minmax2-5.3 {
 178   execsql {
 179     SELECT coalesce(max(x),999) FROM t3;
 180   }
 181 } {999}
 182 do_test minmax2-5.4 {
 183   execsql {
 184     SELECT coalesce(max(rowid),999) FROM t3;
 185   }
 186 } {999}
 187 do_test minmax2-5.5 {
 188   execsql {
 189     SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
 190   }
 191 } {999}
 192
 193 # Make sure the min(x) and max(x) optimizations work when there
 194 # is a LIMIT clause.  Ticket #396.
 195 #
 196 do_test minmax2-6.1 {
 197   execsql {
 198     SELECT min(a) FROM t2 LIMIT 1
 199   }
 200 } {1}
 201 do_test minmax2-6.2 {
 202   execsql {
 203     SELECT max(a) FROM t2 LIMIT 3
 204   }
 205 } {22}
 206 do_test minmax2-6.3 {
 207   execsql {
 208     SELECT min(a) FROM t2 LIMIT 0,100
 209   }
 210 } {1}
 211 do_test minmax2-6.4 {
 212   execsql {
 213     SELECT max(a) FROM t2 LIMIT 1,100
 214   }
 215 } {}
 216 do_test minmax2-6.5 {
 217   execsql {
 218     SELECT min(x) FROM t3 LIMIT 1
 219   }
 220 } {{}}
 221 do_test minmax2-6.6 {
 222   execsql {
 223     SELECT max(x) FROM t3 LIMIT 0
 224   }
 225 } {}
 226 do_test minmax2-6.7 {
 227   execsql {
 228     SELECT max(a) FROM t2 LIMIT 0
 229   }
 230 } {}
 231
 232 # Make sure the max(x) and min(x) optimizations work for nested
 233 # queries.  Ticket #587.
 234 #
 235 do_test minmax2-7.1 {
 236   execsql {
 237     SELECT max(x) FROM t1;
 238   }
 239 } 20
 240 ifcapable subquery {
 241   do_test minmax2-7.2 {
 242     execsql {
 243       SELECT * FROM (SELECT max(x) FROM t1);
 244     }
 245   } 20
 246 }
 247 do_test minmax2-7.3 {
 248   execsql {
 249     SELECT min(x) FROM t1;
 250   }
 251 } 1
 252 ifcapable subquery {
 253   do_test minmax2-7.4 {
 254     execsql {
 255       SELECT * FROM (SELECT min(x) FROM t1);
 256     }
 257   } 1
 258 }
 259
 260 # Make sure min(x) and max(x) work correctly when the datatype is
 261 # TEXT instead of NUMERIC.  Ticket #623.
 262 #
 263 do_test minmax2-8.1 {
 264   execsql {
 265     CREATE TABLE t4(a TEXT);
 266     INSERT INTO t4 VALUES('1234');
 267     INSERT INTO t4 VALUES('234');
 268     INSERT INTO t4 VALUES('34');
 269     SELECT min(a), max(a) FROM t4;
 270   }
 271 } {1234 34}
 272 do_test minmax2-8.2 {
 273   execsql {
 274     CREATE TABLE t5(a INTEGER);
 275     INSERT INTO t5 VALUES('1234');
 276     INSERT INTO t5 VALUES('234');
 277     INSERT INTO t5 VALUES('34');
 278     SELECT min(a), max(a) FROM t5;
 279   }
 280 } {34 1234}
 281
 282 # Ticket #658:  Test the min()/max() optimization when the FROM clause
 283 # is a subquery.
 284 #
 285 ifcapable {compound && subquery} {
 286   do_test minmax2-9.1 {
 287     execsql {
 288       SELECT max(rowid) FROM (
 289         SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
 290       )
 291     }
 292   } {{}}
 293   do_test minmax2-9.2 {
 294     execsql {
 295       SELECT max(rowid) FROM (
 296         SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
 297       )
 298     }
 299   } {{}}
 300 } ;# ifcapable compound&&subquery
 301
 302 # If there is a NULL in an aggregate max() or min(), ignore it.  An
 303 # aggregate min() or max() will only return NULL if all values are NULL.
 304 #
 305 do_test minmax2-10.1 {
 306   execsql {
 307     CREATE TABLE t6(x);
 308     INSERT INTO t6 VALUES(1);
 309     INSERT INTO t6 VALUES(2);
 310     INSERT INTO t6 VALUES(NULL);
 311     SELECT coalesce(min(x),-1) FROM t6;
 312   }
 313 } {1}
 314 do_test minmax2-10.2 {
 315   execsql {
 316     SELECT max(x) FROM t6;
 317   }
 318 } {2}
 319 do_test minmax2-10.3 {
 320   execsql {
 321     CREATE INDEX i6 ON t6(x DESC);
 322     SELECT coalesce(min(x),-1) FROM t6;
 323   }
 324 } {1}
 325 do_test minmax2-10.4 {
 326   execsql {
 327     SELECT max(x) FROM t6;
 328   }
 329 } {2}
 330 do_test minmax2-10.5 {
 331   execsql {
 332     DELETE FROM t6 WHERE x NOT NULL;
 333     SELECT count(*) FROM t6;
 334   }
 335 } 1
 336 do_test minmax2-10.6 {
 337   execsql {
 338     SELECT count(x) FROM t6;
 339   }
 340 } 0
 341 ifcapable subquery {
 342   do_test minmax2-10.7 {
 343     execsql {
 344       SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
 345     }
 346   } {{} {}}
 347 }
 348 do_test minmax2-10.8 {
 349   execsql {
 350     SELECT min(x), max(x) FROM t6;
 351   }
 352 } {{} {}}
 353 do_test minmax2-10.9 {
 354   execsql {
 355     INSERT INTO t6 SELECT * FROM t6;
 356     INSERT INTO t6 SELECT * FROM t6;
 357     INSERT INTO t6 SELECT * FROM t6;
 358     INSERT INTO t6 SELECT * FROM t6;
 359     INSERT INTO t6 SELECT * FROM t6;
 360     INSERT INTO t6 SELECT * FROM t6;
 361     INSERT INTO t6 SELECT * FROM t6;
 362     INSERT INTO t6 SELECT * FROM t6;
 363     INSERT INTO t6 SELECT * FROM t6;
 364     INSERT INTO t6 SELECT * FROM t6;
 365     SELECT count(*) FROM t6;
 366   }
 367 } 1024
 368 do_test minmax2-10.10 {
 369   execsql {
 370     SELECT count(x) FROM t6;
 371   }
 372 } 0
 373 ifcapable subquery {
 374   do_test minmax2-10.11 {
 375     execsql {
 376       SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
 377     }
 378   } {{} {}}
 379 }
 380 do_test minmax2-10.12 {
 381   execsql {
 382     SELECT min(x), max(x) FROM t6;
 383   }
 384 } {{} {}}
 385
 386 # 2017-10-26.  Extend the min/max optimization to indexes on expressions
 387 #
 388 do_execsql_test minmax2-11.100 {
 389   CREATE TABLE t11(a,b,c);
 390   INSERT INTO t11(a,b,c) VALUES(1,10,5),(2,8,11),(3,1,4),(4,20,1),(5,16,4);
 391   CREATE INDEX t11bc ON t11(b+c);
 392   SELECT max(b+c) FROM t11;
 393 } {21}
 394 do_execsql_test minmax2-11.110 {
 395   SELECT a, max(b+c) FROM t11;
 396 } {4 21}
 397 do_test minmax2-11.111 {
 398   db eval {SELECT max(b+c) FROM t11}
 399   db status step
 400 } {0}
 401 do_test minmax2-11.112 {
 402   db eval {SELECT max(c+b) FROM t11}
 403   db status step
 404 } {4}
 405 do_execsql_test minmax2-11.120 {
 406   SELECT a, min(b+c) FROM t11;
 407 } {3 5}
 408 do_test minmax2-11.121 {
 409   db eval {SELECT min(b+c) FROM t11}
 410   db status step
 411 } {0}
 412 do_test minmax2-11.122 {
 413   db eval {SELECT min(c+b) FROM t11}
 414   db status step
 415 } {4}
 416 do_execsql_test minmax2-11.130 {
 417   INSERT INTO t11(a,b,c) VALUES(6,NULL,0),(7,0,NULL);
 418   SELECT a, min(b+c) FROM t11;
 419 } {3 5}
 420
 421 finish_test