Annotation of embedaddon/sqlite3/test/select7.test, revision 1.1
1.1 ! misho 1: # The author disclaims copyright to this source code. In place of
! 2: # a legal notice, here is a blessing:
! 3: #
! 4: # May you do good and not evil.
! 5: # May you find forgiveness for yourself and forgive others.
! 6: # May you share freely, never taking more than you give.
! 7: #
! 8: #***********************************************************************
! 9: # This file implements regression tests for SQLite library. The
! 10: # focus of this file is testing compute SELECT statements and nested
! 11: # views.
! 12: #
! 13: # $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $
! 14:
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: ifcapable compound {
! 20:
! 21: # A 3-way INTERSECT. Ticket #875
! 22: ifcapable tempdb {
! 23: do_test select7-1.1 {
! 24: execsql {
! 25: create temp table t1(x);
! 26: insert into t1 values('amx');
! 27: insert into t1 values('anx');
! 28: insert into t1 values('amy');
! 29: insert into t1 values('bmy');
! 30: select * from t1 where x like 'a__'
! 31: intersect select * from t1 where x like '_m_'
! 32: intersect select * from t1 where x like '__x';
! 33: }
! 34: } {amx}
! 35: }
! 36:
! 37:
! 38: # Nested views do not handle * properly. Ticket #826.
! 39: #
! 40: ifcapable view {
! 41: do_test select7-2.1 {
! 42: execsql {
! 43: CREATE TABLE x(id integer primary key, a TEXT NULL);
! 44: INSERT INTO x (a) VALUES ('first');
! 45: CREATE TABLE tempx(id integer primary key, a TEXT NULL);
! 46: INSERT INTO tempx (a) VALUES ('t-first');
! 47: CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;
! 48: CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;
! 49: CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;
! 50: SELECT * FROM tv2;
! 51: }
! 52: } {1 1}
! 53: } ;# ifcapable view
! 54:
! 55: } ;# ifcapable compound
! 56:
! 57: # Do not allow GROUP BY without an aggregate. Ticket #1039.
! 58: #
! 59: # Change: force any query with a GROUP BY clause to be processed as
! 60: # an aggregate query, whether it contains aggregates or not.
! 61: #
! 62: ifcapable subquery {
! 63: # do_test select7-3.1 {
! 64: # catchsql {
! 65: # SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
! 66: # }
! 67: # } {1 {GROUP BY may only be used on aggregate queries}}
! 68: do_test select7-3.1 {
! 69: catchsql {
! 70: SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
! 71: }
! 72: } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]
! 73: }
! 74:
! 75: # Ticket #2018 - Make sure names are resolved correctly on all
! 76: # SELECT statements of a compound subquery.
! 77: #
! 78: ifcapable {subquery && compound} {
! 79: do_test select7-4.1 {
! 80: execsql {
! 81: CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x);
! 82: CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name);
! 83:
! 84: SELECT P.pk from PHOTO P WHERE NOT EXISTS (
! 85: SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk
! 86: EXCEPT
! 87: SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
! 88: );
! 89: }
! 90: } {}
! 91: do_test select7-4.2 {
! 92: execsql {
! 93: INSERT INTO photo VALUES(1,1);
! 94: INSERT INTO photo VALUES(2,2);
! 95: INSERT INTO photo VALUES(3,3);
! 96: INSERT INTO tag VALUES(11,1,'one');
! 97: INSERT INTO tag VALUES(12,1,'two');
! 98: INSERT INTO tag VALUES(21,1,'one-b');
! 99: SELECT P.pk from PHOTO P WHERE NOT EXISTS (
! 100: SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk
! 101: EXCEPT
! 102: SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
! 103: );
! 104: }
! 105: } {2 3}
! 106: }
! 107:
! 108: # ticket #2347
! 109: #
! 110: ifcapable {subquery && compound} {
! 111: do_test select7-5.1 {
! 112: catchsql {
! 113: CREATE TABLE t2(a,b);
! 114: SELECT 5 IN (SELECT a,b FROM t2);
! 115: }
! 116: } [list 1 \
! 117: {only a single result allowed for a SELECT that is part of an expression}]
! 118: do_test select7-5.2 {
! 119: catchsql {
! 120: SELECT 5 IN (SELECT * FROM t2);
! 121: }
! 122: } [list 1 \
! 123: {only a single result allowed for a SELECT that is part of an expression}]
! 124: do_test select7-5.3 {
! 125: catchsql {
! 126: SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2);
! 127: }
! 128: } [list 1 \
! 129: {only a single result allowed for a SELECT that is part of an expression}]
! 130: do_test select7-5.4 {
! 131: catchsql {
! 132: SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2);
! 133: }
! 134: } [list 1 \
! 135: {only a single result allowed for a SELECT that is part of an expression}]
! 136: }
! 137:
! 138: # Verify that an error occurs if you have too many terms on a
! 139: # compound select statement.
! 140: #
! 141: ifcapable compound {
! 142: if {$SQLITE_MAX_COMPOUND_SELECT>0} {
! 143: set sql {SELECT 0}
! 144: set result 0
! 145: for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} {
! 146: append sql " UNION ALL SELECT $i"
! 147: lappend result $i
! 148: }
! 149: do_test select7-6.1 {
! 150: catchsql $sql
! 151: } [list 0 $result]
! 152: append sql { UNION ALL SELECT 99999999}
! 153: do_test select7-6.2 {
! 154: catchsql $sql
! 155: } {1 {too many terms in compound SELECT}}
! 156: }
! 157: }
! 158:
! 159: # This block of tests verifies that bug aa92c76cd4 is fixed.
! 160: #
! 161: do_test select7-7.1 {
! 162: execsql {
! 163: CREATE TABLE t3(a REAL);
! 164: INSERT INTO t3 VALUES(44.0);
! 165: INSERT INTO t3 VALUES(56.0);
! 166: }
! 167: } {}
! 168: do_test select7-7.2 {
! 169: execsql {
! 170: pragma vdbe_trace = 0;
! 171: SELECT (CASE WHEN a=0 THEN 0 ELSE (a + 25) / 50 END) AS categ, count(*)
! 172: FROM t3 GROUP BY categ
! 173: }
! 174: } {1.38 1 1.62 1}
! 175: do_test select7-7.3 {
! 176: execsql {
! 177: CREATE TABLE t4(a REAL);
! 178: INSERT INTO t4 VALUES( 2.0 );
! 179: INSERT INTO t4 VALUES( 3.0 );
! 180: }
! 181: } {}
! 182: do_test select7-7.4 {
! 183: execsql {
! 184: SELECT (CASE WHEN a=0 THEN 'zero' ELSE a/2 END) AS t FROM t4 GROUP BY t;
! 185: }
! 186: } {1.0 1.5}
! 187: do_test select7-7.5 {
! 188: execsql { SELECT a=0, typeof(a) FROM t4 }
! 189: } {0 real 0 real}
! 190: do_test select7-7.6 {
! 191: execsql { SELECT a=0, typeof(a) FROM t4 GROUP BY a }
! 192: } {0 real 0 real}
! 193:
! 194: do_test select7-7.7 {
! 195: execsql {
! 196: CREATE TABLE t5(a TEXT, b INT);
! 197: INSERT INTO t5 VALUES(123, 456);
! 198: SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
! 199: }
! 200: } {text 123}
! 201:
! 202: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select7.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