| /* |
| * Class org.apache.derbyTesting.functionTests.tests.lang.OrderByAndOffsetFetchInSubqueries |
| * |
| * 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.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.RuntimeStatisticsParser; |
| import org.apache.derbyTesting.junit.SQLUtilities; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Tests for DERBY-4397 Allow {@code ORDER BY} in subqueries |
| * and DERBY-4398 Allow {@code OFFSET/FETCH} in subqueries. |
| */ |
| public class OrderByAndOffsetFetchInSubqueries extends BaseJDBCTestCase { |
| |
| final static String SYNTAX_ERROR = "42X01"; |
| final static String COLUMN_NOT_FOUND = "42X04"; |
| final static String COLUMN_OUT_OF_RANGE = "42X77"; |
| final static String ORDER_BY_COLUMN_NOT_FOUND = "42X78"; |
| |
| public OrderByAndOffsetFetchInSubqueries(String name) { |
| super(name); |
| } |
| |
| /** |
| * Construct top level suite in this JUnit test |
| * |
| * @return A suite containing embedded and client suites. |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite suite = |
| new BaseTestSuite("OrderByAndOffsetFetchInSubqueries"); |
| |
| suite.addTest(makeSuite()); |
| suite.addTest( |
| TestConfiguration.clientServerDecorator(makeSuite())); |
| |
| return suite; |
| } |
| |
| /** |
| * Construct suite of tests |
| * |
| * @return A suite containing the test cases. |
| */ |
| private static Test makeSuite() |
| { |
| return new CleanDatabaseTestSetup( |
| new BaseTestSuite(OrderByAndOffsetFetchInSubqueries.class)) { |
| @Override |
| protected void decorateSQL(Statement s) |
| throws SQLException { |
| getConnection().setAutoCommit(false); |
| |
| s.execute("create table temp1(s varchar(10))"); |
| |
| // GENERATED ALWAYS AS IDENTITY |
| s.execute("create table temp2(" + |
| "i integer not null " + |
| " generated always as identity," + |
| "s varchar(10))"); |
| s.execute("create table temp2b(" + |
| "i integer not null " + |
| " generated always as identity," + |
| "s varchar(10))"); |
| // DEFAULT value |
| s.execute("create table temp3(" + |
| "i integer not null " + |
| " generated always as identity," + |
| "s varchar(10)," + |
| "j integer not null " + |
| " default 66," + |
| "t varchar(10))"); |
| |
| // GENERATED ALWAYS AS (expression) |
| s.execute("create table temp4(" + |
| "i integer not null " + |
| " generated always as identity," + |
| "s varchar(10)," + |
| "j integer not null " + |
| " generated always as (2*i)," + |
| "t varchar(10))"); |
| |
| |
| s.execute("create table t01(c1 int)"); |
| s.execute("create table t02(c2 int)"); |
| |
| s.execute("create table t_source(c1 int, c2 varchar(10))"); |
| s.execute("create table t(i int not null, " + |
| " constraint c unique (i), " + |
| " j int, k int)"); |
| |
| getConnection().commit(); |
| } |
| }; |
| } |
| |
| /** |
| * Test {@code INSERT INTO t SELECT .. FROM .. ORDER BY}. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testInsertSelectOrderBy() throws SQLException { |
| // |
| // Shows that DERBY-4 is now solved. |
| // |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| |
| s.execute("insert into temp1 values 'x','a','c','b','a'"); |
| s.execute("insert into temp2(s) select s from temp1 order by s"); |
| s.execute("insert into temp2(s) select s as a1 from temp1 order by a1"); |
| s.execute("insert into temp2(s) select * from temp1 order by s"); |
| |
| rs = s.executeQuery("select * from temp2"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1", "a"}, |
| {"2", "a"}, |
| {"3", "b"}, |
| {"4", "c"}, |
| {"5", "x"}, |
| {"6", "a"}, |
| {"7", "a"}, |
| {"8", "b"}, |
| {"9", "c"}, |
| {"10", "x"}, |
| {"11", "a"}, |
| {"12", "a"}, |
| {"13", "b"}, |
| {"14", "c"}, |
| {"15", "x"}}); |
| |
| rs = s.executeQuery("select * from temp2 order by i"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1", "a"}, |
| {"2", "a"}, |
| {"3", "b"}, |
| {"4", "c"}, |
| {"5", "x"}, |
| {"6", "a"}, |
| {"7", "a"}, |
| {"8", "b"}, |
| {"9", "c"}, |
| {"10", "x"}, |
| {"11", "a"}, |
| {"12", "a"}, |
| {"13", "b"}, |
| {"14", "c"}, |
| {"15", "x"}}); |
| |
| s.execute("insert into temp2(s) select s as a1 from temp1 order by s"); |
| |
| // This should be rejected as "no such column" errors: |
| assertStatementError( |
| COLUMN_NOT_FOUND, s, |
| "insert into temp2(s) select s as a1 from temp1 order by no_such"); |
| |
| // A similar example, but with integers rather than strings, and some |
| // intermediate select statements to show that the ordering is working. |
| // |
| s.execute("insert into t01 values (50), (10), (1000), (15), (51)"); |
| |
| rs = s.executeQuery("select * from t01"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"50"}, |
| {"10"}, |
| {"1000"}, |
| {"15"}, |
| {"51"}}); |
| |
| |
| s.execute("insert into t02 select * from t01 order by c1"); |
| s.execute("insert into t02 select * from t01"); |
| s.execute("insert into t02 select * from t01 order by c1"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"10"}, |
| {"15"}, |
| {"50"}, |
| {"51"}, |
| {"1000"}, |
| {"50"}, |
| {"10"}, |
| {"1000"}, |
| {"15"}, |
| {"51"}, |
| {"10"}, |
| {"15"}, |
| {"50"}, |
| {"51"}, |
| {"1000"}}); |
| |
| // Combining ORDER BY and VALUES is not legal SQL, cf. SQL 2008, |
| // section 14.11, Syntactic Rule 17: "A <query expression> simply |
| // contained in a <from subquery> shall not be a <table value |
| // constructor>. See also discussion in JIRA on DERBY-4413 |
| // (2009-OCT-23). |
| // |
| assertStatementError( |
| SYNTAX_ERROR, s, |
| "insert into t02 values 66 order by 1"); |
| assertStatementError( |
| SYNTAX_ERROR, s, |
| "insert into t02 values (901), (920), (903) order by 1"); |
| |
| // But this should work: |
| s.executeUpdate("delete from t02"); |
| s.executeUpdate("insert into t02 select 900 from sys.systables " + |
| " union values 66 order by 1"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"66"}, |
| {"900"}}); |
| |
| // other way around: |
| s.executeUpdate("delete from t02"); |
| s.executeUpdate( |
| "insert into t02 values 66 " + |
| " union select 900 from sys.systables order by 1"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"66"}, |
| {"900"}}); |
| |
| // and, somewhat perversely (since a plain "values 66 order by 1" is |
| // illegal), this: |
| s.executeUpdate("delete from t02"); |
| s.executeUpdate("insert into t02 values 66 " + |
| " union values 66 order by 1"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"66"}}); |
| |
| |
| // UNION |
| // |
| // ok: |
| s.execute("delete from t02"); |
| s.execute("insert into t02 select * from t01 union all " + |
| " select * from t01 order by c1"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"10"}, |
| {"10"}, |
| {"15"}, |
| {"15"}, |
| {"50"}, |
| {"50"}, |
| {"51"}, |
| {"51"}, |
| {"1000"}, |
| {"1000"}}); |
| |
| // Not ok (c1 is not a column in the union result set, since t02 has |
| // column c02. |
| assertStatementError( |
| ORDER_BY_COLUMN_NOT_FOUND, s, |
| "insert into t02 select * from t01 union all " + |
| " select * from t02 order by c1"); |
| |
| |
| // Complication: project away sort column |
| s.execute("delete from t02"); |
| s.execute("insert into t_source " + |
| " values (1, 'one'), (2, 'two'), (8, 'three')"); |
| s.execute("insert into t_source(c1) " + |
| " select c1 from t_source order by c2 desc"); |
| rs = s.executeQuery("select * from t_source"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1", "one"}, |
| {"2", "two"}, |
| {"8", "three"}, |
| {"2", null}, |
| {"8", null}, |
| {"1", null}}); |
| |
| // DERBY-4496 |
| s.executeUpdate("create table t4496(x varchar(100))"); |
| s.execute("insert into t4496(x) select ibmreqd from " + |
| " (select * from sysibm.sysdummy1" + |
| " order by length(ibmreqd)) t1"); |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t4496"), |
| new String[][]{{"Y"}}); |
| |
| // DERBY-6006. INSERT INTO ... SELECT FROM could fail with a |
| // NullPointerException in insane builds, or XSCH5 or assert in sane |
| // builds, if the SELECT had an ORDER BY column that was not referenced |
| // in the select list, and if normalization was required because the |
| // types in the select list didn't exactly match the types in the |
| // target table. |
| // |
| // In the test case below, the select list has an INT (the literal 1), |
| // whereas the target type is DOUBLE. Also, the ORDER BY column (X) is |
| // not in the select list. |
| s.execute("create table t6006(x double)"); |
| assertUpdateCount(s, 6, "insert into t6006 values 1,2,3,4,5,6"); |
| assertUpdateCount(s, 6, |
| "insert into t6006 select 1 from t6006 order by x"); |
| |
| rollback(); |
| } |
| |
| /** |
| * Same test as {@code testInsertSelectOrderBy} but with use of |
| * {@code OFFSET/FETCH FIRST}. |
| * <p/> |
| * Test {@code INSERT INTO t SELECT .. FROM .. ORDER BY} + {@code OFFSET |
| * FETCH} |
| * <p/> |
| * This test is a variant made my modifying {@code testInsertSelectOrderBy} |
| * with suitable {@code OFFSET/FETCH FIRST} clauses. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testInsertSelectOrderByOffsetFetch() throws SQLException { |
| // |
| // Shows that DERBY-4 is now solved. |
| // |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| s.execute("insert into temp1 values 'x','a','c','b','a'"); |
| s.execute("insert into temp2b(s) select s from temp1 order by s " + |
| " offset 1 rows fetch next 4 rows only"); |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from temp2b"), |
| new String[][]{ |
| {"1", "a"}, |
| {"2", "b"}, |
| {"3", "c"}, |
| {"4", "x"}}); |
| |
| s.execute( |
| "insert into temp2b(s) select s as a1 from temp1 order by a1" + |
| " offset 1 rows fetch next 4 rows only"); |
| |
| s.execute( |
| "insert into temp2b(s) select * from temp1 order by s " + |
| " offset 1 rows fetch next 4 rows only"); |
| |
| ResultSet rs = s.executeQuery("select * from temp2b"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1", "a"}, |
| {"2", "b"}, |
| {"3", "c"}, |
| {"4", "x"}, |
| {"5", "a"}, |
| {"6", "b"}, |
| {"7", "c"}, |
| {"8", "x"}, |
| {"9", "a"}, |
| {"10", "b"}, |
| {"11", "c"}, |
| {"12", "x"}}); |
| |
| rs = s.executeQuery("select * from temp2b order by i"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1", "a"}, |
| {"2", "b"}, |
| {"3", "c"}, |
| {"4", "x"}, |
| {"5", "a"}, |
| {"6", "b"}, |
| {"7", "c"}, |
| {"8", "x"}, |
| {"9", "a"}, |
| {"10", "b"}, |
| {"11", "c"}, |
| {"12", "x"}}); |
| |
| |
| // A similar example, but with integers rather than strings, and some |
| // intermediate select statements to show that the ordering is working. |
| // |
| s.execute("insert into t01 values (50), (10), (1000), (15), (51)"); |
| |
| rs = s.executeQuery("select * from t01"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"50"}, |
| {"10"}, |
| {"1000"}, |
| {"15"}, |
| {"51"}}); |
| |
| |
| s.execute( |
| "insert into t02 select * from t01 order by c1 " + |
| " fetch first 2 rows only"); |
| s.execute( |
| "insert into t02 select * from t01"); |
| s.execute( |
| "insert into t02 select * from t01 order by c1 offset 0 rows"); |
| |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"10"}, |
| {"15"}, |
| {"50"}, |
| {"10"}, |
| {"1000"}, |
| {"15"}, |
| {"51"}, |
| {"10"}, |
| {"15"}, |
| {"50"}, |
| {"51"}, |
| {"1000"}}); |
| |
| // Illegal context |
| assertStatementError( |
| SYNTAX_ERROR, s, |
| "insert into t02 values 66 offset 1 row"); |
| |
| // But this should work: |
| s.executeUpdate("delete from t02"); |
| s.executeUpdate( |
| "insert into t02 select 900 from sys.systables " + |
| " union values 66 order by 1 offset 1 row"); |
| |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"900"}}); |
| |
| // other way around: |
| s.executeUpdate("delete from t02"); |
| s.executeUpdate( |
| "insert into t02 values 66 " + |
| " union select 900 from sys.systables fetch next 1 row only"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"66"}}); |
| |
| s.executeUpdate("delete from t02"); |
| s.executeUpdate("insert into t02 select * from (values 3,4,5 )v " + |
| " order by 1 offset 1 row fetch next 2 rows only"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"4"}, |
| {"5"}}); |
| |
| |
| // UNION |
| // |
| // ok: |
| s.execute("delete from t02"); |
| s.execute("insert into t02 select * from t01 union all " + |
| " select * from t01 order by c1 " + |
| " fetch next 4 rows only"); |
| rs = s.executeQuery("select * from t02"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"10"}, |
| {"10"}, |
| {"15"}, |
| {"15"}}); |
| |
| // EXCEPT |
| s.execute("delete from t01"); |
| s.execute("insert into t02 values 6,7"); |
| s.execute("insert into t01 select * from t02 except " + |
| " values 10 order by 1 offset 1 row"); |
| rs = s.executeQuery("select * from t01"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"7"}, |
| {"15"}}); |
| |
| // Complication: project away sort column |
| s.execute("delete from t02"); |
| s.execute("insert into t_source " + |
| " values (1, 'one'), (2, 'two'), (8, 'three')"); |
| s.execute("insert into t_source(c1) " + |
| " select c1 from t_source order by c2 desc " + |
| " fetch next 2 rows only"); |
| rs = s.executeQuery("select * from t_source"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1", "one"}, |
| {"2", "two"}, |
| {"8", "three"}, |
| {"2", null}, |
| {"8", null}}); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * {@code SELECT} subqueries with {@code ORDER BY} |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testSelectSubqueriesOrderBy() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| |
| s.execute( |
| "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')"); |
| |
| /* |
| * Simple SELECT FromSubquery |
| */ |
| rs = s.executeQuery( |
| "select * from (select c1 from t_source order by c1 desc) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"8"}, {"2"}, {"1"}}); |
| |
| rs = s.executeQuery( |
| "select * from (select c1+1 from t_source order by c1+1 desc) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"9"}, {"3"}, {"2"}}); |
| |
| rs = s.executeQuery( |
| "select * from (select c1,c2 from t_source order by c1 desc,2) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"8", "three"}, {"2", "two"}, {"1", "one"}}); |
| |
| // Complication: project away sort column |
| rs = s.executeQuery( |
| "select * from (select c2 from t_source order by c1 desc) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"three"}, {"two"}, {"one"}}); |
| |
| rs = s.executeQuery( |
| "select * from " + |
| " (select c2 from t_source order by c1 desc) s order by 1"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"one"}, {"three"}, {"two"}}); |
| |
| /* |
| * Simple VALUES FromSubquery |
| */ |
| rs = s.executeQuery( |
| "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" + |
| " order by 1 desc) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"8", "three"}, {"2", "two"}, {"1", "one"}}); |
| |
| |
| /* |
| * ORDER BY in EXISTS subquery |
| */ |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c1)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"2"}, {"8"}}); |
| |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c1 desc)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"2"}, {"8"}}); |
| |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c2 desc)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"2"}, {"8"}}); |
| |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c2 desc) order by 1 desc"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"8"}, {"2"}, {"1"}}); |
| |
| /* |
| * NOT EXISTS |
| */ |
| rs = s.executeQuery( |
| "select c1 from t_source where not exists " + |
| " (select c1 from t_source order by c2 desc) order by 1 desc"); |
| JDBC.assertEmpty(rs); |
| |
| rs = s.executeQuery( |
| "select c1 from t_source ot where not exists " + |
| " (select c1 from t_source where ot.c1=(c1/2) order by c2 desc)" + |
| " order by 1 desc"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"8"}, {"2"}}); |
| |
| /* |
| * IN subquery |
| */ |
| s.executeUpdate("insert into t values (1,10,1), (2,40,1)," + |
| " (3,45,1), (4,46,1), (5,90,1)"); |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by 1 desc)"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}}); |
| |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by i/5 desc)"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}}); |
| |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by j)"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}}); |
| |
| |
| /* |
| * Scalar subquery inside ALL subquery with correlation |
| */ |
| String[][] expected = new String[][]{ |
| {"1", "10", "1"}, |
| {"2", "40", "1"}}; |
| |
| // First without any ORDER BYs |
| rs = s.executeQuery( |
| "select * from t t_o where i <= all (" + |
| " select i+1 from t where i = t_o.k + (" + |
| " select count(*) from t) - 5)"); |
| JDBC.assertFullResultSet(rs, expected); |
| |
| // Then with ORDER BY at both subquery levels; should be the same result |
| rs = s.executeQuery( |
| "select * from t t_o where i <= all (" + |
| " select i+1 from t where i = t_o.k + (" + |
| " select count(*) from t order by 1) - 5 " + |
| " order by 1 desc)"); |
| JDBC.assertFullResultSet(rs, expected); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * {@code SELECT} subqueries with {@code ORDER BY} and {@code OFFSET/FETCH}. |
| * <p/> |
| * This test is a variant made my modifying {@code |
| * testSelectSubqueriesOrderBy} with suitable {@code OFFSET/FETCH FIRST} |
| * clauses. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testSelectSubqueriesOrderByAndOffsetFetch() |
| throws SQLException { |
| |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| |
| s.execute( |
| "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')"); |
| |
| /* |
| * Simple SELECT FromSubquery |
| */ |
| rs = s.executeQuery( |
| "select * from (select c1 from t_source order by c1 desc " + |
| " offset 1 row) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"2"}, {"1"}}); |
| |
| rs = s.executeQuery( |
| "select * from (select c1+1 from t_source order by c1+1 desc " + |
| " fetch first 2 rows only) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"9"}, {"3"}}); |
| |
| rs = s.executeQuery( |
| "select * from (select c1,c2 from t_source order by c1 desc,2 " + |
| " offset 2 rows fetch next 1 row only) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1", "one"}}); |
| |
| // Complication: project away sort column |
| rs = s.executeQuery( |
| "select * from (select c2 from t_source order by c1 desc " + |
| " offset 2 rows) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"one"}}); |
| |
| rs = s.executeQuery( |
| "select * from " + |
| " (select c2 from t_source order by c1 desc " + |
| " fetch first 2 row only) s order by 1"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"three"}, {"two"}}); |
| |
| /* |
| * Simple VALUES FromSubquery |
| */ |
| rs = s.executeQuery( |
| "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" + |
| " order by 1 desc offset 1 row) s"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"2", "two"}, {"1", "one"}}); |
| |
| |
| /* |
| * ORDER BY in EXISTS subquery |
| */ |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c1 offset 1 row)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"2"}, {"8"}}); |
| |
| // OFFSET so we get an empty result set: |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c1 offset 3 rows)"); |
| JDBC.assertEmpty(rs); |
| |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c1 desc " + |
| " fetch first 1 row only)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"2"}, {"8"}}); |
| |
| // drop order by for once: |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source offset 1 row)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"2"}, {"8"}}); |
| |
| rs = s.executeQuery( |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c2 desc " + |
| " offset 1 row fetch first 1 row only) " + |
| " order by 1 desc offset 1 row fetch first 1 row only"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"2"}}); |
| |
| /* |
| * NOT EXISTS |
| */ |
| |
| // We offset outside inner subquery, so NOT EXISTS should hold for all |
| rs = s.executeQuery( |
| "select c1 from t_source where not exists " + |
| " (select c1 from t_source order by c2 desc " + |
| " offset 3 rows) " + |
| " order by 1 desc"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"8"}, {"2"}, {"1"}}); |
| |
| // should remove the hit for 1 below since we offset past it: |
| rs = s.executeQuery( |
| "select c1 from t_source ot where not exists " + |
| " (select c1 from t_source where ot.c1=(c1/2) order by c2 desc " + |
| " offset 1 row)" + |
| " order by 1 desc"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"8"}, {"2"}, {"1"}}); |
| |
| /* |
| * IN subquery |
| */ |
| s.executeUpdate("insert into t values (1,10,1), (2,40,1)," + |
| " (3,45,1), (4,46,1), (5,90,1)"); |
| |
| // offset away the interesting value in the subquery: |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by 1 desc " + |
| " offset 1 row)"); |
| JDBC.assertEmpty(rs); |
| |
| // turn rs around, and we should get a hit: |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by 1 asc " + |
| " offset 1 row)"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}}); |
| |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by i/5 desc " + |
| " offset 1 row)"); |
| JDBC.assertEmpty(rs); |
| |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by i/5 asc " + |
| " offset 1 row)"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}}); |
| |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by j " + |
| " offset 1 row)"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}}); |
| |
| rs = s.executeQuery( |
| "select * from t where i in (select i/5 from t order by j desc " + |
| " offset 1 row)"); |
| JDBC.assertEmpty(rs); |
| |
| /* |
| * Scalar subquery inside ALL subquery with correlation |
| */ |
| String[][] expected = new String[][]{ |
| {"1", "10", "1"}, |
| {"2", "40", "1"}}; |
| |
| // First without any ORDER BYs |
| rs = s.executeQuery( |
| "select * from t t_o where i <= all (" + |
| " select i+1 from t where i = t_o.k + (" + |
| " select count(*) from t) - 5)"); |
| JDBC.assertFullResultSet(rs, expected); |
| |
| // Should give null from subquery |
| rs = s.executeQuery( |
| "select * from t where i = (select count(*) from t order by 1 " + |
| " offset 1 row)"); |
| JDBC.assertEmpty(rs); |
| |
| rs = s.executeQuery( |
| "select * from t t_o where i <= all (" + |
| " select i+1 from t where i = t_o.k + cast(null as int) +" + |
| " - 5 " + |
| " order by 1 desc)"); |
| // Notice the cast(null as int) I use above to check that the |
| // subquery in the next query using an offset which makes the scalar |
| // subquery return null gives the same result as this one. |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1","10","1"}, |
| {"2","40","1"}, |
| {"3","45","1"}, |
| {"4","46","1"}, |
| {"5","90","1"}}); |
| |
| rs = s.executeQuery( |
| "select * from t t_o where i <= all (" + |
| " select i+1 from t where i = t_o.k + (" + |
| " select count(*) from t order by 1 offset 1 row) - 5 " + |
| " order by 1 desc)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1","10","1"}, |
| {"2","40","1"}, |
| {"3","45","1"}, |
| {"4","46","1"}, |
| {"5","90","1"}}); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * Test JOIN with delimited subqueries |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testJoinsWithOffsetFetch() throws SQLException { |
| |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| |
| s.execute("insert into temp1 values 'x','a','c','b','a'"); |
| |
| PreparedStatement ps = prepareStatement( |
| "select * from " + |
| " (select s from temp1 order by s " + |
| " fetch first ? rows only) t1 join " + |
| " (select s from temp1 order by s offset ? row " + |
| " fetch first ? row only) t2 " + |
| " on t1.s=t2.s"); |
| |
| ps.setInt(1,2); |
| ps.setInt(2,1); |
| ps.setInt(3,1); |
| |
| rs = ps.executeQuery(); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"a", "a"}, |
| {"a", "a"}}); |
| |
| ps.setInt(1,1); |
| rs = ps.executeQuery(); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"a", "a"}}); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * Test {@code ORDER BY} in a view definition |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testView() throws SQLException { |
| |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| |
| s.executeUpdate( |
| "create view v1 as select i from t order by j desc"); |
| s.executeUpdate( |
| "create view v2 as select i from t order by i"); |
| s.executeUpdate( |
| "insert into t values (1,10,1), (2,40,1)," + |
| " (3,45,1), (4,46,1), (5,90,1)"); |
| rs = s.executeQuery( |
| "select i from v1"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"5"},{"4"},{"3"},{"2"},{"1"}}); |
| |
| rs = s.executeQuery( |
| "select i from v2"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"},{"2"},{"3"},{"4"},{"5"}}); |
| |
| rollback(); |
| } |
| |
| /** |
| * Test {@code ORDER BY} + {@code FETCH/OFFSET} in a view definition |
| * <p/> |
| * This test is a variant made my modifying {@code testView} with suitable |
| * {@code OFFSET/FETCH FIRST} clauses. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testViewFetchOffset() throws SQLException { |
| |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| |
| s.executeUpdate( |
| "create view v1 as select i from t order by j desc " + |
| " offset 2 rows fetch first 1 row only"); |
| s.executeUpdate( |
| "create view v2 as select i from t order by i " + |
| " fetch next 2 rows only"); |
| |
| s.executeUpdate( |
| "insert into t values (1,10,1), (2,40,1)," + |
| " (3,45,1), (4,46,1), (5,90,1)"); |
| |
| rs = s.executeQuery( |
| "select i from v1"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"3"}}); |
| |
| rs = s.executeQuery( |
| "select i from v2"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"},{"2"}}); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * {@code SELECT} subqueries with {@code ORDER BY} - negative tests |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testSelectSubqueriesOrderByNegative() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| |
| s.execute( |
| "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')"); |
| |
| /* |
| * Simple SELECT FromSubquery |
| */ |
| assertStatementError( |
| COLUMN_NOT_FOUND, s, |
| "select * from (select c1 from t_source order by c3 desc) s"); |
| |
| assertStatementError( |
| COLUMN_OUT_OF_RANGE, s, |
| "select * from (select c1 from t_source order by 3 desc) s"); |
| |
| |
| /* |
| * Simple VALUES FromSubquery |
| */ |
| assertStatementError( |
| COLUMN_OUT_OF_RANGE, s, |
| "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" + |
| " order by 3 desc) s"); |
| |
| /* |
| * ORDER BY in EXISTS subquery: |
| */ |
| assertStatementError( |
| COLUMN_NOT_FOUND, s, |
| "select c1 from t_source where exists " + |
| " (select c1 from t_source order by c4)"); |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * {@code SELECT} subqueries with {@code ORDER BY} - check sort avoidance |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testSelectSubqueriesSortAvoidance() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| ResultSet rs; |
| RuntimeStatisticsParser rtsp; |
| s.executeUpdate("create table ts(i int, j int)"); |
| PreparedStatement ps = prepareStatement("insert into ts values(?,?)"); |
| for (int i=0; i < 100; i++) { |
| ps.setInt(1,i); |
| ps.setInt(2,i*2); |
| ps.execute(); |
| } |
| |
| s.executeUpdate("create unique index t_i on ts(i)"); |
| s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); |
| |
| // ORDER BY inside a subquery should make use of index to avoid |
| // sorting. |
| rs = s.executeQuery("select * from (select i from ts order by i)tt"); |
| rtsp = SQLUtilities.getRuntimeStatisticsParser(s); |
| |
| // Verify that we use the index scan here and no sorting is incurred |
| assertTrue(rtsp.usedSpecificIndexForIndexScan("TS","T_I")); |
| assertFalse(rtsp.whatSortingRequired()); |
| |
| s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)"); |
| rollback(); |
| } |
| |
| /** |
| * Prevent pushing of where predicates into selects with fetch |
| * and/or offset (DERBY-5911). Similarly, for windowed selects. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testPushAvoidance() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| s.executeUpdate |
| ("CREATE TABLE COFFEES (COF_NAME VARCHAR(254),PRICE INTEGER)"); |
| s.executeUpdate |
| ("INSERT INTO COFFEES VALUES ('Colombian', 5)"); |
| s.executeUpdate |
| ("INSERT INTO COFFEES VALUES ('French_Roast', 5)"); |
| s.executeUpdate |
| ("INSERT INTO COFFEES VALUES ('Colombian_Decaf', 20)"); |
| |
| ResultSet rs = s.executeQuery |
| ("select * from " + |
| " (select COF_NAME, PRICE from COFFEES " + |
| " order by COF_NAME fetch next 2 rows only" + |
| " ) t " + |
| "where t.PRICE < 10"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{{"Colombian", "5"}}); |
| |
| rs = s.executeQuery |
| ("select * from " + |
| " (select COF_NAME, PRICE from COFFEES " + |
| " order by COF_NAME offset 2 row" + |
| " ) t " + |
| "where t.PRICE < 10"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{{"French_Roast", "5"}}); |
| |
| rs = s.executeQuery |
| ("select cof_name, price from " + |
| " (select row_number() over() as rownum, COF_NAME, PRICE from " + |
| " (select * from COFFEES order by COF_NAME) i" + |
| " ) t where rownum <= 2 and PRICE < 10"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{{"Colombian", "5"}}); |
| |
| |
| rollback(); |
| } |
| |
| |
| /** |
| * Test nesting inside set operands, cf. this production in SQL |
| * 2011, section 7.12: |
| * <pre> |
| * <query primary> ::= |
| * <simple table> |
| * | <left paren> <query expression body> |
| * [ <order by clause> ] [ <result offset clause> ] |
| * [ <fetch first clause> ] <right paren> |
| * </pre> |
| * The corresponding production in {@code sqlgrammar.jj} is |
| * {@code nonJoinQueryPrimary}. |
| * |
| * Cf. DERBY-6008. |
| * |
| * @throws java.sql.SQLException |
| */ |
| public void testNestingInsideSetOperation() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| s.executeUpdate("create table t1(i int, j int )"); |
| s.executeUpdate("create table t2(i int, j int)"); |
| |
| s.executeUpdate("insert into t1 values (1,1),(4,8),(2,4)"); |
| s.executeUpdate("insert into t2 values (10,10),(40,80),(20,40)"); |
| |
| ResultSet rs = s.executeQuery( |
| "(select i from t1 order by j desc offset 1 row) union " + |
| "(select i from t2 order by j desc offset 1 rows " + |
| "fetch next 1 row only)"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"2"}, {"20"}}); |
| |
| // Without parentheses, expect syntax error |
| assertCompileError("42X01", |
| "select i from t1 order by j desc offset 1 row union " + |
| "(select i from t2 order by j desc offset 2 rows)"); |
| |
| // With VALUES (single) instead of SELECT: |
| // Single values exercise changes in RowResultSetNode |
| rs = s.executeQuery( |
| "(values 1 order by 1 fetch first 1 row only) union " + |
| "(select i from t2 order by j desc offset 2 rows)"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"10"}}); |
| |
| // With VALUES (single) instead of SELECT and duplicate ordering key |
| rs = s.executeQuery( |
| "(values 1 order by 1,1 fetch first 1 row only) union " + |
| "(select i from t2 order by j desc offset 2 rows)"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"10"}}); |
| |
| // With VALUES (multiple) instead of SELECT |
| // Multiples values exercise changes in SetOperatorNode when used in |
| // table value constructor context (UNION). |
| rs = s.executeQuery( |
| "(values 1,2 order by 1 desc offset 1 row " + |
| " fetch first 1 row only)" + |
| " union (select i from t2 order by j desc offset 2 rows)"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, {"10"}}); |
| |
| // With VALUES (multiple) instead of SELECT plus duplicate ordering |
| // key |
| rs = s.executeQuery( |
| "(values 1,2 order by 1,1 offset 1 row fetch first 1 row only)" + |
| " union (select i from t2 order by j desc offset 2 rows)"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"2"}, {"10"}}); |
| |
| // Intersect and except |
| s.executeUpdate( |
| "create table countries(name varchar(20), " + |
| " population int, " + |
| " area int)"); |
| |
| s.executeUpdate("insert into countries values" + |
| "('Norway', 5033675, 385252)," + |
| "('Sweden', 9540065, 449964)," + |
| "('Denmark', 5580413, 42894)," + |
| "('Iceland', 320060, 103001)," + |
| "('Liechtenstein', 36281, 160)"); |
| |
| rs = s.executeQuery( |
| "(select name from countries " + |
| " order by population desc fetch first 2 rows only)" + |
| " intersect " + |
| "(select name from countries " + |
| " order by area desc fetch first 2 rows only)"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Sweden"}}); |
| |
| rs = s.executeQuery( |
| "(values ('Norway', 5033675, 385252), " + |
| " ('Sweden', 9540065, 449964)," + |
| " ('Denmark', 5580413, 42894)," + |
| " ('Iceland', 320060, 103001)," + |
| " ('Liechtenstein', 36281, 160)" + |
| " order by 2 desc fetch first 3 rows only)" + |
| " intersect " + |
| "(select * from countries " + |
| " order by area desc fetch first 3 rows only)"); |
| // Note: we use 3 rows here to check that both sorts work the way they |
| // should: at the lowest level, the "order by 2 desc", then the |
| // "fetch first" of only three of those rows, then on top the ascending |
| // sort on all columns to get the data ready for the intersect. |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Norway", "5033675", "385252"}, |
| {"Sweden", "9540065", "449964"}}); |
| |
| rs = s.executeQuery( |
| "(values ('Norway', 5033675, 385252)" + |
| " order by 2 desc fetch first 3 rows only)" + |
| " intersect " + |
| "(values ('Norway', 5033675, 385252))"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Norway", "5033675", "385252"}}); |
| |
| rs = s.executeQuery( |
| "(select name from countries " + |
| " order by population desc fetch first 2 rows only)" + |
| " except " + |
| "(select name from countries " + |
| " order by area desc fetch first 2 rows only)"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Denmark"}}); |
| |
| rs = s.executeQuery( |
| "(values ('Norway', 5033675, 385252), " + |
| " ('Sweden', 9540065, 449964)," + |
| " ('Denmark', 5580413, 42894)," + |
| " ('Iceland', 320060, 103001)," + |
| " ('Liechtenstein', 36281, 160)" + |
| " order by 2 desc fetch first 3 rows only)" + |
| " except " + |
| "(select * from countries " + |
| " order by area desc fetch first 3 rows only)"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Denmark", "5580413", "42894"}}); |
| |
| rollback(); |
| } |
| |
| /** |
| * Nested query expression body, with each level contributing to the set of |
| * ORDER BY and/or OFFSET/FETCH FIRST clauses. |
| * |
| * Cf. these productions in SQL 2011, section 7.11: |
| * |
| * <pre> |
| * <query expression> ::= |
| * [ <with clause> ] <query expression body> |
| * [ <order by clause> ] [ <result offset clause> ] |
| * [ <fetch first clause> ] |
| * |
| * <query expression body> ::= |
| * <query term> ... |
| * </pre> |
| * |
| * One of the productions of {@code <query expression body>}, is |
| * |
| * <pre> |
| * <left paren> <query expression body> |
| * [ <order by clause> ] [ <result offset clause> ] |
| * [ <fetch first clause> ] <right paren> |
| * </pre> |
| * so our clauses nests to arbitrary depth given enough parentheses, |
| * including ORDER BY and OFFSET/FETCH FIRST clauses. This nesting |
| * did not work correctly, cf. DERBY-6378. |
| * |
| * The corresponding productions in {@code sqlgrammar.jj} is |
| * {@code queryExpression} and {@code nonJoinQueryPrimary}. |
| * |
| * @throws Exception |
| */ |
| public void testDerby6378() throws Exception |
| { |
| setAutoCommit(false); |
| Statement stm = createStatement(); |
| stm.executeUpdate("create table t1 (a int, b bigint)"); |
| stm.executeUpdate("delete from t1"); |
| stm.executeUpdate("insert into t1 values " + |
| "(1,-10), (2,-11), (3,-9), (4,-20), (5,-1)"); |
| |
| queryAndCheck(stm, |
| "(select * from t1 offset 1 row fetch first 1 row only)", |
| new String [][] {{"2","-11"}}); |
| |
| queryAndCheck(stm, |
| "(select * from t1 order by a desc fetch first 3 rows only) " + |
| " offset 1 row fetch first 1 row only", |
| new String [][] {{"4","-20"}}); |
| |
| queryAndCheck(stm, |
| "((select * from t1 order by a desc) " + |
| " fetch first 3 rows only)", |
| new String [][] {{"5","-1"}, {"4","-20"}, {"3","-9"}}); |
| |
| queryAndCheck(stm, |
| "((((select * from t1 order by a desc) " + |
| " fetch first 3 rows only)) " + |
| " order by b) " + |
| "fetch first 1 row only", |
| new String [][] {{"4","-20"},}); |
| |
| queryAndCheck( |
| stm, |
| "(((((values (1,-10), (2,-11), (3,-9), (4,-20), (5,-1))" + |
| " order by 1 desc) " + |
| " fetch first 3 rows only)) " + |
| " order by 2) " + |
| "fetch first 1 row only", |
| new String [][] {{"4","-20"},}); |
| rollback(); |
| stm.close(); |
| } |
| |
| private void queryAndCheck( |
| Statement stm, |
| String queryText, |
| String [][] expectedRows) throws SQLException { |
| |
| ResultSet rs = stm.executeQuery(queryText); |
| JDBC.assertFullResultSet(rs, expectedRows); |
| } |
| |
| } |
