Skip to content

Commit 9c74a95

Browse files
malwaregarrymalwaregarry
authored
[Postgres] Use generic TLP where oracle (sqlancer#943)
* Implement TLPWhere generator for Postgres * Use generic TLP where oracle for Postgres
1 parent 34e4642 commit 9c74a95

8 files changed

Lines changed: 80 additions & 40 deletions

‎src/sqlancer/citus/oracle/tlp/CitusTLPWhereOracle.java‎

Lines changed: 29 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,20 @@
11
package sqlancer.citus.oracle.tlp;
22

33
import java.sql.SQLException;
4+
import java.util.ArrayList;
45
import java.util.Arrays;
6+
import java.util.Collections;
7+
import java.util.List;
58

9+
import sqlancer.ComparatorHelper;
10+
import sqlancer.Randomly;
611
import sqlancer.citus.CitusGlobalState;
712
import sqlancer.citus.gen.CitusCommon;
813
import sqlancer.postgres.PostgresGlobalState;
9-
import sqlancer.postgres.oracle.tlp.PostgresTLPWhereOracle;
14+
import sqlancer.postgres.PostgresVisitor;
15+
import sqlancer.postgres.oracle.tlp.PostgresTLPBase;
1016

11-
public class CitusTLPWhereOracle extends PostgresTLPWhereOracle {
17+
public class CitusTLPWhereOracle extends PostgresTLPBase {
1218

1319
private final CitusTLPBase citusTLPBase;
1420

@@ -32,4 +38,25 @@ public void check() throws SQLException {
3238
whereCheck();
3339
state.setDefaultAllowedFunctionTypes();
3440
}
41+
42+
void whereCheck() throws SQLException {
43+
if (Randomly.getBooleanWithRatherLowProbability()) {
44+
select.setOrderByClauses(gen.generateOrderBys());
45+
}
46+
String originalQueryString = PostgresVisitor.asString(select);
47+
List<String> resultSet = ComparatorHelper.getResultSetFirstColumnAsString(originalQueryString, errors, state);
48+
49+
select.setOrderByClauses(Collections.emptyList());
50+
select.setWhereClause(predicate);
51+
String firstQueryString = PostgresVisitor.asString(select);
52+
select.setWhereClause(negatedPredicate);
53+
String secondQueryString = PostgresVisitor.asString(select);
54+
select.setWhereClause(isNullPredicate);
55+
String thirdQueryString = PostgresVisitor.asString(select);
56+
List<String> combinedString = new ArrayList<>();
57+
List<String> secondResultSet = ComparatorHelper.getCombinedResultSet(firstQueryString, secondQueryString,
58+
thirdQueryString, combinedString, Randomly.getBoolean(), state, errors);
59+
ComparatorHelper.assumeResultSetsAreEqual(resultSet, secondResultSet, originalQueryString, combinedString,
60+
state);
61+
}
3562
}

‎src/sqlancer/postgres/gen/PostgresExpressionGenerator.java‎

Lines changed: 22 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@
1212
import sqlancer.Randomly;
1313
import sqlancer.common.gen.ExpressionGenerator;
1414
import sqlancer.common.gen.NoRECGenerator;
15+
import sqlancer.common.gen.TLPWhereGenerator;
1516
import sqlancer.common.schema.AbstractTables;
1617
import sqlancer.postgres.PostgresCompoundDataType;
1718
import sqlancer.postgres.PostgresGlobalState;
@@ -64,7 +65,8 @@
6465
import sqlancer.postgres.ast.PostgresSimilarTo;
6566

6667
public class PostgresExpressionGenerator implements ExpressionGenerator<PostgresExpression>,
67-
NoRECGenerator<PostgresSelect, PostgresJoin, PostgresExpression, PostgresTable, PostgresColumn> {
68+
NoRECGenerator<PostgresSelect, PostgresJoin, PostgresExpression, PostgresTable, PostgresColumn>,
69+
TLPWhereGenerator<PostgresSelect, PostgresJoin, PostgresExpression, PostgresTable, PostgresColumn> {
6870

6971
private final int maxDepth;
7072

@@ -108,7 +110,8 @@ public PostgresExpression generateExpression(int depth) {
108110
return generateExpression(depth, PostgresDataType.getRandomType());
109111
}
110112

111-
public List<PostgresExpression> generateOrderBy() {
113+
@Override
114+
public List<PostgresExpression> generateOrderBys() {
112115
List<PostgresExpression> orderBys = new ArrayList<>();
113116
for (int i = 0; i < Randomly.smallNumber(); i++) {
114117
orderBys.add(new PostgresOrderByTerm(PostgresColumnValue.create(Randomly.fromList(columns), null),
@@ -615,7 +618,7 @@ public static PostgresSubquery createSubquery(PostgresGlobalState globalState, S
615618
select.setWhereClause(gen.generateExpression(0, PostgresDataType.BOOLEAN));
616619
}
617620
if (Randomly.getBooleanWithRatherLowProbability()) {
618-
select.setOrderByClauses(gen.generateOrderBy());
621+
select.setOrderByClauses(gen.generateOrderBys());
619622
}
620623
if (Randomly.getBoolean()) {
621624
select.setLimitClause(PostgresConstant.createIntConstant(Randomly.getPositiveOrZeroNonCachedInteger()));
@@ -691,6 +694,21 @@ public List<PostgresExpression> getTableRefs() {
691694
.collect(Collectors.toList());
692695
}
693696

697+
@Override
698+
public List<PostgresExpression> generateFetchColumns(boolean shouldCreateDummy) {
699+
if (shouldCreateDummy && Randomly.getBooleanWithRatherLowProbability()) {
700+
return Arrays.asList(new PostgresColumnValue(PostgresColumn.createDummy("*"), null));
701+
}
702+
allowAggregateFunctions = true;
703+
List<PostgresExpression> fetchColumns = new ArrayList<>();
704+
List<PostgresColumn> targetColumns = Randomly.nonEmptySubset(columns);
705+
for (PostgresColumn c : targetColumns) {
706+
fetchColumns.add(new PostgresColumnValue(c, null));
707+
}
708+
allowAggregateFunctions = false;
709+
return fetchColumns;
710+
}
711+
694712
@Override
695713
public String generateOptimizedQueryString(PostgresSelect select, PostgresExpression whereCondition,
696714
boolean shouldUseAggregate) {
@@ -703,7 +721,7 @@ public String generateOptimizedQueryString(PostgresSelect select, PostgresExpres
703721
}
704722
select.setWhereClause(whereCondition);
705723
if (Randomly.getBooleanWithSmallProbability()) {
706-
select.setOrderByClauses(generateOrderBy());
724+
select.setOrderByClauses(generateOrderBys());
707725
}
708726
select.setSelectType(SelectType.ALL);
709727
return select.asString();

‎src/sqlancer/postgres/gen/PostgresRandomQueryGenerator.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@ public static PostgresSelect createRandomQuery(int nrColumns, PostgresGlobalStat
4545
}
4646
}
4747
if (Randomly.getBooleanWithRatherLowProbability()) {
48-
select.setOrderByClauses(gen.generateOrderBy());
48+
select.setOrderByClauses(gen.generateOrderBys());
4949
}
5050
if (Randomly.getBoolean()) {
5151
select.setLimitClause(PostgresConstant.createIntConstant(Randomly.getPositiveOrZeroNonCachedInteger()));

‎src/sqlancer/postgres/oracle/PostgresPivotedQuerySynthesisOracle.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -63,7 +63,7 @@ public SQLQueryAdapter getRectifiedQuery() throws SQLException {
6363
selectStatement.setOffsetClause(offsetClause);
6464
}
6565
List<PostgresExpression> orderBy = new PostgresExpressionGenerator(globalState).setColumns(columns)
66-
.generateOrderBy();
66+
.generateOrderBys();
6767
selectStatement.setOrderByClauses(orderBy);
6868
return new SQLQueryAdapter(PostgresVisitor.asString(selectStatement));
6969
}

‎src/sqlancer/postgres/oracle/tlp/PostgresTLPAggregateOracle.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -61,7 +61,7 @@ protected void aggregateCheck() throws SQLException {
6161
}
6262
select.setFetchColumns(Arrays.asList(aggregate));
6363
if (Randomly.getBooleanWithRatherLowProbability()) {
64-
select.setOrderByClauses(gen.generateOrderBy());
64+
select.setOrderByClauses(gen.generateOrderBys());
6565
}
6666
originalQuery = PostgresVisitor.asString(select);
6767
firstResult = getAggregateResult(originalQuery);

‎src/sqlancer/postgres/oracle/tlp/PostgresTLPBase.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -103,7 +103,7 @@ public static PostgresSubquery createSubquery(PostgresGlobalState globalState, S
103103
select.setWhereClause(gen.generateExpression(0, PostgresDataType.BOOLEAN));
104104
}
105105
if (Randomly.getBooleanWithRatherLowProbability()) {
106-
select.setOrderByClauses(gen.generateOrderBy());
106+
select.setOrderByClauses(gen.generateOrderBys());
107107
}
108108
if (Randomly.getBoolean()) {
109109
select.setLimitClause(PostgresConstant.createIntConstant(Randomly.getPositiveOrZeroNonCachedInteger()));

‎src/sqlancer/postgres/oracle/tlp/PostgresTLPHavingOracle.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -36,7 +36,7 @@ protected void havingCheck() throws SQLException {
3636

3737
boolean orderBy = Randomly.getBoolean();
3838
if (orderBy) {
39-
select.setOrderByClauses(gen.generateOrderBy());
39+
select.setOrderByClauses(gen.generateOrderBys());
4040
}
4141
select.setHavingClause(predicate);
4242
String firstQueryString = PostgresVisitor.asString(select);

‎src/sqlancer/postgres/oracle/tlp/PostgresTLPWhereOracle.java‎

Lines changed: 24 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1,45 +1,40 @@
11
package sqlancer.postgres.oracle.tlp;
22

33
import java.sql.SQLException;
4-
import java.util.ArrayList;
5-
import java.util.Collections;
6-
import java.util.List;
74

8-
import sqlancer.ComparatorHelper;
9-
import sqlancer.Randomly;
5+
import sqlancer.common.oracle.TLPWhereOracle;
6+
import sqlancer.common.oracle.TestOracle;
7+
import sqlancer.common.query.ExpectedErrors;
108
import sqlancer.postgres.PostgresGlobalState;
11-
import sqlancer.postgres.PostgresVisitor;
9+
import sqlancer.postgres.PostgresSchema;
10+
import sqlancer.postgres.PostgresSchema.PostgresColumn;
11+
import sqlancer.postgres.PostgresSchema.PostgresTable;
12+
import sqlancer.postgres.ast.PostgresExpression;
13+
import sqlancer.postgres.ast.PostgresJoin;
14+
import sqlancer.postgres.ast.PostgresSelect;
15+
import sqlancer.postgres.gen.PostgresCommon;
16+
import sqlancer.postgres.gen.PostgresExpressionGenerator;
1217

13-
public class PostgresTLPWhereOracle extends PostgresTLPBase {
18+
public class PostgresTLPWhereOracle implements TestOracle<PostgresGlobalState> {
19+
20+
private final TLPWhereOracle<PostgresSelect, PostgresJoin, PostgresExpression, PostgresSchema, PostgresTable, PostgresColumn, PostgresGlobalState> oracle;
1421

1522
public PostgresTLPWhereOracle(PostgresGlobalState state) {
16-
super(state);
23+
PostgresExpressionGenerator gen = new PostgresExpressionGenerator(state);
24+
ExpectedErrors expectedErrors = ExpectedErrors.newErrors().with(PostgresCommon.getCommonExpressionErrors())
25+
.with(PostgresCommon.getCommonFetchErrors()).withRegex(PostgresCommon.getCommonExpressionRegexErrors())
26+
.build();
27+
28+
this.oracle = new TLPWhereOracle<>(state, gen, expectedErrors);
1729
}
1830

1931
@Override
2032
public void check() throws SQLException {
21-
super.check();
22-
whereCheck();
33+
oracle.check();
2334
}
2435

25-
protected void whereCheck() throws SQLException {
26-
if (Randomly.getBooleanWithRatherLowProbability()) {
27-
select.setOrderByClauses(gen.generateOrderBy());
28-
}
29-
String originalQueryString = PostgresVisitor.asString(select);
30-
List<String> resultSet = ComparatorHelper.getResultSetFirstColumnAsString(originalQueryString, errors, state);
31-
32-
select.setOrderByClauses(Collections.emptyList());
33-
select.setWhereClause(predicate);
34-
String firstQueryString = PostgresVisitor.asString(select);
35-
select.setWhereClause(negatedPredicate);
36-
String secondQueryString = PostgresVisitor.asString(select);
37-
select.setWhereClause(isNullPredicate);
38-
String thirdQueryString = PostgresVisitor.asString(select);
39-
List<String> combinedString = new ArrayList<>();
40-
List<String> secondResultSet = ComparatorHelper.getCombinedResultSet(firstQueryString, secondQueryString,
41-
thirdQueryString, combinedString, Randomly.getBoolean(), state, errors);
42-
ComparatorHelper.assumeResultSetsAreEqual(resultSet, secondResultSet, originalQueryString, combinedString,
43-
state);
36+
@Override
37+
public String getLastQueryString() {
38+
return oracle.getLastQueryString();
4439
}
4540
}

0 commit comments

Comments
 (0)