rs = st.executeQuery(
" values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
if(usingEmbedded()){
RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
rs.close();
assertTrue(rtsp.usedTableScan());
assertFalse(rtsp.usedDistinctScan());
}
// nested loop exists join, right side should be
// ProjectRestrict on index scan with start and stop keys
//Comparisons between 'REAL' and 'CHAR (UCS_BASIC)' are not supported
assertStatementError("42818", st,
"select c1 from t2 where c1 in (select c1 from t1 "
+ "where c1 in (5, t2.c2) and c1 in ('5', '7'))");
st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
rs=st.executeQuery("SELECT C1 FROM t2");
while(rs.next()){}
rs.close();
rs = st.executeQuery(
" values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
if(usingEmbedded()){
RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
rs.close();
assertTrue(rtsp.usedTableScan());
assertFalse(rtsp.usedDistinctScan());
}
// hash exists join, right side PR on hash index scan, no
// start/stop key, next qualifier "=".
rs = st.executeQuery(
"select c1 from t2 where c1 in (select c1 from t1 "
+ "where c1 in (5, t2.c2))");
expColNames = new String [] {"C1"};
JDBC.assertColumnNames(rs, expColNames);
expRS = new String [][]
{
{"8.0"},
{"5.0"}
};
JDBC.assertFullResultSet(rs, expRS, true);
st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
rs=st.executeQuery("SELECT C1 FROM t2");
while(rs.next()){}
rs.close();
rs = st.executeQuery(
" values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
if(usingEmbedded()){
RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
rs.close();
assertTrue(rtsp.usedTableScan());
assertFalse(rtsp.usedDistinctScan());
}
// hash exists join, right side PR on hash index scan,
// still no start/stop key, next qualifier "=". It still
// doesn't have start/stop key because c1 in ('5', '7') is
// blocked out by 2 others.
//Comparisons between 'REAL' and 'CHAR (UCS_BASIC)' are not supported
assertStatementError("42818", st,
"select c1 from t2 where c1 in (select c1 from t1 "
+ "where c1 in (5, t2.c2) and c1 in ('5', '7'))");
st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
rs=st.executeQuery("SELECT C1 FROM t2");
while(rs.next()){}
rs.close();
rs = st.executeQuery(
" values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
if(usingEmbedded()){
RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
rs.close();
assertTrue(rtsp.usedTableScan());
assertFalse(rtsp.usedDistinctScan());
}
st.executeUpdate(
" create index i12 on t1 (c1, c2)");
// at push "in" time, we determined that it is key and we
// can push; but at hash time we determined it's not key.
// Now the key is it should be filtered out, otherwise we
// get exception.
rs = st.executeQuery(
"select c1 from t2 where c1 in (select c1 from t1 "
+ "where c2 in (5, t2.c2))");
expColNames = new String [] {"C1"};
JDBC.assertColumnNames(rs, expColNames);
JDBC.assertDrainResults(rs, 0);
st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
rs=st.executeQuery("SELECT C1 FROM t2");
while(rs.next()){}
rs.close();
rs = st.executeQuery(
" values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
rs.next();
if(usingEmbedded()){
RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
rs.close();
assertTrue(rtsp.usedTableScan());
assertFalse(rtsp.usedDistinctScan());
}
// just some more tests in different situations, not for
// the bug 4316 many items