/*
* Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
* Version 1.0, and under the Eclipse Public License, Version 1.0
* (http://h2database.com/html/license.html).
* Initial Developer: H2 Group
*/
package org.h2.test.synth;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Random;
import org.h2.test.TestBase;
import org.h2.test.db.Db;
import org.h2.test.db.Db.Prepared;
import org.h2.util.New;
/**
* This test executes random SQL statements to test if optimizations are working
* correctly.
*/
public class TestFuzzOptimizations extends TestBase {
private Connection conn;
/**
* Run just this test.
*
* @param a ignored
*/
public static void main(String... a) throws Exception {
TestBase.createCaller().init().test();
}
public void test() throws Exception {
deleteDb("optimizations");
conn = getConnection("optimizations");
if (!config.diskResult) {
testIn();
}
testGroupSorted();
testInSelect();
conn.close();
deleteDb("optimizations");
}
/*
drop table test0;
drop table test1;
create table test0(a int, b int, c int);
create index idx_1 on test0(a);
create index idx_2 on test0(b, a);
create table test1(a int, b int, c int);
insert into test0 select x / 100,
mod(x / 10, 10), mod(x, 10)
from system_range(0, 999);
update test0 set a = null where a = 9;
update test0 set b = null where b = 9;
update test0 set c = null where c = 9;
insert into test1 select * from test0;
select * from test0 where
b in(null, 0) and a in(2, null, null)
order by 1, 2, 3;
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");
}
private String getRandomCondition(Random random, ArrayList<String> params,
String[] columns, String[] compares, String[] values) {
int comp = 1 + random.nextInt(4);
StringBuilder buff = new StringBuilder();
for (int j = 0; j < comp; j++) {
if (j > 0) {
buff.append(random.nextBoolean() ? " and " : " or ");
}
String column = columns[random.nextInt(columns.length)];
String compare = compares[random.nextInt(compares.length)];
buff.append(column).append(' ').append(compare);
if (compare.endsWith("in(")) {
int len = 1+random.nextInt(3);
for (int k = 0; k < len; k++) {
if (k > 0) {
buff.append(", ");
}
String value = values[random.nextInt(values.length)];
buff.append(value);
if ("?".equals(value)) {
value = values[random.nextInt(values.length - 2)];
params.add(value);
}
}
buff.append(")");
} else if (compare.endsWith("(select")) {
String col = columns[random.nextInt(columns.length)];
buff.append(" ").append(col).append(" from test1 where ");
String condition = getRandomCondition(random, params, columns, compares, values);
buff.append(condition);
buff.append(")");
} else {
String value = values[random.nextInt(values.length)];
buff.append(value);
if ("?".equals(value)) {
value = values[random.nextInt(values.length - 2)];
params.add(value);
}
}
}
return buff.toString();
}
private void testInSelect() {
Db db = new Db(conn);
db.execute("CREATE TABLE TEST(A INT, B INT)");
db.execute("CREATE INDEX IDX ON TEST(A)");
db.execute("INSERT INTO TEST SELECT X/4, MOD(X, 4) FROM SYSTEM_RANGE(1, 16)");
db.execute("UPDATE TEST SET A = NULL WHERE A = 0");
db.execute("UPDATE TEST SET B = NULL WHERE B = 0");
Random random = new Random();
long seed = random.nextLong();
println("seed: " + seed);
for (int i = 0; i < 100; i++) {
String column = random.nextBoolean() ? "A" : "B";
String value = new String[] { "NULL", "0", "A", "B" }[random.nextInt(4)];
String compare = random.nextBoolean() ? "A" : "B";
int x = random.nextInt(3);
String sql1 = "SELECT * FROM TEST T WHERE " + column + "+0 " +
"IN(SELECT " + value + " FROM TEST I WHERE I." + compare + "=?) ORDER BY 1, 2";
String sql2 = "SELECT * FROM TEST T WHERE " + column + " " +
"IN(SELECT " + value + " FROM TEST I WHERE I." + compare + "=?) ORDER BY 1, 2";
List<Map<String, Object>> a = db.prepare(sql1).set(x).query();
List<Map<String, Object>> b = db.prepare(sql2).set(x).query();
assertTrue("seed: " + seed + " sql: " + sql1 + " a: " + a + " b: " + b, a.equals(b));
}
db.execute("DROP TABLE TEST");
}
private void testGroupSorted() {
Db db = new Db(conn);
db.execute("CREATE TABLE TEST(A INT, B INT, C INT)");
Random random = new Random();
long seed = random.nextLong();
println("seed: " + seed);
for (int i = 0; i < 100; i++) {
Prepared p = db.prepare("INSERT INTO TEST VALUES(?, ?, ?)");
p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
p.execute();
}
int len = getSize(1000, 3000);
for (int i = 0; i < len / 10; i++) {
db.execute("CREATE TABLE TEST_INDEXED AS SELECT * FROM TEST");
int jLen = 1 + random.nextInt(2);
for (int j = 0; j < jLen; j++) {
String x = "CREATE INDEX IDX" + j + " ON TEST_INDEXED(";
int kLen = 1 + random.nextInt(2);
for (int k = 0; k < kLen; k++) {
if (k > 0) {
x += ",";
}
x += new String[] { "A", "B", "C" }[random.nextInt(3)];
}
db.execute(x + ")");
}
for (int j = 0; j < 10; j++) {
String x = "SELECT ";
for (int k = 0; k < 3; k++) {
if (k > 0) {
x += ",";
}
x += new String[] { "SUM(A)", "MAX(B)", "AVG(C)", "COUNT(B)" }[random.nextInt(4)];
x += " S" + k;
}
x += " FROM ";
String group = " GROUP BY ";
int kLen = 1 + random.nextInt(2);
for (int k = 0; k < kLen; k++) {
if (k > 0) {
group += ",";
}
group += new String[] { "A", "B", "C" }[random.nextInt(3)];
}
group += " ORDER BY 1, 2, 3";
List<Map<String, Object>> a = db.query(x + "TEST" + group);
List<Map<String, Object>> b = db.query(x + "TEST_INDEXED" + group);
assertEquals(a.toString(), b.toString());
assertTrue(a.equals(b));
}
db.execute("DROP TABLE TEST_INDEXED");
}
db.execute("DROP TABLE TEST");
}
}