| /** |
| * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.OLAPTest |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * OLAP functionality test. |
| * |
| * Please refer to DERBY-581 for more details. |
| */ |
| public class OLAPTest extends BaseJDBCTestCase { |
| |
| private final static String LANG_WINDOW_FUNCTION_CONTEXT_ERROR = "42ZC2"; |
| private final static String NOT_IMPLEMENTED = "0A000"; |
| private final static String LANG_SYNTAX_ERROR = "42X01"; |
| private final static String LANG_COLUMN_NOT_FOUND = "42X04"; |
| |
| public OLAPTest(String name) { |
| super(name); |
| } |
| |
| public static Test makeSuite() { |
| Test clean = new CleanDatabaseTestSetup( |
| new BaseTestSuite(OLAPTest.class)) { |
| protected void decorateSQL(Statement s) |
| throws SQLException |
| { |
| getConnection().setAutoCommit(false); |
| s.executeUpdate("create table t1 (a int, b int)"); |
| s.executeUpdate("create table t2 (x int)"); |
| s.executeUpdate("create table t3 (y int)"); |
| s.executeUpdate("create table t4 (a int, b int)"); |
| s.executeUpdate("create table t5 (a int, b int)"); |
| |
| s.executeUpdate( |
| "insert into t1 values (10,100),(20,200)," + |
| " (30,300),(40,400)," + |
| " (50,500)"); |
| s.executeUpdate( |
| "insert into t2 values (1),(2),(3),(4),(5)"); |
| s.executeUpdate( |
| "insert into t3 values (4),(5),(6),(7),(8)"); |
| s.executeUpdate( |
| "insert into t4 values (10,100),(20,200)"); |
| s.executeUpdate( |
| "insert into t5 values (1,1),(2,4),(3,4),(4,4),(5,9)"); |
| getConnection().commit(); |
| } |
| }; |
| return clean; |
| } |
| |
| |
| public static Test suite() |
| { |
| BaseTestSuite suite = new BaseTestSuite("OLAPTest"); |
| suite.addTest(makeSuite()); |
| suite.addTest(TestConfiguration.clientServerDecorator(makeSuite())); |
| return suite; |
| } |
| |
| |
| /** |
| * Main test body |
| * |
| * @throws SQLException |
| */ |
| public void testBasicOperations() |
| throws SQLException { |
| Statement s = createStatement(); |
| |
| /* |
| * Positive testing of Statements |
| */ |
| |
| |
| |
| /* |
| * Simple queries |
| */ |
| ResultSet rs = s.executeQuery("select row_number() over (), t1.* from t1"); |
| String[][] expectedRows = {{"1", "10", "100"}, {"2", "20", "200"}, {"3", "30", "300"}, {"4", "40", "400"}, {"5", "50", "500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select row_number() over (), t1.* from t1 where a > 30"); |
| expectedRows = new String[][]{{"1", "40", "400"}, {"2", "50", "500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select row_number() over (), a from t1 where b > 300"); |
| expectedRows = new String[][]{{"1", "40"}, {"2", "50"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select row_number() over () as r, a from t1 where b > 300"); |
| expectedRows = new String[][]{{"1", "40"}, {"2", "50"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Two instances of row_number columns in the same RCL */ |
| rs = s.executeQuery("select row_number() over (), row_number() over (), b from t1 where b <= 300"); |
| expectedRows = new String[][]{{"1", "1", "100"}, {"2", "2", "200"}, {"3", "3", "300"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Two instances of row_number columns in the same RCL, reorder columns */ |
| rs = s.executeQuery("select row_number() over (), b, row_number() over (), a from t1 where b < 300 "); |
| expectedRows = new String[][]{{"1", "100", "1", "10"}, {"2", "200", "2", "20"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Pushing predicates (... where r ... ) too far cause this join to fail */ |
| rs = s.executeQuery("select row_number() over(),x from t2,t3 where x=y"); |
| expectedRows = new String[][]{{"1", "4"}, {"2", "5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // DERBY-4069: ORDER BY should be applied at the cursor level, that is |
| // *after* a windowing clause in the. So, with the original ordering |
| // here, the ROW_NUMBER should come backwards: |
| |
| /* Ordering */ |
| rs = s.executeQuery("select row_number() over () as r, t1.* from t1 order by b desc"); |
| |
| expectedRows = new String[][]{{"5", "50", "500"}, |
| {"4", "40", "400"}, |
| {"3", "30", "300"}, |
| {"2", "20", "200"}, |
| {"1", "10", "100"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // DERBY-4069: ORDER BY should be applied at the cursor level, that is |
| // *after* a windowing clause in the. So, with the original ordering |
| // here, the ROW_NUMBER should come backwards: |
| |
| /* Ordering on a column dropped in projection */ |
| rs = s.executeQuery("select row_number() over () as r, t1.a from t1 order by b desc"); |
| |
| expectedRows = new String[][]{{"5", "50"}, |
| {"4", "40"}, |
| {"3", "30"}, |
| {"2", "20"}, |
| {"1", "10"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Only expressions in RCL */ |
| rs = s.executeQuery("select row_number() over (), row_number() over (), 2*t1.a from t1"); |
| expectedRows = new String[][]{{"1", "1", "20"}, {"2", "2","40"}, {"3", "3","60"}, {"4", "4", "80"}, {"5", "5", "100"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| |
| |
| /* |
| * Subqueries |
| */ |
| |
| /* This query returned no rows at one time */ |
| rs = s.executeQuery("select * from (select row_number() over () as r,x from t2,t3 where x=y) s(r,x) where r < 3"); |
| expectedRows = new String[][]{{"1", "4"}, {"2", "5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from (select row_number() over () as r, t1.* from t1) as tr where r < 3"); |
| expectedRows = new String[][]{{"1", "10", "100"}, {"2", "20", "200"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from (select row_number() over () as r, t1.* from t1) as tr where r > 3"); |
| expectedRows = new String[][]{{"4", "40", "400"}, {"5", "50", "500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Two instances of row_number columns */ |
| rs = s.executeQuery("select row_number() over(), tr.* from (select row_number() over () as r, t1.* from t1) as tr where r > 2 and r < 5"); |
| expectedRows = new String[][]{{"1", "3", "30", "300"}, {"2", "4", "40", "400"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Two instances of row_number columns, with projection */ |
| rs = s.executeQuery("select row_number() over(), tr.b from (select row_number() over () as r, t1.* from t1) as tr where r > 2 and r < 5"); |
| expectedRows = new String[][]{{"1", "300"}, {"2", "400"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Column ordering */ |
| rs = s.executeQuery("select * from (select t1.b, row_number() over () as r from t1) as tr where r > 3"); |
| expectedRows = new String[][]{{"400", "4"}, {"500", "5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Column ordering with projection*/ |
| rs = s.executeQuery("select b from (select t1.b, row_number() over () as r from t1) as tr where r > 3"); |
| expectedRows = new String[][]{{"400"}, {"500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* |
| * Aggregates over window functions once failed |
| */ |
| rs = s.executeQuery("select count(*) from (select row_number() over() from t1) x"); |
| expectedRows = new String[][]{{"5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select count(*) from (select row_number() over () as r from t1) as t(r) where r <=3"); |
| expectedRows = new String[][]{{"3"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* |
| * Some other joins with window functions. |
| * Run off a smaller table t4 to reduce expected row count. |
| */ |
| rs = s.executeQuery("select row_number() over () from t1 union all select row_number() over () from t1"); |
| expectedRows = new String[][]{{"1"},{"2"},{"3"},{"4"},{"5"},{"1"},{"2"},{"3"},{"4"},{"5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select 2 * r from (select row_number() over () from t1) x(r)"); |
| expectedRows = new String[][]{{"2"},{"4"},{"6"},{"8"},{"10"},}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select c3, c1, c2 from " + |
| "(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), " + |
| "(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4)"); |
| expectedRows = new String[][]{{"100", "10", "100"}, |
| {"200", "10", "100"}, |
| {"100", "20", "200"}, |
| {"200", "20", "200"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select c3, c1, c2 from " + |
| "(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), " + |
| "(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), " + |
| "t4"); |
| expectedRows = new String[][]{{"100", "10", "100"}, |
| {"100", "10", "100"}, |
| {"200", "10", "100"}, |
| {"200", "10", "100"}, |
| {"100", "20", "200"}, |
| {"100", "20", "200"}, |
| {"200", "20", "200"}, |
| {"200", "20", "200"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select c3, c1, c2 from "+ |
| "(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), "+ |
| "(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), "+ |
| "t4 "+ |
| "where x1.r1 = 2 * x2.r2"); |
| expectedRows = new String[][]{{"100", "20", "200"}, {"100", "20", "200"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select c3, c1, c2 from "+ |
| "(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), "+ |
| "(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), "+ |
| "t4 "+ |
| "where x1.r1 = 2 * x2.r2"); |
| expectedRows = new String[][]{{"100", "20", "200"}, {"100", "20", "200"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* Two problematic joins reported during development */ |
| rs = s.executeQuery("select c3, c1, c2 from "+ |
| "(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), "+ |
| "(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), "+ |
| "t4 "+ |
| "where x2.c4 = t4.a"); |
| expectedRows = new String[][]{{"100", "10", "100"}, |
| {"100", "20", "200"}, |
| {"200", "10", "100"}, |
| {"200", "20", "200"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select c3, c1, c2 from "+ |
| "(select a, b, row_number() over() as r from t1) x1 (c1, c2, r1), "+ |
| "(select row_number() over() as r, b, a from t1) x2 (r2, c3, c4), "+ |
| "t1 "+ |
| "where x1.r1 = 2 * x2.r2 and x2.c4 = t1.a"); |
| expectedRows = new String[][]{{"100", "20", "200"}, {"200", "40", "400"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Check that flattening does not happen when a window is used in a |
| // subquery |
| |
| rs = s.executeQuery("select * from t5 o where o.a in " + |
| "(select x + row_number() over () from t2)"); |
| expectedRows = new String[][]{{"2", "4"}, |
| {"4", "4"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Subquery in SELECT list. DERBY-5954 |
| rs = s.executeQuery( |
| "SELECT rn_t1, (" + |
| " SELECT rn_t2 FROM (" + |
| " SELECT row_number() over() as rn_t2 FROM t2) " + |
| " as T_2" + |
| " where T_2.rn_t2 = T_1.rn_t1 + 1 ) " + |
| " as rn_outer" + |
| " FROM (SELECT row_number() over() as rn_t1 from t2) as T_1"); |
| |
| expectedRows = new String[][]{{"1", "2"}, |
| {"2", "3"}, |
| {"3", "4"}, |
| {"4", "5"}, |
| {"5", null}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| |
| /* |
| * Group by and having |
| */ |
| rs = s.executeQuery("select r from (select a, row_number() over() as r, b from t1) x group by r"); |
| expectedRows = new String[][]{{"1"}, {"2"}, {"3"}, {"4"}, {"5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from (select a, row_number() over() as r, b from t1) x group by a, b, r"); |
| expectedRows = new String[][]{{"10", "1", "100"}, |
| {"20", "2", "200"}, |
| {"30", "3", "300"}, |
| {"40", "4", "400"}, |
| {"50", "5", "500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from (select a, row_number() over() as r, b from t1) x group by b, r, a"); |
| expectedRows = new String[][]{{"10", "1", "100"}, |
| {"20", "2", "200"}, |
| {"30", "3", "300"}, |
| {"40", "4", "400"}, |
| {"50", "5", "500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from "+ |
| "(select a, row_number() over() as r, b from t1) x "+ |
| "group by b, r, a "+ |
| "having r > 2"); |
| expectedRows = new String[][]{{"30", "3", "300"}, |
| {"40", "4", "400"}, |
| {"50", "5", "500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from "+ |
| "(select a, row_number() over() as r, b from t1) x "+ |
| "group by b, r, a "+ |
| "having r > 2 and a >=30 "+ |
| "order by a desc"); |
| expectedRows = new String[][]{{"50", "5", "500"}, |
| {"40", "4", "400"}, |
| {"30", "3", "300"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from "+ |
| "(select a, row_number() over() as r, b from t1) x "+ |
| "group by b, r, a "+ |
| "having r > 2 and a >=30 "+ |
| "order by r desc"); |
| expectedRows = new String[][]{{"50", "5", "500"}, |
| {"40", "4", "400"}, |
| {"30", "3", "300"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from "+ |
| "(select a, row_number() over() as r, b from t1) x "+ |
| "group by b, r, a "+ |
| "having r > 2 and a >=30 "+ |
| "order by a asc, r desc"); |
| expectedRows = new String[][]{{"30", "3", "300"}, |
| {"40", "4", "400"}, |
| {"50", "5", "500"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* A couple of distinct queries */ |
| rs = s.executeQuery("select distinct row_number() over (), 'ABC' from t1"); |
| expectedRows = new String[][]{{"1", "ABC"}, |
| {"2", "ABC"}, |
| {"3", "ABC"}, |
| {"4", "ABC"}, |
| {"5", "ABC"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery( |
| "select * from (select distinct row_number() over (), " + |
| " 'ABC' from t1) tmp"); |
| expectedRows = new String[][]{{"1", "ABC"}, |
| {"2", "ABC"}, |
| {"3", "ABC"}, |
| {"4", "ABC"}, |
| {"5", "ABC"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Test explicitly declared window |
| rs = s.executeQuery( |
| "select * from (select distinct row_number() over w, 'ABC' " + |
| "from t1 window w as ()) tmp"); |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // DERBY-3634 Cannot use row_number() in ORDER BY clause |
| rs = s.executeQuery( |
| "select row_number() over () r, a from t1 order by r desc"); |
| expectedRows = new String[][]{{"5", "50"}, |
| {"4", "40"}, |
| {"3", "30"}, |
| {"2", "20"}, |
| {"1", "10"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery( |
| "select a from t1 order by row_number() over () desc"); |
| expectedRows = new String[][]{{"50"}, |
| {"40"}, |
| {"30"}, |
| {"20"}, |
| {"10"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Another case mentioned in DERBY-3634 |
| rs = s.executeQuery("select a, row_number() over () from t1 except " + |
| "select a, row_number() over () from t1"); |
| JDBC.assertEmpty(rs); |
| |
| // And yet another case mentioned in DERBY-3634 This actually also |
| // tests that ROW_NUMBER in an ORDER BY does not get optimized away if |
| // there is a restriction, see SelectNode's call to |
| // orderByList.removeConstantColumns(wherePredicates). |
| rs = s.executeQuery("select abs(a), row_number() over () c " + |
| "from t1 where a > 30 and a <= 50 " + |
| "order by c desc"); |
| expectedRows = new String[][]{{"50", "2"}, |
| {"40", "1"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| |
| // DERBY-3635 Cannot build SELECT LIST expressions involving |
| // ROW_NUMBER() |
| rs = s.executeQuery("select row_number() over () + 10, a from t1 " + |
| "order by row_number() over () desc"); |
| expectedRows = new String[][]{{"15", "50"}, |
| {"14", "40"}, |
| {"13", "30"}, |
| {"12", "20"}, |
| {"11", "10"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| |
| // Check that a non-used window doesn't cause problems |
| rs = s.executeQuery("select a from t1 window r as () order by a desc"); |
| expectedRows = new String[][]{{"50"}, |
| {"40"}, |
| {"30"}, |
| {"20"}, |
| {"10"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Check that a row_number combined with group by works. Good to test |
| // this since windowing uses a similar rewrite mechanism to group by |
| // and could interfere (seen during development). |
| rs = s.executeQuery("select row_number() over r, b, sum(a) from t5 " + |
| "group by b window r as ()"); |
| expectedRows = new String[][]{{"1", "1", "1"}, |
| {"2", "4", "9"}, |
| {"3", "9", "5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Check that a row_number combined with group by works. Good to test |
| // this since windowing uses a similar rewrite mechanism to group by |
| // and could interfere (seen during development). |
| rs = s.executeQuery("select row_number() over r, b, sum(a) from t5 " + |
| "group by b window r as ()"); |
| expectedRows = new String[][]{{"1", "1", "1"}, |
| {"2", "4", "9"}, |
| {"3", "9", "5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select b, sum(a) from t5 " + |
| "group by b window r as ()"); |
| expectedRows = new String[][]{{"1", "1"}, |
| {"4", "9"}, |
| {"9", "5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Check that row_number inside EXISTS works. |
| rs = s.executeQuery("SELECT * FROM t2 WHERE EXISTS " + |
| "(SELECT ROW_NUMBER() OVER () FROM t5)"); |
| expectedRows = new String[][]{{"1"},{"2"},{"3"},{"4"},{"5"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| /* |
| * Negative testing of Statements |
| */ |
| |
| // Missing required OVER () |
| assertStatementError( |
| LANG_SYNTAX_ERROR, s, |
| "select row_number() as r, * from t1 where t1.a > 2"); |
| |
| // Illegal where clause, r not a named column of t1. |
| assertStatementError( |
| LANG_COLUMN_NOT_FOUND, s, |
| "select row_number() over () as r, a from t1 where r < 3"); |
| |
| // Illegal use of asterisk with another column identifier. |
| assertStatementError( |
| LANG_SYNTAX_ERROR, s, |
| "select row_number() over () as r, * from t1 where t1.a > 2"); |
| |
| // Order by in window specification |
| assertStatementError( |
| NOT_IMPLEMENTED, |
| s, |
| "select row_number() over (order by i) as r from t1"); |
| |
| // Other window function than row_number: |
| assertStatementError(NOT_IMPLEMENTED, |
| s, |
| "select max(i) over () from t1"); |
| |
| // Illegal context: WHERE, cf. SQL 2003, section 7.8 SR 2 |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "select * from t4 where row_number() over () > 3"); |
| |
| // But nested inside a subquery it should work: |
| rs = s.executeQuery("select * from t2 where x in " + |
| " (select row_number() over () from t4)"); |
| expectedRows = new String[][]{{"1"},{"2"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Illegal context: GROUP BY |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "select * from t4 group by row_number() over ()"); |
| |
| // But nested inside a subquery it should work. |
| // Fails: DERBY-4403, enable this test when that issue is fixed. |
| // rs = s.executeQuery("select * from t4 group by a + " + |
| // "(select row_number() over () from t4 where a=10)"); |
| // JDBC.assertEmpty(rs); |
| |
| |
| // Illegal context: HAVING, cf. SQL 2003, section 7.10 SR 4 |
| assertStatementError( |
| LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "select * from t4 group by a having b = row_number() over ()"); |
| |
| // But nested inside a subquery it should work: |
| rs = s.executeQuery( |
| "select sum(a) from t5 group by b " + |
| " having b = (select row_number() over () + 3 " + |
| " from t5 where a=1)"); |
| expectedRows = new String[][]{{"9"}}; |
| |
| |
| // Illegal context: VALUES |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "values row_number() over ()"); |
| |
| // But nested inside a subquery it should work: |
| rs = s.executeQuery("values 3 + " + |
| "(select row_number() over () from t2 where x=1)"); |
| expectedRows = new String[][]{{"4"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| // Illegal context: Aggregate function, cf. SQL 2003, section 10.9 SR 7 |
| // a) |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "select sum(row_number() over ()) from t4"); |
| |
| // Illegal context: JOIN ON clause, cf. SQL 2003, section 7.7 SR 5 |
| assertStatementError( |
| LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "select * from t4 t_1 join t4 t_2 on " + |
| " t_1.a = row_number() over () + t_2.a"); |
| |
| // DERBY-6565: NPE before |
| assertStatementError( |
| LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "update t3 set y = y - row_number() over ()"); |
| |
| // DERBY-6688: subquery using SubqueryNode rather than FromSubquery |
| // had problems with presence of window function in order by. |
| |
| JDBC.assertFullResultSet(s.executeQuery("select * from t3"), |
| new String[][]{{"4"},{"5"},{"6"},{"7"},{"8"}}); |
| |
| // failed prior to DERBY-6688 |
| s.executeUpdate( |
| "update t3 set y = y - " + |
| " (select y from t3 order by row_number() over () " + |
| " fetch first 1 row only)"); |
| JDBC.assertFullResultSet(s.executeQuery("select * from t3"), |
| new String[][]{{"0"},{"1"},{"2"},{"3"},{"4"}}); |
| |
| // Used to work before |
| JDBC.assertFullResultSet(s.executeQuery( |
| "select * from " + |
| " (select y from t3 order by row_number() over () " + |
| " fetch first 1 row only) tt"), |
| new String[][]{{"0"}}); |
| |
| // failed prior to DERBY-6688 |
| JDBC.assertFullResultSet(s.executeQuery( |
| "select * from t3 where y = " + |
| " (select y from t3 order by row_number() over () " + |
| " fetch first row only)"), |
| new String[][]{{"0"}}); |
| |
| // DERBY-6689: ArrayIndexOutOfBoundsException before |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "merge into t2 using t3 on (t2.x=t3.y) when not matched then " + |
| " insert values (row_number() over ())"); |
| |
| // DERBY-6691: NPE before |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "call syscs_util.syscs_compress_table(" + |
| " 'APP', 'T2', row_number() over ())"); |
| |
| // DERBY-6690: a window function in generated clause was not detected |
| // before |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "create table t (x int generated always as " + |
| " (row_number() over ()))"); |
| |
| assertStatementError(LANG_WINDOW_FUNCTION_CONTEXT_ERROR, |
| s, |
| "alter table t2 add column foo int generated always as " + |
| " (row_number() over ())"); |
| } |
| |
| |
| public void testMetaData() |
| throws SQLException { |
| |
| if (JDBC.vmSupportsJSR169()) { |
| // does not support metadata |
| return; |
| } |
| |
| Statement s = createStatement(); |
| ResultSet rs = s.executeQuery( |
| "select row_number() over () from sys.systables"); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| |
| assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(1)); |
| } |
| |
| |
| private String makeString(int len) |
| { |
| StringBuffer buf = new StringBuffer(len); |
| for (int i = 0; i < len; i++) |
| buf.append('a'); |
| return buf.toString(); |
| } |
| /** |
| * Basic test of GROUP BY ROLLUP capability. |
| * |
| * This test case has a few basic tests of GROUP BY ROLLUP, both |
| * positive and negative tests. |
| */ |
| public void testGroupByRollup() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| s.executeUpdate("create table ru (a int, b int, c int, d int)"); |
| s.executeUpdate("insert into ru values (1,1,1,1), (1,2,3,4),"+ |
| "(1,1,2,2), (4,3,2,1), (4,4,4,4)"); |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select a,b,c,sum(d) from ru group by rollup(a,b,c)"), |
| new String[][]{ |
| {"1","1","1","1"}, |
| {"1","1","2","2"}, |
| {"1","2","3","4"}, |
| {"4","3","2","1"}, |
| {"4","4","4","4"}, |
| {"1","1",null,"3"}, |
| {"1","2",null,"4"}, |
| {"4","3",null,"1"}, |
| {"4","4",null,"4"}, |
| {"1",null,null,"7"}, |
| {"4",null,null,"5"}, |
| {null,null,null,"12"}}); |
| JDBC.assertFullResultSet( s.executeQuery( |
| "select count(*) from ru group by mod(a,b)"), |
| new String[][]{ {"3"},{"2"}}); |
| |
| // Try a few negative tests: |
| assertStatementError("42X04", s, |
| "select a,b,c,sum(d) from ru group by rollup"); |
| assertStatementError("42X01", s, |
| "select a,b,c,sum(d) from ru group by rollup("); |
| assertStatementError("42X01", s, |
| "select a,b,c,sum(d) from ru group by rollup)"); |
| assertStatementError("42X01", s, |
| "select a,b,c,sum(d) from ru group by rollup()"); |
| |
| s.executeUpdate("drop table ru"); |
| s.close(); |
| } |
| /** |
| * Verify that ROLLUP can still be used as the name of a column or table. |
| */ |
| public void testRollupReservedWord() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| s.executeUpdate("create table t_roll(rollup int, x int)"); |
| JDBC.assertEmpty( s.executeQuery( |
| "select rollup, sum(x) from t_roll group by rollup")); |
| JDBC.assertEmpty( s.executeQuery( |
| "select count(*) from t_roll group by mod(rollup,x)")); |
| JDBC.assertEmpty( s.executeQuery( |
| "select count(*) from t_roll group by mod(x,rollup)")); |
| s.executeUpdate("create table rollup(a int, x int)"); |
| JDBC.assertEmpty( s.executeQuery("select a, x from rollup")); |
| s.executeUpdate("insert into rollup(a,x) values(1,2)"); |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select a,sum(x) from rollup group by rollup(a)"), |
| new String[][]{ |
| {"1","2"}, {null,"2"}}); |
| s.executeUpdate("drop table rollup"); |
| s.executeUpdate("drop table t_roll"); |
| s.close(); |
| } |
| /** |
| * Verify that non-aggregate columns are returned as NULLABLE if ROLLUP. |
| * |
| * If a GROUP BY ROLLUP is used, the un-aggregated columns may contain |
| * NULL values, so we need to verify that the DatabaseMetadata returns |
| * the right values for the nullability of the columns. |
| */ |
| public void testRollupColumnNullability() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| s.executeUpdate( |
| "create table t_notnull(a int not null, b int," + |
| " c int not null, d int)"); |
| ResultSet rs = s.executeQuery( |
| "select a,b,c,sum(d) from t_notnull group by rollup(a,b,c)"); |
| JDBC.assertNullability(rs, |
| new boolean[]{true, true, true, true}); |
| rs.close(); |
| |
| rs = s.executeQuery( |
| "select 1,2,3,sum(d) from t_notnull group by rollup(1,2,3)"); |
| JDBC.assertNullability(rs, |
| new boolean[]{true, true, true, true}); |
| rs.close(); |
| |
| s.executeUpdate("drop table t_notnull"); |
| s.close(); |
| } |
| |
| /** |
| * Verify the behavior of GROUP BY ROLLUP for empty result sets. |
| */ |
| public void testRollupEmptyTables() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| s.executeUpdate("create table ru (a int, b int, c int, d int)"); |
| |
| JDBC.assertEmpty( s.executeQuery("select sum(a) from ru group by b")); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select sum(a) from ru"), (String)null); |
| s.executeUpdate("insert into ru values (1,1,1,1), (1,2,3,4),"+ |
| "(1,1,2,2), (4,3,2,1), (4,4,4,4)"); |
| JDBC.assertEmpty( s.executeQuery( |
| "select b, sum(a) from ru where 1<>1 group by rollup(b)")); |
| |
| s.executeUpdate("drop table ru"); |
| s.close(); |
| } |
| |
| /** |
| * A ROLLUP case suggested by Dag in 1-sep-2009 comment on DERBY-3002 |
| */ |
| public void testRollupNullabilityCasts() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| s.executeUpdate("create table t(c varchar(2) not null," + |
| "c2 varchar(2), i integer)"); |
| s.executeUpdate("insert into t values('aa',null,null)"); |
| s.executeUpdate("insert into t values('bb',null,null)"); |
| String [][]rows = |
| new String[][]{ |
| {"aa",null,null}, |
| {"aa",null,null}, |
| {null,null,null}, |
| {"bb",null,null}, |
| {"bb",null,null}}; |
| JDBC.assertUnorderedResultSet( |
| s.executeQuery("select c,c2,sum(i) from t group by rollup(c,c2)"), |
| rows); |
| JDBC.assertUnorderedResultSet(s.executeQuery( |
| "select cast(c as varchar(2)),c2,sum(i) from t " + |
| "group by rollup(c,c2)"), |
| rows); |
| /* FIXME -- this test currently fails due to improper handling of |
| the nullability of the result columns. |
| JDBC.assertUnorderedResultSet(s.executeQuery( |
| "select cast(x as varchar(2)),y,z from " + |
| " (select c,c2,sum(i) from t " + |
| " group by rollup (c,c2)) t(x,y,z)"), |
| rows); |
| */ |
| |
| s.executeUpdate("drop table t"); |
| s.close(); |
| } |
| |
| /** |
| * Verify the behavior of GROUP BY ROLLUP when it can use a covering index. |
| */ |
| public void testRollupOfCoveringIndex() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| s.executeUpdate("create table ru (a int,b int,c int,d varchar(1000))"); |
| s.executeUpdate("create index ru_idx on ru(a,b,c)"); |
| PreparedStatement ps = prepareStatement( |
| "insert into ru (a,b,c,d) values (?,?,?,?)"); |
| for (int i = 0; i < 100; i++) |
| { |
| ps.setInt(1, (i%5)); |
| ps.setInt(2, 2*i); |
| ps.setInt(3, 100+i); |
| ps.setString(4, makeString(900)); |
| ps.executeUpdate(); |
| } |
| ps.close(); |
| // FIXME |
| //dumpIt(s, 2, "select a,sum(c) from ru group by a"); |
| //dumpIt(s, 3, "select a,b,sum(c) from ru group by a,b"); |
| //dumpIt(s, 3, "select a,b,sum(c) from ru group by rollup(a,b)"); |
| s.executeUpdate("drop table ru"); |
| s.close(); |
| } |
| private void dumpIt(Statement s, int cols, String sql) |
| throws SQLException |
| { |
| System.out.println(sql); |
| ResultSet rs = s.executeQuery(sql); |
| while (rs.next()) |
| { |
| StringBuffer buf = new StringBuffer(); |
| for (int i = 1; i <= cols; i++) |
| { |
| if (i > 1) |
| buf.append(","); |
| buf.append(rs.getString(i)); |
| } |
| System.out.println(buf.toString()); |
| } |
| rs.close(); |
| } |
| /* |
| * Various GROUP BY tests, with and without ROLLUP. |
| */ |
| public void testGroupByWithAndWithoutRollup() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| // A very simple set of master-detail ORDER and ORDER_ITEM tables, |
| // with some fake customer data: |
| s.executeUpdate( |
| "create table orders(order_id int primary key," + |
| " customer varchar(10)," + |
| " order_date date, " + |
| " shipping int)"); |
| s.executeUpdate( |
| "create table order_items(item_id int primary key," + |
| " order_id int," + |
| " order_item varchar(10), " + |
| " cost int)"); |
| s.executeUpdate( |
| "create table customers(customer varchar(10) primary key," + |
| " name varchar(100), city varchar(100), state varchar(2))"); |
| s.executeUpdate("insert into customers values " + |
| "('ABC','ABC Corporation','ABC City', 'AB')," + |
| "('DEF','DEF, Inc.', 'DEFburg', 'DE')"); |
| s.executeUpdate("insert into orders values(1,'ABC','2009-01-01',40)"); |
| s.executeUpdate("insert into orders values(2,'ABC','2009-01-02',30)"); |
| s.executeUpdate("insert into orders values(3,'ABC','2009-01-03',25)"); |
| s.executeUpdate("insert into orders values(4,'DEF','2009-01-02',10)"); |
| s.executeUpdate("insert into order_items values(1,1,'Item A',100)"); |
| s.executeUpdate("insert into order_items values(2,1,'Item B',150)"); |
| s.executeUpdate("insert into order_items values(3,2,'Item C',125)"); |
| s.executeUpdate("insert into order_items values(4,2,'Item B',50)"); |
| s.executeUpdate("insert into order_items values(5,2,'Item H',200)"); |
| s.executeUpdate("insert into order_items values(6,3,'Item X',100)"); |
| s.executeUpdate("insert into order_items values(7,4,'Item Y',50)"); |
| s.executeUpdate("insert into order_items values(8,4,'Item Z',300)"); |
| // Joining the two tables produces one row per order item: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.order_id, o.customer, o.order_date, " + |
| "o.shipping, od.item_id, od.order_item, od.cost " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id"), |
| new String[][]{ |
| {"1","ABC","2009-01-01","40","1","Item A","100"}, |
| {"1","ABC","2009-01-01","40","2","Item B","150"}, |
| {"2","ABC","2009-01-02","30","3","Item C","125"}, |
| {"2","ABC","2009-01-02","30","4","Item B","50"}, |
| {"2","ABC","2009-01-02","30","5","Item H","200"}, |
| {"3","ABC","2009-01-03","25","6","Item X","100"}, |
| {"4","DEF","2009-01-02","10","7","Item Y","50"}, |
| {"4","DEF","2009-01-02","10","8","Item Z","300"}, |
| }); |
| // Grouping the items by customer to compute items/customer: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by o.customer"), |
| new String[][]{ |
| {"ABC","6"}, |
| {"DEF","2"}, |
| }); |
| // Also include the total cost per customer: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " sum(od.cost) as order_total " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by o.customer"), |
| new String[][]{ |
| {"ABC","6","725"}, |
| {"DEF","2","350"}, |
| }); |
| // ROLLUP the items and costs to grand totals: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " sum(od.cost) as order_total " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by ROLLUP(o.customer)"), |
| new String[][]{ |
| {"ABC","6","725"}, |
| {"DEF","2","350"}, |
| {null,"8","1075"}, |
| }); |
| // Show a usage of Count(distinct) to compute the orders/customer, |
| // which is not the same as the items/customer: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " count(distinct o.order_id) as orders_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by o.customer"), |
| new String[][]{ |
| {"ABC","6","3"}, |
| {"DEF","2","1"}, |
| }); |
| // ROLLUP should work for the distinct count, too: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " count(distinct o.order_id) as orders_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by ROLLUP(o.customer)"), |
| new String[][]{ |
| {"ABC","6","3"}, |
| {"DEF","2","1"}, |
| {null,"8","4"}, |
| }); |
| // can we compute the total shipping per customer: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " sum(od.cost) as order_total, " + |
| " count(distinct o.order_id) as orders_per_customer, " + |
| " sum(o.shipping) as shipping_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by o.customer"), |
| new String[][]{ |
| {"ABC","6","725","3","195"}, |
| {"DEF","2","350","1","20"}, |
| }); |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " sum(od.cost) as order_total, " + |
| " count(distinct o.order_id) as orders_per_customer, " + |
| " sum(o.shipping) as shipping_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by ROLLUP(o.customer)"), |
| new String[][]{ |
| {"ABC","6","725","3","195"}, |
| {"DEF","2","350","1","20"}, |
| {null,"8","1075","4","215"}, |
| }); |
| // Show a usage of distinct shipping aggregate, similar to the |
| // distinct count aggregate: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " sum(od.cost) as order_total, " + |
| " count(distinct o.order_id) as orders_per_customer, " + |
| " sum(distinct o.shipping) as shipping_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by o.customer"), |
| new String[][]{ |
| {"ABC","6","725","3","95"}, |
| {"DEF","2","350","1","10"}, |
| }); |
| // Demonstrate some of the dangers of using distinct aggregates. |
| // Duplicate SUM values may be real duplicates from the data, not |
| // from duplicate-producing master-detail joins. The COUNT changes |
| // from 1 to 2 for customer DEF, but the shipping_per_customer is |
| // still 10, which is logically wrong (there are 2 DEF orders, each |
| // with value 10, so we "expected" 20 for shipping_per_customer). |
| s.executeUpdate("insert into orders values(5,'DEF','2009-01-04',10)"); |
| s.executeUpdate("insert into order_items values(9,5,'Item J',125)"); |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " sum(od.cost) as order_total, " + |
| " count(distinct o.order_id) as orders_per_customer, " + |
| " sum(distinct o.shipping) as shipping_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by o.customer"), |
| new String[][]{ |
| {"ABC","6","725","3","95"}, |
| {"DEF","3","475","2","10"}, |
| }); |
| // Same as before, but with ROLLUP: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.Customer, count(*) as items_per_customer, " + |
| " sum(od.cost) as order_total, " + |
| " count(distinct o.order_id) as orders_per_customer, " + |
| " sum(distinct o.shipping) as shipping_per_customer " + |
| " from orders o inner join order_items od " + |
| " on o.order_id = od.order_id " + |
| " group by ROLLUP(o.customer)"), |
| new String[][]{ |
| {"ABC","6","725","3","95"}, |
| {"DEF","3","475","2","10"}, |
| {null,"9","1200","5","105"}, |
| }); |
| // Produce the results we expected by constructing a sub-query: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by order_id"), |
| new String[][]{ |
| {"1","2","250"}, |
| {"2","3","375"}, |
| {"3","1","100"}, |
| {"4","2","350"}, |
| {"5","1","125"}, |
| }); |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by ROLLUP(order_id)"), |
| new String[][]{ |
| {"1","2","250"}, |
| {"2","3","375"}, |
| {"3","1","100"}, |
| {"4","2","350"}, |
| {"5","1","125"}, |
| {null,"9","1200"}, |
| }); |
| // ... then encapsulate that sub-select with a join to the orders: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.order_id, o.Customer, o.Shipping, " + |
| " d.items_per_order, d.order_total " + |
| " from orders o inner join (" + |
| " select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by order_id " + |
| " ) d on o.order_id = d.order_id"), |
| new String[][]{ |
| {"1","ABC","40","2","250"}, |
| {"2","ABC","30","3","375"}, |
| {"3","ABC","25","1","100"}, |
| {"4","DEF","10","2","350"}, |
| {"5","DEF","10","1","125"}, |
| }); |
| // ... and group *THAT* join, in turn, by customer, to get the |
| // correct values of shipping_per_customer and items_per_customer. |
| // Note that total_per_customer is a SUM(SUM()), while |
| // items_per_customer is a SUM(COUNT()). And no DISTINCT needed. |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.customer, count(*) as orders_per_customer, " + |
| " sum(o.shipping) as shipping_per_customer, " + |
| " sum(d.items_per_order) as items_per_customer, " + |
| " sum(d.order_total) as total_per_customer " + |
| " from orders o inner join (" + |
| " select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by order_id " + |
| " ) d on o.order_id = d.order_id " + |
| " group by o.customer"), |
| new String[][]{ |
| {"ABC","3","95","6","725"}, |
| {"DEF","2","20","3","475"}, |
| }); |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.customer, count(*) as orders_per_customer, " + |
| " sum(o.shipping) as shipping_per_customer, " + |
| " sum(d.items_per_order) as items_per_customer, " + |
| " sum(d.order_total) as total_per_customer " + |
| " from orders o inner join (" + |
| " select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by order_id " + |
| " ) d on o.order_id = d.order_id " + |
| " group by ROLLUP(o.customer)"), |
| new String[][]{ |
| {"ABC","3","95","6","725"}, |
| {"DEF","2","20","3","475"}, |
| {null,"5","115","9","1200"}, |
| }); |
| // Include customer address information. First by joining and grouping: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select o.customer, c.name, c.city, c.state, " + |
| " count(*) as orders_per_customer, " + |
| " sum(o.shipping) as shipping_per_customer, " + |
| " sum(d.items_per_order) as items_per_customer, " + |
| " sum(d.order_total) as total_per_customer " + |
| " from orders o inner join (" + |
| " select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by order_id " + |
| " ) d on o.order_id = d.order_id " + |
| " inner join customers c on o.customer = c.customer " + |
| " group by ROLLUP(o.customer,c.name, c.city,c.state)"), |
| new String[][]{ |
| {"ABC","ABC Corporation","ABC City","AB","3","95","6","725"}, |
| {"DEF","DEF, Inc.","DEFburg","DE","2","20","3","475"}, |
| {"ABC","ABC Corporation","ABC City",null,"3","95","6","725"}, |
| {"DEF","DEF, Inc.","DEFburg",null,"2","20","3","475"}, |
| {"ABC","ABC Corporation",null,null,"3","95","6","725"}, |
| {"DEF","DEF, Inc.",null,null,"2","20","3","475"}, |
| {"ABC",null,null,null,"3","95","6","725"}, |
| {"DEF",null,null,null,"2","20","3","475"}, |
| {null,null,null,null,"5","115","9","1200"}, |
| }); |
| // Then, alternately, by sub-selecting and grouping: |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select c.customer, c.name, c.city, c.state, " + |
| " o.orders_per_customer, o.shipping_per_customer, " + |
| " o.items_per_customer, o.total_per_customer " + |
| " from ( " + |
| " select o.customer, count(*) as orders_per_customer, " + |
| " sum(o.shipping) as shipping_per_customer, " + |
| " sum(d.items_per_order) as items_per_customer, " + |
| " sum(d.order_total) as total_per_customer " + |
| " from orders o inner join (" + |
| " select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by order_id " + |
| " ) d on o.order_id = d.order_id " + |
| " group by o.customer) o " + |
| " inner join customers c on o.customer = c.customer"), |
| new String[][]{ |
| {"ABC","ABC Corporation","ABC City","AB","3","95","6","725"}, |
| {"DEF","DEF, Inc.","DEFburg","DE","2","20","3","475"}, |
| }); |
| // Note that we can put the ROLLUP in the sub-query, but then we |
| // need to outer-join with the customers table since the rollup |
| // results will have NULL in the join key. |
| JDBC.assertUnorderedResultSet( s.executeQuery( |
| "select c.customer, c.name, c.city, c.state, " + |
| " o.orders_per_customer, o.shipping_per_customer, " + |
| " o.items_per_customer, o.total_per_customer " + |
| " from ( " + |
| " select o.customer, count(*) as orders_per_customer, " + |
| " sum(o.shipping) as shipping_per_customer, " + |
| " sum(d.items_per_order) as items_per_customer, " + |
| " sum(d.order_total) as total_per_customer " + |
| " from orders o inner join (" + |
| " select order_id, count(*) as Items_per_order, " + |
| " sum(cost) as Order_total "+ |
| " from order_items " + |
| " group by order_id " + |
| " ) d on o.order_id = d.order_id " + |
| " group by ROLLUP(o.customer)) o " + |
| " left outer join customers c on o.customer = c.customer"), |
| new String[][]{ |
| {"ABC","ABC Corporation","ABC City","AB","3","95","6","725"}, |
| {"DEF","DEF, Inc.","DEFburg","DE","2","20","3","475"}, |
| {null,null,null,null,"5","115","9","1200"}, |
| }); |
| |
| s.close(); |
| } |
| } |
