Skip to content

Commit 03ddf95

Browse files
bajinshengbajinsheng
authored
cockroachdb: cert init (sqlancer#842)
* cockroachdb: cert init * tidb: getRandomExcept Join
1 parent 5a69cad commit 03ddf95

6 files changed

Lines changed: 320 additions & 11 deletions

File tree

‎src/sqlancer/cockroachdb/CockroachDBOptions.java‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@
1212
import sqlancer.OracleFactory;
1313
import sqlancer.cockroachdb.CockroachDBOptions.CockroachDBOracleFactory;
1414
import sqlancer.cockroachdb.CockroachDBProvider.CockroachDBGlobalState;
15+
import sqlancer.cockroachdb.oracle.CockroachDBCERTOracle;
1516
import sqlancer.cockroachdb.oracle.CockroachDBNoRECOracle;
1617
import sqlancer.cockroachdb.oracle.tlp.CockroachDBTLPAggregateOracle;
1718
import sqlancer.cockroachdb.oracle.tlp.CockroachDBTLPDistinctOracle;
@@ -88,6 +89,17 @@ public TestOracle<CockroachDBGlobalState> create(CockroachDBGlobalState globalSt
8889
oracles.add(new CockroachDBTLPDistinctOracle(globalState));
8990
return new CompositeTestOracle<CockroachDBGlobalState>(oracles, globalState);
9091
}
92+
},
93+
CERT {
94+
@Override
95+
public TestOracle<CockroachDBGlobalState> create(CockroachDBGlobalState globalState) throws SQLException {
96+
return new CockroachDBCERTOracle(globalState);
97+
}
98+
99+
@Override
100+
public boolean requiresAllTablesToContainRows() {
101+
return true;
102+
}
91103
};
92104

93105
}

‎src/sqlancer/cockroachdb/CockroachDBProvider.java‎

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@
3636
import sqlancer.cockroachdb.gen.CockroachDBTruncateGenerator;
3737
import sqlancer.cockroachdb.gen.CockroachDBUpdateGenerator;
3838
import sqlancer.cockroachdb.gen.CockroachDBViewGenerator;
39+
import sqlancer.cockroachdb.oracle.CockroachDBCERTOracle;
3940
import sqlancer.common.query.ExpectedErrors;
4041
import sqlancer.common.query.SQLQueryAdapter;
4142
import sqlancer.common.query.SQLQueryProvider;
@@ -130,13 +131,13 @@ public void generateDatabase(CockroachDBGlobalState globalState) throws Exceptio
130131
QueryManager<SQLConnection> manager = globalState.getManager();
131132
MainOptions options = globalState.getOptions();
132133
List<String> standardSettings = new ArrayList<>();
133-
standardSettings.add("--Don't send automatic bug reports\n"
134-
+ "SET CLUSTER SETTING debug.panic_on_failed_assertions = true;");
134+
standardSettings.add("--Don't send automatic bug reports");
135+
standardSettings.add("SET CLUSTER SETTING debug.panic_on_failed_assertions = true;");
135136
standardSettings.add("SET CLUSTER SETTING diagnostics.reporting.enabled = false;");
136137
standardSettings.add("SET CLUSTER SETTING diagnostics.reporting.send_crash_reports = false;");
137138

