Skip to content

Commit aed8f36

Browse files
mriggermrigger
committed
[SQLite] Refactor the NoREC implementation
1 parent 74d9105 commit aed8f36

File tree

2 files changed

+70
-82
lines changed

2 files changed

+70
-82
lines changed

‎src/sqlancer/sqlite3/SQLite3Errors.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@ public static void addDeleteErrors(List<String> errors) {
1919

2020
public static void addExpectedExpressionErrors(Collection<String> errors) {
2121
errors.add("FTS expression tree is too large");
22+
errors.add("String or BLOB exceeds size limit");
2223
errors.add("[SQLITE_ERROR] SQL error or missing database (integer overflow)");
2324
errors.add("second argument to likelihood() must be a constant between 0.0 and 1.0");
2425
errors.add("ORDER BY term out of range");

‎src/sqlancer/sqlite3/oracle/SQLite3NoRECOracle.java‎

Lines changed: 69 additions & 82 deletions
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,7 @@
44
import java.sql.ResultSet;
55
import java.sql.SQLException;
66
import java.util.Arrays;
7+
import java.util.Collections;
78
import java.util.HashSet;
89
import java.util.List;
910
import java.util.Set;
@@ -18,14 +19,14 @@
1819
import sqlancer.sqlite3.SQLite3Errors;
1920
import sqlancer.sqlite3.SQLite3Provider.SQLite3GlobalState;
2021
import sqlancer.sqlite3.SQLite3Visitor;
22+
import sqlancer.sqlite3.ast.SQLite3Aggregate;
2123
import sqlancer.sqlite3.ast.SQLite3Expression;
2224
import sqlancer.sqlite3.ast.SQLite3Expression.Join;
2325
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3ColumnName;
2426
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3PostfixText;
2527
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3PostfixUnaryOperation;
2628
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3PostfixUnaryOperation.PostfixUnaryOperator;
2729
import sqlancer.sqlite3.ast.SQLite3Select;
28-
import sqlancer.sqlite3.ast.SQLite3Select.SelectType;
2930
import sqlancer.sqlite3.gen.SQLite3Common;
3031
import sqlancer.sqlite3.gen.SQLite3ExpressionGenerator;
3132
import sqlancer.sqlite3.schema.SQLite3Schema;
@@ -35,20 +36,17 @@
3536

3637
public class SQLite3NoRECOracle implements TestOracle {
3738

38-
private static final int NOT_FOUND = -1;
39+
private static final int NO_VALID_RESULT = -1;
40+
private final SQLite3Schema s;
41+
private final Connection con;
42+
private final SQLite3StateToReproduce state;
43+
private final Set<String> errors = new HashSet<>();
44+
private final StateLogger logger;
45+
private final MainOptions options;
46+
private final SQLite3GlobalState globalState;
3947
private SQLite3ExpressionGenerator gen;
40-
private SQLite3Schema s;
41-
private Connection con;
42-
private SQLite3StateToReproduce state;
4348
private String firstQueryString;
4449
private String secondQueryString;
45-
private final Set<String> errors = new HashSet<>();
46-
private StateLogger logger;
47-
private MainOptions options;
48-
private SQLite3GlobalState globalState;
49-
private SQLite3Column randomColumnToCheck;
50-
Set<String> firstValues = new HashSet<>();
51-
Set<String> secondValues = new HashSet<>();
5250

5351
public SQLite3NoRECOracle(SQLite3GlobalState globalState) {
5452
this.s = globalState.getSchema();
@@ -60,133 +58,122 @@ public SQLite3NoRECOracle(SQLite3GlobalState globalState) {
6058
SQLite3Errors.addExpectedExpressionErrors(errors);
6159
SQLite3Errors.addMatchQueryErrors(errors);
6260
SQLite3Errors.addQueryErrors(errors);
63-
// aggregate
6461
errors.add("misuse of aggregate");
6562
errors.add("misuse of window function");
6663
errors.add("second argument to nth_value must be a positive integer");
6764
errors.add("no such table");
68-
// FIXME implement
69-
errors.add("no query solution"); // INDEXED BY
65+
errors.add("no query solution");
7066
errors.add("unable to use function MATCH in the requested context");
7167
}
7268

7369
@Override
7470
public void check() throws SQLException {
75-
firstValues.clear();
76-
secondValues.clear();
7771
SQLite3Tables randomTables = s.getRandomTableNonEmptyTables();
7872
List<SQLite3Column> columns = randomTables.getColumns();
7973
gen = new SQLite3ExpressionGenerator(globalState).setColumns(columns);
8074
SQLite3Expression randomWhereCondition = gen.generateExpression();
8175
List<SQLite3Table> tables = randomTables.getTables();
8276
List<Join> joinStatements = gen.getRandomJoinClauses(tables);
8377
List<SQLite3Expression> tableRefs = SQLite3Common.getTableRefs(tables, s);
84-
randomColumnToCheck = Randomly.fromList(randomTables.getColumns());
85-
int firstCount = getFirstQueryCount(con, tableRefs, randomWhereCondition, joinStatements);
86-
if (firstQueryString.contains("EXISTS")) {
78+
SQLite3Select select = new SQLite3Select();
79+
select.setFromTables(tableRefs);
80+
select.setJoinClauses(joinStatements);
81+
82+
int optimizedCount = getOptimizedQuery(select, randomWhereCondition);
83+
int unoptimizedCount = getUnoptimizedQuery(select, randomWhereCondition);
84+
if (optimizedCount == NO_VALID_RESULT || unoptimizedCount == NO_VALID_RESULT) {
8785
throw new IgnoreMeException();
8886
}
89-
int secondCount = getSecondQuery(tableRefs, randomWhereCondition, joinStatements);
90-
if (firstCount != secondCount && firstCount != NOT_FOUND && secondCount != NOT_FOUND) {
87+
if (optimizedCount != unoptimizedCount) {
9188
state.queryString = firstQueryString + ";\n" + secondQueryString + ";";
92-
throw new AssertionError(firstCount + " " + secondCount);
93-
}
94-
if (firstCount != NOT_FOUND && secondCount != NOT_FOUND
95-
&& (!firstValues.containsAll(secondValues) || !secondValues.containsAll(firstValues))) {
96-
state.queryString = firstQueryString + ";\n" + secondQueryString + ";";
97-
throw new AssertionError(firstCount + " " + secondCount + "\n" + firstValues + "\n" + secondValues);
89+
throw new AssertionError(optimizedCount + " " + unoptimizedCount);
9890
}
91+
9992
}
10093

101-
private int getSecondQuery(List<SQLite3Expression> fromList, SQLite3Expression randomWhereCondition,
102-
List<Join> joinStatements) throws SQLException {
103-
SQLite3Select select = new SQLite3Select();
104-
setRandomOrderBy(select);
94+
private int getUnoptimizedQuery(SQLite3Select select, SQLite3Expression randomWhereCondition) throws SQLException {
10595
SQLite3PostfixUnaryOperation isTrue = new SQLite3PostfixUnaryOperation(PostfixUnaryOperator.IS_TRUE,
10696
randomWhereCondition);
10797
SQLite3PostfixText asText = new SQLite3PostfixText(isTrue, " as count", null);
108-
select.setFetchColumns(Arrays.asList(asText, new SQLite3ColumnName(randomColumnToCheck, null)));
109-
select.setFromTables(fromList);
110-
select.setSelectType(SelectType.ALL);
111-
select.setJoinClauses(joinStatements);
112-
int secondCount = 0;
113-
secondQueryString = SQLite3Visitor.asString(select);
98+
select.setFetchColumns(Arrays.asList(asText));
99+
secondQueryString = "SELECT SUM(count) FROM (" + SQLite3Visitor.asString(select) + ")";
114100
if (options.logEachSelect()) {
115101
logger.writeCurrent(secondQueryString);
116102
}
117103
QueryAdapter q = new QueryAdapter(secondQueryString, errors);
104+
return extractCounts(q);
105+
}
106+
107+
private int getOptimizedQuery(SQLite3Select select, SQLite3Expression randomWhereCondition) throws SQLException {
108+
boolean useAggregate = Randomly.getBoolean();
109+
if (Randomly.getBoolean()) {
110+
select.setOrderByExpressions(gen.generateOrderBys());
111+
}
112+
if (useAggregate) {
113+
select.setFetchColumns(Arrays.asList(new SQLite3Aggregate(Collections.emptyList(),
114+
SQLite3Aggregate.SQLite3AggregateFunction.COUNT_ALL)));
115+
} else {
116+
SQLite3ColumnName aggr = new SQLite3ColumnName(SQLite3Column.createDummy("*"), null);
117+
select.setFetchColumns(Arrays.asList(aggr));
118+
}
119+
select.setWhereClause(randomWhereCondition);
120+
if (useAggregate && Randomly.getBooleanWithSmallProbability()) {
121+
select.setGroupByClause(gen.getRandomExpressions(Randomly.smallNumber() + 1));
122+
}
123+
firstQueryString = SQLite3Visitor.asString(select);
124+
if (options.logEachSelect()) {
125+
logger.writeCurrent(firstQueryString);
126+
}
127+
QueryAdapter q = new QueryAdapter(firstQueryString, errors);
128+
return useAggregate ? extractCounts(q) : countRows(q);
129+
}
130+
131+
private int countRows(QueryAdapter q) {
132+
int count = 0;
118133
try (ResultSet rs = q.executeAndGet(con)) {
119134
if (rs == null) {
120-
return NOT_FOUND;
135+
return NO_VALID_RESULT;
121136
} else {
122137
try {
123138
while (rs.next()) {
124-
int val = rs.getInt(1);
125-
if (val == 1) {
126-
secondCount++;
127-
String string = rs.getString(2);
128-
if (string == null) {
129-
secondValues.add("null");
130-
} else {
131-
secondValues.add(string);
132-
}
133-
}
139+
count++;
134140
}
135141
} catch (SQLException e) {
136-
throw new IgnoreMeException();
142+
count = NO_VALID_RESULT;
137143
}
138144
rs.getStatement().close();
139145
}
140146
} catch (Exception e) {
141147
if (e instanceof IgnoreMeException) {
142-
throw e;
148+
throw (IgnoreMeException) e;
143149
}
144150
throw new AssertionError(secondQueryString, e);
145151
}
146-
return secondCount;
152+
return count;
147153
}
148154

149-
private int getFirstQueryCount(Connection con, List<SQLite3Expression> fromList,
150-
SQLite3Expression randomWhereCondition, List<Join> joinStatements) throws SQLException {
151-
SQLite3Select select = new SQLite3Select();
152-
setRandomOrderBy(select);
153-
SQLite3ColumnName aggr = new SQLite3ColumnName(randomColumnToCheck, null);
154-
select.setFetchColumns(Arrays.asList(aggr));
155-
select.setFromTables(fromList);
156-
select.setWhereClause(randomWhereCondition);
157-
select.setSelectType(SelectType.ALL);
158-
select.setJoinClauses(joinStatements);
159-
int firstCount = 0;
160-
firstQueryString = SQLite3Visitor.asString(select);
161-
if (options.logEachSelect()) {
162-
logger.writeCurrent(firstQueryString);
163-
}
164-
QueryAdapter q = new QueryAdapter(firstQueryString, errors);
155+
private int extractCounts(QueryAdapter q) {
156+
int count = 0;
165157
try (ResultSet rs = q.executeAndGet(con)) {
166158
if (rs == null) {
167-
firstCount = NOT_FOUND;
159+
return NO_VALID_RESULT;
168160
} else {
169161
try {
170162
while (rs.next()) {
171-
firstCount += 1;
172-
firstValues.add(String.valueOf(rs.getString(1)));
163+
count += rs.getInt(1);
173164
}
174-
} catch (Exception e) {
175-
q.checkException(e);
176-
firstCount = NOT_FOUND;
165+
} catch (SQLException e) {
166+
count = NO_VALID_RESULT;
177167
}
178168
rs.getStatement().close();
179169
}
180170
} catch (Exception e) {
181-
throw new AssertionError(firstQueryString, e);
182-
}
183-
return firstCount;
184-
}
185-
186-
private void setRandomOrderBy(SQLite3Select select) {
187-
if (Randomly.getBoolean()) {
188-
select.setOrderByExpressions(gen.generateOrderBys());
171+
if (e instanceof IgnoreMeException) {
172+
throw (IgnoreMeException) e;
173+
}
174+
throw new AssertionError(secondQueryString, e);
189175
}
176+
return count;
190177
}
191178

192179
}

0 commit comments

Comments
 (0)