Source code: org/hsqldb/test/TestSubselect.java
1 /* Copyright (c) 2001-2002, The HSQL Development Group
2 * All rights reserved.
3 *
4 * Redistribution and use in source and binary forms, with or without
5 * modification, are permitted provided that the following conditions are met:
6 *
7 * Redistributions of source code must retain the above copyright notice, this
8 * list of conditions and the following disclaimer.
9 *
10 * Redistributions in binary form must reproduce the above copyright notice,
11 * this list of conditions and the following disclaimer in the documentation
12 * and/or other materials provided with the distribution.
13 *
14 * Neither the name of the HSQL Development Group nor the names of its
15 * contributors may be used to endorse or promote products derived from this
16 * software without specific prior written permission.
17 *
18 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29 */
30
31
32 package org.hsqldb.test;
33
34 import java.io.FileInputStream;
35 import java.io.IOException;
36 import java.io.InputStream;
37 import java.sql.Connection;
38 import java.sql.DriverManager;
39 import java.sql.ResultSet;
40 import java.sql.SQLException;
41 import java.sql.Statement;
42 import junit.framework.Test;
43 import junit.framework.TestCase;
44 import junit.framework.TestSuite;
45
46 /**
47 * Test cases for HSQL subselects.
48 *
49 * @author David Moles Apr 30, 2002
50 */
51
52 // fredt@users - modified to remove dependecy on DBUnit
53 public class TestSubselect extends TestCase {
54
55 //------------------------------------------------------------
56 // Class variables
57 //------------------------------------------------------------
58 private static final String databaseDriver = "org.hsqldb.jdbcDriver";
59 private static final String databaseURL = "jdbc:hsqldb:../test/subselect";
60 private static final String databaseUser = "sa";
61 private static final String databasePassword = "";
62 public static final String datasetPath = "../test/dataset-subselect.xml";
63
64 //------------------------------------------------------------
65 // Instance variables
66 //------------------------------------------------------------
67 private Connection jdbcConnection;
68
69 //------------------------------------------------------------
70 // Constructors
71 //------------------------------------------------------------
72
73 /**
74 * Constructs a new SubselectTest.
75 */
76 public TestSubselect(String s) {
77 super(s);
78 }
79
80 //------------------------------------------------------------
81 // Class methods
82 //------------------------------------------------------------
83 protected static Connection getJDBCConnection() throws SQLException {
84 return DriverManager.getConnection(databaseURL, databaseUser,
85 databasePassword);
86 }
87
88 protected void setUp() throws Exception {
89
90 super.setUp();
91 Class.forName(databaseDriver);
92
93 jdbcConnection = getJDBCConnection();
94 }
95
96 protected void tearDown() throws Exception {
97
98 super.tearDown();
99 jdbcConnection.close();
100
101 jdbcConnection = null;
102 }
103
104 //------------------------------------------------------------
105 // Helper methods
106 //------------------------------------------------------------
107 private static void compareResults(String sql, String[] expected,
108 Connection jdbcConnection)
109 throws SQLException {
110
111 Statement statement = jdbcConnection.createStatement();
112 ResultSet results = statement.executeQuery(sql);
113 int rowCount = 0;
114
115 while (results.next()) {
116 assertTrue("Statement <" + sql + "> returned too many rows.",
117 (rowCount < expected.length));
118 assertEquals("Statement <" + sql + "> returned wrong value.",
119 expected[rowCount], results.getString(1));
120
121 rowCount++;
122 }
123
124 assertEquals("Statement <" + sql
125 + "> returned wrong number of rows.", expected.length,
126 rowCount);
127 }
128
129 //------------------------------------------------------------
130 // Test methods
131 //------------------------------------------------------------
132
133 /**
134 * This test is basically a sanity check of the data set.
135 */
136 public void testSimpleJoin() throws SQLException {
137
138 String sql =
139 "select trees.id, trees.name, sizes.val, fruits.name, colors.val"
140 + " from trees, sizes, fruits, colors"
141 + " where trees.size_id = sizes.id"
142 + " and trees.fruit_id = fruits.id"
143 + " and fruits.color_id = colors.id" + " order by 1";
144 int expectedRows = 5;
145 String[] expectedTrees = new String[] {
146 "small golden delicious tree", "large macintosh tree",
147 "large red delicious tree", "small red delicious tree",
148 "medium granny smith tree"
149 };
150 String[] expectedSizes = new String[] {
151 "small", "large", "large", "small", "medium"
152 };
153 String[] expectedFruits = new String[] {
154 "golden delicious", "macintosh", "red delicious", "red delicious",
155 "granny smith"
156 };
157 String[] expectedColors = new String[] {
158 "green", "red", "red", "red", "green"
159 };
160 Statement statement = jdbcConnection.createStatement();
161 ResultSet results = statement.executeQuery(sql);
162 String[] trees = new String[expectedRows];
163 String[] fruits = new String[expectedRows];
164 String[] sizes = new String[expectedRows];
165 String[] colors = new String[expectedRows];
166 int rowCount = 0;
167
168 while (results.next()) {
169 assertTrue("Statement <" + sql + "> returned too many rows.",
170 (rowCount <= expectedRows));
171 assertEquals("Statement <" + sql
172 + "> returned rows in wrong order.", (1 + rowCount),
173 results.getInt(1));
174 assertEquals("Statement <" + sql + "> returned wrong value.",
175 expectedTrees[rowCount], results.getString(2));
176 assertEquals("Statement <" + sql + "> returned wrong value.",
177 expectedSizes[rowCount], results.getString(3));
178 assertEquals("Statement <" + sql + "> returned wrong value.",
179 expectedFruits[rowCount], results.getString(4));
180 assertEquals("Statement <" + sql + "> returned wrong value.",
181 expectedColors[rowCount], results.getString(5));
182
183 rowCount++;
184 }
185
186 assertEquals("Statement <" + sql
187 + "> returned wrong number of rows.", expectedRows,
188 rowCount);
189 }
190
191 /**
192 * Inner select with where clause in outer select having column with same name as where clause in inner select
193 */
194 public void testWhereClausesColliding() throws SQLException {
195
196 String sql =
197 "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";
198 String[] expected = new String[] {
199 "golden delicious", "macintosh"
200 };
201
202 compareResults(sql, expected, jdbcConnection);
203 }
204
205 /**
206 * As above, with table aliases.
207 */
208 public void testWhereClausesCollidingWithAliases() throws SQLException {
209
210 String sql =
211 "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";
212 String[] expected = new String[] {
213 "golden delicious", "macintosh"
214 };
215
216 compareResults(sql, expected, jdbcConnection);
217 }
218
219 /**
220 * Inner select with two tables having columns with the same name, one of which is referred to in the
221 * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column,
222 * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all).
223 */
224 public void testHiddenCollision() throws SQLException {
225
226 String sql =
227 "select name from fruits where id in (select fruit_id from trees) order by name";
228 String[] expected = new String[] {
229 "golden delicious", "granny smith", "macintosh", "red delicious"
230 };
231
232 compareResults(sql, expected, jdbcConnection);
233 }
234
235 /**
236 * As above, with table aliases.
237 */
238 public void testHiddenCollisionWithAliases() throws SQLException {
239
240 String sql =
241 "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";
242 String[] expected = new String[] {
243 "golden delicious", "granny smith", "macintosh", "red delicious"
244 };
245
246 compareResults(sql, expected, jdbcConnection);
247 }
248
249 /**
250 * Inner select with where clause in outer select having column with same name as select clause in inner select
251 */
252 public void testWhereSelectColliding() throws SQLException {
253
254 // Yes, this is a nonsensical query
255 String sql =
256 "select val from colors where id in (select id from trees where fruit_id = 3) order by val";
257 String[] expected = new String[] {
258 "indigo", "orange"
259 };
260
261 compareResults(sql, expected, jdbcConnection);
262 }
263
264 /**
265 * As above, with aliases.
266 */
267 public void testWhereSelectCollidingWithAliases() throws SQLException {
268
269 // Yes, this is a nonsensical query
270 String sql =
271 "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";
272 String[] expected = new String[] {
273 "indigo", "orange"
274 };
275
276 compareResults(sql, expected, jdbcConnection);
277 }
278
279 /**
280 * Inner select involving same table
281 */
282 public void testSameTable() throws SQLException {
283
284 String sql =
285 "select name from trees where id in (select id from trees where fruit_id = 3) order by name";
286 String[] expected = new String[] {
287 "large red delicious tree", "small red delicious tree"
288 };
289
290 compareResults(sql, expected, jdbcConnection);
291 }
292
293 /**
294 * As above with aliases.
295 */
296 public void testSameTableWithAliases() throws SQLException {
297
298 String sql =
299 "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";
300 String[] expected = new String[] {
301 "large red delicious tree", "small red delicious tree"
302 };
303
304 compareResults(sql, expected, jdbcConnection);
305 }
306
307 /**
308 * Inner select involving same table as one of two joined tables in outer select
309 */
310 public void testSameTableWithJoin() throws SQLException {
311
312 String sql =
313 "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";
314 String[] expected = new String[] {
315 "large", "small"
316 };
317
318 compareResults(sql, expected, jdbcConnection);
319 }
320
321 /**
322 * Tests two subselects, anded.
323 */
324 public void testAndedSubselects() throws SQLException {
325
326 String sql =
327 "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";
328 String[] expected = new String[] {
329 "large macintosh tree", "large red delicious tree"
330 };
331
332 compareResults(sql, expected, jdbcConnection);
333 }
334
335 /**
336 * Test nested subselects.
337 */
338 public void testNestedSubselects() throws SQLException {
339
340 String sql =
341 "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";
342 String[] expected = new String[] {
343 "large macintosh tree", "large red delicious tree",
344 "small red delicious tree"
345 };
346
347 compareResults(sql, expected, jdbcConnection);
348 }
349
350 /**
351 * Inner select with "not in" in outer select where clause.
352 */
353 public void testNotIn() throws SQLException {
354
355 String sql =
356 "select name from fruits where id not in (select fruit_id from trees) order by name";
357 String[] expected = new String[]{ "tangerine" };
358
359 compareResults(sql, expected, jdbcConnection);
360 }
361
362 /**
363 * Inner select with "not in" in outer select where clause and same table in inner select where clause.
364 */
365 public void testNotInSameTableAndColumn() throws SQLException {
366
367 String sql =
368 "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";
369 String[] expected = new String[] {
370 "macintosh", "red delicious"
371 };
372
373 compareResults(sql, expected, jdbcConnection);
374 }
375
376 /**
377 * Inner select reusing alias names from outer select, but using them for different tables
378 */
379 public void testAliasScope() throws SQLException {
380
381 String sql =
382 "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";
383 String expectedSizes[] = new String[] {
384 "large", "small"
385 };
386 String expectedTrees[] = new String[] {
387 "large red delicious tree", "small red delicious tree"
388 };
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 + "> returned too many rows.",
400 (rowCount < expectedSizes.length));
401 assertEquals("Statement <" + sql + "> returned wrong value.",
402 expectedSizes[rowCount], results.getString(1));
403 assertEquals("Statement <" + sql + "> returned wrong value.",
404 expectedTrees[rowCount], results.getString(2));
405
406 rowCount++;
407 }
408
409 assertEquals(
410 "Statement <" + sql + "> returned wrong number of rows.",
411 expectedSizes.length, rowCount);
412 }
413
414 //------------------------------------------------------------
415 // Main program
416 //------------------------------------------------------------
417 public static void main(String[] args) throws IOException {
418 junit.swingui.TestRunner.run(TestSubselect.class);
419 }
420 }