138-
standardSettings.add("-- Disable the collection of metrics and hope that it helps performance\n"
139-
+ "SET CLUSTER SETTING sql.metrics.statement_details.enabled = 'off'");
139+
standardSettings.add("-- Disable the collection of metrics and hope that it helps performance");
140+
standardSettings.add("SET CLUSTER SETTING sql.metrics.statement_details.enabled = 'off'");
140141
standardSettings.add("SET CLUSTER SETTING sql.metrics.statement_details.plan_collection.enabled = 'off'");
141142
standardSettings.add("SET CLUSTER SETTING sql.stats.automatic_collection.enabled = 'off'");
142143
standardSettings.add("SET CLUSTER SETTING timeseries.storage.enabled = 'off'");
@@ -250,6 +251,16 @@ public void generateDatabase(CockroachDBGlobalState globalState) throws Exceptio
250251
}
251252
total--;
252253
}
254+
255+
if (globalState.getDbmsSpecificOptions().getTestOracleFactory().size() == 1 && globalState
256+
.getDbmsSpecificOptions().getTestOracleFactory().get(0).create(globalState) instanceof CockroachDBCERTOracle) {
257+
// Enfore statistic collected for all tables
258+
ExpectedErrors errors = new ExpectedErrors();
259+
CockroachDBErrors.addExpressionErrors(errors);
260+
for (CockroachDBTable table : globalState.getSchema().getDatabaseTables()) {
261+
globalState.executeStatement(new SQLQueryAdapter("ANALYZE " + table.getName() + ";", errors));
262+
}
263+
}
253264
}
254265

255266
@Override

‎src/sqlancer/cockroachdb/ast/CockroachDBJoin.java‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,7 @@
11
package sqlancer.cockroachdb.ast;
22

3+
import java.util.Arrays;
4+
35
import sqlancer.Randomly;
46

