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