001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.GroupByExpressionTest
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.Connection;
025: import java.sql.PreparedStatement;
026: import java.sql.ResultSet;
027: import java.sql.Statement;
028:
029: import junit.extensions.TestSetup;
030: import junit.framework.Test;
031: import junit.framework.TestSuite;
032:
033: import org.apache.derbyTesting.junit.BaseJDBCTestCase;
034: import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
035:
036: public class GroupByExpressionTest extends BaseJDBCTestCase {
037:
038: private static String[][] TABLES = {
039: { "test",
040: "create table test (c1 int, c2 int, c3 int, c4 int)" },
041: { "coal",
042: "create table coal (vc1 varchar(2), vc2 varchar(2))" },
043: {
044: "alltypes",
045: "create table alltypes (i int, s smallint, l bigint, "
046: + "c char(10), v varchar(50), lvc long varchar, "
047: + " d double precision, r real, "
048: + " dt date, t time, ts timestamp, "
049: + " b char(2) for bit data, bv varchar(8) for bit data, "
050: + " lbv long varchar for bit data, dc decimal(5,2))" },
051: { "t1", "create table t1 (c1 varchar(30))" },
052: { "t2", "create table t2 (c1 varchar(10))" },
053: { "t3", "create table t3 (c1 int, c2 int)" } };
054:
055: private static String[][] FUNCTIONS = { {
056: "r",
057: "create function r() returns double external name "
058: + "'java.lang.Math.random' language java parameter style java" } };
059:
060: /**
061: * Basic test case. Checks functionality with simple arithmetic expressions
062: */
063: public void testSimpleExpressions() throws Exception {
064: verifyQueryResults("Q1",
065: "select c1,c2,sum(c3) from test group by c2,c1",
066: new int[][] { { 1, 10, 100 }, { 2, 10, 100 },
067: { 1, 11, 100 }, { 2, 11, 202 } });
068:
069: verifyQueryResults("Q2",
070: "select c1+c2, sum(c3) from test group by c1,c2",
071: new int[][] { { 11, 100 }, { 12, 100 }, { 12, 100 },
072: { 13, 202 } });
073: verifyQueryResults("Q3",
074: "select c1+c2, sum(c3) from test group by c1+c2",
075: new int[][] { { 11, 100 }, { 12, 200 }, { 13, 202 } });
076: verifyQueryResults("Q4",
077: "select (c1+c2)+1, sum(c3) from test group by c1+c2",
078: new int[][] { { 12, 100 }, { 13, 200 }, { 14, 202 } });
079: verifyQueryResults(
080: "Q5",
081: "select (c1+c2), sum(c3)+(c1+c2) from test group by c1+c2",
082: new int[][] { { 11, 111 }, { 12, 212 }, { 13, 215 } });
083: verifyQueryResults(
084: "Q6",
085: "select c2-c1, c1+c2, count(*) from test group by c1+c2, c2-c1",
086: new int[][] { { 9, 11, 1 }, { 8, 12, 1 },
087: { 10, 12, 1 }, { 9, 13, 2 } });
088: }
089:
090: public void testSubSelect() throws Exception {
091: /* query with a group by on a subselect */
092: verifyQueryResults(
093: "Q1",
094: "select a+1, sum(b) from (select c1+1 a , c2+1 b from test) t group by a",
095: new int[][] { { 3, 23 }, { 4, 35 } });
096:
097: verifyQueryResults(
098: "Q2",
099: "select a+1, sum(b) from (select c1+1 a , c2+1 b from test) t group by a+1",
100: new int[][] { { 3, 23 }, { 4, 35 } });
101:
102: verifyQueryResults(
103: "Q3",
104: "select b/2,sum(a) from "
105: + "(select c1+1 a, max(c2) b from test group by c1+1) t "
106: + "group by b/2", new int[][] { { 5, 5 } });
107: }
108:
109: public void testMiscExpressions() throws Exception {
110: // cast
111: verifyQueryResults("cast",
112: "select (cast (c1 as char(2))), count(*) from test "
113: + " group by (cast (c1 as char(2)))",
114: new Object[][] { { "1 ", new Integer(2) },
115: { "2 ", new Integer(3) } });
116:
117: // coalesce
118: verifyQueryResults("coalesce",
119: "select (coalesce(vc1,vc2)), count(*) from coal "
120: + " group by (coalesce(vc1,vc2))",
121: new Object[][] { { "1", new Integer(2) },
122: { "2", new Integer(1) } });
123: // concat
124: verifyQueryResults("concat",
125: "select c||v, count(*) from alltypes group by c||v",
126: new Object[][] {
127: { "duplicate noone is here", new Integer(1) },
128: { "duplicate this is duplicated",
129: new Integer(13) },
130: { "goodbye this is duplicated",
131: new Integer(1) } });
132: // conditional.
133: verifyQueryResults(
134: "cond",
135: "select (case when c1 = 1 then 2 else 1 end), sum(c2) from test "
136: + " group by (case when c1 = 1 then 2 else 1 end)",
137: new int[][] { { 1, 32 }, { 2, 21 } });
138:
139: // length
140: verifyQueryResults(
141: "length",
142: "select length(v), count(*) from alltypes group by length(v)",
143: new int[][] { { 13, 1 }, { 18, 14 } });
144:
145: // current time. ignore the value of current time.
146: // just make sure we can group by it and get the right results for
147: // the aggregate.
148: verifyQueryResults(
149: "current_time",
150: "select co from "
151: + "(select current_time ct, count(*) co from test t1, test t2, test t3 group by current_time) t",
152: new int[][] { { 125 } });
153: // concat + substr
154: verifyQueryResults(
155: "concat+substr",
156: "select substr(c||v, 1, 4), count(*) from alltypes group by substr(c||v, 1, 4)",
157: new Object[][] { { "dupl", new Integer(14) },
158: { "good", new Integer(1) } });
159:
160: // DERBY-2008
161: // substr (2-args)
162: verifyQueryResults("substr-Q1",
163: "select substr(c1, 3) from t1 group by substr(c1, 3)",
164: new String[][] { { "03-12-08" }, { "28-09-21" } });
165: // substr (3-args)
166: verifyQueryResults(
167: "substr-Q2",
168: "select substr(c1, 3, 4) from t1 group by substr(c1, 3, 4)",
169: new String[][] { { "03-1" }, { "28-0" } });
170:
171: // ltrim
172: verifyQueryResults("ltrim",
173: "select ltrim(c1) from t2 group by ltrim(c1)",
174: new String[][] { { "123 " }, { "abc " } });
175:
176: // rtrim
177: verifyQueryResults("rtrim",
178: "select rtrim(c1) from t2 group by rtrim(c1)",
179: new String[][] { { "123" }, { "abc" } });
180:
181: // locate (2-args)
182: verifyQueryResults(
183: "locate-Q1",
184: "select locate(c1, 'abc') from t2 group by locate(c1, 'abc')",
185: new int[][] { { 0 }, { 1 } });
186:
187: // locate (3-args)
188: verifyQueryResults(
189: "locate-Q2",
190: "select locate(c1, 'abc', 1) from t2 group by locate(c1, 'abc',1)",
191: new int[][] { { 0 }, { 1 } });
192:
193: // cast with NULL
194: verifyQueryResults(
195: "cast-Q2",
196: "select (cast (NULL as INTEGER)) from t2 group by (cast (NULL as INTEGER))",
197: new Object[][] { { null } });
198:
199: // DERBY-2014
200: // nullif
201: verifyQueryResults("nullif-Q1",
202: "select nullif(c1,c1) from t3 group by nullif(c1,c1)",
203: new Object[][] { { null } });
204:
205: verifyQueryResults("nullif-Q2",
206: "select nullif(c1,c2) from t3 group by nullif(c1,c2)",
207: new Object[][] { { new Integer(5) }, { null } });
208:
209: verifyQueryResults("nullif-Q3",
210: "select nullif(c1,10) from t3 group by nullif(c1,10)",
211: new Object[][] { { new Integer(1) },
212: { new Integer(2) }, { new Integer(3) },
213: { new Integer(5) }, { null } });
214:
215: verifyQueryResults("nullif-Q4",
216: "select nullif(1,c1) from t3 group by nullif(1,c1)",
217: new Object[][] { { new Integer(1) }, { null } });
218: }
219:
220: public void testExtractOperator() throws Exception {
221: verifyQueryResults(
222: "year",
223: "select year(dt), count(*) from alltypes group by year(dt)",
224: new int[][] { { 1992, 15 } });
225: verifyQueryResults(
226: "month",
227: "select month(dt), count(*) from alltypes group by month(dt)",
228: new int[][] { { 1, 5 }, { 2, 6 }, { 3, 4 } });
229: verifyQueryResults(
230: "day",
231: "select day(dt), count(*) from alltypes group by day(dt)",
232: new int[][] { { 1, 3 }, { 2, 3 }, { 3, 3 }, { 4, 3 },
233: { 5, 2 }, { 6, 1 } });
234: verifyQueryResults(
235: "hour",
236: "select hour(t), count(*) from alltypes group by hour(t)",
237: new int[][] { { 12, 15 } });
238: verifyQueryResults(
239: "hour2",
240: "select hour(ts), count(*) from alltypes group by hour(ts)",
241: new int[][] { { 12, 15 } });
242: verifyQueryResults(
243: "minute",
244: "select minute(ts), count(*) from alltypes group by minute(ts)",
245: new int[][] { { 30, 14 }, { 55, 1 } });
246: verifyQueryResults(
247: "second",
248: "select second(t), count(*) from alltypes group by second(t)",
249: new int[][] { { 30, 2 }, { 31, 1 }, { 32, 1 },
250: { 33, 1 }, { 34, 1 }, { 35, 1 }, { 36, 1 },
251: { 37, 1 }, { 38, 1 }, { 39, 1 }, { 40, 1 },
252: { 41, 1 }, { 42, 1 }, { 55, 1 } });
253: }
254:
255: /**
256: * Check that duplicate columns are now allowed in group by's. Earlier
257: * (pre 883), derby would flag an error.
258: *
259: */
260: public void testDuplicateColumns() throws Exception {
261: verifyQueryResults("Q1",
262: "select c1, sum(c2) from test group by c1,c1",
263: new int[][] { { 1, 21 }, { 2, 32 } });
264:
265: verifyQueryResults("Q2",
266: "select c1, c1, sum(c2) from test group by c1,c1",
267: new int[][] { { 1, 1, 21 }, { 2, 2, 32 } });
268: }
269:
270: /**
271: * Negative tests. These queries should not compile at all.
272: */
273: public void testNegative() {
274: // disallow java function
275: assertCompileError("42Y30",
276: "select r(), count(*) from test group by r()");
277:
278: // invalid grouping expression.
279: assertCompileError("42Y30",
280: "select c1+1, count(*) from test group by c1+2");
281:
282: // again invalid grouping expression because cast type is different.
283: assertCompileError(
284: "42Y30",
285: "select (cast (c as char(2))), count(*) "
286: + " from alltypes group by (cast (c as char(3)))");
287:
288: // same column name, same table but different tablenumber in the query
289: assertCompileError("42Y30",
290: "select t1.c1, count(*) from test t1, test t2 "
291: + " group by t2.c1");
292: // ternary operator, not equivalent test.
293: assertCompileError("42Y30",
294: "select substr(c, 3, 4) from alltypes group by substr(v, 3, 4)");
295:
296: // DERBY-2008
297: // invalid grouping expression
298: assertCompileError("42Y30",
299: "select substr(c1, 3, 4) from t1 group by substr(c1, 3)");
300: assertCompileError("42Y30",
301: "select substr(c1, 3) from t1 group by substr(c1, 3, 4)");
302: assertCompileError("42Y30",
303: "select locate(c1, 'abc') from t2 group by locate(c1, 'abc',3)");
304: assertCompileError("42Y30",
305: "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc')");
306: assertCompileError("42Y30",
307: "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc',3)");
308:
309: // DERBY-2014
310: // invalid grouping expression
311: assertCompileError("42Y30",
312: "select nullif(c1,c2) from t3 group by nullif(c2,c1)");
313: assertCompileError("42Y30",
314: "select nullif(c1,100) from t3 group by nullif(c1,200)");
315:
316: // aggregates in group by list.
317: assertCompileError("42Y26",
318: "select 1, max(c1) from test group by max(c1)");
319: }
320:
321: /* --------------------- begin helper methods -------------------- */
322:
323: private Object[] intRow(int[] expected) {
324: Object[] arr = new Object[expected.length];
325: for (int i = 0; i < expected.length; i++) {
326: arr[i] = new Integer(expected[i]);
327: }
328: return arr;
329: }
330:
331: private void verifyQueryResults(String assertString, String query,
332: Object[][] golden) throws Exception {
333:
334: PreparedStatement ps = prepareStatement(query);
335: ResultSet rs = ps.executeQuery();
336: for (int i = 0; i < golden.length; i++) {
337: assertTrue("Not enough rows. Expected " + golden.length
338: + " but found " + i, rs.next());
339:
340: assertRow(assertString + ":Row:" + i, rs, golden[i]);
341: }
342: rs.close();
343: ps.close();
344: }
345:
346: private void verifyQueryResults(String assertString, String query,
347: int[][] golden) throws Exception {
348: PreparedStatement ps = prepareStatement(query);
349: ResultSet rs = ps.executeQuery();
350: for (int i = 0; i < golden.length; i++) {
351: assertTrue("Not enough rows. Expected " + golden.length
352: + " but found " + i, rs.next());
353: assertRow(assertString + ":Row:" + i, rs, intRow(golden[i]));
354: }
355: assertFalse("more rows than expected", rs.next());
356: rs.close();
357: ps.close();
358: }
359:
360: public void assertRow(String assertString, ResultSet rs,
361: Object[] expectedRow) throws Exception {
362: int count = expectedRow.length;
363:
364: for (int i = 0; i < count; i++) {
365: int columnNumber = i + 1;
366: Object expected = expectedRow[i];
367: Object actual = rs.getObject(columnNumber);
368: assertEquals(assertString + ":Column number ", expected,
369: actual);
370: }
371: }
372:
373: /* ------------------- end helper methods -------------------------- */
374: public GroupByExpressionTest(String name) {
375: super (name);
376: }
377:
378: public static Test suite() {
379: TestSuite suite = new TestSuite();
380: suite.addTestSuite(GroupByExpressionTest.class);
381:
382: TestSetup wrapper = new BaseJDBCTestSetup(suite) {
383: public void setUp() throws Exception {
384: Connection c = getConnection();
385: c.setAutoCommit(false);
386: Statement s = c.createStatement();
387: for (int i = 0; i < TABLES.length; i++) {
388: s.execute(TABLES[i][1]);
389: }
390: for (int i = 0; i < FUNCTIONS.length; i++) {
391: s.execute(FUNCTIONS[i][1]);
392: }
393:
394: s.execute("insert into test values (1, 10, 100, 1000)");
395: s.execute("insert into test values (1, 11, 100, 1001)");
396: s.execute("insert into test values (2, 10, 100, 1000)");
397: s.execute("insert into test values (2, 11, 101, 1001)");
398: s.execute("insert into test values (2, 11, 101, 1000)");
399:
400: s.execute("insert into coal values ('1', '2')");
401: s.execute("insert into coal values (null, '2')");
402: s.execute("insert into coal values ('1', null)");
403:
404: s
405: .execute("insert into alltypes values (0, 100, 1000000, "
406: + "'duplicate', 'this is duplicated', 'also duplicated', "
407: + "200.0e0, 200.0e0, "
408: + " date('1992-01-01'), time('12:30:30'), "
409: + " timestamp('1992-01-01 12:30:30'), "
410: + "X'12af', x'0000111100001111', X'1234', 111.11) ");
411: s
412: .execute("insert into alltypes values (0, 100, 1000000, "
413: + " 'duplicate', 'this is duplicated', 'also duplicated', "
414: + " 200.0e0, 200.0e0, "
415: + " date('1992-01-02'), time('12:30:31'), "
416: + "timestamp('1992-01-02 12:30:31'), "
417: + " X'12af', X'0000111100001111', X'1234', 111.11)");
418: s
419: .execute("insert into alltypes values (1, 100, 1000000, "
420: + "'duplicate', 'this is duplicated', 'also duplicated', "
421: + " 200.0e0, 200.0e0, "
422: + " date('1992-01-03'), time('12:30:32'), "
423: + " timestamp('1992-01-03 12:30:32'), "
424: + " X'12af', X'0000111100001111', X'1234', 111.11)");
425: s
426: .execute("insert into alltypes values (0, 200, 1000000, "
427: + " 'duplicate', 'this is duplicated', 'also duplicated', "
428: + " 200.0e0, 200.0e0, "
429: + " date('1992-01-04'), time('12:30:33'), "
430: + " timestamp('1992-01-04 12:30:33'), "
431: + " X'12af', X'0000111100001111', X'1234', 222.22)");
432: s
433: .execute("insert into alltypes values (0, 100, 2000000, "
434: + " 'duplicate', 'this is duplicated', 'also duplicated', "
435: + " 200.0e0, 200.0e0, "
436: + " date('1992-01-05'), time('12:30:34'), "
437: + " timestamp('1992-01-05 12:30:34'), "
438: + " X'12af', X'0000111100001111', X'1234', 222.22)");
439: s
440: .execute("insert into alltypes values (0, 100, 1000000, "
441: + " 'goodbye', 'this is duplicated', 'also duplicated', "
442: + " 200.0e0, 200.0e0, "
443: + " date('1992-02-01'), time('12:30:35'), "
444: + " timestamp('1992-02-01 12:30:35'), "
445: + " X'12af', X'0000111100001111', X'1234', 111.11)");
446: s
447: .execute("insert into alltypes values (0, 100, 1000000, "
448: + " 'duplicate', 'noone is here', 'jimmie noone was here', "
449: + " 200.0e0, 200.0e0, "
450: + " date('1992-02-02'), time('12:30:36'), "
451: + " timestamp('1992-02-02 12:30:36'), "
452: + " X'12af', X'0000111100001111', X'1234', 111.11) ");
453: s
454: .execute("insert into alltypes values (0, 100, 1000000, "
455: + " 'duplicate', 'this is duplicated', 'also duplicated', "
456: + " 200.0e0, 200.0e0, "
457: + " date('1992-02-03'), time('12:30:37'), "
458: + " timestamp('1992-02-03 12:30:37'), "
459: + " X'12af', X'0000111100001111', X'1234', 111.11)");
460: s
461: .execute("insert into alltypes values (0, 100, 1000000, "
462: + " 'duplicate', 'this is duplicated', 'also duplicated', "
463: + " 100.0e0, 200.0e0, "
464: + " date('1992-02-04'), time('12:30:38'), "
465: + " timestamp('1992-02-04 12:30:38'), "
466: + " X'12af', X'0000111100001111', X'1234', 111.11) ");
467: s
468: .execute("insert into alltypes values (0, 100, 1000000, "
469: + " 'duplicate', 'this is duplicated', 'also duplicated', "
470: + " 200.0e0, 100.0e0, "
471: + " date('1992-02-05'), time('12:30:39'), "
472: + " timestamp('1992-02-05 12:30:39'), "
473: + " X'12af', X'0000111100001111', X'1234', 111.11) ");
474: s
475: .execute("insert into alltypes values (0, 100, 1000000, "
476: + " 'duplicate', 'this is duplicated', 'also duplicated', "
477: + " 200.0e0, 200.0e0, "
478: + " date('1992-02-06'), time('12:30:40'), "
479: + " timestamp('1992-02-06 12:30:40'), "
480: + " X'12af', X'0000111100001111', X'1234', 111.11) ");
481: s
482: .execute("insert into alltypes values (0, 100, 1000000, "
483: + " 'duplicate', 'this is duplicated', 'also duplicated', "
484: + " 200.0e0, 200.0e0, "
485: + " date('1992-03-01'), time('12:55:55'), "
486: + "timestamp('1992-03-01 12:30:30'), "
487: + " X'12af', X'0000111100001111', X'1234', 111.11) ");
488: s
489: .execute("insert into alltypes values (0, 100, 1000000, "
490: + " 'duplicate', 'this is duplicated', 'also duplicated', "
491: + " 200.0e0, 200.0e0, "
492: + " date('1992-03-02'), time('12:30:30'), "
493: + "timestamp('1992-03-02 12:55:55'), "
494: + " X'12af', X'0000111100001111', X'1234', 111.11) ");
495: s
496: .execute("insert into alltypes values (0, 100, 1000000, "
497: + " 'duplicate', 'this is duplicated', 'also duplicated', "
498: + " 200.0e0, 200.0e0, "
499: + " date('1992-03-03'), time('12:30:41'), "
500: + " timestamp('1992-03-03 12:30:41'), "
501: + " X'ffff', X'0000111100001111', X'1234', 111.11) ");
502: s
503: .execute("insert into alltypes values (0, 100, 1000000, "
504: + " 'duplicate', 'this is duplicated', 'also duplicated', "
505: + " 200.0e0, 200.0e0, "
506: + " date('1992-03-04'), time('12:30:42'), "
507: + " timestamp('1992-03-04 12:30:42'), "
508: + " X'12af', X'1111111111111111', X'1234', 111.11) ");
509:
510: s
511: .execute("insert into t1 values ('1928-09-21'), ('1903-12-08')");
512: s
513: .execute("insert into t2 values '123 ', 'abc ', '123', 'abc'");
514: s
515: .execute("insert into t3 values (1,1), (2,2), (2,2), (3,3), (null, null), (5,100)");
516:
517: s.close();
518: c.commit();
519: c.close();
520: }
521:
522: protected void tearDown() throws Exception {
523: Connection c = getConnection();
524: c.setAutoCommit(false);
525: Statement s = c.createStatement();
526:
527: for (int i = 0; i < TABLES.length; i++) {
528: s.execute("drop table " + TABLES[i][0]);
529: }
530: for (int i = 0; i < FUNCTIONS.length; i++) {
531: s.execute("drop function " + FUNCTIONS[i][0]);
532: }
533:
534: c.commit();
535: super.tearDown();
536: }
537: };
538: return wrapper;
539: }
540: }
|