57
public class CockroachDBJoin implements CockroachDBExpression {
@@ -15,6 +17,12 @@ public enum JoinType {
1517
public static JoinType getRandom() {
1618
return Randomly.fromOptions(values());
1719
}
20+
21+
public static JoinType getRandomExcept(JoinType... exclude) {
22+
JoinType[] values = Arrays.stream(values()).filter(m -> !Arrays.asList(exclude).contains(m))
23+
.toArray(JoinType[]::new);
24+
return Randomly.fromOptions(values);
25+
}
1826
}
1927

2028
public CockroachDBJoin(CockroachDBExpression leftTable, CockroachDBExpression rightTable, JoinType joinType,

‎src/sqlancer/cockroachdb/oracle/CockroachDBCERTOracle.java‎

Lines changed: 275 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,275 @@
1+
package sqlancer.cockroachdb.oracle;
2+
3+
import java.io.IOException;
4+
import java.sql.SQLException;
5+
import java.util.ArrayList;
6+
import java.util.List;
7+
import java.util.stream.Collectors;
8+
9+
import sqlancer.IgnoreMeException;
10+
import sqlancer.Randomly;
11+
import sqlancer.SQLGlobalState;
12+
import sqlancer.cockroachdb.CockroachDBCommon;
13+
import sqlancer.cockroachdb.CockroachDBErrors;
14+
import sqlancer.cockroachdb.CockroachDBProvider.CockroachDBGlobalState;
15+
import sqlancer.cockroachdb.CockroachDBSchema.CockroachDBColumn;
16+
import sqlancer.cockroachdb.CockroachDBSchema.CockroachDBDataType;
17+
import sqlancer.cockroachdb.CockroachDBSchema.CockroachDBTables;
18+
import sqlancer.cockroachdb.CockroachDBVisitor;
19+
import sqlancer.cockroachdb.ast.CockroachDBBinaryLogicalOperation;
20+
import sqlancer.cockroachdb.ast.CockroachDBBinaryLogicalOperation.CockroachDBBinaryLogicalOperator;
21+
import sqlancer.cockroachdb.ast.CockroachDBColumnReference;
22+
import sqlancer.cockroachdb.ast.CockroachDBExpression;
23+
import sqlancer.cockroachdb.ast.CockroachDBJoin;
24+
import sqlancer.cockroachdb.ast.CockroachDBJoin.JoinType;
25+
import sqlancer.cockroachdb.ast.CockroachDBSelect;
26+
import sqlancer.cockroachdb.ast.CockroachDBTableReference;
27+
import sqlancer.cockroachdb.gen.CockroachDBExpressionGenerator;
28+
import sqlancer.common.DBMSCommon;
29+
import sqlancer.common.oracle.CERTOracleBase;
30+
import sqlancer.common.oracle.TestOracle;
31+
import sqlancer.common.query.SQLQueryAdapter;
32+
import sqlancer.common.query.SQLancerResultSet;
33+
34+
public class CockroachDBCERTOracle extends CERTOracleBase<CockroachDBGlobalState>
35+
implements TestOracle<CockroachDBGlobalState> {
36+
private CockroachDBExpressionGenerator gen;
37+
private CockroachDBSelect select;
38+
39+
public CockroachDBCERTOracle(CockroachDBGlobalState globalState) {
40+
super(globalState);
41+
CockroachDBErrors.addExpressionErrors(errors);
42+
}
43+
44+
@Override
45+
public void check() throws SQLException {
46+
queryPlan1Sequences = new ArrayList<>();
47+
queryPlan2Sequences = new ArrayList<>();
48+
49+
// Randomly generate a query
50+
CockroachDBTables tables = state.getSchema().getRandomTableNonEmptyTables(2);
51+
List<CockroachDBExpression> tableList = CockroachDBCommon.getTableReferences(
52+
tables.getTables().stream().map(t -> new CockroachDBTableReference(t)).collect(Collectors.toList()));
53+
gen = new CockroachDBExpressionGenerator(state).setColumns(tables.getColumns());
54+
List<CockroachDBExpression> fetchColumns = new ArrayList<>();
55+
fetchColumns.addAll(Randomly.nonEmptySubset(tables.getColumns()).stream()
56+
.map(c -> new CockroachDBColumnReference(c)).collect(Collectors.toList()));
57+
select = new CockroachDBSelect();
58+
select.setFetchColumns(fetchColumns);
59+
select.setFromList(tableList);
60+
select.setDistinct(Randomly.getBoolean());
61+
if (Randomly.getBoolean()) {
62+
select.setWhereClause(gen.generateExpression(CockroachDBDataType.BOOL.get()));
63+
}
64+
if (Randomly.getBoolean()) {
65+
select.setGroupByExpressions(fetchColumns);
66+
if (Randomly.getBoolean()) {
67+
select.setHavingClause(gen.generateExpression(CockroachDBDataType.BOOL.get()));
68+
}
69+
}
70+
71+
// Set the join.
72+
List<CockroachDBExpression> joinExpressions = getJoins(tableList, state);
73+
select.setJoinList(joinExpressions);
74+
75+
// Get the result of the first query
76+
String queryString1 = CockroachDBVisitor.asString(select);
77+
int rowCount1 = getRow(state, queryString1, queryPlan1Sequences);
78+
79+
// Mutate the query
80+
// Disable limit due to its false positive
81+
boolean increase = mutate();
82+
83+
// Get the result of the second query
84+
String queryString2 = CockroachDBVisitor.asString(select);
85+
int rowCount2 = getRow(state, queryString2, queryPlan2Sequences);
86+
87+
// Check structural equivalence
88+
if (DBMSCommon.editDistance(queryPlan1Sequences, queryPlan2Sequences) > 1) {
89+
return;
90+
}
91+
92+
// Check the results
93+
if (increase && rowCount1 > rowCount2 || !increase && rowCount1 < rowCount2) {
94+
throw new AssertionError("Inconsistent result for query: EXPLAIN " + queryString1 + "; --" + rowCount1
95+
+ "\nEXPLAIN " + queryString2 + "; --" + rowCount2);
96+
}
97+
}
98+
99+
private List<CockroachDBExpression> getJoins(List<CockroachDBExpression> tableList,
100+
CockroachDBGlobalState globalState) throws AssertionError {
101+
List<CockroachDBExpression> joinExpressions = new ArrayList<>();
102+
while (tableList.size() >= 2 && Randomly.getPercentage() < 0.8) {
103+
CockroachDBTableReference leftTable = (CockroachDBTableReference) tableList.remove(0);
104+
CockroachDBTableReference rightTable = (CockroachDBTableReference) tableList.remove(0);
105+
List<CockroachDBColumn> columns = new ArrayList<>(leftTable.getTable().getColumns());
106+
columns.addAll(rightTable.getTable().getColumns());
107+
CockroachDBExpressionGenerator joinGen = new CockroachDBExpressionGenerator(globalState)
108+
.setColumns(columns);
109+
joinExpressions.add(CockroachDBJoin.createJoin(leftTable, rightTable,
110+
CockroachDBJoin.JoinType.getRandomExcept(JoinType.NATURAL),
111+
joinGen.generateExpression(CockroachDBDataType.BOOL.get())));
112+
}
113+
return joinExpressions;
114+
}
115+
116+
@Override
117+
protected boolean mutateJoin() {
118+
if (select.getJoinList().isEmpty()) {
119+
return false;
120+
}
121+
122+
CockroachDBJoin join = (CockroachDBJoin) Randomly.fromList(select.getJoinList());
123+
124+
// CROSS does not need ON Condition, while other joins do
125+
// To avoid Null pointer, generating a new new condition when mutating CROSS to other joins
126+
if (join.getJoinType() == JoinType.CROSS) {
127+
List<CockroachDBColumn> columns = new ArrayList<>();
128+
columns.addAll(((CockroachDBTableReference) join.getLeftTable()).getTable().getColumns());
129+
columns.addAll(((CockroachDBTableReference) join.getRightTable()).getTable().getColumns());
130+
CockroachDBExpressionGenerator joinGen2 = new CockroachDBExpressionGenerator(state).setColumns(columns);
131+
join.setOnCondition(joinGen2.generateExpression(CockroachDBDataType.BOOL.get()));
132+
}
133+
134+
JoinType newJoinType = CockroachDBJoin.JoinType.INNER;
135+
if (join.getJoinType() == JoinType.LEFT || join.getJoinType() == JoinType.RIGHT) { // No invarient relation
136+
// between LEFT and RIGHT
137+
// join
138+
newJoinType = CockroachDBJoin.JoinType.getRandomExcept(JoinType.NATURAL, JoinType.LEFT, JoinType.RIGHT);
139+
} else {
140+
newJoinType = CockroachDBJoin.JoinType.getRandomExcept(JoinType.NATURAL, join.getJoinType());
141+
}
142+
assert newJoinType != JoinType.NATURAL; // Natural Join is not supported for CERT
143+
boolean increase = join.getJoinType().ordinal() < newJoinType.ordinal();
144+
join.setJoinType(newJoinType);
145+
return increase;
146+
}
147+
148+
@Override
149+
protected boolean mutateDistinct() {
150+
boolean increase = select.isDistinct();
151+
select.setDistinct(!select.isDistinct());
152+
return increase;
153+
}
154+
155+
@Override
156+
protected boolean mutateWhere() {
157+
boolean increase = select.getWhereClause() != null;
158+
if (increase) {
159+
select.setWhereClause(null);
160+
} else {
161+
select.setWhereClause(gen.generateExpression(CockroachDBDataType.BOOL.get()));
162+
}
163+
return increase;
164+
}
165+
166+
@Override
167+
protected boolean mutateGroupBy() {
168+
boolean increase = select.getGroupByExpressions().size() > 0;
169+
if (increase) {
170+
select.clearGroupByExpressions();
171+
} else {
172+
select.setGroupByExpressions(select.getFetchColumns());
173+
}
174+
return increase;
175+
}
176+
177+
@Override
178+
protected boolean mutateHaving() {
179+
if (select.getGroupByExpressions().size() == 0) {
180+
select.setGroupByExpressions(select.getFetchColumns());
181+
select.setHavingClause(gen.generateExpression(CockroachDBDataType.BOOL.get()));
182+
return false;
183+
} else {
184+
if (select.getHavingClause() == null) {
185+
select.setHavingClause(gen.generateExpression(CockroachDBDataType.BOOL.get()));
186+
return false;
187+
} else {
188+
select.setHavingClause(null);
189+
return true;
190+
}
191+
}
192+
}
193+
194+
@Override
195+
protected boolean mutateAnd() {
196+
if (select.getWhereClause() == null) {
197+
select.setWhereClause(gen.generateExpression(CockroachDBDataType.BOOL.get()));
198+
} else {
199+
CockroachDBExpression newWhere = new CockroachDBBinaryLogicalOperation(select.getWhereClause(),
200+
gen.generateExpression(CockroachDBDataType.BOOL.get()), CockroachDBBinaryLogicalOperator.AND);
201+
select.setWhereClause(newWhere);
202+
}
203+
return false;
204+
}
205+
206+
@Override
207+
protected boolean mutateOr() {
208+
if (select.getWhereClause() == null) {
209+
select.setWhereClause(gen.generateExpression(CockroachDBDataType.BOOL.get()));
210+
return false;
211+
} else {
212+
CockroachDBExpression newWhere = new CockroachDBBinaryLogicalOperation(select.getWhereClause(),
213+
gen.generateExpression(CockroachDBDataType.BOOL.get()), CockroachDBBinaryLogicalOperator.OR);
214+
select.setWhereClause(newWhere);
215+
return true;
216+
}
217+
}
218+
219+
@Override
220+
protected boolean mutateLimit() {
221+
boolean increase = select.getLimitClause() != null;
222+
if (increase) {
223+
select.setLimitClause(null);
224+
} else {
225+
select.setLimitClause(gen.generateConstant(CockroachDBDataType.INT.get()));
226+
}
227+
return increase;
228+
}
229+
230+
private int getRow(SQLGlobalState<?, ?> globalState, String selectStr, List<String> queryPlanSequences)
231+
throws AssertionError, SQLException {
232+
int row = -1;
233+
String explainQuery = "EXPLAIN (VERBOSE) " + selectStr;
234+
235+
// Log the query
236+
if (globalState.getOptions().logEachSelect()) {
237+
globalState.getLogger().writeCurrent(explainQuery);
238+
try {
239+
globalState.getLogger().getCurrentFileWriter().flush();
240+
} catch (IOException e) {
241+
e.printStackTrace();
242+
}
243+
}
244+
245+
// Get the row count
246+
SQLQueryAdapter q = new SQLQueryAdapter(explainQuery, errors);
247+
try (SQLancerResultSet rs = q.executeAndGet(globalState)) {
248+
if (rs != null) {
249+
while (rs.next()) {
250+
String content = rs.getString(1);
251+
if (content.contains("count:")) {
252+
try {
253+
int number = Integer.parseInt(content.split("count: ")[1].split(" ")[0].replace(",", ""));
254+
if (row == -1) {
255+
row = number;
256+
}
257+
} catch (Exception e) { // To avoid the situation that no number is found
258+
}
259+
}
260+
if (content.contains("• ")) {
261+
String operation = content.split("• ")[1].split(" ")[0];
262+
queryPlanSequences.add(operation);
263+
}
264+
}
265+
}
266+
} catch (Exception e) {
267+
throw new AssertionError(q.getQueryString(), e);
268+
}
269+
if (row == -1) {
270+
throw new IgnoreMeException();
271+
}
272+
return row;
273+
}
274+
275+
}

‎src/sqlancer/tidb/ast/TiDBJoin.java‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
package sqlancer.tidb.ast;
22

33
import java.util.ArrayList;
4+
import java.util.Arrays;
45
import java.util.List;
56

67
import sqlancer.Randomly;
@@ -22,6 +23,12 @@ public enum JoinType {
2223
public static JoinType getRandom() {
2324
return Randomly.fromOptions(values());
2425
}
26+
27+
public static JoinType getRandomExcept(JoinType... exclude) {
28+
JoinType[] values = Arrays.stream(values()).filter(m -> !Arrays.asList(exclude).contains(m))
29+
.toArray(JoinType[]::new);
30+
return Randomly.fromOptions(values);
31+
}
2532
}
2633

2734
public enum NaturalJoinType {

0 commit comments

Comments
 (0)