Skip to content

Commit 56c5e98

Browse files
malwaregarrymalwaregarry
authored
[SQLite3] Use generic NoREC implementation (#933)
* Implement Join interface for SQlite3 Join * Implement Select interface for SQLite3 Select * Fix typo in NoREC oracle * Add logging to NoREC oracle * Add reproducer to NoREC oracle * Implement NoRECGenerator interface for SQLite3 * Use generic NoREC oracle for SQLite3
1 parent 0c6dcbd commit 56c5e98

File tree

6 files changed

+168
-170
lines changed

6 files changed

+168
-170
lines changed

‎src/sqlancer/common/ast/newast/Join.java‎

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,8 +6,6 @@
66
public interface Join<E extends Expression<C>, T extends AbstractTable<C, ?, ?>, C extends AbstractTableColumn<?, ?>>
77
extends Expression<C> {
88

9-
T getTable();
10-
119
Expression<C> getOnClause();
1210

1311
void setOnClause(E onClause);

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

Lines changed: 40 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,12 @@
11
package sqlancer.common.oracle;
22

33
import java.sql.SQLException;
4+
import java.util.Objects;
5+
import java.util.function.Function;
46

57
import sqlancer.IgnoreMeException;
68
import sqlancer.Randomly;
9+
import sqlancer.Reproducer;
710
import sqlancer.SQLGlobalState;
811
import sqlancer.common.ast.newast.Expression;
912
import sqlancer.common.ast.newast.Join;
@@ -25,19 +28,37 @@ public class NoRECOracle<J extends Join<E, T, C>, E extends Expression<C>, S ext
2528
private NoRECGenerator<J, E, T, C> gen;
2629
private final ExpectedErrors errors;
2730

31+
private Reproducer<G> reproducer;
2832
private String lastQueryString;
2933

34+
private static class NoRECReproducer<G extends SQLGlobalState<?, ?>> implements Reproducer<G> {
35+
private final Function<G, Integer> optimizedQuery;
36+
private final Function<G, Integer> unoptimizedQuery;
37+
38+
NoRECReproducer(Function<G, Integer> optimizedQuery, Function<G, Integer> unoptimizedQuery) {
39+
this.optimizedQuery = optimizedQuery;
40+
this.unoptimizedQuery = unoptimizedQuery;
41+
}
42+
43+
@Override
44+
public boolean bugStillTriggers(G globalState) {
45+
return !Objects.equals(optimizedQuery.apply(globalState), unoptimizedQuery.apply(globalState));
46+
}
47+
}
48+
3049
public NoRECOracle(G state, NoRECGenerator<J, E, T, C> gen, ExpectedErrors expectedErrors) {
3150
if (state == null || gen == null || expectedErrors == null) {
3251
throw new IllegalArgumentException("Null variables used to initialize test oracle.");
3352
}
3453
this.state = state;
3554
this.gen = gen;
3655
this.errors = expectedErrors;
56+
this.reproducer = null;
3757
}
3858

3959
@Override
4060
public void check() throws SQLException {
61+
reproducer = null;
4162
S schema = state.getSchema();
4263
AbstractTables<T, C> targetTables = TestOracleUtils.getRandomTableNonEmptyTables(schema);
4364
gen = gen.setTablesAndColumns(targetTables);
@@ -52,18 +73,31 @@ public void check() throws SQLException {
5273
String optimizedQueryString = gen.generateOptimizedQueryString(select, randomWhereCondition,
5374
shouldUseAggregate);
5475
lastQueryString = optimizedQueryString;
76+
if (state.getOptions().logEachSelect()) {
77+
state.getLogger().writeCurrent(optimizedQueryString);
78+
}
5579

5680
String unoptimizedQueryString = gen.generateUnoptimizedQueryString(select, randomWhereCondition);
81+
if (state.getOptions().logEachSelect()) {
82+
state.getLogger().writeCurrent(unoptimizedQueryString);
83+
}
5784

5885
int optimizedCount = shouldUseAggregate ? extractCounts(optimizedQueryString, errors, state)
5986
: countRows(optimizedQueryString, errors, state);
60-
int unoptimizedCount = extractCounts(optimizedQueryString, errors, state);
87+
int unoptimizedCount = extractCounts(unoptimizedQueryString, errors, state);
6188

6289
if (optimizedCount == -1 || unoptimizedCount == -1) {
6390
throw new IgnoreMeException();
6491
}
6592

6693
if (unoptimizedCount != optimizedCount) {
94+
Function<G, Integer> optimizedQuery = state -> shouldUseAggregate
95+
? extractCounts(optimizedQueryString, errors, state)
96+
: countRows(optimizedQueryString, errors, state);
97+
98+
Function<G, Integer> unoptimizedQuery = state -> extractCounts(unoptimizedQueryString, errors, state);
99+
reproducer = new NoRECReproducer<>(optimizedQuery, unoptimizedQuery);
100+
67101
String queryFormatString = "-- %s;\n-- count: %d";
68102
String firstQueryStringWithCount = String.format(queryFormatString, optimizedQueryString, optimizedCount);
69103
String secondQueryStringWithCount = String.format(queryFormatString, unoptimizedQueryString,
@@ -81,6 +115,11 @@ public String getLastQueryString() {
81115
return lastQueryString;
82116
}
83117

118+
@Override
119+
public Reproducer<G> getLastReproducer() {
120+
return reproducer;
121+
}
122+
84123
private int countRows(String queryString, ExpectedErrors errors, SQLGlobalState<?, ?> state) {
85124
SQLQueryAdapter q = new SQLQueryAdapter(queryString, errors);
86125

‎src/sqlancer/sqlite3/ast/SQLite3Expression.java‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@
66
import sqlancer.IgnoreMeException;
77
import sqlancer.LikeImplementationHelper;
88
import sqlancer.Randomly;
9+
import sqlancer.common.ast.newast.Expression;
910
import sqlancer.common.visitor.BinaryOperation;
1011
import sqlancer.common.visitor.UnaryOperation;
1112
import sqlancer.sqlite3.SQLite3CollateHelper;
@@ -18,7 +19,7 @@
1819
import sqlancer.sqlite3.schema.SQLite3Schema.SQLite3Column.SQLite3CollateSequence;
1920
import sqlancer.sqlite3.schema.SQLite3Schema.SQLite3Table;
2021

21-
public abstract class SQLite3Expression {
22+
public abstract class SQLite3Expression implements Expression<SQLite3Column> {
2223

2324
public static class SQLite3TableReference extends SQLite3Expression {
2425

@@ -128,7 +129,8 @@ public SQLite3CollateSequence getExplicitCollateSequence() {
128129

129130
}
130131

131-
public static class Join extends SQLite3Expression {
132+
public static class Join extends SQLite3Expression
133+
implements sqlancer.common.ast.newast.Join<SQLite3Expression, SQLite3Table, SQLite3Column> {
132134

133135
public enum JoinType {
134136
INNER, CROSS, OUTER, NATURAL, RIGHT, FULL;
@@ -163,6 +165,7 @@ public SQLite3Table getTable() {
163165
return table;
164166
}
165167

168+
@Override
166169
public SQLite3Expression getOnClause() {
167170
return onClause;
168171
}
@@ -176,14 +179,14 @@ public SQLite3CollateSequence getExplicitCollateSequence() {
176179
return null;
177180
}
178181

182+
@Override
179183
public void setOnClause(SQLite3Expression onClause) {
180184
this.onClause = onClause;
181185
}
182186

183187
public void setType(JoinType type) {
184188
this.type = type;
185189
}
186-
187190
}
188191

189192
public static class Subquery extends SQLite3Expression {

‎src/sqlancer/sqlite3/ast/SQLite3Select.java‎

Lines changed: 29 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,15 @@
44
import java.util.Collections;
55
import java.util.List;
66

7+
import sqlancer.common.ast.newast.Select;
8+
import sqlancer.sqlite3.SQLite3Visitor;
9+
import sqlancer.sqlite3.ast.SQLite3Expression.Join;
10+
import sqlancer.sqlite3.schema.SQLite3Schema.SQLite3Column;
711
import sqlancer.sqlite3.schema.SQLite3Schema.SQLite3Column.SQLite3CollateSequence;
12+
import sqlancer.sqlite3.schema.SQLite3Schema.SQLite3Table;
813

9-
public class SQLite3Select extends SQLite3Expression {
14+
public class SQLite3Select extends SQLite3Expression
15+
implements Select<Join, SQLite3Expression, SQLite3Table, SQLite3Column> {
1016

1117
private SelectType fromOptions = SelectType.ALL;
1218
private List<SQLite3Expression> fromList = Collections.emptyList();
@@ -54,66 +60,82 @@ public void setFromOptions(SelectType fromOptions) {
5460
this.fromOptions = fromOptions;
5561
}
5662

63+
@Override
5764
public List<SQLite3Expression> getFromList() {
5865
return fromList;
5966
}
6067

68+
@Override
6169
public void setFromList(List<SQLite3Expression> fromList) {
6270
this.fromList = fromList;
6371
}
6472

73+
@Override
6574
public SQLite3Expression getWhereClause() {
6675
return whereClause;
6776
}
6877

78+
@Override
6979
public void setWhereClause(SQLite3Expression whereClause) {
7080
this.whereClause = whereClause;
7181
}
7282

83+
@Override
7384
public void setGroupByClause(List<SQLite3Expression> groupByClause) {
7485
this.groupByClause = groupByClause;
7586
}
7687

88+
@Override
7789
public List<SQLite3Expression> getGroupByClause() {
7890
return groupByClause;
7991
}
8092

93+
@Override
8194
public void setLimitClause(SQLite3Expression limitClause) {
8295
this.limitClause = limitClause;
8396
}
8497

98+
@Override
8599
public SQLite3Expression getLimitClause() {
86100
return limitClause;
87101
}
88102

103+
@Override
89104
public List<SQLite3Expression> getOrderByClauses() {
90105
return orderByClause;
91106
}
92107

108+
@Override
93109
public void setOrderByClauses(List<SQLite3Expression> orderBy) {
94110
this.orderByClause = orderBy;
95111
}
96112

113+
@Override
97114
public void setOffsetClause(SQLite3Expression offsetClause) {
98115
this.offsetClause = offsetClause;
99116
}
100117

118+
@Override
101119
public SQLite3Expression getOffsetClause() {
102120
return offsetClause;
103121
}
104122

123+
@Override
105124
public void setFetchColumns(List<SQLite3Expression> fetchColumns) {
106125
this.fetchColumns = fetchColumns;
107126
}
108127

128+
@Override
109129
public List<SQLite3Expression> getFetchColumns() {
110130
return fetchColumns;
111131
}
112132

133+
@Override
113134
public void setJoinClauses(List<Join> joinStatements) {
114135
this.joinStatements = joinStatements;
115136
}
116137

138+
@Override
117139
public List<Join> getJoinClauses() {
118140
return joinStatements;
119141
}
@@ -124,13 +146,19 @@ public SQLite3CollateSequence getExplicitCollateSequence() {
124146
return null;
125147
}
126148

149+
@Override
127150
public void setHavingClause(SQLite3Expression havingClause) {
128151
this.havingClause = havingClause;
129152
}
130153

154+
@Override
131155
public SQLite3Expression getHavingClause() {
132156
assert orderByClause != null;
133157
return havingClause;
134158
}
135159

160+
@Override
161+
public String asString() {
162+
return SQLite3Visitor.asString(this);
163+
}
136164
}

‎src/sqlancer/sqlite3/gen/SQLite3ExpressionGenerator.java‎

Lines changed: 76 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,10 @@
77
import java.util.stream.Collectors;
88

99
import sqlancer.Randomly;
10+
import sqlancer.common.ast.newast.Select;
1011
import sqlancer.common.gen.ExpressionGenerator;
12+
import sqlancer.common.gen.NoRECGenerator;
13+
import sqlancer.common.schema.AbstractTables;
1114
import sqlancer.sqlite3.SQLite3GlobalState;
1215
import sqlancer.sqlite3.ast.SQLite3Aggregate;
1316
import sqlancer.sqlite3.ast.SQLite3Aggregate.SQLite3AggregateFunction;
@@ -31,12 +34,14 @@
3134
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3PostfixText;
3235
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3PostfixUnaryOperation;
3336
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3PostfixUnaryOperation.PostfixUnaryOperator;
37+
import sqlancer.sqlite3.ast.SQLite3Expression.SQLite3TableReference;
3438
import sqlancer.sqlite3.ast.SQLite3Expression.Sqlite3BinaryOperation;
3539
import sqlancer.sqlite3.ast.SQLite3Expression.Sqlite3BinaryOperation.BinaryOperator;
3640
import sqlancer.sqlite3.ast.SQLite3Expression.TypeLiteral;
3741
import sqlancer.sqlite3.ast.SQLite3Function;
3842
import sqlancer.sqlite3.ast.SQLite3Function.ComputableFunction;
3943
import sqlancer.sqlite3.ast.SQLite3RowValueExpression;
44+
import sqlancer.sqlite3.ast.SQLite3Select;
4045
import sqlancer.sqlite3.ast.SQLite3UnaryOperation;
4146
import sqlancer.sqlite3.ast.SQLite3UnaryOperation.UnaryOperator;
4247
import sqlancer.sqlite3.oracle.SQLite3RandomQuerySynthesizer;
@@ -45,12 +50,14 @@
4550
import sqlancer.sqlite3.schema.SQLite3Schema.SQLite3RowValue;
4651
import sqlancer.sqlite3.schema.SQLite3Schema.SQLite3Table;
4752

48-
public class SQLite3ExpressionGenerator implements ExpressionGenerator<SQLite3Expression> {
53+
public class SQLite3ExpressionGenerator implements ExpressionGenerator<SQLite3Expression>,
54+
NoRECGenerator<Join, SQLite3Expression, SQLite3Table, SQLite3Column> {
4955

5056
private SQLite3RowValue rw;
5157
private final SQLite3GlobalState globalState;
5258
private boolean tryToGenerateKnownResult;
5359
private List<SQLite3Column> columns = Collections.emptyList();
60+
private List<SQLite3Table> targetTables;
5461
private final Randomly r;
5562
private boolean deterministicOnly;
5663
private boolean allowMatchClause;
@@ -63,6 +70,7 @@ public SQLite3ExpressionGenerator(SQLite3ExpressionGenerator other) {
6370
this.globalState = other.globalState;
6471
this.tryToGenerateKnownResult = other.tryToGenerateKnownResult;
6572
this.columns = new ArrayList<>(other.columns);
73+
this.targetTables = other.targetTables;
6674
this.r = other.r;
6775
this.deterministicOnly = other.deterministicOnly;
6876
this.allowMatchClause = other.allowMatchClause;
@@ -702,4 +710,71 @@ public SQLite3Expression generateResultKnownExpression() {
702710
return expr;
703711
}
704712

713+
@Override
714+
public SQLite3ExpressionGenerator setTablesAndColumns(AbstractTables<SQLite3Table, SQLite3Column> targetTables) {
715+
SQLite3ExpressionGenerator gen = new SQLite3ExpressionGenerator(this);
716+
gen.targetTables = targetTables.getTables();
717+
gen.columns = targetTables.getColumns();
718+
return gen;
719+
}
720+
721+
@Override
722+
public SQLite3Expression generateBooleanExpression() {
723+
return generateExpression();
724+
}
725+
726+
@Override
727+
public SQLite3Select generateSelect() {
728+
return new SQLite3Select();
729+
}
730+
731+
@Override
732+
public List<Join> getRandomJoinClauses() {
733+
return getRandomJoinClauses(targetTables);
734+
}
735+
736+
@Override
737+
public List<SQLite3Expression> getTableRefs() {
738+
List<SQLite3Expression> tableRefs = new ArrayList<>();
739+
for (SQLite3Table t : targetTables) {
740+
SQLite3TableReference tableRef;
741+
if (Randomly.getBooleanWithSmallProbability() && !globalState.getSchema().getIndexNames().isEmpty()) {
742+
tableRef = new SQLite3TableReference(globalState.getSchema().getRandomIndexOrBailout(), t);
743+
} else {
744+
tableRef = new SQLite3TableReference(t);
745+
}
746+
tableRefs.add(tableRef);
747+
}
748+
return tableRefs;
749+
}
750+
751+
@Override
752+
public String generateOptimizedQueryString(Select<Join, SQLite3Expression, SQLite3Table, SQLite3Column> select,
753+
SQLite3Expression whereCondition, boolean shouldUseAggregate) {
754+
if (Randomly.getBoolean()) {
755+
select.setOrderByClauses(generateOrderBys());
756+
}
757+
if (shouldUseAggregate) {
758+
select.setFetchColumns(Arrays.asList(new SQLite3Aggregate(Collections.emptyList(),
759+
SQLite3Aggregate.SQLite3AggregateFunction.COUNT_ALL)));
760+
} else {
761+
SQLite3ColumnName aggr = new SQLite3ColumnName(SQLite3Column.createDummy("*"), null);
762+
select.setFetchColumns(Arrays.asList(aggr));
763+
}
764+
select.setWhereClause(whereCondition);
765+
766+
return select.asString();
767+
}
768+
769+
@Override
770+
public String generateUnoptimizedQueryString(Select<Join, SQLite3Expression, SQLite3Table, SQLite3Column> select,
771+
SQLite3Expression whereCondition) {
772+
SQLite3PostfixUnaryOperation isTrue = new SQLite3PostfixUnaryOperation(PostfixUnaryOperator.IS_TRUE,
773+
whereCondition);
774+
SQLite3PostfixText asText = new SQLite3PostfixText(isTrue, " as count", null);
775+
select.setFetchColumns(Arrays.asList(asText));
776+
select.setWhereClause(null);
777+
778+
return "SELECT SUM(count) FROM (" + select.asString() + ")";
779+
}
705780
}

0 commit comments

Comments
 (0)