001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.io.IOException;
034: import java.sql.Connection;
035: import java.sql.DriverManager;
036: import java.sql.ResultSet;
037: import java.sql.SQLException;
038: import java.sql.Statement;
039:
040: import junit.framework.TestCase;
041:
042: /**
043: * Test cases for HSQL subselects.
044: *
045: * @author David Moles Apr 30, 2002
046: */
047:
048: // fredt@users - modified to remove dependecy on DBUnit
049: public class TestSubselect extends TestCase {
050:
051: //------------------------------------------------------------
052: // Class variables
053: //------------------------------------------------------------
054: private static final String databaseDriver = "org.hsqldb.jdbcDriver";
055: private static final String databaseURL = "jdbc:hsqldb:/hsql/test/subselect";
056: private static final String databaseUser = "sa";
057: private static final String databasePassword = "";
058:
059: //------------------------------------------------------------
060: // Instance variables
061: //------------------------------------------------------------
062: private Connection jdbcConnection;
063:
064: //------------------------------------------------------------
065: // Constructors
066: //------------------------------------------------------------
067:
068: /**
069: * Constructs a new SubselectTest.
070: */
071: public TestSubselect(String s) {
072: super (s);
073: }
074:
075: //------------------------------------------------------------
076: // Class methods
077: //------------------------------------------------------------
078: protected static Connection getJDBCConnection() throws SQLException {
079: return DriverManager.getConnection(databaseURL, databaseUser,
080: databasePassword);
081: }
082:
083: protected void setUp() throws Exception {
084:
085: TestSelf.deleteDatabase("/hsql/test/subselect");
086: Class.forName(databaseDriver);
087:
088: jdbcConnection = getJDBCConnection();
089:
090: createDataset();
091: }
092:
093: protected void tearDown() throws Exception {
094:
095: super .tearDown();
096: jdbcConnection.close();
097:
098: jdbcConnection = null;
099: }
100:
101: void createDataset() throws SQLException {
102:
103: Statement statement = jdbcConnection.createStatement();
104:
105: statement.execute("drop table colors if exists; "
106: + "drop table sizes if exists; "
107: + "drop table fruits if exists; "
108: + "drop table trees if exists; ");
109: statement
110: .execute("create table colors(id int, val char); "
111: + "insert into colors values(1,'red'); "
112: + "insert into colors values(2,'green'); "
113: + "insert into colors values(3,'orange'); "
114: + "insert into colors values(4,'indigo'); "
115: + "create table sizes(id int, val char); "
116: + "insert into sizes values(1,'small'); "
117: + "insert into sizes values(2,'medium'); "
118: + "insert into sizes values(3,'large'); "
119: + "insert into sizes values(4,'odd'); "
120: + "create table fruits(id int, name char, color_id int); "
121: + "insert into fruits values(1, 'golden delicious',2); "
122: + "insert into fruits values(2, 'macintosh',1); "
123: + "insert into fruits values(3, 'red delicious',1); "
124: + "insert into fruits values(4, 'granny smith',2); "
125: + "insert into fruits values(5, 'tangerine',4); "
126: + "create table trees(id int, name char, fruit_id int, size_id int); "
127: + "insert into trees values(1, 'small golden delicious tree',1,1); "
128: + "insert into trees values(2, 'large macintosh tree',2,3); "
129: + "insert into trees values(3, 'large red delicious tree',3,3); "
130: + "insert into trees values(4, 'small red delicious tree',3,1); "
131: + "insert into trees values(5, 'medium granny smith tree',4,2); ");
132: statement.close();
133: }
134:
135: //------------------------------------------------------------
136: // Helper methods
137: //------------------------------------------------------------
138: private static void compareResults(String sql, String[] expected,
139: Connection jdbcConnection) throws SQLException {
140:
141: Statement statement = jdbcConnection.createStatement();
142: ResultSet results = statement.executeQuery(sql);
143: int rowCount = 0;
144:
145: while (results.next()) {
146: assertTrue("Statement <" + sql
147: + "> returned too many rows.",
148: (rowCount < expected.length));
149: assertEquals("Statement <" + sql
150: + "> returned wrong value.", expected[rowCount],
151: results.getString(1));
152:
153: rowCount++;
154: }
155:
156: assertEquals("Statement <" + sql
157: + "> returned wrong number of rows.", expected.length,
158: rowCount);
159: }
160:
161: //------------------------------------------------------------
162: // Test methods
163: //------------------------------------------------------------
164:
165: /**
166: * This test is basically a sanity check of the data set.
167: */
168: public void testSimpleJoin() throws SQLException {
169:
170: String sql = "select trees.id, trees.name, sizes.val, fruits.name, colors.val"
171: + " from trees, sizes, fruits, colors"
172: + " where trees.size_id = sizes.id"
173: + " and trees.fruit_id = fruits.id"
174: + " and fruits.color_id = colors.id" + " order by 1";
175: int expectedRows = 5;
176: String[] expectedTrees = new String[] {
177: "small golden delicious tree", "large macintosh tree",
178: "large red delicious tree", "small red delicious tree",
179: "medium granny smith tree" };
180: String[] expectedSizes = new String[] { "small", "large",
181: "large", "small", "medium" };
182: String[] expectedFruits = new String[] { "golden delicious",
183: "macintosh", "red delicious", "red delicious",
184: "granny smith" };
185: String[] expectedColors = new String[] { "green", "red", "red",
186: "red", "green" };
187: Statement statement = jdbcConnection.createStatement();
188: ResultSet results = statement.executeQuery(sql);
189: String[] trees = new String[expectedRows];
190: String[] fruits = new String[expectedRows];
191: String[] sizes = new String[expectedRows];
192: String[] colors = new String[expectedRows];
193: int rowCount = 0;
194:
195: while (results.next()) {
196: assertTrue("Statement <" + sql
197: + "> returned too many rows.",
198: (rowCount <= expectedRows));
199: assertEquals("Statement <" + sql
200: + "> returned rows in wrong order.",
201: (1 + rowCount), results.getInt(1));
202: assertEquals("Statement <" + sql
203: + "> returned wrong value.",
204: expectedTrees[rowCount], results.getString(2));
205: assertEquals("Statement <" + sql
206: + "> returned wrong value.",
207: expectedSizes[rowCount], results.getString(3));
208: assertEquals("Statement <" + sql
209: + "> returned wrong value.",
210: expectedFruits[rowCount], results.getString(4));
211: assertEquals("Statement <" + sql
212: + "> returned wrong value.",
213: expectedColors[rowCount], results.getString(5));
214:
215: rowCount++;
216: }
217:
218: assertEquals("Statement <" + sql
219: + "> returned wrong number of rows.", expectedRows,
220: rowCount);
221: }
222:
223: /**
224: * Inner select with where clause in outer select having column with same name as where clause in inner select
225: */
226: public void testWhereClausesColliding() throws SQLException {
227:
228: String sql = "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";
229: String[] expected = new String[] { "golden delicious",
230: "macintosh" };
231:
232: compareResults(sql, expected, jdbcConnection);
233: }
234:
235: /**
236: * As above, with table aliases.
237: */
238: public void testWhereClausesCollidingWithAliases()
239: throws SQLException {
240:
241: String sql = "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";
242: String[] expected = new String[] { "golden delicious",
243: "macintosh" };
244:
245: compareResults(sql, expected, jdbcConnection);
246: }
247:
248: /**
249: * Inner select with two tables having columns with the same name, one of which is referred to in the
250: * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column,
251: * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all).
252: */
253: public void testHiddenCollision() throws SQLException {
254:
255: String sql = "select name from fruits where id in (select fruit_id from trees) order by name";
256: String[] expected = new String[] { "golden delicious",
257: "granny smith", "macintosh", "red delicious" };
258:
259: compareResults(sql, expected, jdbcConnection);
260: }
261:
262: /**
263: * As above, with table aliases.
264: */
265: public void testHiddenCollisionWithAliases() throws SQLException {
266:
267: String sql = "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";
268: String[] expected = new String[] { "golden delicious",
269: "granny smith", "macintosh", "red delicious" };
270:
271: compareResults(sql, expected, jdbcConnection);
272: }
273:
274: /**
275: * Inner select with where clause in outer select having column with same name as select clause in inner select
276: */
277: public void testWhereSelectColliding() throws SQLException {
278:
279: // Yes, this is a nonsensical query
280: String sql = "select val from colors where id in (select id from trees where fruit_id = 3) order by val";
281: String[] expected = new String[] { "indigo", "orange" };
282:
283: compareResults(sql, expected, jdbcConnection);
284: }
285:
286: /**
287: * As above, with aliases.
288: */
289: public void testWhereSelectCollidingWithAliases()
290: throws SQLException {
291:
292: // Yes, this is a nonsensical query
293: String sql = "select a.val from colors a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.val";
294: String[] expected = new String[] { "indigo", "orange" };
295:
296: compareResults(sql, expected, jdbcConnection);
297: }
298:
299: /**
300: * Inner select involving same table
301: */
302: public void testSameTable() throws SQLException {
303:
304: String sql = "select name from trees where id in (select id from trees where fruit_id = 3) order by name";
305: String[] expected = new String[] { "large red delicious tree",
306: "small red delicious tree" };
307:
308: compareResults(sql, expected, jdbcConnection);
309: }
310:
311: /**
312: * As above with aliases.
313: */
314: public void testSameTableWithAliases() throws SQLException {
315:
316: String sql = "select a.name from trees a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.name";
317: String[] expected = new String[] { "large red delicious tree",
318: "small red delicious tree" };
319:
320: compareResults(sql, expected, jdbcConnection);
321: }
322:
323: /**
324: * Inner select involving same table as one of two joined tables in outer select
325: */
326: public void testSameTableWithJoin() throws SQLException {
327:
328: String sql = "select sizes.val from trees, sizes where sizes.id = trees.size_id and trees.id in (select id from trees where fruit_id = 3) order by sizes.val";
329: String[] expected = new String[] { "large", "small" };
330:
331: compareResults(sql, expected, jdbcConnection);
332: }
333:
334: /**
335: * Tests two subselects, anded.
336: */
337: public void testAndedSubselects() throws SQLException {
338:
339: String sql = "select name from trees where size_id in (select id from sizes where val = 'large') and fruit_id in (select id from fruits where color_id = 1) order by name";
340: String[] expected = new String[] { "large macintosh tree",
341: "large red delicious tree" };
342:
343: compareResults(sql, expected, jdbcConnection);
344: }
345:
346: /**
347: * Test nested subselects.
348: */
349: public void testNestedSubselects() throws SQLException {
350:
351: String sql = "select name from trees where fruit_id in (select id from fruits where color_id in (select id from colors where val = 'red')) order by name";
352: String[] expected = new String[] { "large macintosh tree",
353: "large red delicious tree", "small red delicious tree" };
354:
355: compareResults(sql, expected, jdbcConnection);
356: }
357:
358: /**
359: * Inner select with "not in" in outer select where clause.
360: */
361: public void testNotIn() throws SQLException {
362:
363: String sql = "select name from fruits where id not in (select fruit_id from trees) order by name";
364: String[] expected = new String[] { "tangerine" };
365:
366: compareResults(sql, expected, jdbcConnection);
367: }
368:
369: /**
370: * Inner select with "not in" in outer select where clause and same table in inner select where clause.
371: */
372: public void testNotInSameTableAndColumn() throws SQLException {
373:
374: String sql = "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";
375: String[] expected = new String[] { "macintosh", "red delicious" };
376:
377: compareResults(sql, expected, jdbcConnection);
378: }
379:
380: /**
381: * Inner select reusing alias names from outer select, but using them for different tables
382: */
383: public void testAliasScope() throws SQLException {
384:
385: String sql = "select a.val, b.name from sizes a, trees b where a.id = b.size_id and b.id in (select a.id from trees a, fruits b where a.fruit_id = b.id and b.name='red delicious') order by a.val";
386: String[] expectedSizes = new String[] { "large", "small" };
387: String[] expectedTrees = new String[] {
388: "large red delicious tree", "small red delicious tree" };
389:
390: assertEquals(
391: "Programmer error: expected arrays should be of equal length.",
392: expectedSizes.length, expectedTrees.length);
393:
394: Statement statement = jdbcConnection.createStatement();
395: ResultSet results = statement.executeQuery(sql);
396: int rowCount = 0;
397:
398: while (results.next()) {
399: assertTrue("Statement <" + sql
400: + "> returned too many rows.",
401: (rowCount < expectedSizes.length));
402: assertEquals("Statement <" + sql
403: + "> returned wrong value.",
404: expectedSizes[rowCount], results.getString(1));
405: assertEquals("Statement <" + sql
406: + "> returned wrong value.",
407: expectedTrees[rowCount], results.getString(2));
408:
409: rowCount++;
410: }
411:
412: assertEquals("Statement <" + sql
413: + "> returned wrong number of rows.",
414: expectedSizes.length, rowCount);
415: }
416:
417: //------------------------------------------------------------
418: // Main program
419: //------------------------------------------------------------
420: public static void main(String[] args) throws IOException {
421: junit.swingui.TestRunner.run(TestSubselect.class);
422: }
423: }
|