Annotation of embedaddon/sqlite3/test/minmax.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to minmax.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