| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.OffsetFetchNextTest |
| |
| 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.ParameterMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Types; |
| 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; |
| |
| /** |
| * Test {@code <result offset clause>} and {@code <fetch first clause>}. |
| */ |
| public class OffsetFetchNextTest extends BaseJDBCTestCase { |
| |
| private final static String LANG_FORMAT_EXCEPTION = "22018"; |
| private final static String LANG_INTEGER_LITERAL_EXPECTED = "42X20"; |
| private final static String LANG_INVALID_ROW_COUNT_FIRST = "2201W"; |
| private final static String LANG_INVALID_ROW_COUNT_OFFSET = "2201X"; |
| private final static String LANG_MISSING_PARMS = "07000"; |
| private final static String LANG_SYNTAX_ERROR = "42X01"; |
| private final static String LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL = "2201Z"; |
| |
| private final static String PERCENT_TOKEN = "%"; |
| |
| // flavors of SQL Standard syntax |
| private final static String FIRST_ROWS_ONLY = "fetch first % rows only"; |
| private final static String FIRST_ROW_ONLY = "fetch first % row only"; |
| private final static String NEXT_ROWS_ONLY = "fetch next % rows only"; |
| |
| // variants |
| private final static int SQL_STANDARD_VARIANT = 0; |
| private final static int JDBC_VARIANT = SQL_STANDARD_VARIANT + 1; |
| private final static int VARIANT_COUNT = JDBC_VARIANT + 1; |
| |
| public OffsetFetchNextTest(String name) { |
| super(name); |
| } |
| |
| public static Test suite() { |
| BaseTestSuite suite = new BaseTestSuite("OffsetFetchNextTest"); |
| |
| suite.addTest( |
| baseSuite("OffsetFetchNextTest:embedded")); |
| suite.addTest( |
| TestConfiguration.clientServerDecorator( |
| baseSuite("OffsetFetchNextTest:client"))); |
| |
| return suite; |
| } |
| |
| public static Test baseSuite(String suiteName) { |
| return new CleanDatabaseTestSetup( |
| new BaseTestSuite(OffsetFetchNextTest.class, |
| suiteName)) { |
| @Override |
| protected void decorateSQL(Statement s) |
| throws SQLException { |
| createSchemaObjects(s); |
| } |
| }; |
| } |
| |
| |
| /** |
| * Creates tables used by the tests (never modified, we use rollback after |
| * changes). |
| */ |
| private static void createSchemaObjects(Statement st) throws SQLException |
| { |
| // T1 (no indexes) |
| st.executeUpdate("create table t1 (a int, b bigint)"); |
| st.executeUpdate("insert into t1 (a, b) " + |
| "values (1,1), (1,2), (1,3), (1,4), (1,5)"); |
| |
| // T2 (primary key) |
| st.executeUpdate("create table t2 (a int primary key, b bigint)"); |
| st.executeUpdate("insert into t2 (a, b) " + |
| "values (1,1), (2,1), (3,1), (4,1), (5,1)"); |
| |
| // T3 (primary key + secondary key) |
| st.executeUpdate("create table t3 (a int primary key, " + |
| " b bigint unique)"); |
| st.executeUpdate("insert into t3 (a, b) " + |
| "values (1,1), (2,2), (3,3), (4,4), (5,5)"); |
| } |
| |
| /** |
| * Negative tests. Test various invalid OFFSET and FETCH NEXT clauses. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testErrors() throws SQLException |
| { |
| Statement st = createStatement(); |
| |
| String stub = "select * from t1 %"; |
| |
| // Wrong range in row count argument |
| vetStatement( st, LANG_INVALID_ROW_COUNT_OFFSET, stub, FIRST_ROWS_ONLY, "-1", null, null ); |
| |
| vetStatement( st, LANG_SYNTAX_ERROR, stub, FIRST_ROWS_ONLY, "-?", null, null ); |
| |
| assertStatementError(LANG_INVALID_ROW_COUNT_FIRST, st, |
| "select * from t1 fetch first 0 rows only"); |
| |
| vetStatement( st, LANG_INVALID_ROW_COUNT_FIRST, stub, FIRST_ROWS_ONLY, null, "-1", null ); |
| |
| // Wrong type in row count argument |
| vetStatement( st, LANG_INTEGER_LITERAL_EXPECTED, stub, FIRST_ROWS_ONLY, null, "3.14", null ); |
| |
| // Wrong order of clauses |
| assertStatementError(LANG_SYNTAX_ERROR, st, |
| "select * from t1 " + |
| "fetch first 0 rows only offset 0 rows"); |
| assertStatementError(LANG_SYNTAX_ERROR, st, |
| "select * from t1 { offset 0 limit 0 }"); |
| } |
| |
| |
| /** |
| * Positive tests. Check that the new keyword OFFSET introduced is not |
| * reserved so we don't risk breaking existing applications. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testNewKeywordNonReserved() throws SQLException |
| { |
| setAutoCommit(false); |
| prepareStatement("select a,b as offset from t1 offset 0 rows"); |
| prepareStatement("select a,b as limit from t1 offset 0 rows"); |
| |
| // Column and table correlation name usage |
| prepareStatement("select a,b from t1 as offset"); |
| prepareStatement("select a,b from t1 as limit"); |
| |
| prepareStatement("select a,b offset from t1 offset"); |
| prepareStatement("select a,b limit from t1 limit"); |
| |
| prepareStatement("select a,b offset from t1 offset +2 rows"); |
| |
| prepareStatement("select a offset,b from t1 offset ? rows"); |
| |
| prepareStatement("select offset.a, offset.b offset from t1 as offset offset ? rows"); |
| prepareStatement("select limit.a, limit.b offset from t1 as limit offset ? rows"); |
| |
| // DERBY-4562 |
| Statement s = createStatement(); |
| s.executeUpdate("create table t4562(i int, offset int)"); |
| ResultSet rs = s.executeQuery( |
| "select * from t4562 where i > 0 and offset + i < 0 offset 2 rows"); |
| rs.next(); |
| |
| rs = s.executeQuery( |
| "select * from t4562 where i > 0 and offset - i < 0 offset 2 rows"); |
| rs.next(); |
| |
| rs = s.executeQuery( |
| "select * from t4562 where i > 0 and offset * i < 0 offset 2 rows"); |
| rs.next(); |
| |
| rs.close(); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * Positive tests. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testOffsetFetchFirstReadOnlyForwardOnlyRS() throws SQLException |
| { |
| Statement stm = createStatement(); |
| |
| /* |
| * offset 0 rows (a no-op) |
| */ |
| |
| vetStatement |
| ( |
| stm, null, "select a, b from t1%", FIRST_ROWS_ONLY, "0", null, |
| new String [][] { {"1","1"}, {"1","2"},{"1","3"}, {"1","4"},{"1","5"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t2%", FIRST_ROWS_ONLY, "0", null, |
| new String [][] { {"1","1"}, {"2","1"},{"3","1"}, {"4","1"},{"5","1"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t3%", FIRST_ROWS_ONLY, "0", null, |
| new String [][] { {"1","1"}, {"2","2"},{"3","3"}, {"4","4"},{"5","5"} } |
| ); |
| |
| /* |
| * offset 1 rows |
| */ |
| |
| vetStatement |
| ( |
| stm, null, "select a,b from t1%", FIRST_ROWS_ONLY, "1", null, |
| new String [][] { {"1","2"},{"1","3"}, {"1","4"},{"1","5"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t2%", FIRST_ROWS_ONLY, "1", null, |
| new String [][] { {"2","1"},{"3","1"}, {"4","1"},{"5","1"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t3%", FIRST_ROWS_ONLY, "1", null, |
| new String [][] { {"2","2"},{"3","3"}, {"4","4"},{"5","5"} } |
| ); |
| |
| /* |
| * offset 4 rows |
| */ |
| |
| vetStatement |
| ( |
| stm, null, "select a,b from t1%", FIRST_ROWS_ONLY, "4", null, |
| new String [][] { {"1","5"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t2%", FIRST_ROWS_ONLY, "4", null, |
| new String [][] { {"5","1"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t3%", FIRST_ROWS_ONLY, "4", null, |
| new String [][] { {"5","5"} } |
| ); |
| |
| /* |
| * offset 1 rows fetch 1 row. Use "next"/"rows" syntax |
| */ |
| vetStatement |
| ( |
| stm, null, "select a,b from t1%", FIRST_ROWS_ONLY, "1", "1", |
| new String [][] { {"1","2"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t2%", FIRST_ROWS_ONLY, "1", "1", |
| new String [][] { {"2","1"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t3%", FIRST_ROWS_ONLY, "1", "1", |
| new String [][] { {"2","2"} } |
| ); |
| |
| /* |
| * offset 1 rows fetch so many rows we drain rs row. Use "first"/"row" |
| * syntax |
| */ |
| vetStatement |
| ( |
| stm, null, "select a,b from t1%", FIRST_ROW_ONLY, "1", "10", |
| new String [][] { {"1","2"},{"1","3"}, {"1","4"},{"1","5"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t2%", FIRST_ROW_ONLY, "1", "10", |
| new String [][] { {"2","1"},{"3","1"}, {"4","1"},{"5","1"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t3%", FIRST_ROW_ONLY, "1", "10", |
| new String [][] { {"2","2"},{"3","3"}, {"4","4"},{"5","5"} } |
| ); |
| |
| /* |
| * offset so many rows that we see empty rs |
| */ |
| vetStatement |
| ( |
| stm, null, "select a,b from t1%", FIRST_ROW_ONLY, "10", null, |
| new String [][] { } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t2%", FIRST_ROW_ONLY, "10", null, |
| new String [][] { } |
| ); |
| vetStatement |
| ( |
| stm, null, "select a,b from t3%", FIRST_ROW_ONLY, "10", null, |
| new String [][] { } |
| ); |
| |
| /* |
| * fetch first/next row (no row count given) |
| */ |
| queryAndCheck( |
| stm, |
| "select a,b from t1 fetch first row only", |
| new String [][] {{"1","1"}}); |
| queryAndCheck( |
| stm, |
| "select a,b from t2 fetch next row only", |
| new String [][] {{"1","1"}}); |
| queryAndCheck( |
| stm, |
| "select a,b from t3 fetch next row only", |
| new String [][] {{"1","1"}}); |
| |
| /* |
| * Combine with order by asc |
| */ |
| queryAndCheck( |
| stm, |
| "select a,b from t1 order by b asc fetch first row only", |
| new String [][] {{"1","1"}}); |
| queryAndCheck( |
| stm, |
| "select a,b from t2 order by a asc fetch next row only", |
| new String [][] {{"1","1"}}); |
| queryAndCheck( |
| stm, |
| "select a,b from t3 order by a asc fetch next row only", |
| new String [][] {{"1","1"}}); |
| |
| /* |
| * Combine with order by desc. |
| */ |
| queryAndCheck( |
| stm, |
| // Note: use column b here since for t1 all column a values are the |
| // same and order can change after sorting, want unique row first |
| // in rs so we can test it. |
| "select a,b from t1 order by b desc fetch first row only", |
| new String [][] {{"1","5"}}); |
| queryAndCheck( |
| stm, |
| "select a,b from t2 order by a desc fetch next row only", |
| new String [][] {{"5","1"}}); |
| queryAndCheck( |
| stm, |
| "select a,b from t3 order by a desc fetch next row only", |
| new String [][] {{"5","5"}}); |
| |
| /* |
| * Combine with group by, order by. |
| */ |
| queryAndCheck( |
| stm, |
| "select max(a) from t1 group by b fetch first row only", |
| new String [][] {{"1"}}); |
| vetStatement |
| ( |
| stm, null, "select max(a) from t2 group by b %", FIRST_ROW_ONLY, "0", null, |
| new String [][] { {"5"} } |
| ); |
| vetStatement |
| ( |
| stm, null, "select max(a) from t3 group by b order by max(a) %", NEXT_ROWS_ONLY, null, "2", |
| new String [][] { {"1"},{"2"} } |
| ); |
| |
| /* |
| * Combine with union |
| */ |
| |
| vetStatement |
| ( |
| stm, null, "select * from t1 union all select * from t1 %", FIRST_ROW_ONLY, null, "2", |
| new String [][] { {"1","1"}, {"1","2"} } |
| ); |
| |
| /* |
| * Combine with join |
| */ |
| vetStatement |
| ( |
| stm, null, "select t2.b, t3.b from t2,t3 where t2.a=t3.a %", FIRST_ROW_ONLY, null, "2", |
| new String [][] { {"1","1"}, {"1","2"} } |
| ); |
| |
| stm.close(); |
| } |
| |
| |
| /** |
| * Positive tests. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testOffsetFetchFirstUpdatableForwardOnlyRS() throws SQLException |
| { |
| Statement stm = createStatement(ResultSet.TYPE_FORWARD_ONLY, |
| ResultSet.CONCUR_UPDATABLE); |
| ResultSet rs; |
| String[] variants; |
| |
| setAutoCommit(false); |
| |
| /* |
| * offset 0 rows (a no-op), update a row and verify result |
| */ |
| variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "0", null ); |
| for (String variant : variants) |
| { |
| rs = stm.executeQuery( variant ); |
| rs.next(); |
| rs.next(); // at row 2 |
| rs.updateInt(1, -rs.getInt(1)); |
| rs.updateRow(); |
| rs.close(); |
| |
| queryAndCheck( |
| stm, |
| "select a,b from t1", |
| new String [][] { |
| {"1","1"}, {"-1","2"},{"1","3"}, {"1","4"},{"1","5"}}); |
| |
| rollback(); |
| } |
| |
| /* |
| * offset 1 rows, update a row and verify result |
| */ |
| variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "1", null ); |
| for ( String variant : variants ) |
| { |
| rs = stm.executeQuery( variant ); |
| rs.next(); // at row 1, but row 2 of underlying rs |
| |
| rs.updateInt(1, -rs.getInt(1)); |
| rs.updateRow(); |
| rs.close(); |
| |
| queryAndCheck( |
| stm, |
| "select a,b from t1", |
| new String [][] { |
| {"1","1"}, {"-1","2"},{"1","3"}, {"1","4"},{"1","5"}}); |
| |
| rollback(); |
| } |
| |
| stm.close(); |
| } |
| |
| |
| /** |
| * Positive tests with scrollable read-only. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testOffsetFetchFirstReadOnlyScrollableRS() throws SQLException |
| { |
| Statement stm = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, |
| ResultSet.CONCUR_READ_ONLY); |
| ResultSet rs; |
| String[] variants; |
| |
| /* |
| * offset 0 rows (a no-op), update a row and verify result |
| */ |
| variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "0", null ); |
| for ( String variant : variants ) |
| { |
| rs = stm.executeQuery( variant ); |
| rs.next(); |
| rs.next(); // at row 2 |
| assertTrue(rs.getInt(2) == 2); |
| rs.close(); |
| } |
| |
| /* |
| * offset 1 rows, fetch 3 row, check that we have the right ones |
| */ |
| variants = makeVariants( "select * from t1 %", FIRST_ROWS_ONLY, "1", "3" ); |
| for ( String variant : variants ) |
| { |
| rs = stm.executeQuery( variant ); |
| rs.next(); |
| rs.next(); // at row 2, but row 3 of underlying rs |
| |
| assertTrue(rs.getInt(2) == 3); |
| |
| // Go backbards and update |
| rs.previous(); |
| assertTrue(rs.getInt(2) == 2); |
| |
| // Try some navigation and border conditions |
| rs.previous(); |
| assertTrue(rs.isBeforeFirst()); |
| rs.next(); |
| rs.next(); |
| rs.next(); |
| rs.next(); |
| assertTrue(rs.isAfterLast()); |
| } |
| |
| stm.close(); |
| } |
| |
| |
| /** |
| * Positive tests with SUR (Scrollable updatable result set). |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testOffsetFetchFirstUpdatableScrollableRS() throws SQLException |
| { |
| Statement stm = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, |
| ResultSet.CONCUR_UPDATABLE); |
| ResultSet rs; |
| String[] variants; |
| |
| setAutoCommit(false); |
| |
| /* |
| * offset 0 rows (a no-op), update a row and verify result |
| * also try the "for update" syntax so we see that it still works |
| */ |
| variants = makeVariants( "select * from t1 % for update", FIRST_ROWS_ONLY, "0", null ); |
| for (String variant : variants) |
| { |
| rs = stm.executeQuery( variant ); |
| rs.next(); |
| rs.next(); // at row 2 |
| rs.updateInt(1, -rs.getInt(1)); |
| rs.updateRow(); |
| rs.close(); |
| |
| queryAndCheck( |
| stm, |
| "select a,b from t1", |
| new String [][] { |
| {"1","1"}, {"-1","2"},{"1","3"}, {"1","4"},{"1","5"}}); |
| |
| rollback(); |
| } |
| |
| /* |
| * offset 1 rows, fetch 3 row, update some rows and verify result |
| */ |
| variants = makeVariants( "select * from t1 %", NEXT_ROWS_ONLY, "1", "3" ); |
| for ( String variant : variants ) |
| { |
| rs = stm.executeQuery( variant ); |
| rs.next(); |
| rs.next(); // at row 2, but row 3 of underlying rs |
| |
| rs.updateInt(1, -rs.getInt(1)); |
| rs.updateRow(); |
| |
| // Go backbards and update |
| rs.previous(); |
| rs.updateInt(1, -rs.getInt(1)); |
| rs.updateRow(); |
| |
| // Try some navigation and border conditions |
| rs.previous(); |
| assertTrue(rs.isBeforeFirst()); |
| rs.next(); |
| rs.next(); |
| rs.next(); |
| rs.next(); |
| assertTrue(rs.isAfterLast()); |
| |
| // Insert a row |
| rs.moveToInsertRow(); |
| rs.updateInt(1,42); |
| rs.updateInt(2,42); |
| rs.insertRow(); |
| |
| // Delete a row |
| rs.previous(); |
| rs.deleteRow(); |
| |
| // .. and see that a hole is left in its place |
| rs.previous(); |
| rs.next(); |
| assertTrue(rs.rowDeleted()); |
| |
| rs.close(); |
| |
| queryAndCheck( |
| stm, |
| "select a,b from t1", |
| new String [][] { |
| {"1","1"}, {"-1","2"},{"-1","3"},{"1","5"},{"42","42"}}); |
| rollback(); |
| } |
| |
| // Test with projection |
| variants = makeVariants( "select * from t1 where a + 1 < b%", NEXT_ROWS_ONLY, "1", null ); |
| for (String variant : variants) |
| { |
| rs = stm.executeQuery( variant ); |
| // should yield 2 rows |
| rs.absolute(2); |
| assertTrue(rs.getInt(2) == 5); |
| rs.updateInt(2, -5); |
| rs.updateRow(); |
| rs.close(); |
| |
| queryAndCheck( |
| stm, |
| "select a,b from t1", |
| new String [][] { |
| {"1","1"}, {"1","2"},{"1","3"},{"1","4"},{"1","-5"}}); |
| rollback(); |
| } |
| |
| stm.close(); |
| } |
| |
| |
| public void testValues() throws SQLException |
| { |
| Statement stm = createStatement(); |
| |
| vetStatement |
| ( |
| stm, null, "values 4%", FIRST_ROW_ONLY, null, "2", |
| new String [][] { {"4"} } |
| ); |
| |
| vetStatement |
| ( |
| stm, null, "values 4%", FIRST_ROW_ONLY, "1", null, |
| new String [][] { } |
| ); |
| |
| stm.close(); |
| } |
| |
| /** |
| * Positive tests, result set metadata |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testMetadata() throws SQLException |
| { |
| Statement stm = createStatement(); |
| ResultSet rs; |
| String[] variants; |
| |
| variants = makeVariants( "select * from t1%", NEXT_ROWS_ONLY, "1", null ); |
| for (String variant : variants) |
| { |
| rs = stm.executeQuery( variant ); |
| ResultSetMetaData rsmd= rs.getMetaData(); |
| int cnt = rsmd.getColumnCount(); |
| |
| String[] cols = new String[]{"A","B"}; |
| int[] types = {Types.INTEGER, Types.BIGINT}; |
| |
| for (int i=1; i <= cnt; i++) { |
| String name = rsmd.getColumnName(i); |
| int type = rsmd.getColumnType(i); |
| |
| assertTrue(name.equals(cols[i-1])); |
| assertTrue(type == types[i-1]); |
| } |
| |
| rs.close(); |
| } |
| |
| stm.close(); |
| } |
| |
| |
| /** |
| * Test that we see correct traces of the filtering in the statistics |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testRunTimeStatistics() throws SQLException |
| { |
| Statement stm = createStatement(); |
| ResultSet rs; |
| String[] variants; |
| |
| variants = makeVariants( "select a,b from t1%", NEXT_ROWS_ONLY, "2", null ); |
| for (String variant : variants) |
| { |
| stm.executeUpdate( "call syscs_util.syscs_set_runtimestatistics(1)" ); |
| |
| queryAndCheck( |
| stm, |
| variant, |
| new String [][] { |
| {"1","3"}, {"1","4"},{"1","5"}}); |
| |
| stm.executeUpdate( "call syscs_util.syscs_set_runtimestatistics(0)" ); |
| |
| rs = stm.executeQuery( "values syscs_util.syscs_get_runtimestatistics()" ); |
| rs.next(); |
| String plan = rs.getString(1); |
| |
| // Verify that the plan shows the filtering (2 rows of 3 seen): |
| assertTrue(plan.indexOf("Row Count (1):\n" + |
| "Number of opens = 1\n" + |
| "Rows seen = 3\n" + |
| "Rows filtered = 2") != -1); |
| |
| rs.close(); |
| } |
| |
| stm.close(); |
| } |
| |
| |
| /** |
| * Test against a bigger table |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testBigTable() throws SQLException |
| { |
| Statement stm = createStatement(); |
| |
| setAutoCommit(false); |
| |
| stm.executeUpdate("declare global temporary table session.t (i int) " + |
| "on commit preserve rows not logged"); |
| |
| PreparedStatement ps = |
| prepareStatement("insert into session.t values ?"); |
| |
| for (int i=1; i <= 100000; i++) { |
| ps.setInt(1, i); |
| ps.executeUpdate(); |
| |
| if (i % 10000 == 0) { |
| commit(); |
| } |
| } |
| |
| queryAndCheck( |
| stm, |
| "select count(*) from session.t", |
| new String [][] { |
| {"100000"}}); |
| |
| vetStatement |
| ( |
| stm, null, "select i from session.t%", FIRST_ROWS_ONLY, "99999", null, |
| new String [][] { {"100000"} } |
| ); |
| |
| stm.executeUpdate("drop table session.t"); |
| stm.close(); |
| } |
| |
| /** |
| * Test that the values of offset and fetch first are not forgotten if |
| * a {@code PreparedStatement} is executed multiple times (DERBY-4212). |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testRepeatedExecution() throws SQLException |
| { |
| PreparedStatement ps; |
| String[] variants; |
| |
| variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "2", "2" ); |
| for (String variant : variants) |
| { |
| ps = prepareStatement( variant ); |
| String[][] expected = {{"1", "3"}, {"1", "4"}}; |
| for (int i = 0; i < 10; i++) { |
| JDBC.assertFullResultSet(ps.executeQuery(), expected); |
| } |
| } |
| } |
| |
| /** |
| * Test dynamic arguments |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testDynamicArgs() throws SQLException |
| { |
| PreparedStatement ps; |
| String[] variants; |
| String[][] expected = null; |
| |
| // Check look-ahead also for ? in grammar since offset is not reserved |
| variants = makeVariants( "select * from t1%", NEXT_ROWS_ONLY, "?", null ); |
| for (String variant : variants) |
| { |
| ps = prepareStatement( variant ); |
| } |
| |
| |
| variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "?", "?" ); |
| for ( int j = 0; j < variants.length; j++ ) |
| { |
| // SQL Standard and JDBC limit/offset parameter orders are different |
| int offsetParam = ( j == SQL_STANDARD_VARIANT ) ? 1 : 2; |
| int fetchParam = ( j == SQL_STANDARD_VARIANT ) ? 2 : 1; |
| |
| expected = new String[][] {{"1", "3"}, {"1", "4"}}; |
| ps = prepareStatement( variants[ j ] ); |
| |
| // Check range errors |
| |
| ps.setInt( offsetParam, 0 ); |
| assertPreparedStatementError(LANG_MISSING_PARMS, ps); |
| |
| ps.setInt( offsetParam, -1 ); |
| ps.setInt( fetchParam, 2 ); |
| assertPreparedStatementError(LANG_INVALID_ROW_COUNT_OFFSET, ps); |
| |
| ps.setInt( offsetParam, 0 ); |
| ps.setInt( fetchParam, ( j == SQL_STANDARD_VARIANT ) ? 0 : -1 ); |
| assertPreparedStatementError(LANG_INVALID_ROW_COUNT_FIRST, ps); |
| |
| // Check non-integer values |
| try { |
| ps.setString( offsetParam, "aaa"); |
| } catch (SQLException e) { |
| assertSQLState(LANG_FORMAT_EXCEPTION, e); |
| } |
| |
| try { |
| ps.setString( fetchParam, "aaa"); |
| } catch (SQLException e) { |
| assertSQLState(LANG_FORMAT_EXCEPTION, e); |
| } |
| |
| |
| // A normal case |
| for (int i = 0; i < 2; i++) { |
| ps.setInt( offsetParam,2 ); |
| ps.setInt( fetchParam,2 ); |
| JDBC.assertFullResultSet(ps.executeQuery(), expected); |
| } |
| |
| // Now, note that since we now have different values for offset and |
| // fetch first, we also exercise reusing the result set for this |
| // prepared statement (i.e. the values are computed at execution time, |
| // not at result set generation time). Try long value for change. |
| ps.setLong( offsetParam, 1L ); |
| ps.setInt( fetchParam, 3 ); |
| expected = new String[][]{{"1", "2"}, {"1", "3"}, {"1", "4"}}; |
| JDBC.assertFullResultSet(ps.executeQuery(), expected); |
| |
| |
| // Try a large number |
| ps.setLong( offsetParam, Integer.MAX_VALUE * 2L ); |
| ps.setInt( fetchParam, 5 ); |
| JDBC.assertEmpty(ps.executeQuery()); |
| } |
| |
| // Mix of prepared and not |
| variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "?", "3" ); |
| for (String variant : variants) |
| { |
| ps = prepareStatement( variant ); |
| ps.setLong(1, 1L); |
| JDBC.assertFullResultSet(ps.executeQuery(), expected); |
| } |
| |
| variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "4", "?" ); |
| for (String variant : variants) |
| { |
| ps = prepareStatement( variant ); |
| ps.setLong(1, 1L); |
| JDBC.assertFullResultSet(ps.executeQuery(), new String[][]{{"1", "5"}}); |
| } |
| |
| // Mix of other dyn args and ours: |
| variants = makeVariants( "select * from t1 where a = ? order by b%", NEXT_ROWS_ONLY, "?", "3" ); |
| for (String variant : variants) |
| { |
| ps = prepareStatement( variant ); |
| ps.setInt(1, 1); |
| ps.setLong(2, 1L); |
| JDBC.assertFullResultSet(ps.executeQuery(), expected); |
| } |
| |
| variants = makeVariants( "select * from t1 where a = ? order by b%", NEXT_ROWS_ONLY, "1", "?" ); |
| for (String variant : variants) |
| { |
| ps = prepareStatement( variant ); |
| ps.setInt(1, 1); |
| ps.setLong(2, 2L); |
| expected = new String[][]{{"1", "2"}, {"1", "3"}}; |
| JDBC.assertFullResultSet(ps.executeQuery(), expected); |
| } |
| |
| // NULLs not allowed (Note: parameter metadata says "isNullable" for |
| // all ? args in Derby...) |
| variants = makeVariants( "select * from t1 order by b%", NEXT_ROWS_ONLY, "?", "?" ); |
| for ( int i = 0; i < variants.length; i++ ) |
| { |
| ps = prepareStatement( variants[ i ] ); |
| int offsetParam = ( i == SQL_STANDARD_VARIANT ) ? 1 : 2; |
| int fetchParam = ( i == SQL_STANDARD_VARIANT ) ? 2 : 1; |
| |
| ps.setNull( offsetParam, Types.BIGINT ); |
| ps.setInt( fetchParam, 2 ); |
| assertPreparedStatementError(LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL, ps); |
| |
| ps.setInt( offsetParam,1 ); |
| ps.setNull( fetchParam, Types.BIGINT ); |
| assertPreparedStatementError(LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL, ps); |
| |
| ps.close(); |
| } |
| } |
| |
| /** |
| * Test dynamic arguments |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testDynamicArgsMetaData() throws SQLException |
| { |
| |
| //since there is no getParameterMetaData() call available in JSR169 |
| //implementations, do not run this test if we are running JSR169 |
| if (JDBC.vmSupportsJSR169()) return; |
| |
| PreparedStatement ps; |
| String[] variants; |
| |
| variants = makeVariants( "select * from t1 where a = ? order by b%", NEXT_ROWS_ONLY, "?", "?" ); |
| for (String variant : variants) |
| { |
| ps = prepareStatement( variant ); |
| |
| ParameterMetaData pmd = ps.getParameterMetaData(); |
| int[] expectedTypes = { Types.INTEGER, Types.BIGINT, Types.BIGINT }; |
| |
| for (int i = 0; i < 3; i++) { |
| assertEquals("Unexpected parameter type", |
| expectedTypes[i], pmd.getParameterType(i+1)); |
| assertEquals("Derby ? args are nullable", |
| // Why is that? Cf. logic in ParameterNode.setType |
| ParameterMetaData.parameterNullable, |
| pmd.isNullable(i+1)); |
| } |
| ps.close(); |
| } |
| } |
| |
| /** |
| * Test some additional corner cases in JDBC limit/offset syntax. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testJDBCLimitOffset() throws SQLException |
| { |
| // LIMIT 0 is allowed. It means: everything from the OFFSET forward |
| PreparedStatement ps = prepareStatement( "select a from t2 order by a { limit ? }" ); |
| ps.setInt( 1, 0 ); |
| JDBC.assertFullResultSet |
| ( |
| ps.executeQuery(), |
| new String[][] { { "1" }, { "2" }, { "3" }, { "4" }, { "5" } } |
| ); |
| ps.close(); |
| |
| ps = prepareStatement( "select a from t2 order by a { limit ? offset 3 }" ); |
| ps.setInt( 1, 0 ); |
| JDBC.assertFullResultSet |
| ( |
| ps.executeQuery(), |
| new String[][] { { "4" }, { "5" } } |
| ); |
| ps.close(); |
| |
| // mix JDBC and SQL Standard syntax |
| ps = prepareStatement |
| ( |
| "select t.a from\n" + |
| "( select * from t2 order by a { limit 3 offset 1 } ) t,\n" + |
| "( select * from t3 order by a offset 2 rows fetch next 10 rows only ) s\n" + |
| "where t.a = s.a order by t.a" |
| ); |
| JDBC.assertFullResultSet |
| ( |
| ps.executeQuery(), |
| new String[][] { { "3" }, { "4" } } |
| ); |
| ps.close(); |
| } |
| |
| /** |
| * Run a statement with both SQL Standard and JDBC limit/offset syntax. Verify |
| * that we get the expected error or results. The statement has a % literal at the |
| * point where the offset/fetchFirst and limit/offset clauses are to be inserted. |
| */ |
| private void vetStatement |
| ( Statement stmt, String sqlState, String stub, String fetchFormat, String offset, String fetchFirst, String[][] expectedResults ) |
| throws SQLException |
| { |
| String[] variants = makeVariants( stub, fetchFormat, offset, fetchFirst ); |
| |
| for (String text : variants) |
| { |
| if ( sqlState != null ) |
| { |
| assertStatementError( sqlState, stmt, text ); |
| } |
| else |
| { |
| queryAndCheck( stmt, text, expectedResults ); |
| } |
| } |
| } |
| |
| /** |
| * Make the SQL Standard and JDBC limit/offset variants of a stub statement, |
| * plugging in the given offset and fetch count. |
| */ |
| private String[] makeVariants |
| ( String stub, String fetchFormat, String offset, String fetchFirst ) |
| { |
| String[] result = new String[ VARIANT_COUNT ]; |
| |
| result[ SQL_STANDARD_VARIANT ] = makeSQLStandardText( stub, fetchFormat, offset, fetchFirst ); |
| result[ JDBC_VARIANT ] = makeJDBCText( stub, offset, fetchFirst ); |
| |
| return result; |
| } |
| |
| /** |
| * Substitute the SQL Standard syntax into a stub statement, given an offset and fetch count. |
| */ |
| private String makeSQLStandardText |
| ( String stub, String fetchFormat, String offset, String fetchFirst ) |
| { |
| String sqlStandardText = ""; |
| |
| if ( offset != null ) |
| { |
| sqlStandardText = " offset " + offset + " rows "; |
| } |
| if ( fetchFirst != null ) |
| { |
| sqlStandardText = sqlStandardText + substitute( fetchFormat, PERCENT_TOKEN, fetchFirst ); |
| } |
| |
| sqlStandardText = substitute( stub, PERCENT_TOKEN, sqlStandardText ); |
| |
| println( sqlStandardText ); |
| |
| return sqlStandardText; |
| } |
| /** |
| * Substitute JDBC limit/offset syntax into a stub statement, given an offset and fetch count. |
| */ |
| private String makeJDBCText |
| ( String stub, String offset, String fetchFirst ) |
| { |
| String jdbcText = ""; |
| |
| if ( offset != null ) |
| { |
| jdbcText = " offset " + offset; |
| } |
| if ( fetchFirst != null ) |
| { |
| jdbcText = " limit " + fetchFirst + " " + jdbcText; |
| } |
| else |
| { |
| jdbcText = "limit 0 " + jdbcText; |
| } |
| |
| jdbcText = substitute( stub, PERCENT_TOKEN, " { " + jdbcText + " } " ); |
| |
| println( jdbcText ); |
| |
| return jdbcText; |
| } |
| |
| private String substitute( String stub, String token, String replacement ) |
| { |
| int substitutionIndex = stub.indexOf( token ); |
| if ( substitutionIndex < 0 ) { fail( "Bad stub: " + stub + ". Can't find token: " + token ); } |
| |
| String prefix = stub.substring( 0, substitutionIndex ); |
| String suffix = ( substitutionIndex == stub.length() - 1 ) ? |
| "" : stub.substring( substitutionIndex + 1, stub.length() ); |
| |
| return prefix + replacement + suffix; |
| } |
| |
| private void queryAndCheck( |
| Statement stm, |
| String queryText, |
| String [][] expectedRows) throws SQLException { |
| |
| ResultSet rs = stm.executeQuery(queryText); |
| JDBC.assertFullResultSet(rs, expectedRows); |
| } |
| |
| } |
