third_party/sqlite/src/test/minmax.test

   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 SELECT statements that contain
  13 # aggregate min() and max() functions and which are handled as
  14 # as a special case.
  15 #
  16 # $Id: minmax.test,v 1.21 2008/07/08 18:05:26 drh Exp $
  17
  18 set testdir [file dirname $argv0]
  19 source $testdir/tester.tcl
  20
  21 do_test minmax-1.0 {
  22   execsql {
  23     BEGIN;
  24     CREATE TABLE t1(x, y);
  25     INSERT INTO t1 VALUES(1,1);
  26     INSERT INTO t1 VALUES(2,2);
  27     INSERT INTO t1 VALUES(3,2);
  28     INSERT INTO t1 VALUES(4,3);
  29     INSERT INTO t1 VALUES(5,3);
  30     INSERT INTO t1 VALUES(6,3);
  31     INSERT INTO t1 VALUES(7,3);
  32     INSERT INTO t1 VALUES(8,4);
  33     INSERT INTO t1 VALUES(9,4);
  34     INSERT INTO t1 VALUES(10,4);
  35     INSERT INTO t1 VALUES(11,4);
  36     INSERT INTO t1 VALUES(12,4);
  37     INSERT INTO t1 VALUES(13,4);
  38     INSERT INTO t1 VALUES(14,4);
  39     INSERT INTO t1 VALUES(15,4);
  40     INSERT INTO t1 VALUES(16,5);
  41     INSERT INTO t1 VALUES(17,5);
  42     INSERT INTO t1 VALUES(18,5);
  43     INSERT INTO t1 VALUES(19,5);
  44     INSERT INTO t1 VALUES(20,5);
  45     COMMIT;
  46     SELECT DISTINCT y FROM t1 ORDER BY y;
  47   }
  48 } {1 2 3 4 5}
  49
  50 do_test minmax-1.1 {
  51   set sqlite_search_count 0
  52   execsql {SELECT min(x) FROM t1}
  53 } {1}
  54 do_test minmax-1.2 {
  55   set sqlite_search_count
  56 } {19}
  57 do_test minmax-1.3 {
  58   set sqlite_search_count 0
  59   execsql {SELECT max(x) FROM t1}
  60 } {20}
  61 do_test minmax-1.4 {
  62   set sqlite_search_count
  63 } {19}
  64 do_test minmax-1.5 {
  65   execsql {CREATE INDEX t1i1 ON t1(x)}
  66   set sqlite_search_count 0
  67   execsql {SELECT min(x) FROM t1}
  68 } {1}
  69 do_test minmax-1.6 {
  70   set sqlite_search_count
  71 } {1}
  72 do_test minmax-1.7 {
  73   set sqlite_search_count 0
  74   execsql {SELECT max(x) FROM t1}
  75 } {20}
  76 do_test minmax-1.8 {
  77   set sqlite_search_count
  78 } {0}
  79 do_test minmax-1.9 {
  80   set sqlite_search_count 0
  81   execsql {SELECT max(y) FROM t1}
  82 } {5}
  83 do_test minmax-1.10 {
  84   set sqlite_search_count
  85 } {19}
  86
  87 do_test minmax-1.21 {
  88   execsql {SELECT min(x) FROM t1 WHERE x=5}
  89 } {5}
  90 do_test minmax-1.22 {
  91   execsql {SELECT min(x) FROM t1 WHERE x>=5}
  92 } {5}
  93 do_test minmax-1.23 {
  94   execsql {SELECT min(x) FROM t1 WHERE x>=4.5}
  95 } {5}
  96 do_test minmax-1.24 {
  97   execsql {SELECT min(x) FROM t1 WHERE x<4.5}
  98 } {1}
  99
 100 do_test minmax-2.0 {
 101   execsql {
 102     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
 103     INSERT INTO t2 SELECT * FROM t1;
 104   }
 105   set sqlite_search_count 0
 106   execsql {SELECT min(a) FROM t2}
 107 } {1}
 108 do_test minmax-2.1 {
 109   set sqlite_search_count
 110 } {0}
 111 do_test minmax-2.2 {
 112   set sqlite_search_count 0
 113   execsql {SELECT max(a) FROM t2}
 114 } {20}
 115 do_test minmax-2.3 {
 116   set sqlite_search_count
 117 } {0}
 118
 119 do_test minmax-3.0 {
 120   ifcapable subquery {
 121     execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
 122   } else {
 123     db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
 124     execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
 125   }
 126   set sqlite_search_count 0
 127   execsql {SELECT max(a) FROM t2}
 128 } {21}
 129 do_test minmax-3.1 {
 130   set sqlite_search_count
 131 } {0}
 132 do_test minmax-3.2 {
 133   ifcapable subquery {
 134     execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
 135   } else {
 136     db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
 137     execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
 138   }
 139   set sqlite_search_count 0
 140   ifcapable subquery {
 141     execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
 142   } else {
 143     execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
 144   }
 145 } {999}
 146 do_test minmax-3.3 {
 147   set sqlite_search_count
 148 } {0}
 149
 150 ifcapable {compound && subquery} {
 151   do_test minmax-4.1 {
 152     execsql {
 153       SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
 154         (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
 155     }
 156   } {1 20}
 157   do_test minmax-4.2 {
 158     execsql {
 159       SELECT y, coalesce(sum(x),0) FROM
 160         (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
 161       GROUP BY y ORDER BY y;
 162     }
 163   } {1 1 2 5 3 22 4 92 5 90 6 0}
 164   do_test minmax-4.3 {
 165     execsql {
 166       SELECT y, count(x), count(*) FROM
 167         (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
 168       GROUP BY y ORDER BY y;
 169     }
 170   } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
 171 } ;# ifcapable compound
 172
 173 # Make sure the min(x) and max(x) optimizations work on empty tables
 174 # including empty tables with indices. Ticket #296.
 175 #
 176 do_test minmax-5.1 {
 177   execsql {
 178     CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
 179     SELECT coalesce(min(x),999) FROM t3;
 180   }
 181 } {999}
 182 do_test minmax-5.2 {
 183   execsql {
 184     SELECT coalesce(min(rowid),999) FROM t3;
 185   }
 186 } {999}
 187 do_test minmax-5.3 {
 188   execsql {
 189     SELECT coalesce(max(x),999) FROM t3;
 190   }
 191 } {999}
 192 do_test minmax-5.4 {
 193   execsql {
 194     SELECT coalesce(max(rowid),999) FROM t3;
 195   }
 196 } {999}
 197 do_test minmax-5.5 {
 198   execsql {
 199     SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
 200   }
 201 } {999}
 202
 203 # Make sure the min(x) and max(x) optimizations work when there
 204 # is a LIMIT clause.  Ticket #396.
 205 #
 206 do_test minmax-6.1 {
 207   execsql {
 208     SELECT min(a) FROM t2 LIMIT 1
 209   }
 210 } {1}
 211 do_test minmax-6.2 {
 212   execsql {
 213     SELECT max(a) FROM t2 LIMIT 3
 214   }
 215 } {22}
 216 do_test minmax-6.3 {
 217   execsql {
 218     SELECT min(a) FROM t2 LIMIT 0,100
 219   }
 220 } {1}
 221 do_test minmax-6.4 {
 222   execsql {
 223     SELECT max(a) FROM t2 LIMIT 1,100
 224   }
 225 } {}
 226 do_test minmax-6.5 {
 227   execsql {
 228     SELECT min(x) FROM t3 LIMIT 1
 229   }
 230 } {{}}
 231 do_test minmax-6.6 {
 232   execsql {
 233     SELECT max(x) FROM t3 LIMIT 0
 234   }
 235 } {}
 236 do_test minmax-6.7 {
 237   execsql {
 238     SELECT max(a) FROM t2 LIMIT 0
 239   }
 240 } {}
 241
 242 # Make sure the max(x) and min(x) optimizations work for nested
 243 # queries.  Ticket #587.
 244 #
 245 do_test minmax-7.1 {
 246   execsql {
 247     SELECT max(x) FROM t1;
 248   }
 249 } 20
 250 ifcapable subquery {
 251   do_test minmax-7.2 {
 252     execsql {
 253       SELECT * FROM (SELECT max(x) FROM t1);
 254     }
 255   } 20
 256 }
 257 do_test minmax-7.3 {
 258   execsql {
 259     SELECT min(x) FROM t1;
 260   }
 261 } 1
 262 ifcapable subquery {
 263   do_test minmax-7.4 {
 264     execsql {
 265       SELECT * FROM (SELECT min(x) FROM t1);
 266     }
 267   } 1
 268 }
 269
 270 # Make sure min(x) and max(x) work correctly when the datatype is
 271 # TEXT instead of NUMERIC.  Ticket #623.
 272 #
 273 do_test minmax-8.1 {
 274   execsql {
 275     CREATE TABLE t4(a TEXT);
 276     INSERT INTO t4 VALUES('1234');
 277     INSERT INTO t4 VALUES('234');
 278     INSERT INTO t4 VALUES('34');
 279     SELECT min(a), max(a) FROM t4;
 280   }
 281 } {1234 34}
 282 do_test minmax-8.2 {
 283   execsql {
 284     CREATE TABLE t5(a INTEGER);
 285     INSERT INTO t5 VALUES('1234');
 286     INSERT INTO t5 VALUES('234');
 287     INSERT INTO t5 VALUES('34');
 288     SELECT min(a), max(a) FROM t5;
 289   }
 290 } {34 1234}
 291
 292 # Ticket #658:  Test the min()/max() optimization when the FROM clause
 293 # is a subquery.
 294 #
 295 ifcapable {compound && subquery} {
 296   do_test minmax-9.1 {
 297     execsql {
 298       SELECT max(rowid) FROM (
 299         SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
 300       )
 301     }
 302   } {1}
 303   do_test minmax-9.2 {
 304     execsql {
 305       SELECT max(rowid) FROM (
 306         SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
 307       )
 308     }
 309   } {{}}
 310 } ;# ifcapable compound&&subquery
 311
 312 # If there is a NULL in an aggregate max() or min(), ignore it.  An
 313 # aggregate min() or max() will only return NULL if all values are NULL.
 314 #
 315 do_test minmax-10.1 {
 316   execsql {
 317     CREATE TABLE t6(x);
 318     INSERT INTO t6 VALUES(1);
 319     INSERT INTO t6 VALUES(2);
 320     INSERT INTO t6 VALUES(NULL);
 321     SELECT coalesce(min(x),-1) FROM t6;
 322   }
 323 } {1}
 324 do_test minmax-10.2 {
 325   execsql {
 326     SELECT max(x) FROM t6;
 327   }
 328 } {2}
 329 do_test minmax-10.3 {
 330   execsql {
 331     CREATE INDEX i6 ON t6(x);
 332     SELECT coalesce(min(x),-1) FROM t6;
 333   }
 334 } {1}
 335 do_test minmax-10.4 {
 336   execsql {
 337     SELECT max(x) FROM t6;
 338   }
 339 } {2}
 340 do_test minmax-10.5 {
 341   execsql {
 342     DELETE FROM t6 WHERE x NOT NULL;
 343     SELECT count(*) FROM t6;
 344   }
 345 } 1
 346 do_test minmax-10.6 {
 347   execsql {
 348     SELECT count(x) FROM t6;
 349   }
 350 } 0
 351 ifcapable subquery {
 352   do_test minmax-10.7 {
 353     execsql {
 354       SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
 355     }
 356   } {{} {}}
 357 }
 358 do_test minmax-10.8 {
 359   execsql {
 360     SELECT min(x), max(x) FROM t6;
 361   }
 362 } {{} {}}
 363 do_test minmax-10.9 {
 364   execsql {
 365     INSERT INTO t6 SELECT * FROM t6;
 366     INSERT INTO t6 SELECT * FROM t6;
 367     INSERT INTO t6 SELECT * FROM t6;
 368     INSERT INTO t6 SELECT * FROM t6;
 369     INSERT INTO t6 SELECT * FROM t6;
 370     INSERT INTO t6 SELECT * FROM t6;
 371     INSERT INTO t6 SELECT * FROM t6;
 372     INSERT INTO t6 SELECT * FROM t6;
 373     INSERT INTO t6 SELECT * FROM t6;
 374     INSERT INTO t6 SELECT * FROM t6;
 375     SELECT count(*) FROM t6;
 376   }
 377 } 1024
 378 do_test minmax-10.10 {
 379   execsql {
 380     SELECT count(x) FROM t6;
 381   }
 382 } 0
 383 ifcapable subquery {
 384   do_test minmax-10.11 {
 385     execsql {
 386       SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
 387     }
 388   } {{} {}}
 389 }
 390 do_test minmax-10.12 {
 391   execsql {
 392     SELECT min(x), max(x) FROM t6;
 393   }
 394 } {{} {}}
 395
 396
 397 do_test minmax-11.1 {
 398   execsql {
 399     CREATE INDEX t1i2 ON t1(y,x);
 400     SELECT min(x) FROM t1 WHERE y=5;
 401   }
 402 } {16}
 403 do_test minmax-11.2 {
 404   execsql {
 405     SELECT max(x) FROM t1 WHERE y=5;
 406   }
 407 } {20}
 408 do_test minmax-11.3 {
 409   execsql {
 410     SELECT min(x) FROM t1 WHERE y=6;
 411   }
 412 } {{}}
 413 do_test minmax-11.4 {
 414   execsql {
 415     SELECT max(x) FROM t1 WHERE y=6;
 416   }
 417 } {{}}
 418 do_test minmax-11.5 {
 419   execsql {
 420     SELECT min(x) FROM t1 WHERE y=1;
 421   }
 422 } {1}
 423 do_test minmax-11.6 {
 424   execsql {
 425     SELECT max(x) FROM t1 WHERE y=1;
 426   }
 427 } {1}
 428 do_test minmax-11.7 {
 429   execsql {
 430     SELECT min(x) FROM t1 WHERE y=0;
 431   }
 432 } {{}}
 433 do_test minmax-11.8 {
 434   execsql {
 435     SELECT max(x) FROM t1 WHERE y=0;
 436   }
 437 } {{}}
 438 do_test minmax-11.9 {
 439   execsql {
 440     SELECT min(x) FROM t1 WHERE y=5 AND x>=17.5;
 441   }
 442 } {18}
 443 do_test minmax-11.10 {
 444   execsql {
 445     SELECT max(x) FROM t1 WHERE y=5 AND x>=17.5;
 446   }
 447 } {20}
 448
 449 do_test minmax-12.1 {
 450   execsql {
 451     CREATE TABLE t7(a,b,c);
 452     INSERT INTO t7 SELECT y, x, x*y FROM t1;
 453     INSERT INTO t7 SELECT y, x, x*y+1000 FROM t1;
 454     CREATE INDEX t7i1 ON t7(a,b,c);
 455     SELECT min(a) FROM t7;
 456   }
 457 } {1}
 458 do_test minmax-12.2 {
 459   execsql {
 460     SELECT max(a) FROM t7;
 461   }
 462 } {5}
 463 do_test minmax-12.3 {
 464   execsql {
 465     SELECT max(a) FROM t7 WHERE a=5;
 466   }
 467 } {5}
 468 do_test minmax-12.4 {
 469   execsql {
 470     SELECT min(b) FROM t7 WHERE a=5;
 471   }
 472 } {16}
 473 do_test minmax-12.5 {
 474   execsql {
 475     SELECT max(b) FROM t7 WHERE a=5;
 476   }
 477 } {20}
 478 do_test minmax-12.6 {
 479   execsql {
 480     SELECT min(b) FROM t7 WHERE a=4;
 481   }
 482 } {8}
 483 do_test minmax-12.7 {
 484   execsql {
 485     SELECT max(b) FROM t7 WHERE a=4;
 486   }
 487 } {15}
 488 do_test minmax-12.8 {
 489   execsql {
 490     SELECT min(c) FROM t7 WHERE a=4 AND b=10;
 491   }
 492 } {40}
 493 do_test minmax-12.9 {
 494   execsql {
 495     SELECT max(c) FROM t7 WHERE a=4 AND b=10;
 496   }
 497 } {1040}
 498 do_test minmax-12.10 {
 499   execsql {
 500     SELECT min(rowid) FROM t7;
 501   }
 502 } {1}
 503 do_test minmax-12.11 {
 504   execsql {
 505     SELECT max(rowid) FROM t7;
 506   }
 507 } {40}
 508 do_test minmax-12.12 {
 509   execsql {
 510     SELECT min(rowid) FROM t7 WHERE a=3;
 511   }
 512 } {4}
 513 do_test minmax-12.13 {
 514   execsql {
 515     SELECT max(rowid) FROM t7 WHERE a=3;
 516   }
 517 } {27}
 518 do_test minmax-12.14 {
 519   execsql {
 520     SELECT min(rowid) FROM t7 WHERE a=3 AND b=5;
 521   }
 522 } {5}
 523 do_test minmax-12.15 {
 524   execsql {
 525     SELECT max(rowid) FROM t7 WHERE a=3 AND b=5;
 526   }
 527 } {25}
 528 do_test minmax-12.16 {
 529   execsql {
 530     SELECT min(rowid) FROM t7 WHERE a=3 AND b=5 AND c=1015;
 531   }
 532 } {25}
 533 do_test minmax-12.17 {
 534   execsql {
 535     SELECT max(rowid) FROM t7 WHERE a=3 AND b=5 AND c=15;
 536   }
 537 } {5}
 538
 539
 540
 541
 542 finish_test