/*
*
* Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SubqueryTest
*
* 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 java.util.Properties;
import junit.framework.Test;
import junit.framework.TestSuite;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
/**
* Test case for subquery.sql.
*/
public class SubqueryTest extends BaseJDBCTestCase {
public SubqueryTest(String name) {
super(name);
}
public static Test suite() {
Properties props = new Properties();
props.setProperty("derby.language.statementCacheSize", "0");
return new DatabasePropertyTestSetup(
new SystemPropertyTestSetup(new CleanDatabaseTestSetup(
new TestSuite(SubqueryTest.class, "SubqueryTest")) {
/**
* @see org.apache.derbyTesting.junit.CleanDatabaseTestSetup#decorateSQL(java.sql.Statement)
*/
protected void decorateSQL(Statement s) throws SQLException {
s.execute(
"CREATE FUNCTION ConsistencyChecker() " +
"RETURNS VARCHAR(128) " +
"EXTERNAL NAME " +
"'org.apache.derbyTesting.functionTests." +
"util.T_ConsistencyChecker.runConsistencyChecker' " +
"LANGUAGE JAVA PARAMETER STYLE JAVA");
s.execute("create table s " +
"(i int, s smallint, c char(30), " +
"vc char(30), b bigint)");
s.execute("create table t " +
"(i int, s smallint, c char(30), " +
"vc char(30), b bigint)");
s.execute("create table tt " +
"(ii int, ss smallint, cc char(30), " +
"vcvc char(30), b bigint)");
s.execute("create table ttt " +
"(iii int, sss smallint, ccc char(30), " +
"vcvcvc char(30))");
// populate the tables
s.execute("insert into s values " +
"(null, null, null, null, null)");
s.execute("insert into s values (0, 0, '0', '0', 0)");
s.execute("insert into s values (1, 1, '1', '1', 1)");
s.execute("insert into t values " +
"(null, null, null, null, null)");
s.execute("insert into t values (0, 0, '0', '0', 0)");
s.execute("insert into t values (1, 1, '1', '1', 1)");
s.execute("insert into t values (1, 1, '1', '1', 1)");
s.execute("insert into t values (2, 2, '2', '2', 1)");
s.execute("insert into tt values " +
"(null, null, null, null, null)");
s.execute("insert into tt values (0, 0, '0', '0', 0)");
s.execute("insert into tt values (1, 1, '1', '1', 1)");
s.execute("insert into tt values (1, 1, '1', '1', 1)");
s.execute("insert into tt values (2, 2, '2', '2', 1)");
s.execute("insert into ttt values (null, null, null, null)");
s.execute("insert into ttt values (11, 11, '11', '11')");
s.execute("insert into ttt values (11, 11, '11', '11')");
s.execute("insert into ttt values (22, 22, '22', '22')");
}
}, props), props, true);
}
/**
* exists non-correlated negative tests "mis"qualified all
*
* @throws Exception
*/
public void testExistsNonCorrelated() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
assertStatementError("42X10", st,
"select * from s where exists (select tt.* from t)");
assertStatementError("42X10", st,
"select * from s where exists (select t.* from t tt)");
// invalid column reference in select list
assertStatementError("42X04", st,
"select * from s where exists (select nosuchcolumn from t)");
// multiple matches at subquery level
assertStatementError("42X03", st,
"select * from s where exists (select i from s, t)");
// ? parameter in select list of exists subquery
assertStatementError("42X34", st,
"select * from s where exists (select ? from s)");
// positive tests
// qualified *
rs = st.executeQuery(
"select * from s where exists (select s.* from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s t where exists (select t.* from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s u where exists (select u.* from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
// column reference in select list
rs = st.executeQuery("select * from s where exists (select i from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where exists (select t.i from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where exists (select i, s from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
// subquery returns empty result set
rs = st.executeQuery(
"select * from s where exists (select * from t where i = -1)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// test semantics of AnyResultSet
rs = st.executeQuery(
"select * from s where exists (select t.* from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select * from s where exists (select 0 from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
// subquery in derived table
rs = st.executeQuery(
"select * from (select * from s where exists " +
"(select * from t) and i = 0) a");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "0", "0", "0", "0", "0" } };
JDBC.assertFullResultSet(rs, expRS, true);
// exists under an OR
rs = st.executeQuery(
"select * from s where 0=1 or exists (select * from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where 1=1 or exists " +
"(select * from t where 0=1)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select * from s where exists (select * from t "
+ "where 0=1) or exists (select * from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(" select * from s where exists (select * from t "
+ "where exists (select * from t where 0=1) or exists "
+ "(select * from t))");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
// (exists empty set) is null
rs = st.executeQuery("select * from s where (exists (select * from t "
+ "where 0=1)) is null");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
// Not exists
rs = st.executeQuery(
"select * from s where not exists (select * from t)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select * from s where not exists " +
"(select * from t where i = -1)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs.close();
st.close();
}
/**
* expression subqueries non-correlated negative tests all node
*
* @throws Exception
*/
public void testExpressionNonCorrelated() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
assertStatementError("42X38", st,
"select * from s where i = (select * from t)");
// too many columns in select list
assertStatementError("42X39", st,
"select * from s where i = (select i, s from t)");
// no conversions
assertStatementError("21000", st,
"select * from s where i = (select 1 from t)");
assertStatementError("21000", st,
"select * from s where i = (select b from t)");
// ? parameter in select list of expression subquery
assertStatementError("42X34", st,
"select * from s where i = (select ? from t)");
// do consistency check on scans, etc.
rs = st.executeQuery("values ConsistencyChecker()");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
// cardinality violation
assertStatementError("21000", st,
"select * from s where i = (select i from t)");
// do consistency check on scans, etc.
rs = st.executeQuery("values ConsistencyChecker()");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
assertStatementError("21000", st,
"select * from s where s = (select s from t where s = 1)");
// do consistency check on scans, etc.
rs = st.executeQuery("values ConsistencyChecker()");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
if (usingEmbedded()) {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
} else {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
}
assertStatementError("21000", st,
"update s set b = (select max(b) from t) where vc " +
"<> (select vc from t where vc = '1')");
// do consistency check on scans, etc.
rs = st.executeQuery("values ConsistencyChecker()");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
if (usingEmbedded()) {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
} else {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
}
assertStatementError("21000", st,
"delete from s where c = (select c from t where c = '1')");
// do consistency check on scans, etc.
rs = st.executeQuery("values ConsistencyChecker()");
expColNames = new String[] { "1" };
JDBC.assertColumnNames(rs, expColNames);
if (usingEmbedded()) {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
} else {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
}
// positive tests
rs = st.executeQuery("select * from s");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(" select * from t");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { null, null, null, null, null },
{ "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" },
{ "1", "1", "1", "1", "1" }, { "2", "2", "2", "2", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* Testing simple subquery for each data type
*
* @throws Exception
*/
public void testSimpleSubquery() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select * from s where i = (select i from t where i = 0)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "0", "0", "0", "0", "0" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where s = (select s from t where s = 0)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "0", "0", "0", "0", "0" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where c = (select c from t where c = '0')");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "0", "0", "0", "0", "0" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where vc = " +
"(select vc from t where vc = '0')");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "0", "0", "0", "0", "0" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where b = " +
"(select max(b) from t where b = 0)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "0", "0", "0", "0", "0" } };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where b = " +
"(select max(b) from t where i = 2)");
expColNames = new String[] { "I", "S", "C", "VC", "B" };
JDBC.assertColumnNames(rs, expColNames);
expRS = new String[][] { { "1", "1", "1", "1", "1" } };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* ? parameter on left hand side of expression subquery
* @throws Exception
*/
public void testParameterOnLeft()throws Exception {
Statement st = createStatement();
PreparedStatement pSt;
ResultSetMetaData rsmd;
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
pSt = prepareStatement(
"select * from s where ? = (select i from t where i = 0)");
rs = st.executeQuery("values (0)");
rs.next();
rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++)
pSt.setObject(i, rs.getObject(i));
rs = pSt.executeQuery();
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] {
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* Testing conversions
* @throws Exception
*/
public void testConversions()throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select * from s where i = (select s from t where s = 0)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where s = (select i from t where i = 0)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where c = (select vc from t where vc = '0')");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where vc = (select c from t where c = '0')");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* (select nullable_column ...) is null On of each data
* type to test clone()
* @throws Exception
*/
public void testClone() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select * from s where (select s from s where i is "
+ "null) is null");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] {
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select * from s where " +
"(select i from s where i is null) is null");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] {
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select * from s where " +
"(select c from s where i is null) is null");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select * from s where " +
"(select vc from s where i is null) is null");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select * from s where " +
"(select b from s where i is null) is null");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select * from s where " +
"(select 1 from t where exists " +
"(select * from t where 1 = 0) and s = -1) is null");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* Test subquery in subqueries
* @throws Exception
*/
public void testSubqueryInSubquery() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery("select * from s where " +
"(select i from t where i = 0) = (select s from t where s = 0)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// multiple subqueries at the same level
rs = st.executeQuery("select * from s where i = " +
"(select s from t where s = 0) " +
"and s = (select i from t where i = 2)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery("select * from s where i = " +
"(select s from t where s = 0) " +
"and s = (select i from t where i = 0)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
// nested subqueries
rs = st.executeQuery(
"select * from s where i = " +
"(select i from t where s = " +
"(select i from t where s = 2))");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select * from s where i = " +
"(select i - 1 from t where s = " +
"(select i from t where s = 2))");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1", "1", "1", "1", "1"} };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* Test expression subqueries in select list
* @throws Exception
*/
public void testSubqueriesInSelect() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery("select (select i from t where 0=1) from s");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {null}, {null}, {null} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select " +
"(select i from t where i = 2) * " +
"(select s from t where i = 2) from s " +
"where i > " +
"(select i from t where i = 0) - " +
"(select i from t where i = 0)");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"4"} };
JDBC.assertFullResultSet(rs, expRS, true);
// in subqueries negative tests select * subquery
assertStatementError("42X38", st,
"select * from s where s in (select * from s)");
// incompatable types
rs = st.executeQuery(
"select * from s where s in (select b from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* Test constants in left, right and both sides of the subquery
* @throws Exception
*/
public void testConstants() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
// positive tests constants on left side of subquery
rs = st.executeQuery(
"select * from s where 1 in (select s from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where -1 in (select i from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select * from s where '1' in (select vc from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where 0 in (select b from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// constants in subquery select list
rs = st.executeQuery(
"select * from s where i in (select 1 from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where i in (select -1 from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select * from s where c in (select '1' from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1", "1", "1", "1", "1"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where b in (select 0 from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
// constants on both sides
rs = st.executeQuery(
"select * from s where 0 in (select 0 from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// compatable types
rs = st.executeQuery(
"select * from s where c in (select vc from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where vc in (select c from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where i in (select s from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where s in (select i from t)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* empty subquery result set
* @throws Exception
*/
public void testEmptyResultSet() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[] expColNames;
rs = st.executeQuery(
"select * from s where i in (select i from t where 1 = 0)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select * from s where (i in " +
"(select i from t where i = 0)) is null");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
}
/**
* Test subqueries in select list
*/
public void testSubqueriesInSelectList() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select ( i in (select i from t) ) a from s order by a");
expColNames = new String [] {"A"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] {{"false"}, {"true"}, {"true"}};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select " +
"( i in (select i from t where 1 = 0) ) a from s order by a");
expColNames = new String [] {"A"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] {{"false"}, {"false"}, {"false"}};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select " +
"( (i in " +
"(select i from t where 1 = 0)) is null ) a " +
"from s order by a");
expColNames = new String [] {"A"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] {{"false"}, {"false"}, {"false"}};
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* subquery under an or
* @throws Exception
*/
public void testSubqueryUnderOR() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select i from s where i = -1 or i in (select i from t)");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0"}, {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select i from s where i = 0 or i in " +
"(select i from t where i = -1)");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select i from s where i = -1 or i in " +
"(select i from t where i = -1 or i = 1)");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* distinct elimination
* @throws Exception
*/
public void testDistinct() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select i from s where i in (select i from s)");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0"}, {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select i from s where i in (select distinct i from s)");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0"}, {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select i from s ss where i in " +
"(select i from s where s.i = ss.i)");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0"}, {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select i from s ss where i in " +
"(select distinct i from s where s.i = ss.i)");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0"}, {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
// do consistency check on scans, etc.
rs = st.executeQuery("values ConsistencyChecker()");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
if (usingEmbedded()) {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
} else {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
}
}
/**
* Test Matches
*
*/
public void testMatches() throws Exception {
Statement st = createStatement();
// correlated subqueries negative tests multiple matches
// at parent level
assertStatementError("42X03", st,
"select * from s, t where exists (select i from tt)");
// match is against base table, but not derived column list
assertStatementError("42X04", st,
"select * from s ss (c1, c2, c3, c4, c5) where "
+ "exists (select i from tt)");
assertStatementError("42X04", st,
" select * from s ss (c1, c2, c3, c4, c5) where "
+ "exists (select ss.i from tt)");
// correlation name exists at both levels, but only column
// match is at parent level
assertStatementError("42X04", st,
"select * from s where exists (select s.i from tt s)");
// only match is at peer level
assertStatementError("42X04", st,
"select * from s where exists (select * from tt) and "
+ "exists (select ii from t)");
assertStatementError("42X04", st,
" select * from s where exists (select * from tt) "
+ "and exists (select tt.ii from t)");
// correlated column in a derived table
assertStatementError("42X04", st,
"select * from s, (select * from tt where i = ii) a");
assertStatementError("42X04", st,
" select * from s, (select * from tt where s.i = ii) a");
}
/**
* Test Simple correlated subqueries
*/
public void testSimpleCorrelated() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
// positive tests simple correlated subqueries
rs = st.executeQuery(
"select (select i from tt where ii = i and ii <> 1) from s");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {null}, {"0"}, {null} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("select " +
"(select s.i from tt where ii = s.i and ii <> 1) from s");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {null}, {"0"}, {null} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select (select s.i from ttt where iii = i) from s");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {null}, {null}, {null} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where exists " +
"(select * from tt where i = ii and ii <> 1)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where exists " +
"(select * from tt where s.i = ii and ii <> 1)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where exists " +
"(select * from ttt where i = iii)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// 1 case where we get a cardinality violation after a few
// rows
try{
rs = st.executeQuery(
"select (select i from tt where ii = i) from s");
}catch(SQLException sqle){
BaseJDBCTestCase.assertSQLState(
"Scalar subquery is only allowed to return a single row.","21000",sqle);
}
// skip levels to find match
rs = st.executeQuery(
"select * from s where exists (select * from ttt "
+ "where iii = (select 11 from tt where ii = i and ii <> 1))");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* join in subquery
* @throws Exception
*/
public void testJoinInSubqueries() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select * from s where i in " +
"(select i from t, tt where s.i <> i and i = ii)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select * from s where i in " +
"(select i from t, ttt where s.i < iii and s.i = t.i)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// join in outer query block
rs = st.executeQuery(
"select s.i, t.i from s, t where exists " +
"(select * from ttt where iii = 1)");
expColNames = new String [] {"I", "I"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select s.i, t.i from s, t where exists " +
"(select * from ttt where iii = 11)");
expColNames = new String [] {"I", "I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null},
{null, "0"},
{null, "1"},
{null, "1"},
{null, "2"},
{"0", null},
{"0", "0"},
{"0", "1"},
{"0", "1"},
{"0", "2"},
{"1", null},
{"1", "0"},
{"1", "1"},
{"1", "1"},
{"1", "2"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// joins in both query blocks
rs = st.executeQuery(
"select s.i, t.i from s, t where t.i = " +
"(select iii from ttt, tt where iii = t.i)");
expColNames = new String [] {"I", "I"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
rs = st.executeQuery(
"select s.i, t.i from s, t " +
"where t.i = (select ii from ttt, tt " +
"where s.i = t.i and t.i = tt.ii " +
"and iii = 22 and ii <> 1)");
expColNames = new String [] {"I", "I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* Test proper caching of subqueries in prepared statements
* This section (old Cloudscape reference 'Beetle 5382') tests the fix for
* a problem where sub-queries were executed not once per execution of the
* statement, but only once, when the statement was first executed.
* If the parameter changed between executions or if the data changed
* between executions then the top level select returned the wrong results.
* @throws Exception
*/
public void testSubqueriesInPS() throws Exception {
Statement st = createStatement();
PreparedStatement pSt;
ResultSetMetaData rsmd;
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
pSt = prepareStatement(
"select s.i from s where s.i in " +
"(select s.i from s, t where s.i = t.i and t.s = ?)");
rs = st.executeQuery("values(0)");
rs.next();
rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++)
pSt.setObject(i, rs.getObject(i));
rs = pSt.executeQuery();
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("values(1)");
rs.next();
rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++)
pSt.setObject(i, rs.getObject(i));
rs = pSt.executeQuery();
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
setAutoCommit(false);
pSt = prepareStatement(
"select s.i from s where s.i in " +
"(select s.i from s, t where s.i = t.i and t.s = 3)");
rs = pSt.executeQuery();
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
setAutoCommit(false);
st.executeUpdate("insert into t(i,s) values(1,3)");
rs = pSt.executeQuery();
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"} };
JDBC.assertFullResultSet(rs, expRS, true);
rollback();
}
/**
* correlated subquery in select list of a derived table
* @throws Exception
*/
public void testSubuqeryInSelectListOfDerivedTable() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select * from " +
"(select (select iii from ttt " +
"where sss > i and " +
"sss = iii and iii <> 11) " +
"from s) a");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {null}, {"22"}, {"22"} };
JDBC.assertFullResultSet(rs, expRS, true);
// bigint and subqueries
st.executeUpdate("create table li(i int, s smallint, l bigint)");
st.executeUpdate("insert into li values (null, null, null)");
st.executeUpdate("insert into li values (1, 1, 1)");
st.executeUpdate("insert into li values (2, 2, 2)");
rs = st.executeQuery(
"select l from li o where l = " +
"(select i from li i where o.l = i.i)");
expColNames = new String [] {"L"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select l from li o where l = " +
"(select s from li i where o.l = i.s)");
expColNames = new String [] {"L"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select l from li o where l = " +
"(select l from li i where o.l = i.l)");
expColNames = new String [] {"L"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select l from li where l in (select i from li)");
expColNames = new String [] {"L"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select l from li where l in (select s from li)");
expColNames = new String [] {"L"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select l from li where l in (select l from li)");
expColNames = new String [] {"L"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* Some extra tests for subquery flattening on table expressions
* (remapColumnReferencesToExpressions() binary list node
* @throws Exception
*/
public void testSubqueryFlattening() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select i in (1,2) from (select i from s) as tmp(i)");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] {{null}, {"false"}, {"true"}};
JDBC.assertFullResultSet(rs, expRS, true);
// conditional expression
assertStatementError("42X01", st,
"select i = 1 ? 1 : i from (select i from s) as tmp(i)");
// more tests for correlated column resolution
rs = st.executeQuery(
"select * from s where i = (values i)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select t.* from s, t where t.i = (values s.i)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select * from s where i in (values i)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"select t.* from s, t where t.i in (values s.i)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* tests for not needing to do cardinality check
* @throws Exception
*/
public void testNoNeedForCardinalityCheck() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select * from s where i = " +
"(select min(i) from s where i is not null)");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"0", "0", "0", "0", "0"} };
JDBC.assertFullResultSet(rs, expRS, true);
assertStatementError("21000", st,
"select * from s where i = (select min(i) from s group by i)");
// tests for distinct expression subquery
st.executeUpdate("create table dist1 (c1 int)");
st.executeUpdate("create table dist2 (c1 int)");
st.executeUpdate("insert into dist1 values null, 1, 2");
st.executeUpdate("insert into dist2 values null, null");
// no match, no violation
rs = st.executeQuery(
"select * from dist1 where c1 = " +
"(select distinct c1 from dist2)");
expColNames = new String [] {"C1"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// violation
st.executeUpdate("insert into dist2 values 1");
assertStatementError("21000", st,
"select * from dist1 where c1 = " +
"(select distinct c1 from dist2)");
// match, no violation
assertUpdateCount(st, 3, "update dist2 set c1 = 2");
rs = st.executeQuery(
"select * from dist1 where c1 = " +
"(select distinct c1 from dist2)");
expColNames = new String [] {"C1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
st.executeUpdate("drop table dist1");
st.executeUpdate("drop table dist2");
// update
st.executeUpdate("create table u " +
"(i int, s smallint, c char(30), vc char(30), b bigint)");
st.executeUpdate("insert into u select * from s");
rs = st.executeQuery("select * from u");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
assertStatementError("42821", st,
"update u set b = exists " +
"(select b from t) where " +
"vc <> (select vc from s where vc = '1')");
rs = st.executeQuery("select * from u");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
assertUpdateCount(st, 3,"delete from u");
st.executeUpdate("insert into u select * from s");
// delete
assertUpdateCount(st, 2,
"delete from u where c < (select c from t where c = '2')");
rs = st.executeQuery("select * from u");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null}
};
JDBC.assertFullResultSet(rs, expRS, true);
// restore u
assertUpdateCount(st, 1, "delete from u");
st.executeUpdate("insert into u select * from s");
}
/**
* check clean up when errors occur in subqueries insert
* @throws Exception
*/
public void testErrorsInNestedSubqueries() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
assertStatementError("22012", st,
"insert into u select * from s s_outer where i = " +
"(select s_inner.i/(s_inner.i-1) from s s_inner " +
"where s_outer.i = s_inner.i)");
rs = st.executeQuery("select * from u");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// delete
assertStatementError("22012", st,
"delete from u " +
"where i = (select i/(i-1) from s where u.i = s.i)");
rs = st.executeQuery("select * from u");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// update
assertStatementError("22012", st,
"update u set i = (select i from s where u.i = s.i) " +
"where i = (select i/(i-1) from s where u.i = s.i)");
assertStatementError("22012", st,
"update u set i = (select i/i-1 from s where u.i = s.i) " +
"where i = (select i from s where u.i = s.i)");
rs = st.executeQuery("select * from u");
expColNames = new String [] {"I", "S", "C", "VC", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{null, null, null, null, null},
{"0", "0", "0", "0", "0"},
{"1", "1", "1", "1", "1"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// error in nested subquery
assertStatementError("21000", st,
"select (select (select (select i from s) from s) from s) from s");
// do consistency check on scans, etc.
rs = st.executeQuery("values ConsistencyChecker()");
expColNames = new String [] {"1"};
JDBC.assertColumnNames(rs, expColNames);
if (usingEmbedded()) {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
} else {
expRS = new String[][]
{ { "No open scans, etc.\n16 dependencies found" } };
JDBC.assertFullResultSet(rs, expRS, true);
}
// reset autocommit
setAutoCommit(true);
}
/**
* subquery with groupby and having clause
*/
public void testSubqueryWithClause() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
rs = st.executeQuery(
"select distinct vc, i from t as myt1 " +
"where s <= (select max(myt1.s) from t as myt2 " +
"where myt1.vc = myt2.vc " +
"and myt1.s <= myt2.s group by s " +
"having count(distinct s) <= 3)");
expColNames = new String [] {"VC", "I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0"},
{"1", "1"},
{"2", "2"}
};
JDBC.assertFullResultSet(rs, expRS, true);
// subquery with having clause but no groupby
rs = st.executeQuery(
"select distinct vc, i from t as myt1 " +
"where s <= (select max(myt1.s) from t as myt2 " +
"where myt1.vc = myt2.vc and myt1.s <= myt2.s " +
"having count(distinct s) <= 3)");
expColNames = new String [] {"VC", "I"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"0", "0"},
{"1", "1"},
{"2", "2"}
};
JDBC.assertFullResultSet(rs, expRS, true);
}
/**
* DERBY-1007: Optimizer for subqueries can return
* incorrect cost estimates leading to sub-optimal join
* orders for the outer query. Before the patch for that
* issue, the following query plan will show T3 first and
* then T1-- but that's determined by the optimizer to be
* the "bad" join order. After the fix, the join order
* will show T1 first, then T3, which is correct (based on
* the optimizer's estimates).
* @throws Exception
*/
public void testDERBY1007() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
st.executeUpdate("create table t_1 (i int, j int)");
st.executeUpdate
("insert into T_1 values (1,1), (2,2), (3,3), (4,4), (5,5)");
st.executeUpdate("create table t_3 (a int, b int)");
st.executeUpdate(
"insert into T_3 values (1,1), (2,2), (3,3), (4,4)");
st.executeUpdate("insert into t_3 values " +
"(6, 24), (7, 28), (8, 32), (9, 36), (10, 40)");
st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
rs = st.executeQuery(
"select x1.j, x2.b from (select distinct i,j from t_1) x1, " +
"(select distinct a,b from t_3) x2 " +
"where x1.i = x2.a order by x1.j, x2.b");
expColNames = new String [] {"J", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"1", "1"},
{"2", "2"},
{"3", "3"},
{"4", "4"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
String rts = rs.getString(1);
// Now verify the correct runtimeStatistics output
RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rts);
// print out the full stats if derby.tests.debug is true
println("full stats: \n" + rtsp.toString());
// Checking only on the sequence of T3 and T1 scans.
// If further checking is needed, uncomment more lines.
rtsp.assertSequence(
new String[] {
"Source result set:",
"_Project-Restrict ResultSet (5):",
"_Source result set:",
"__Hash Join ResultSet:",
"__Left result set:",
"___Distinct Scan ResultSet for T_1 at read committed isolation level using instantaneous share row locking:",
"____Bit set of columns fetched=All",
"____Scan type=heap",
"__Right result set:",
"___Hash Table ResultSet (4):",
"___Source result set:",
"____Distinct Scan ResultSet for T_3 at read committed isolation level using instantaneous share row locking:",
"_____Bit set of columns fetched=All",
"_____Scan type=heap"
});
st.executeUpdate("drop table t_1");
st.executeUpdate("drop table t_3");
}
/**
* DERBY-781: Materialize subqueries where possible to avoid creating
* invariant result sets many times. This test case executes a query that
* that has subqueries twice: the first time the tables have only a few
* rows in them; the second time they have hundreds of rows in them.
*/
public void testDERBY781() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
st.executeUpdate("create table t1 (i int, j int)");
st.executeUpdate("create table t2 (i int, j int)");
st.executeUpdate
("insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5)");
st.executeUpdate
("insert into t2 values (1,1), (2,2), (3,3), (4,4), (5,5)");
st.executeUpdate("create table t3 (a int, b int)");
st.executeUpdate("create table t4 (a int, b int)");
st.executeUpdate
("insert into t3 values (2,2), (4,4), (5,5)");
st.executeUpdate
("insert into t4 values (2,2), (4,4), (5,5)");
// Use of the term "DISTINCT" makes it so that we don't flatten
// the subqueries.
st.executeUpdate("create view V1 as " +
"select distinct T1.i, T2.j from T1, T2 where T1.i = T2.i");
st.executeUpdate("create view V2 as " +
"select distinct T3.a, T4.b from T3, T4 where T3.a = T4.a");
st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
/* Run the test query the first time, with only a small number
* of rows in each table. Before the patch for DERBY-781
* the optimizer would have chosen a nested loop join, which
* means that we would generate the result set for the inner
* view multiple times. After DERBY-781 the optimizer will
* choose to do a hash join and thereby materialize the inner
* result set, thus improving performance. Should see a Hash join
* as the top-level join with a HashTableResult as the right child
* of the outermost join.
*/
rs = st.executeQuery(
"select * from V1, V2 where V1.j = V2.b and V1.i in (1,2,3,4,5)");
expColNames = new String [] {"I", "J", "A", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"2", "2", "2", "2"},
{"4", "4", "4", "4"},
{"5", "5", "5", "5"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
String rts = rs.getString(1);
// Now verify the correct runtimeStatistics output
RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rts);
// print out the full stats if derby.tests.debug is true
println("full stats: \n" + rtsp.toString());
// the essentials are getting checked as per the comments
// above. If further checking is needed, uncomment more lines.
rtsp.assertSequence(
new String[] {
"Hash Join ResultSet:",
//"Left result set:",
//"_Sort ResultSet:",
//"_Source result set:",
//"__Project-Restrict ResultSet (7):",
//"__Source result set:",
//"___Nested Loop Join ResultSet:",
//"___Left result set:",
//"____Project-Restrict ResultSet (5):",
//"____Source result set:",
//"_____Table Scan ResultSet for T1 at read committed " +
// "isolation level using share row locking chosen " +
// "by the optimizer",
//"______Bit set of columns fetched={0}",
//"______Scan type=heap",
//"___Right result set:",
//"____Table Scan ResultSet for T2 at read committed " +
// "isolation level using share row locking chosen " +
// "by the optimizer",
//"_____Bit set of columns fetched=All",
//"_____Scan type=heap",
//"______Operator: =",
"Right result set:",
"_Hash Table ResultSet (13):"
//"_Source result set:",
//"__Sort ResultSet:",
//"__Source result set:",
//"___Project-Restrict ResultSet (12):",
//"___Source result set:",
//"____Hash Join ResultSet:",
//"____Left result set:",
//"_____Table Scan ResultSet for T3 at read committed " +
// "isolation level using share row locking chosen " +
// "by the optimizer",
//"______Bit set of columns fetched=All",
//"______Scan type=heap"
//,
// after this, there's something peculiar with the
// 'Right result set' line output, and this RuntimeStatisticsParser
// method cannot find any further matches...
//"___Right result set:",
//"_____Hash Scan ResultSet for T4 at read committed " +
// "isolation level using instantaneous share row" +
// "locking: ",
//"______Bit set of columns fetched=All",
//"______Scan type=heap",
//"_______Operator: ="
});
// ...so checking on the remaining output another way.
assertTrue(rtsp.findString("Right result set:",3));
assertTrue(rtsp.findString("Hash Scan ResultSet for T4 at read " +
"committed isolation level using instantaneous share row " +
"locking: ",1));
//assertTrue(rtsp.findString("Bit set of columns fetched=All",2));
//assertTrue(rtsp.findString("Scan type=heap",4));
// Now add more data to the tables.
st.executeUpdate("insert into t1 select * from t2");
st.executeUpdate("insert into t2 select * from t1");
st.executeUpdate("insert into t2 select * from t1");
st.executeUpdate("insert into t1 select * from t2");
st.executeUpdate("insert into t2 select * from t1");
st.executeUpdate("insert into t1 select * from t2");
st.executeUpdate("insert into t2 select * from t1");
st.executeUpdate("insert into t1 select * from t2");
st.executeUpdate("insert into t2 select * from t1");
st.executeUpdate("insert into t1 select * from t2");
st.executeUpdate("insert into t3 select * from t4");
st.executeUpdate("insert into t4 select * from t3");
st.executeUpdate("insert into t3 select * from t4");
st.executeUpdate("insert into t4 select * from t3");
st.executeUpdate("insert into t3 select * from t4");
st.executeUpdate("insert into t4 select * from t3");
st.executeUpdate("insert into t3 select * from t4");
st.executeUpdate("insert into t4 select * from t3");
st.executeUpdate("insert into t3 select * from t4");
st.executeUpdate("insert into t4 select * from t3");
st.executeUpdate("insert into t3 select * from t4");
/* Drop the views and recreate them with slightly different
* names. The reason we use different names is to ensure that
* the query will be "different" from the last time and thus we'll
* we'll go through optimization again (instead of just using
* the cached plan from last time).
*/
st.executeUpdate("drop view v1");
st.executeUpdate("drop view v2");
// Use of the term "DISTINCT" makes it so that we don't flatten
// the subqueries.
st.executeUpdate("create view VV1 as " +
"select distinct T1.i, T2.j from T1, T2 where T1.i = T2.i");
st.executeUpdate("create view VV2 as " +
"select distinct T3.a, T4.b from T3, T4 where T3.a = T4.a");
// Now execute the query again using the larger tables.
rs = st.executeQuery(
"select * from VV1, VV2 " +
"where VV1.j = VV2.b and VV1.i in (1,2,3,4,5)");
expColNames = new String [] {"I", "J", "A", "B"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"2", "2", "2", "2"},
{"4", "4", "4", "4"},
{"5", "5", "5", "5"}
};
JDBC.assertFullResultSet(rs, expRS, true);
rs = st.executeQuery(
"values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
rts = rs.getString(1);
// Now verify the correct runtimeStatistics output
rtsp = new RuntimeStatisticsParser(rts);
// print out the full stats if derby.tests.debug is true
println("full stats: \n" + rtsp.toString());
// the essentials are getting checked as per the comments
// above. If more detailed checking is needed, uncomment lines.
rtsp.assertSequence(
new String[] {
"Hash Join ResultSet:",
//"Left result set:",
//"_Sort ResultSet:",
"_Rows input = 53055",
//"_Source result set:",
//"__Project-Restrict ResultSet (7):",
//"__Source result set:",
//"___Hash Join ResultSet:",
//"___Left result set:",
//"____Project-Restrict ResultSet (5):",
//"____Source result set:",
//"_____Table Scan ResultSet for T1 at read committed " +
// "isolation level using share row locking chosen " +
// "by the optimizer",
//"______Bit set of columns fetched={0}",
//"______Scan type=heap",
//"___Right result set:",
// with fewer roles, the optimizer chose a Table Scan
//"____Hash Scan ResultSet for T2 at read committed " +
// "isolation level using instantaneous share row " +
// "locking: ",
//"_____Bit set of columns fetched=All",
//"_____Scan type=heap",
//"______Operator: =",
//"Right result set:",
//"_Hash Table ResultSet (13):",
//"_Source result set:",
//"__Sort ResultSet:",
//"__Source result set:",
//"___Project-Restrict ResultSet (12):",
//"___Source result set:",
//"____Hash Join ResultSet:",
//"____Left result set:",
// with 4 rows, the optimizer used a Table Scan on T3
// for left node and a Hash Scan on T4 for the right.
//"_____Table Scan ResultSet for T4 at read committed " +
// "isolation level using share row locking chosen " +
// "by the optimizer",
//"______Bit set of columns fetched=All",
//"______Scan type=heap",
"____Right result set:",
"_____Hash Scan ResultSet for T3 at read committed " +
"isolation level using instantaneous share row " +
"locking: "
//,
//"______Bit set of columns fetched={0}",
//"______Scan type=heap"
});
// clean up.
st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
st.executeUpdate("drop view vv1");
st.executeUpdate("drop view vv2");
st.executeUpdate("drop table t1");
st.executeUpdate("drop table t2");
st.executeUpdate("drop table t3");
st.executeUpdate("drop table t4");
}
/**
* DERBY-1574: Subquery in COALESCE gives NPE due to
* preprocess not implemented for that node type
*/
public void testSubqueryInCOALESCE() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
st.executeUpdate("create table t1 (id int)");
st.executeUpdate("create table t2 (i integer primary key, j int)");
st.executeUpdate("insert into t1 values 1,2,3,4,5");
st.executeUpdate("insert into t2 values (1,1),(2,4),(3,9),(4,16)");
assertUpdateCount(st, 5,
"update t1 set id = coalesce((select j from t2 " +
"where t2.i=t1.id), 0)");
rs = st.executeQuery("select * from t1");
expColNames = new String [] {"ID"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"4"}, {"9"}, {"16"}, {"0"} };
JDBC.assertFullResultSet(rs, expRS, true);
st.executeUpdate("drop table t1");
st.executeUpdate("drop table t2");
}
/**
* Test the fix for DERBY-2218
* @throws Exception
*/
public void testDERBY_2218() throws Exception {
Statement st = createStatement();
ResultSet rs = null;
String[] expColNames;
st.executeUpdate("create table t1 (i int)");
rs = st.executeQuery(
"select * from t1 " +
"where i in (1, 2, (values cast(null as integer)))");
expColNames = new String [] {"I"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// expect error, this used to throw NPE
assertStatementError("42X07", st,
"select * from t1 where i in (1, 2, (values null))");
assertStatementError("42X07", st,
"select * from t1 where i in " +
"(select i from t1 where i in (1, 2, (values null)))");
// expect error
assertStatementError("42X07", st,
"select * from t1 where exists (values null)");
assertStatementError("42X07", st,
"select * from t1 where exists " +
"(select * from t1 where exists(values null))");
assertStatementError("42X07", st,
"select i from t1 where exists " +
"(select i from t1 where exists(values null))");
assertStatementError("42X07", st,
"select * from (values null) as t2");
assertStatementError("42X07", st,
"select * from t1 where exists " +
"(select 1 from (values null) as t2)");
assertStatementError("42X07", st,
"select * from t1 where exists " +
"(select * from (values null) as t2)");
st.executeUpdate("drop table t1");
st.close();
}
/**
* DERBY-4549: NPE in JBitSet
*/
public void testDERBY_4549() throws Exception {
Statement st = createStatement();
PreparedStatement pSt;
ResultSet rs = null;
String[][] expRS;
String[] expColNames;
st.executeUpdate("create table ABC (ID int)");
st.executeUpdate("create table DEF (ID int)");
//compilation of the statement used to fail with NPE
pSt = prepareStatement(
"select * from ABC t1 " +
"where (select distinct t2.ID from DEF t2) in " +
"(select t3.ID from DEF t3)");
// empty tables, should give empty result
rs = pSt.executeQuery();
expColNames = new String [] {"ID"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
// now, test with data in the tables
st.executeUpdate("insert into ABC values 1, 2");
rs = pSt.executeQuery();
expColNames = new String [] {"ID"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
st.executeUpdate("insert into DEF values 2");
rs = pSt.executeQuery();
expColNames = new String [] {"ID"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
st.executeUpdate("insert into DEF values 2");
rs = pSt.executeQuery();
expColNames = new String [] {"ID"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][] { {"1"}, {"2"} };
JDBC.assertFullResultSet(rs, expRS, true);
st.executeUpdate("insert into DEF values 3");
// will fail because left operand of IN is no longer scalar
// expect ERROR 21000:
// Scalar subquery is only allowed to return a single row
assertStatementError("21000", pSt);
st.executeUpdate("drop table ABC");
st.executeUpdate("drop table DEF");
}
}