select * from test1 where
b in(null, 0) and a in(2, null, null)
order by 1, 2, 3;
*/
private void testIn() throws SQLException {
Db db = new Db(conn);
db.execute("create table test0(a int, b int, c int)");
db.execute("create index idx_1 on test0(a)");
db.execute("create index idx_2 on test0(b, a)");
db.execute("create table test1(a int, b int, c int)");
db.execute("insert into test0 select x / 100, mod(x / 10, 10), mod(x, 10) from system_range(0, 999)");
db.execute("update test0 set a = null where a = 9");
db.execute("update test0 set b = null where b = 9");
db.execute("update test0 set c = null where c = 9");
db.execute("insert into test1 select * from test0");
// this failed at some point
Prepared p = db.prepare("select * from test0 where b in(" +
"select a from test1 where a <? and a not in(" +
"select c from test1 where b <=10 and a in(" +
"select a from test1 where b =1 or b =2 and b not in(2))) or c <>a) " +
"and c in(0, 10) and c in(10, 0, 0) order by 1, 2, 3");
p.set(1);
p.execute();
Random seedGenerator = new Random();
String[] columns = new String[] { "a", "b", "c" };
String[] values = new String[] { null, "0", "0", "1", "2", "10", "a", "?" };
String[] compares = new String[] { "in(", "not in(", "=", "=", ">",
"<", ">=", "<=", "<>", "in(select", "not in(select" };
int size = getSize(100, 1000);
for (int i = 0; i < size; i++) {
long seed = seedGenerator.nextLong();
println("seed: " + seed);
Random random = new Random(seed);
ArrayList<String> params = New.arrayList();
String condition = getRandomCondition(random, params, columns, compares, values);
// System.out.println(condition + " " + params);
PreparedStatement prep0 = conn.prepareStatement(
"select * from test0 where " + condition
+ " order by 1, 2, 3");
PreparedStatement prep1 = conn.prepareStatement(
"select * from test1 where " + condition
+ " order by 1, 2, 3");
for (int j = 0; j < params.size(); j++) {
prep0.setString(j + 1, params.get(j));
prep1.setString(j + 1, params.get(j));
}
ResultSet rs0 = prep0.executeQuery();
ResultSet rs1 = prep1.executeQuery();
assertEquals("seed: " + seed + " " + condition, rs0, rs1);
if (params.size() > 0) {
for (int j = 0; j < params.size(); j++) {
String value = values[random.nextInt(values.length - 2)];
params.set(j, value);
prep0.setString(j + 1, value);
prep1.setString(j + 1, value);
}
assertEquals("seed: " + seed + " " + condition, rs0, rs1);
}
}
db.execute("drop table test0, test1");
}