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 org.hsqldb.Trace;
041:
042: import junit.framework.TestCase;
043:
044: /**
045: * Test cases for HSQL aggregates and HAVING clause.
046: *
047: * @author Tony Lai
048: */
049:
050: // fredt@users - modified to remove dependecy on DBUnit
051: public class TestGroupByHaving extends TestCase {
052:
053: //------------------------------------------------------------
054: // Class variables
055: //------------------------------------------------------------
056: private static final String databaseDriver = "org.hsqldb.jdbcDriver";
057: private static final String databaseURL = "jdbc:hsqldb:mem:.";
058: private static final String databaseUser = "sa";
059: private static final String databasePassword = "";
060:
061: //------------------------------------------------------------
062: // Instance variables
063: //------------------------------------------------------------
064: private Connection conn;
065: private Statement stmt;
066:
067: //------------------------------------------------------------
068: // Constructors
069: //------------------------------------------------------------
070:
071: /**
072: * Constructs a new SubselectTest.
073: */
074: public TestGroupByHaving(String s) {
075: super (s);
076: }
077:
078: //------------------------------------------------------------
079: // Class methods
080: //------------------------------------------------------------
081: protected static Connection getJDBCConnection() throws SQLException {
082: return DriverManager.getConnection(databaseURL, databaseUser,
083: databasePassword);
084: }
085:
086: protected void setUp() throws Exception {
087:
088: super .setUp();
089:
090: if (conn != null) {
091: return;
092: }
093:
094: Class.forName(databaseDriver);
095:
096: conn = getJDBCConnection();
097: stmt = conn.createStatement();
098:
099: // I decided not the use the "IF EXISTS" clause since it is not a
100: // SQL standard.
101: try {
102:
103: // stmt.execute("drop table employee");
104: stmt.execute("drop table employee if exists");
105: } catch (Exception x) {
106: }
107:
108: stmt.execute("create table employee(id int, "
109: + "firstname VARCHAR(50), " + "lastname VARCHAR(50), "
110: + "salary decimal(10, 2), " + "superior_id int, "
111: + "CONSTRAINT PK_employee PRIMARY KEY (id), "
112: + "CONSTRAINT FK_superior FOREIGN KEY (superior_id) "
113: + "REFERENCES employee(ID))");
114: addEmployee(1, "Mike", "Smith", 160000, -1);
115: addEmployee(2, "Mary", "Smith", 140000, -1);
116:
117: // Employee under Mike
118: addEmployee(10, "Joe", "Divis", 50000, 1);
119: addEmployee(11, "Peter", "Mason", 45000, 1);
120: addEmployee(12, "Steve", "Johnson", 40000, 1);
121: addEmployee(13, "Jim", "Hood", 35000, 1);
122:
123: // Employee under Mike
124: addEmployee(20, "Jennifer", "Divis", 60000, 2);
125: addEmployee(21, "Helen", "Mason", 50000, 2);
126: addEmployee(22, "Daisy", "Johnson", 40000, 2);
127: addEmployee(23, "Barbara", "Hood", 30000, 2);
128: }
129:
130: protected void tearDown() throws Exception {
131:
132: super .tearDown();
133:
134: // I decided not the use the "IF EXISTS" clause since it is not a
135: // SQL standard.
136: try {
137:
138: // stmt.execute("drop table employee");
139: stmt.execute("drop table employee if exists");
140: } catch (Exception x) {
141: }
142:
143: if (stmt != null) {
144: stmt.close();
145:
146: stmt = null;
147: }
148:
149: if (conn != null) {
150: conn.close();
151:
152: conn = null;
153: }
154: }
155:
156: private void addEmployee(int id, String firstName, String lastName,
157: double salary, int super iorId) throws Exception {
158:
159: stmt.execute("insert into employee values(" + id + ", '"
160: + firstName + "', '" + lastName + "', " + salary + ", "
161: + (super iorId <= 0 ? "null" : ("" + super iorId)) + ")");
162: }
163:
164: /**
165: * Tests aggregated selection with a <b>GROUP_BY</b> clause. This is
166: * a normal use of the <b>GROUP_BY</b> clause. The first two employees
167: * do not have a superior, and must be grouped within the same group,
168: * according to <b>GROUP_BY</b> standard.
169: */
170: public void testAggregatedGroupBy() throws SQLException {
171:
172: String sql = "select avg(salary), max(id) from employee "
173: + "group by superior_id " + "order by superior_id "
174: + "";
175: Object[][] expected = new Object[][] {
176: { new Double(150000), new Integer(2) },
177: { new Double(42500), new Integer(13) },
178: { new Double(45000), new Integer(23) }, };
179:
180: compareResults(sql, expected, 0);
181: }
182:
183: /**
184: * Tests aggregated selection with a <b>GROUP_BY</b> clause and a
185: * <b>HAVING</b> clause.
186: * <p>
187: * This is a typical use of the <b>GROUP_BY</b> + <b>HAVING</b> clause.
188: * The first two employees are eliminated due to the <b>HAVING</b>
189: * condition.
190: * <p>
191: * This test uses aggregated function to eliminate first group.
192: */
193: public void testAggregatedGroupByHaving1() throws SQLException {
194:
195: String sql = "select avg(salary), max(id) from employee "
196: + "group by superior_id " + "having max(id) > 5 "
197: + "order by superior_id " + "";
198: Object[][] expected = new Object[][] {
199: { new Double(42500), new Integer(13) },
200: { new Double(45000), new Integer(23) }, };
201:
202: compareResults(sql, expected, 0);
203: }
204:
205: /**
206: * Tests aggregated selection with a <b>GROUP_BY</b> clause and a
207: * <b>HAVING</b> clause.
208: * <p>
209: * This is a typical use of the <b>GROUP_BY</b> + <b>HAVING</b> clause.
210: * The first two employees are eliminated due to the <b>HAVING</b>
211: * condition.
212: * <p>
213: * This test uses <b>GROUP_BY</b> column to eliminate first group.
214: */
215: public void testAggregatedGroupByHaving2() throws SQLException {
216:
217: String sql = "select avg(salary), max(id) from employee "
218: + "group by superior_id "
219: + "having superior_id is not null "
220: + "order by superior_id " + "";
221: Object[][] expected = new Object[][] {
222: { new Double(42500), new Integer(13) },
223: { new Double(45000), new Integer(23) }, };
224:
225: compareResults(sql, expected, 0);
226: }
227:
228: /**
229: * Tests an unusual usage of the <b>HAVING</b> clause, without a
230: * <b>GROUP BY</b> clause.
231: * <p>
232: * Only one row is returned by the aggregate selection without a
233: * <b>GROUP BY</b> clause. The <b>HAVING</b> clause is applied to the
234: * only returned row. In this case, the <b>HAVING</b> condition is
235: * satisfied.
236: */
237: public void testHavingWithoutGroupBy1() throws SQLException {
238:
239: String sql = "select avg(salary), max(id) from employee "
240: + "having avg(salary) > 1000 " + "";
241: Object[][] expected = new Object[][] { { new Double(65000),
242: new Integer(23) }, };
243:
244: compareResults(sql, expected, 0);
245: }
246:
247: /**
248: * Tests an unusual usage of the <b>HAVING</b> clause, without a
249: * <b>GROUP BY</b> clause.
250: * <p>
251: * Only one row is returned by the aggregate selection without a
252: * <b>GROUP BY</b> clause. The <b>HAVING</b> clause is applied to the
253: * only returned row. In this case, the <b>HAVING</b> condition is
254: * NOT satisfied.
255: */
256: public void testHavingWithoutGroupBy2() throws SQLException {
257:
258: String sql = "select avg(salary), max(id) from employee "
259: + "having avg(salary) > 1000000 " + "";
260: Object[][] expected = new Object[][] {};
261:
262: compareResults(sql, expected, 0);
263: }
264:
265: /**
266: * Tests an invalid <b>HAVING</b> clause that contains columns not in
267: * the <b>GROUP BY</b> clause. A SQLException should be thrown.
268: */
269: public void testInvalidHaving() throws SQLException {
270:
271: String sql = "select avg(salary), max(id) from employee "
272: + "group by lastname "
273: + "having (max(id) > 1) and (superior_id > 1) " + "";
274: Object[][] expected = new Object[][] {};
275:
276: compareResults(sql, expected,
277: -Trace.NOT_IN_AGGREGATE_OR_GROUP_BY);
278: }
279:
280: //------------------------------------------------------------
281: // Helper methods
282: //------------------------------------------------------------
283: private void compareResults(String sql, Object[][] rows,
284: int errorCode) throws SQLException {
285:
286: ResultSet rs = null;
287:
288: try {
289: rs = stmt.executeQuery(sql);
290:
291: assertTrue("Statement <" + sql
292: + "> \nexpecting error code: " + errorCode,
293: (0 == errorCode));
294: } catch (SQLException sqlx) {
295: if (sqlx.getErrorCode() != errorCode) {
296: sqlx.printStackTrace();
297: }
298:
299: assertTrue("Statement <" + sql
300: + "> \nthrows wrong error code: "
301: + sqlx.getErrorCode() + " expecting error code: "
302: + errorCode, (sqlx.getErrorCode() == errorCode));
303:
304: return;
305: }
306:
307: int rowCount = 0;
308: int colCount = rows.length > 0 ? rows[0].length : 0;
309:
310: while (rs.next()) {
311: assertTrue("Statement <" + sql
312: + "> \nreturned too many rows.",
313: (rowCount < rows.length));
314:
315: Object[] columns = rows[rowCount];
316:
317: for (int col = 1, i = 0; i < colCount; i++, col++) {
318: Object result = null;
319: Object expected = columns[i];
320:
321: if (expected == null) {
322: result = rs.getString(col);
323: result = rs.wasNull() ? null : result;
324: } else if (expected instanceof String) {
325: result = rs.getString(col);
326: } else if (expected instanceof Double) {
327: result = new Double(rs.getString(col));
328: } else if (expected instanceof Integer) {
329: result = new Integer(rs.getInt(col));
330: }
331:
332: assertEquals("Statement <" + sql
333: + "> \nreturned wrong value.", columns[i],
334: result);
335: }
336:
337: rowCount++;
338: }
339:
340: assertEquals("Statement <" + sql
341: + "> \nreturned wrong number of rows.", rows.length,
342: rowCount);
343: }
344:
345: //------------------------------------------------------------
346: // Main program
347: //------------------------------------------------------------
348: public static void main(String[] args) throws IOException {
349:
350: // junit.swingui.TestRunner.run(TestGroupByHaving.class);
351: junit.textui.TestRunner.run(TestGroupByHaving.class);
352: }
353: }
|