Skip to content

Commit 0254bf1

Browse files
mriggermrigger
committed
[SQLite] Apply various minor PQS refactorings
1 parent 2cab566 commit 0254bf1

1 file changed

Lines changed: 74 additions & 48 deletions

File tree

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

Lines changed: 74 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@
1111

1212
import sqlancer.IgnoreMeException;
1313
import sqlancer.Randomly;
14+
import sqlancer.StateToReproduce.OracleRunReproductionState;
1415
import sqlancer.common.oracle.PivotedQuerySynthesisBase;
1516
import sqlancer.common.query.Query;
1617
import sqlancer.common.query.QueryAdapter;
@@ -46,29 +47,32 @@ public class SQLite3PivotedQuerySynthesisOracle
4647
extends PivotedQuerySynthesisBase<SQLite3GlobalState, SQLite3RowValue, SQLite3Expression> {
4748

4849
private List<SQLite3Column> fetchColumns;
49-
private List<SQLite3Expression> colExpressions;
50+
private List<SQLite3Expression> pivotRowExpression;
51+
private OracleRunReproductionState localState;
5052

51-
public SQLite3PivotedQuerySynthesisOracle(SQLite3GlobalState globalState) throws SQLException {
53+
public SQLite3PivotedQuerySynthesisOracle(SQLite3GlobalState globalState) {
5254
super(globalState);
5355
}
5456

5557
@Override
5658
public Query getQueryThatContainsAtLeastOneRow() throws SQLException {
57-
SQLite3Select selectStatement = getQuery(globalState);
59+
SQLite3Select selectStatement = getQuery();
5860
SQLite3ToStringVisitor visitor = new SQLite3ToStringVisitor();
5961
visitor.visit(selectStatement);
6062
String queryString = visitor.get();
6163
SQLite3Errors.addExpectedExpressionErrors(errors);
6264
return new QueryAdapter(queryString, errors);
6365
}
6466

65-
public SQLite3Select getQuery(SQLite3GlobalState globalState) throws SQLException {
66-
if (globalState.getSchema().getDatabaseTables().isEmpty()) {
67-
throw new IgnoreMeException();
68-
}
67+
public SQLite3Select getQuery() throws SQLException {
68+
assert !globalState.getSchema().getDatabaseTables().isEmpty();
69+
localState = globalState.getState().getLocalState();
70+
assert localState != null;
6971
SQLite3Tables randomFromTables = globalState.getSchema().getRandomTableNonEmptyTables();
7072
List<SQLite3Table> tables = randomFromTables.getTables();
7173

74+
pivotRow = randomFromTables.getRandomRowValue(globalState.getConnection());
75+
7276
globalState.getState().queryTargetedTablesString = randomFromTables.tableNamesAsString();
7377
SQLite3Select selectStatement = new SQLite3Select();
7478
selectStatement.setSelectType(Randomly.fromOptions(SQLite3Select.SelectType.values()));
@@ -78,10 +82,8 @@ public SQLite3Select getQuery(SQLite3GlobalState globalState) throws SQLExceptio
7882
columns.add(t.getRowid());
7983
}
8084
}
81-
pivotRow = randomFromTables.getRandomRowValue(globalState.getConnection());
8285

8386
List<Join> joinStatements = getJoinStatements(globalState, tables, columns);
84-
8587
selectStatement.setJoinClauses(joinStatements);
8688
selectStatement.setFromTables(SQLite3Common.getTableRefs(tables, globalState.getSchema()));
8789

@@ -91,41 +93,14 @@ public SQLite3Select getQuery(SQLite3GlobalState globalState) throws SQLExceptio
9193
List<SQLite3Column> columnsWithoutRowid = columns.stream()
9294
.filter(c -> !SQLite3Schema.ROWID_STRINGS.contains(c.getName())).collect(Collectors.toList());
9395
fetchColumns = Randomly.nonEmptySubset(columnsWithoutRowid);
94-
colExpressions = new ArrayList<>();
9596
List<SQLite3Table> allTables = new ArrayList<>();
9697
allTables.addAll(tables);
9798
allTables.addAll(joinStatements.stream().map(join -> join.getTable()).collect(Collectors.toList()));
9899
boolean allTablesContainOneRow = allTables.stream().allMatch(t -> t.getNrRows() == 1);
99-
for (SQLite3Column c : fetchColumns) {
100-
SQLite3Expression colName = new SQLite3ColumnName(c, pivotRow.getValues().get(c));
101-
if (allTablesContainOneRow && Randomly.getBoolean()) {
102-
boolean generateDistinct = Randomly.getBoolean();
103-
if (generateDistinct) {
104-
colName = new SQLite3Distinct(colName);
105-
}
106-
SQLite3AggregateFunction aggFunc = SQLite3AggregateFunction.getRandom(c.getType());
107-
colName = new SQLite3Aggregate(Arrays.asList(colName), aggFunc);
108-
if (Randomly.getBoolean() && !generateDistinct) {
109-
colName = generateWindowFunction(columns, colName, true);
110-
}
111-
errors.add("second argument to nth_value must be a positive integer");
112-
}
113-
if (Randomly.getBoolean()) {
114-
SQLite3Expression randomExpression = new SQLite3ExpressionGenerator(globalState).setColumns(columns)
115-
.generateResultKnownExpression();
116-
colExpressions.add(randomExpression);
117-
} else {
118-
colExpressions.add(colName);
119-
}
120-
}
121-
if (Randomly.getBoolean() && allTablesContainOneRow) {
122-
SQLite3WindowFunction windowFunction = SQLite3WindowFunction.getRandom(columnsWithoutRowid, globalState);
123-
SQLite3Expression windowExpr = generateWindowFunction(columnsWithoutRowid, windowFunction, false);
124-
colExpressions.add(windowExpr);
125-
}
126-
selectStatement.setFetchColumns(colExpressions);
127-
globalState.getState().queryTargetedColumnsString = fetchColumns.stream().map(c -> c.getFullQualifiedName())
128-
.collect(Collectors.joining(", "));
100+
pivotRowExpression = getColExpressions(allTablesContainOneRow, columns, columnsWithoutRowid);
101+
selectStatement.setFetchColumns(pivotRowExpression);
102+
localState.log("queryTargetedColumnsString: "
103+
+ fetchColumns.stream().map(c -> c.getFullQualifiedName()).collect(Collectors.joining(", ")));
129104
SQLite3Expression whereClause = generateRectifiedExpression(columns, pivotRow);
130105
selectStatement.setWhereClause(whereClause);
131106
List<SQLite3Expression> groupByClause = generateGroupByClause(columns, pivotRow, allTablesContainOneRow);
@@ -137,8 +112,8 @@ public SQLite3Select getQuery(SQLite3GlobalState globalState) throws SQLExceptio
137112
SQLite3Expression offsetClause = generateOffset();
138113
selectStatement.setOffsetClause(offsetClause);
139114
}
140-
List<SQLite3Expression> orderBy = new SQLite3ExpressionGenerator(globalState).setColumns(columns)
141-
.generateOrderBys();
115+
/* PQS does not check for ordering, so we can generate any ORDER BY clause */
116+
List<SQLite3Expression> orderBy = new SQLite3ExpressionGenerator(globalState).generateOrderBys();
142117
selectStatement.setOrderByExpressions(orderBy);
143118
if (!groupByClause.isEmpty() && Randomly.getBoolean()) {
144119
SQLite3Expression randomExpression = SQLite3Common.getTrueExpression(columns, globalState);
@@ -166,6 +141,52 @@ private List<Join> getJoinStatements(SQLite3GlobalState globalState, List<SQLite
166141
return joinStatements;
167142
}
168143

144+
private List<SQLite3Expression> getColExpressions(boolean allTablesContainOneRow, List<SQLite3Column> columns,
145+
List<SQLite3Column> columnsWithoutRowid) {
146+
List<SQLite3Expression> colExpressions = new ArrayList<>();
147+
148+
for (SQLite3Column c : fetchColumns) {
149+
SQLite3Expression colName = new SQLite3ColumnName(c, pivotRow.getValues().get(c));
150+
if (allTablesContainOneRow && Randomly.getBoolean()) {
151+
152+
/*
153+
* PQS cannot detect omitted or incorrectly-fetched duplicate rows, so we can generate DISTINCT
154+
* statements
155+
*/
156+
boolean generateDistinct = Randomly.getBooleanWithRatherLowProbability();
157+
if (generateDistinct) {
158+
colName = new SQLite3Distinct(colName);
159+
}
160+
161+
SQLite3AggregateFunction aggFunc = SQLite3AggregateFunction.getRandom(c.getType());
162+
colName = new SQLite3Aggregate(Arrays.asList(colName), aggFunc);
163+
if (Randomly.getBoolean() && !generateDistinct) {
164+
colName = generateWindowFunction(columns, colName, true);
165+
}
166+
errors.add("second argument to nth_value must be a positive integer");
167+
}
168+
if (Randomly.getBoolean()) {
169+
SQLite3Expression randomExpression;
170+
randomExpression = new SQLite3ExpressionGenerator(globalState).setColumns(columns)
171+
.generateResultKnownExpression();
172+
colExpressions.add(randomExpression);
173+
} else {
174+
colExpressions.add(colName);
175+
}
176+
}
177+
if (allTablesContainOneRow) {
178+
SQLite3WindowFunction windowFunction = SQLite3WindowFunction.getRandom(columnsWithoutRowid, globalState);
179+
SQLite3Expression windowExpr = generateWindowFunction(columnsWithoutRowid, windowFunction, false);
180+
colExpressions.add(windowExpr);
181+
}
182+
for (SQLite3Expression expr : colExpressions) {
183+
if (expr.getExpectedValue() == null) {
184+
throw new IgnoreMeException(); // TODO: aggregates
185+
}
186+
}
187+
return colExpressions;
188+
}
189+
169190
private SQLite3Expression generateOffset() {
170191
if (Randomly.getBoolean()) {
171192
// OFFSET 0
@@ -182,9 +203,11 @@ protected boolean isContainedIn(Query query) throws SQLException {
182203

183204
StringBuilder sb = new StringBuilder();
184205
sb.append("SELECT ");
185-
addExpectedValues(sb);
186-
StringBuilder sb2 = new StringBuilder();
187-
addExpectedValues(sb2);
206+
String checkForContainmentValues = getGeneralizedPivotRowValues();
207+
sb.append(checkForContainmentValues);
208+
globalState.getState().getLocalState()
209+
.log("-- we expect the following expression to be contained in the result set: "
210+
+ checkForContainmentValues);
188211
sb.append(" INTERSECT SELECT * FROM ("); // ANOTHER SELECT TO USE ORDER BY without restrictions
189212
if (query.getQueryString().endsWith(";")) {
190213
sb.append(query.getQueryString().substring(0, query.getQueryString().length() - 1));
@@ -193,6 +216,7 @@ protected boolean isContainedIn(Query query) throws SQLException {
193216
}
194217
sb.append(")");
195218
String resultingQueryString = sb.toString();
219+
globalState.getState().getLocalState().log(resultingQueryString);
196220
Query finalQuery = new QueryAdapter(resultingQueryString, query.getExpectedErrors());
197221
try (ResultSet result = createStatement.executeQuery(finalQuery.getQueryString())) {
198222
boolean isContainedIn = !result.isClosed();
@@ -207,14 +231,16 @@ protected boolean isContainedIn(Query query) throws SQLException {
207231
}
208232
}
209233

210-
private void addExpectedValues(StringBuilder sb) {
211-
for (int i = 0; i < colExpressions.size(); i++) {
234+
private String getGeneralizedPivotRowValues() {
235+
StringBuilder sb = new StringBuilder();
236+
for (int i = 0; i < pivotRowExpression.size(); i++) {
212237
if (i != 0) {
213238
sb.append(", ");
214239
}
215-
SQLite3Constant expectedValue = colExpressions.get(i).getExpectedValue();
240+
SQLite3Constant expectedValue = pivotRowExpression.get(i).getExpectedValue();
216241
sb.append(SQLite3Visitor.asString(expectedValue));
217242
}
243+
return sb.toString();
218244
}
219245

220246
private SQLite3Expression generateLimit(long l) {

0 commit comments

Comments
 (0)