|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.Connection  ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.Statement ;
28
29 import junit.extensions.TestSetup;
30 import junit.framework.Test;
31 import junit.framework.TestSuite;
32
33 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
34 import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
35
36 public class GroupByExpressionTest extends BaseJDBCTestCase
37 {
38
39 private static String [][] TABLES = {
40 {"test", "create table test (c1 int, c2 int, c3 int, c4 int)"},
41 {"coal", "create table coal (vc1 varchar(2), vc2 varchar(2))"},
42 {"alltypes",
43 "create table alltypes (i int, s smallint, l bigint, " +
44 "c char(10), v varchar(50), lvc long varchar, " +
45 " d double precision, r real, " +
46 " dt date, t time, ts timestamp, " +
47 " b char(2) for bit data, bv varchar(8) for bit data, " +
48 " lbv long varchar for bit data, dc decimal(5,2))"},
49 {"t1", "create table t1 (c1 varchar(30))"},
50 {"t2", "create table t2 (c1 varchar(10))"},
51 {"t3", "create table t3 (c1 int, c2 int)"}
52 };
53
54 private static String [][] FUNCTIONS = {
55 {"r", "create function r() returns double external name " +
56 "'java.lang.Math.random' language java parameter style java"}};
57
58
61 public void testSimpleExpressions() throws Exception
62 {
63 verifyQueryResults(
64 "Q1",
65 "select c1,c2,sum(c3) from test group by c2,c1",
66 new int[][] {
67 {1,10,100},
68 {2,10,100},
69 {1,11,100},
70 {2,11,202}});
71
72 verifyQueryResults(
73 "Q2",
74 "select c1+c2, sum(c3) from test group by c1,c2",
75 new int[][] {
76 {11, 100},
77 {12, 100},
78 {12, 100},
79 {13, 202}});
80 verifyQueryResults(
81 "Q3",
82 "select c1+c2, sum(c3) from test group by c1+c2",
83 new int[][] {
84 {11, 100},
85 {12, 200},
86 {13, 202}});
87 verifyQueryResults(
88 "Q4",
89 "select (c1+c2)+1, sum(c3) from test group by c1+c2",
90 new int[][] {
91 {12, 100},
92 {13, 200},
93 {14, 202}});
94 verifyQueryResults(
95 "Q5",
96 "select (c1+c2), sum(c3)+(c1+c2) from test group by c1+c2",
97 new int[][] {
98 {11,111},
99 {12,212},
100 {13,215}});
101 verifyQueryResults(
102 "Q6",
103 "select c2-c1, c1+c2, count(*) from test group by c1+c2, c2-c1",
104 new int[][] {
105 {9,11,1},
106 {8,12,1},
107 {10,12,1},
108 {9,13,2}});
109 }
110
111
112 public void testSubSelect() throws Exception
113 {
114
115 verifyQueryResults(
116 "Q1",
117 "select a+1, sum(b) from (select c1+1 a , c2+1 b from test) t group by a",
118 new int[][] {
119 {3,23}, {4,35}});
120
121 verifyQueryResults(
122 "Q2",
123 "select a+1, sum(b) from (select c1+1 a , c2+1 b from test) t group by a+1",
124 new int[][] {
125 {3,23}, {4,35}});
126
127 verifyQueryResults(
128 "Q3",
129 "select b/2,sum(a) from " +
130 "(select c1+1 a, max(c2) b from test group by c1+1) t " +
131 "group by b/2",
132 new int[][] {{5,5}});
133 }
134
135
136 public void testMiscExpressions() throws Exception
137 {
138 verifyQueryResults(
140 "cast",
141 "select (cast (c1 as char(2))), count(*) from test " +
142 " group by (cast (c1 as char(2)))",
143 new Object [][] {
144 {"1 ", new Integer (2)},
145 {"2 ", new Integer (3)}});
146
147 verifyQueryResults(
149 "coalesce",
150 "select (coalesce(vc1,vc2)), count(*) from coal " +
151 " group by (coalesce(vc1,vc2))",
152 new Object [][] {{"1", new Integer (2)}, {"2", new Integer (1)}});
153 verifyQueryResults(
155 "concat",
156 "select c||v, count(*) from alltypes group by c||v",
157 new Object [][] {
158 {"duplicate noone is here", new Integer (1)},
159 {"duplicate this is duplicated", new Integer (13)},
160 {"goodbye this is duplicated", new Integer (1)}});
161 verifyQueryResults(
163 "cond",
164 "select (case when c1 = 1 then 2 else 1 end), sum(c2) from test " +
165 " group by (case when c1 = 1 then 2 else 1 end)",
166 new int[][] {{1,32}, {2, 21}});
167
168 verifyQueryResults(
170 "length",
171 "select length(v), count(*) from alltypes group by length(v)",
172 new int[][] {{13,1},{18,14}});
173
174 verifyQueryResults(
178 "current_time",
179 "select co from " +
180 "(select current_time ct, count(*) co from test t1, test t2, test t3 group by current_time) t",
181 new int[][] {{125}});
182 verifyQueryResults(
184 "concat+substr",
185 "select substr(c||v, 1, 4), count(*) from alltypes group by substr(c||v, 1, 4)",
186 new Object [][] {
187 {"dupl", new Integer (14)},
188 {"good", new Integer (1)}});
189
190 verifyQueryResults(
193 "substr-Q1",
194 "select substr(c1, 3) from t1 group by substr(c1, 3)",
195 new String [][] { {"03-12-08"},
196 {"28-09-21"} });
197 verifyQueryResults(
199 "substr-Q2",
200 "select substr(c1, 3, 4) from t1 group by substr(c1, 3, 4)",
201 new String [][] { {"03-1"},
202 {"28-0"} });
203
204 verifyQueryResults(
206 "ltrim",
207 "select ltrim(c1) from t2 group by ltrim(c1)",
208 new String [][] { {"123 "},
209 {"abc "} });
210
211 verifyQueryResults(
213 "rtrim",
214 "select rtrim(c1) from t2 group by rtrim(c1)",
215 new String [][] { {"123"},
216 {"abc"} });
217
218 verifyQueryResults(
220 "locate-Q1",
221 "select locate(c1, 'abc') from t2 group by locate(c1, 'abc')",
222 new int[][] { { 0 },
223 { 1 } });
224
225 verifyQueryResults(
227 "locate-Q2",
228 "select locate(c1, 'abc', 1) from t2 group by locate(c1, 'abc',1)",
229 new int[][] { { 0 },
230 { 1 } });
231
232 verifyQueryResults(
234 "cast-Q2",
235 "select (cast (NULL as INTEGER)) from t2 group by (cast (NULL as INTEGER))",
236 new Object [][] { { null } } );
237
238 verifyQueryResults(
241 "nullif-Q1",
242 "select nullif(c1,c1) from t3 group by nullif(c1,c1)",
243 new Object [][] { { null } } );
244
245 verifyQueryResults(
246 "nullif-Q2",
247 "select nullif(c1,c2) from t3 group by nullif(c1,c2)",
248 new Object [][] { { new Integer (5) },
249 { null } });
250
251 verifyQueryResults(
252 "nullif-Q3",
253 "select nullif(c1,10) from t3 group by nullif(c1,10)",
254 new Object [][] { { new Integer (1) },
255 { new Integer (2) },
256 { new Integer (3) },
257 { new Integer (5) },
258 { null } });
259
260 verifyQueryResults(
261 "nullif-Q4",
262 "select nullif(1,c1) from t3 group by nullif(1,c1)",
263 new Object [][] { { new Integer (1) },
264 { null } });
265 }
266
267 public void testExtractOperator() throws Exception
268 {
269 verifyQueryResults(
270 "year",
271 "select year(dt), count(*) from alltypes group by year(dt)",
272 new int[][] {{1992, 15}});
273 verifyQueryResults(
274 "month",
275 "select month(dt), count(*) from alltypes group by month(dt)",
276 new int[][] {{1,5},{2,6},{3,4}});
277 verifyQueryResults(
278 "day",
279 "select day(dt), count(*) from alltypes group by day(dt)",
280 new int[][] {{1,3},{2,3},{3,3},{4,3},{5,2},{6,1}});
281 verifyQueryResults(
282 "hour",
283 "select hour(t), count(*) from alltypes group by hour(t)",
284 new int[][] {{12, 15}});
285 verifyQueryResults(
286 "hour2",
287 "select hour(ts), count(*) from alltypes group by hour(ts)",
288 new int[][] {{12,15}});
289 verifyQueryResults(
290 "minute",
291 "select minute(ts), count(*) from alltypes group by minute(ts)",
292 new int[][] {{30,14},{55,1}});
293 verifyQueryResults(
294 "second",
295 "select second(t), count(*) from alltypes group by second(t)",
296 new int[][]{
297 {30,2},{31,1},{32,1},{33,1},{34,1},{35,1},
298 {36,1},{37,1},{38,1},{39,1},{40,1},{41,1},
299 {42,1},{55,1}});
300 }
301
302
307 public void testDuplicateColumns() throws Exception
308 {
309 verifyQueryResults(
310 "Q1",
311 "select c1, sum(c2) from test group by c1,c1",
312 new int[][]{ {1,21}, {2,32}});
313
314 verifyQueryResults(
315 "Q2",
316 "select c1, c1, sum(c2) from test group by c1,c1",
317 new int[][]{ {1,1,21}, {2,2,32}});
318 }
319
322 public void testNegative()
323 {
324 assertCompileError(
326 "42Y30", "select r(), count(*) from test group by r()");
327
328 assertCompileError(
330 "42Y30", "select c1+1, count(*) from test group by c1+2");
331
332 assertCompileError(
334 "42Y30", "select (cast (c as char(2))), count(*) " +
335 " from alltypes group by (cast (c as char(3)))");
336
337 assertCompileError(
339 "42Y30",
340 "select t1.c1, count(*) from test t1, test t2 " +
341 " group by t2.c1");
342 assertCompileError(
344 "42Y30",
345 "select substr(c, 3, 4) from alltypes group by substr(v, 3, 4)");
346
347 assertCompileError(
350 "42Y30",
351 "select substr(c1, 3, 4) from t1 group by substr(c1, 3)");
352 assertCompileError(
353 "42Y30",
354 "select substr(c1, 3) from t1 group by substr(c1, 3, 4)");
355 assertCompileError(
356 "42Y30",
357 "select locate(c1, 'abc') from t2 group by locate(c1, 'abc',3)");
358 assertCompileError(
359 "42Y30",
360 "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc')");
361 assertCompileError(
362 "42Y30",
363 "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc',3)");
364
365 assertCompileError(
368 "42Y30",
369 "select nullif(c1,c2) from t3 group by nullif(c2,c1)");
370 assertCompileError(
371 "42Y30",
372 "select nullif(c1,100) from t3 group by nullif(c1,200)");
373
374 assertCompileError(
376 "42Y26",
377 "select 1, max(c1) from test group by max(c1)");
378 }
379
380
381 private Object [] intRow(int[] expected)
382 {
383 Object [] arr = new Object [expected.length];
384 for (int i = 0; i < expected.length; i++)
385 {
386 arr[i] = new Integer (expected[i]);
387 }
388 return arr;
389 }
390
391 private void verifyQueryResults(
392 String assertString, String query, Object [][] golden)
393 throws Exception
394 {
395
396 PreparedStatement ps = prepareStatement(query);
397 ResultSet rs = ps.executeQuery();
398 for (int i = 0; i < golden.length; i++)
399 {
400 assertTrue(
401 "Not enough rows. Expected " + golden.length +
402 " but found " + i,
403 rs.next());
404
405 assertRow(assertString + ":Row:" + i, rs, golden[i]);
406 }
407 rs.close();
408 ps.close();
409 }
410
411 private void verifyQueryResults(
412 String assertString, String query, int[][] golden)
413 throws Exception
414 {
415 PreparedStatement ps = prepareStatement(query);
416 ResultSet rs = ps.executeQuery();
417 for (int i = 0; i < golden.length; i++)
418 {
419 assertTrue(
420 "Not enough rows. Expected " + golden.length +
421 " but found " + i,
422 rs.next());
423 assertRow(assertString + ":Row:" + i, rs, intRow(golden[i]));
424 }
425 assertFalse("more rows than expected", rs.next());
426 rs.close();
427 ps.close();
428 }
429
430 public void assertRow(
431 String assertString, ResultSet rs, Object [] expectedRow)
432 throws Exception
433 {
434 int count = expectedRow.length;
435
436 for ( int i = 0; i < count; i++ )
437 {
438 int columnNumber = i + 1;
439 Object expected = expectedRow[i];
440 Object actual = rs.getObject(columnNumber);
441 assertEquals(assertString + ":Column number ", expected, actual);
442 }
443 }
444
445
446 public GroupByExpressionTest(String name)
447 {
448 super(name);
449 }
450
451 public static Test suite()
452 {
453 TestSuite suite = new TestSuite();
454 suite.addTestSuite(GroupByExpressionTest.class);
455
456 TestSetup wrapper = new BaseJDBCTestSetup(suite) {
457 public void setUp() throws Exception
458 {
459 Connection c = getConnection();
460 c.setAutoCommit(false);
461 Statement s = c.createStatement();
462 for (int i = 0; i < TABLES.length; i++) {
463 s.execute(TABLES[i][1]);
464 }
465 for (int i = 0; i < FUNCTIONS.length; i++) {
466 s.execute(FUNCTIONS[i][1]);
467 }
468
469 s.execute("insert into test values (1, 10, 100, 1000)");
470 s.execute("insert into test values (1, 11, 100, 1001)");
471 s.execute("insert into test values (2, 10, 100, 1000)");
472 s.execute("insert into test values (2, 11, 101, 1001)");
473 s.execute("insert into test values (2, 11, 101, 1000)");
474
475 s.execute("insert into coal values ('1', '2')");
476 s.execute("insert into coal values (null, '2')");
477 s.execute("insert into coal values ('1', null)");
478
479 s.execute(
480 "insert into alltypes values (0, 100, 1000000, " +
481 "'duplicate', 'this is duplicated', 'also duplicated', " +
482 "200.0e0, 200.0e0, " +
483 " date('1992-01-01'), time('12:30:30'), " +
484 " timestamp('1992-01-01 12:30:30'), " +
485 "X'12af', x'0000111100001111', X'1234', 111.11) ");
486 s.execute(
487 "insert into alltypes values (0, 100, 1000000, " +
488 " 'duplicate', 'this is duplicated', 'also duplicated', " +
489 " 200.0e0, 200.0e0, " +
490 " date('1992-01-02'), time('12:30:31'), " +
491 "timestamp('1992-01-02 12:30:31'), " +
492 " X'12af', X'0000111100001111', X'1234', 111.11)");
493 s.execute(
494 "insert into alltypes values (1, 100, 1000000, " +
495 "'duplicate', 'this is duplicated', 'also duplicated', " +
496 " 200.0e0, 200.0e0, " +
497 " date('1992-01-03'), time('12:30:32'), " +
498 " timestamp('1992-01-03 12:30:32'), " +
499 " X'12af', X'0000111100001111', X'1234', 111.11)");
500 s.execute(
501 "insert into alltypes values (0, 200, 1000000, " +
502 " 'duplicate', 'this is duplicated', 'also duplicated', " +
503 " 200.0e0, 200.0e0, " +
504 " date('1992-01-04'), time('12:30:33'), " +
505 " timestamp('1992-01-04 12:30:33'), " +
506 " X'12af', X'0000111100001111', X'1234', 222.22)");
507 s.execute(
508 "insert into alltypes values (0, 100, 2000000, " +
509 " 'duplicate', 'this is duplicated', 'also duplicated', " +
510 " 200.0e0, 200.0e0, " +
511 " date('1992-01-05'), time('12:30:34'), " +
512 " timestamp('1992-01-05 12:30:34'), " +
513 " X'12af', X'0000111100001111', X'1234', 222.22)");
514 s.execute("insert into alltypes values (0, 100, 1000000, " +
515 " 'goodbye', 'this is duplicated', 'also duplicated', " +
516 " 200.0e0, 200.0e0, " +
517 " date('1992-02-01'), time('12:30:35'), " +
518 " timestamp('1992-02-01 12:30:35'), " +
519 " X'12af', X'0000111100001111', X'1234', 111.11)");
520 s.execute("insert into alltypes values (0, 100, 1000000, " +
521 " 'duplicate', 'noone is here', 'jimmie noone was here', " +
522 " 200.0e0, 200.0e0, " +
523 " date('1992-02-02'), time('12:30:36'), " +
524 " timestamp('1992-02-02 12:30:36'), " +
525 " X'12af', X'0000111100001111', X'1234', 111.11) ");
526 s.execute("insert into alltypes values (0, 100, 1000000, " +
527 " 'duplicate', 'this is duplicated', 'also duplicated', " +
528 " 200.0e0, 200.0e0, " +
529 " date('1992-02-03'), time('12:30:37'), " +
530 " timestamp('1992-02-03 12:30:37'), " +
531 " X'12af', X'0000111100001111', X'1234', 111.11)");
532 s.execute("insert into alltypes values (0, 100, 1000000, " +
533 " 'duplicate', 'this is duplicated', 'also duplicated', " +
534 " 100.0e0, 200.0e0, " +
535 " date('1992-02-04'), time('12:30:38'), " +
536 " timestamp('1992-02-04 12:30:38'), " +
537 " X'12af', X'0000111100001111', X'1234', 111.11) ");
538 s.execute("insert into alltypes values (0, 100, 1000000, " +
539 " 'duplicate', 'this is duplicated', 'also duplicated', " +
540 " 200.0e0, 100.0e0, " +
541 " date('1992-02-05'), time('12:30:39'), " +
542 " timestamp('1992-02-05 12:30:39'), " +
543 " X'12af', X'0000111100001111', X'1234', 111.11) ");
544 s.execute("insert into alltypes values (0, 100, 1000000, " +
545 " 'duplicate', 'this is duplicated', 'also duplicated', " +
546 " 200.0e0, 200.0e0, " +
547 " date('1992-02-06'), time('12:30:40'), " +
548 " timestamp('1992-02-06 12:30:40'), " +
549 " X'12af', X'0000111100001111', X'1234', 111.11) ");
550 s.execute("insert into alltypes values (0, 100, 1000000, " +
551 " 'duplicate', 'this is duplicated', 'also duplicated', " +
552 " 200.0e0, 200.0e0, " +
553 " date('1992-03-01'), time('12:55:55'), " +
554 "timestamp('1992-03-01 12:30:30'), " +
555 " X'12af', X'0000111100001111', X'1234', 111.11) ");
556 s.execute("insert into alltypes values (0, 100, 1000000, " +
557 " 'duplicate', 'this is duplicated', 'also duplicated', " +
558 " 200.0e0, 200.0e0, " +
559 " date('1992-03-02'), time('12:30:30'), " +
560 "timestamp('1992-03-02 12:55:55'), " +
561 " X'12af', X'0000111100001111', X'1234', 111.11) ");
562 s.execute("insert into alltypes values (0, 100, 1000000, " +
563 " 'duplicate', 'this is duplicated', 'also duplicated', " +
564 " 200.0e0, 200.0e0, " +
565 " date('1992-03-03'), time('12:30:41'), " +
566 " timestamp('1992-03-03 12:30:41'), " +
567 " X'ffff', X'0000111100001111', X'1234', 111.11) ");
568 s.execute("insert into alltypes values (0, 100, 1000000, " +
569 " 'duplicate', 'this is duplicated', 'also duplicated', " +
570 " 200.0e0, 200.0e0, " +
571 " date('1992-03-04'), time('12:30:42'), " +
572 " timestamp('1992-03-04 12:30:42'), " +
573 " X'12af', X'1111111111111111', X'1234', 111.11) " );
574
575 s.execute("insert into t1 values ('1928-09-21'), ('1903-12-08')");
576 s.execute("insert into t2 values '123 ', 'abc ', '123', 'abc'") ;
577 s.execute("insert into t3 values (1,1), (2,2), (2,2), (3,3), (null, null), (5,100)");
578
579 s.close();
580 c.commit();
581 c.close();
582 }
583 protected void tearDown() throws Exception
584 {
585 Connection c = getConnection();
586 c.setAutoCommit(false);
587 Statement s = c.createStatement();
588
589 for (int i = 0; i < TABLES.length; i++) {
590 s.execute("drop table " + TABLES[i][0]);
591 }
592 for (int i = 0; i < FUNCTIONS.length; i++) {
593 s.execute("drop function " + FUNCTIONS[i][0]);
594 }
595
596 c.commit();
597 super.tearDown();
598 }
599 };
600 return wrapper;
601 }
602 }
603 |
Popular Tags |
Java API By Example, From Geeks To Geeks.