Annotation of embedaddon/sqlite3/test/minmax2.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to minmax2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test