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   } {1}

   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 

   387 finish_test