Annotation of embedaddon/sqlite3/test/select5.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 aggregate functions and the
! 13: # GROUP BY and HAVING clauses of SELECT statements.
! 14: #
! 15: # $Id: select5.test,v 1.20 2008/08/21 14:15:59 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Build some test data
! 21: #
! 22: execsql {
! 23: CREATE TABLE t1(x int, y int);
! 24: BEGIN;
! 25: }
! 26: for {set i 1} {$i<32} {incr i} {
! 27: for {set j 0} {(1<<$j)<$i} {incr j} {}
! 28: execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
! 29: }
! 30: execsql {
! 31: COMMIT
! 32: }
! 33:
! 34: do_test select5-1.0 {
! 35: execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
! 36: } {5 6 7 8 9 10}
! 37:
! 38: # Sort by an aggregate function.
! 39: #
! 40: do_test select5-1.1 {
! 41: execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY y}
! 42: } {5 15 6 8 7 4 8 2 9 1 10 1}
! 43: do_test select5-1.2 {
! 44: execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY count(*), y}
! 45: } {9 1 10 1 8 2 7 4 6 8 5 15}
! 46: do_test select5-1.3 {
! 47: execsql {SELECT count(*), y FROM t1 GROUP BY y ORDER BY count(*), y}
! 48: } {1 9 1 10 2 8 4 7 8 6 15 5}
! 49:
! 50: # Some error messages associated with aggregates and GROUP BY
! 51: #
! 52: do_test select5-2.1.1 {
! 53: catchsql {
! 54: SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
! 55: }
! 56: } {1 {no such column: z}}
! 57: do_test select5-2.1.2 {
! 58: catchsql {
! 59: SELECT y, count(*) FROM t1 GROUP BY temp.t1.y ORDER BY y
! 60: }
! 61: } {1 {no such column: temp.t1.y}}
! 62: do_test select5-2.2 {
! 63: set v [catch {execsql {
! 64: SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
! 65: }} msg]
! 66: lappend v $msg
! 67: } {1 {no such function: z}}
! 68: do_test select5-2.3 {
! 69: set v [catch {execsql {
! 70: SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y
! 71: }} msg]
! 72: lappend v $msg
! 73: } {0 {8 2 9 1 10 1}}
! 74: do_test select5-2.4 {
! 75: set v [catch {execsql {
! 76: SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y
! 77: }} msg]
! 78: lappend v $msg
! 79: } {1 {no such function: z}}
! 80: do_test select5-2.5 {
! 81: set v [catch {execsql {
! 82: SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
! 83: }} msg]
! 84: lappend v $msg
! 85: } {1 {no such column: z}}
! 86:
! 87: # Get the Agg function to rehash in vdbe.c
! 88: #
! 89: do_test select5-3.1 {
! 90: execsql {
! 91: SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
! 92: }
! 93: } {1 1 5.0 2 1 5.0 3 1 5.0}
! 94:
! 95: # Run various aggregate functions when the count is zero.
! 96: #
! 97: do_test select5-4.1 {
! 98: execsql {
! 99: SELECT avg(x) FROM t1 WHERE x>100
! 100: }
! 101: } {{}}
! 102: do_test select5-4.2 {
! 103: execsql {
! 104: SELECT count(x) FROM t1 WHERE x>100
! 105: }
! 106: } {0}
! 107: do_test select5-4.3 {
! 108: execsql {
! 109: SELECT min(x) FROM t1 WHERE x>100
! 110: }
! 111: } {{}}
! 112: do_test select5-4.4 {
! 113: execsql {
! 114: SELECT max(x) FROM t1 WHERE x>100
! 115: }
! 116: } {{}}
! 117: do_test select5-4.5 {
! 118: execsql {
! 119: SELECT sum(x) FROM t1 WHERE x>100
! 120: }
! 121: } {{}}
! 122:
! 123: # Some tests for queries with a GROUP BY clause but no aggregate functions.
! 124: #
! 125: # Note: The query in test cases 5.1 through 5.5 are not legal SQL. So if the
! 126: # implementation changes in the future and it returns different results,
! 127: # this is not such a big deal.
! 128: #
! 129: do_test select5-5.1 {
! 130: execsql {
! 131: CREATE TABLE t2(a, b, c);
! 132: INSERT INTO t2 VALUES(1, 2, 3);
! 133: INSERT INTO t2 VALUES(1, 4, 5);
! 134: INSERT INTO t2 VALUES(6, 4, 7);
! 135: CREATE INDEX t2_idx ON t2(a);
! 136: }
! 137: } {}
! 138: do_test select5-5.2 {
! 139: execsql {
! 140: SELECT a FROM t2 GROUP BY a;
! 141: }
! 142: } {1 6}
! 143: do_test select5-5.3 {
! 144: execsql {
! 145: SELECT a FROM t2 WHERE a>2 GROUP BY a;
! 146: }
! 147: } {6}
! 148: do_test select5-5.4 {
! 149: execsql {
! 150: SELECT a, b FROM t2 GROUP BY a, b;
! 151: }
! 152: } {1 2 1 4 6 4}
! 153: do_test select5-5.5 {
! 154: execsql {
! 155: SELECT a, b FROM t2 GROUP BY a;
! 156: }
! 157: } {1 4 6 4}
! 158:
! 159: # Test rendering of columns for the GROUP BY clause.
! 160: #
! 161: do_test select5-5.11 {
! 162: execsql {
! 163: SELECT max(c), b*a, b, a FROM t2 GROUP BY b*a, b, a
! 164: }
! 165: } {3 2 2 1 5 4 4 1 7 24 4 6}
! 166:
! 167: # NULL compare equal to each other for the purposes of processing
! 168: # the GROUP BY clause.
! 169: #
! 170: do_test select5-6.1 {
! 171: execsql {
! 172: CREATE TABLE t3(x,y);
! 173: INSERT INTO t3 VALUES(1,NULL);
! 174: INSERT INTO t3 VALUES(2,NULL);
! 175: INSERT INTO t3 VALUES(3,4);
! 176: SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1
! 177: }
! 178: } {1 4 2 {}}
! 179: do_test select5-6.2 {
! 180: execsql {
! 181: CREATE TABLE t4(x,y,z);
! 182: INSERT INTO t4 VALUES(1,2,NULL);
! 183: INSERT INTO t4 VALUES(2,3,NULL);
! 184: INSERT INTO t4 VALUES(3,NULL,5);
! 185: INSERT INTO t4 VALUES(4,NULL,6);
! 186: INSERT INTO t4 VALUES(4,NULL,6);
! 187: INSERT INTO t4 VALUES(5,NULL,NULL);
! 188: INSERT INTO t4 VALUES(5,NULL,NULL);
! 189: INSERT INTO t4 VALUES(6,7,8);
! 190: SELECT max(x), count(x), y, z FROM t4 GROUP BY y, z ORDER BY 1
! 191: }
! 192: } {1 1 2 {} 2 1 3 {} 3 1 {} 5 4 2 {} 6 5 2 {} {} 6 1 7 8}
! 193:
! 194: do_test select5-7.2 {
! 195: execsql {
! 196: SELECT count(*), count(x) as cnt FROM t4 GROUP BY y ORDER BY cnt;
! 197: }
! 198: } {1 1 1 1 1 1 5 5}
! 199:
! 200: # See ticket #3324.
! 201: #
! 202: do_test select5-8.1 {
! 203: execsql {
! 204: CREATE TABLE t8a(a,b);
! 205: CREATE TABLE t8b(x);
! 206: INSERT INTO t8a VALUES('one', 1);
! 207: INSERT INTO t8a VALUES('one', 2);
! 208: INSERT INTO t8a VALUES('two', 3);
! 209: INSERT INTO t8a VALUES('one', NULL);
! 210: INSERT INTO t8b(rowid,x) VALUES(1,111);
! 211: INSERT INTO t8b(rowid,x) VALUES(2,222);
! 212: INSERT INTO t8b(rowid,x) VALUES(3,333);
! 213: SELECT a, count(b) FROM t8a, t8b WHERE b=t8b.rowid GROUP BY a ORDER BY a;
! 214: }
! 215: } {one 2 two 1}
! 216: do_test select5-8.2 {
! 217: execsql {
! 218: SELECT a, count(b) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
! 219: }
! 220: } {one 2 two 1}
! 221: do_test select5-8.3 {
! 222: execsql {
! 223: SELECT t8a.a, count(t8a.b) FROM t8a, t8b WHERE t8a.b=t8b.rowid
! 224: GROUP BY 1 ORDER BY 1;
! 225: }
! 226: } {one 2 two 1}
! 227: do_test select5-8.4 {
! 228: execsql {
! 229: SELECT a, count(*) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
! 230: }
! 231: } {one 2 two 1}
! 232: do_test select5-8.5 {
! 233: execsql {
! 234: SELECT a, count(b) FROM t8a, t8b WHERE b<x GROUP BY a ORDER BY a;
! 235: }
! 236: } {one 6 two 3}
! 237: do_test select5-8.6 {
! 238: execsql {
! 239: SELECT a, count(t8a.b) FROM t8a, t8b WHERE b=t8b.rowid
! 240: GROUP BY a ORDER BY 2;
! 241: }
! 242: } {two 1 one 2}
! 243: do_test select5-8.7 {
! 244: execsql {
! 245: SELECT a, count(b) FROM t8a, t8b GROUP BY a ORDER BY 2;
! 246: }
! 247: } {two 3 one 6}
! 248: do_test select5-8.8 {
! 249: execsql {
! 250: SELECT a, count(*) FROM t8a, t8b GROUP BY a ORDER BY 2;
! 251: }
! 252: } {two 3 one 9}
! 253:
! 254:
! 255:
! 256:
! 257: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select5.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