Skip to content

Commit 4e33b7e

Browse files
malwaregarrymalwaregarry
authored
[WIP] Generic NoREC oracle (sqlancer#928)
* Add NoRECGenerator interface * Add `toSum` method for Select interface Used in the NoREC oracle to aggregate unoptimized query results * Add `getRandomTableNonEmptyTables` function * Add generic NoREC oracle * Check for null arguments in NoREC oracle constructor * Update NoREC oracle implementation and add comments for generator
1 parent 82bb0c3 commit 4e33b7e

3 files changed

Lines changed: 212 additions & 0 deletions

File tree

‎src/sqlancer/common/gen/NoRECGenerator.java‎

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
package sqlancer.common.gen;
2+
3+
import java.util.List;
4+
5+
import sqlancer.common.ast.newast.Expression;
6+
import sqlancer.common.ast.newast.Join;
7+
import sqlancer.common.ast.newast.Select;
8+
import sqlancer.common.schema.AbstractTable;
9+
import sqlancer.common.schema.AbstractTableColumn;
10+
import sqlancer.common.schema.AbstractTables;
11+
12+
public interface NoRECGenerator<J extends Join<E, T, C>, E extends Expression<C>, T extends AbstractTable<C, ?, ?>, C extends AbstractTableColumn<?, ?>> {
13+
14+
NoRECGenerator<J, E, T, C> setTablesAndColumns(AbstractTables<T, C> tables);
15+
16+
E generateBooleanExpression();
17+
18+
Select<J, E, T, C> generateSelect();
19+
20+
List<J> getRandomJoinClauses();
21+
22+
List<E> getTableRefs();
23+
24+
/**
25+
* Generates a query string that is likely to be optimized by the DBMS.
26+
*
27+
* @param select
28+
* the base select expression used to generate the query
29+
* @param whereCondition
30+
* a condition where records will be checked with
31+
* @param shouldUseAggregate
32+
* whether to aggregate the record counts (`true`) or display records as is (`false`)
33+
*
34+
* @return a query string to be executed
35+
*/
36+
String generateOptimizedQueryString(Select<J, E, T, C> select, E whereCondition, boolean shouldUseAggregate);
37+
38+
/**
39+
* Generates a query string that is unlikely to be optimized by the DBMS.
40+
*
41+
* @param select
42+
* the base select expression used to generate the query
43+
* @param whereCondition
44+
* the condition each record will be checked with
45+
*
46+
* @return a query string to be executed
47+
*/
48+
String generateUnoptimizedQueryString(Select<J, E, T, C> select, E whereCondition);
49+
}

‎src/sqlancer/common/oracle/NoRECOracle.java‎

Lines changed: 141 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,141 @@
1+
package sqlancer.common.oracle;
2+
3+
import java.sql.SQLException;
4+
5+
import sqlancer.IgnoreMeException;
6+
import sqlancer.Randomly;
7+
import sqlancer.SQLGlobalState;
8+
import sqlancer.common.ast.newast.Expression;
9+
import sqlancer.common.ast.newast.Join;
10+
import sqlancer.common.ast.newast.Select;
11+
import sqlancer.common.gen.NoRECGenerator;
12+
import sqlancer.common.query.ExpectedErrors;
13+
import sqlancer.common.query.SQLQueryAdapter;
14+
import sqlancer.common.query.SQLancerResultSet;
15+
import sqlancer.common.schema.AbstractSchema;
16+
import sqlancer.common.schema.AbstractTable;
17+
import sqlancer.common.schema.AbstractTableColumn;
18+
import sqlancer.common.schema.AbstractTables;
19+
20+
public class NoRECOracle<J extends Join<E, T, C>, E extends Expression<C>, S extends AbstractSchema<?, T>, T extends AbstractTable<C, ?, ?>, C extends AbstractTableColumn<?, ?>, G extends SQLGlobalState<?, S>>
21+
implements TestOracle<G> {
22+
23+
private final G state;
24+
25+
private NoRECGenerator<J, E, T, C> gen;
26+
private final ExpectedErrors errors;
27+
28+
private String lastQueryString;
29+
30+
public NoRECOracle(G state, NoRECGenerator<J, E, T, C> gen, ExpectedErrors expectedErrors) {
31+
if (state == null || gen == null || expectedErrors == null) {
32+
throw new IllegalArgumentException("Null variables used to initialize test oracle.");
33+
}
34+
this.state = state;
35+
this.gen = gen;
36+
this.errors = expectedErrors;
37+
}
38+
39+
@Override
40+
public void check() throws SQLException {
41+
S schema = state.getSchema();
42+
AbstractTables<T, C> targetTables = TestOracleUtils.getRandomTableNonEmptyTables(schema);
43+
gen = gen.setTablesAndColumns(targetTables);
44+
45+
Select<J, E, T, C> select = gen.generateSelect();
46+
select.setJoinClauses(gen.getRandomJoinClauses());
47+
select.setFromList(gen.getTableRefs());
48+
49+
E randomWhereCondition = gen.generateBooleanExpression();
50+
51+
boolean shouldUseAggregate = Randomly.getBoolean();
52+
String optimizedQueryString = gen.generateOptimizedQueryString(select, randomWhereCondition,
53+
shouldUseAggregate);
54+
lastQueryString = optimizedQueryString;
55+
56+
String unoptimizedQueryString = gen.generateUnoptimizedQueryString(select, randomWhereCondition);
57+
58+
int optimizedCount = shouldUseAggregate ? extractCounts(optimizedQueryString, errors, state)
59+
: countRows(optimizedQueryString, errors, state);
60+
int unoptimizedCount = extractCounts(optimizedQueryString, errors, state);
61+
62+
if (optimizedCount == -1 || unoptimizedCount == -1) {
63+
throw new IgnoreMeException();
64+
}
65+
66+
if (unoptimizedCount != optimizedCount) {
67+
String queryFormatString = "-- %s;\n-- count: %d";
68+
String firstQueryStringWithCount = String.format(queryFormatString, optimizedQueryString, optimizedCount);
69+
String secondQueryStringWithCount = String.format(queryFormatString, unoptimizedQueryString,
70+
unoptimizedCount);
71+
state.getState().getLocalState()
72+
.log(String.format("%s\n%s", firstQueryStringWithCount, secondQueryStringWithCount));
73+
String assertionMessage = String.format("the counts mismatch (%d and %d)!\n%s\n%s", optimizedCount,
74+
unoptimizedCount, firstQueryStringWithCount, secondQueryStringWithCount);
75+
throw new AssertionError(assertionMessage);
76+
}
77+
}
78+
79+
@Override
80+
public String getLastQueryString() {
81+
return lastQueryString;
82+
}
83+
84+
private int countRows(String queryString, ExpectedErrors errors, SQLGlobalState<?, ?> state) {
85+
SQLQueryAdapter q = new SQLQueryAdapter(queryString, errors);
86+
87+
if (state.getOptions().logEachSelect()) {
88+
state.getLogger().writeCurrent(queryString);
89+
}
90+
91+
int count = 0;
92+
try (SQLancerResultSet rs = q.executeAndGet(state)) {
93+
if (rs == null) {
94+
return -1;
95+
} else {
96+
try {
97+
while (rs.next()) {
98+
count++;
99+
}
100+
} catch (SQLException e) {
101+
count = -1;
102+
}
103+
}
104+
} catch (Exception e) {
105+
if (e instanceof IgnoreMeException) {
106+
throw (IgnoreMeException) e;
107+
}
108+
throw new AssertionError(q.getQueryString(), e);
109+
}
110+
return count;
111+
}
112+
113+
private int extractCounts(String queryString, ExpectedErrors errors, SQLGlobalState<?, ?> state) {
114+
SQLQueryAdapter q = new SQLQueryAdapter(queryString, errors);
115+
if (state.getOptions().logEachSelect()) {
116+
state.getLogger().writeCurrent(queryString);
117+
}
118+
119+
int count = 0;
120+
try (SQLancerResultSet rs = q.executeAndGet(state)) {
121+
if (rs == null) {
122+
return -1;
123+
} else {
124+
try {
125+
while (rs.next()) {
126+
count += rs.getInt(1);
127+
}
128+
} catch (SQLException e) {
129+
count = -1;
130+
}
131+
}
132+
} catch (Exception e) {
133+
if (e instanceof IgnoreMeException) {
134+
throw (IgnoreMeException) e;
135+
}
136+
throw new AssertionError(q.getQueryString(), e);
137+
}
138+
return count;
139+
}
140+
141+
}

‎src/sqlancer/common/oracle/TestOracleUtils.java‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
package sqlancer.common.oracle;
2+
3+
import sqlancer.IgnoreMeException;
4+
import sqlancer.Randomly;
5+
import sqlancer.common.schema.AbstractSchema;
6+
import sqlancer.common.schema.AbstractTable;
7+
import sqlancer.common.schema.AbstractTableColumn;
8+
import sqlancer.common.schema.AbstractTables;
9+
10+
public final class TestOracleUtils {
11+
12+
private TestOracleUtils() {
13+
}
14+
15+
public static <T extends AbstractTable<C, ?, ?>, C extends AbstractTableColumn<?, ?>> AbstractTables<T, C> getRandomTableNonEmptyTables(
16+
AbstractSchema<?, T> schema) {
17+
if (schema.getDatabaseTables().isEmpty()) {
18+
throw new IgnoreMeException();
19+
}
20+
return new AbstractTables<>(Randomly.nonEmptySubset(schema.getDatabaseTables()));
21+
}
22+
}

0 commit comments

Comments
 (0)