Skip to content

Commit 4a74bd4

Browse files
mriggermrigger
committed
Add additional functions and the having oracle
1 parent bf7130a commit 4a74bd4

12 files changed

+237
-17
lines changed

‎src/sqlancer/tidb/TiDBErrors.java‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
package sqlancer.tidb;
2+
3+
import java.util.Set;
4+
5+
public class TiDBErrors {
6+
7+
public static void addExpressionErrors(Set<String> errors) {
8+
errors.add("error parsing regexp");
9+
errors.add("BIGINT UNSIGNED value is out of range");
10+
errors.add("overflows bigint");
11+
errors.add("strconv.ParseFloat: parsing");
12+
errors.add("in 'order clause'"); // int constants in order by clause
13+
14+
// functions
15+
errors.add("BIGINT value is out of range");
16+
17+
// https://github.com/pingcap/tidb/issues/15790
18+
errors.add("Data truncation: %s value is out of range in '%s'");
19+
}
20+
21+
public static void addExpressionHavingErrors(Set<String> errors) {
22+
errors.add("is not in GROUP BY clause and contains nonaggregated column");
23+
errors.add("Unknown column");
24+
}
25+
26+
}

‎src/sqlancer/tidb/TiDBExpressionGenerator.java‎

Lines changed: 22 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,8 @@
99
import sqlancer.tidb.TiDBSchema.TiDBDataType;
1010
import sqlancer.tidb.ast.TiDBBinaryComparisonOperation;
1111
import sqlancer.tidb.ast.TiDBBinaryComparisonOperation.TiDBComparisonOperator;
12+
import sqlancer.tidb.ast.TiDBBinaryLogicalOperation;
13+
import sqlancer.tidb.ast.TiDBBinaryLogicalOperation.TiDBBinaryLogicalOperator;
1214
import sqlancer.tidb.ast.TiDBCollate;
1315
import sqlancer.tidb.ast.TiDBColumnReference;
1416
import sqlancer.tidb.ast.TiDBConstant;
@@ -19,6 +21,8 @@
1921
import sqlancer.tidb.ast.TiDBRegexOperation.TiDBRegexOperator;
2022
import sqlancer.tidb.ast.TiDBUnaryPostfixOperation;
2123
import sqlancer.tidb.ast.TiDBUnaryPostfixOperation.TiDBUnaryPostfixOperator;
24+
import sqlancer.tidb.ast.TiDBUnaryPrefixOperation;
25+
import sqlancer.tidb.ast.TiDBUnaryPrefixOperation.TiDBUnaryPrefixOperator;
2226

2327
public class TiDBExpressionGenerator {
2428

@@ -30,14 +34,15 @@ public TiDBExpressionGenerator(TiDBGlobalState globalState) {
3034
}
3135

3236
private static enum Gen {
33-
// UNARY_PREFIX, //
37+
UNARY_PREFIX, //
3438
UNARY_POSTFIX, //
3539
CONSTANT, //
3640
COLUMN, //
3741
COMPARISON,
3842
REGEX,
3943
COLLATE,
4044
FUNCTION,
45+
BINARY_LOGICAL
4146
// BINARY_ARITHMETIC
4247
}
4348

@@ -61,8 +66,12 @@ private TiDBExpression generateExpression(int depth) {
6166
case UNARY_POSTFIX:
6267
return new TiDBUnaryPostfixOperation(generateExpression(depth + 1), TiDBUnaryPostfixOperator.getRandom());
6368
// https://github.com/pingcap/tidb/issues/15725
64-
// case UNARY_PREFIX:
65-
// return new TiDBUnaryPrefixOperation(generateExpression(depth + 1), TiDBUnaryPrefixOperator.getRandom());
69+
case UNARY_PREFIX:
70+
TiDBUnaryPrefixOperator rand;
71+
do {
72+
rand = TiDBUnaryPrefixOperator.getRandom();
73+
} while (rand == TiDBUnaryPrefixOperator.NOT);
74+
return new TiDBUnaryPrefixOperation(generateExpression(depth + 1), rand);
6675
case COLUMN:
6776
return generateColumn();
6877
case CONSTANT:
@@ -76,6 +85,8 @@ private TiDBExpression generateExpression(int depth) {
7685
case FUNCTION:
7786
TiDBFunction func = TiDBFunction.getRandom();
7887
return new TiDBFunctionCall(func, generateExpressions(depth, func.getNrArgs()));
88+
case BINARY_LOGICAL:
89+
return new TiDBBinaryLogicalOperation(generateExpression(depth + 1), generateExpression(depth + 1), TiDBBinaryLogicalOperator.getRandom());
7990
// case BINARY_ARITHMETIC:
8091
// return new TiDBBinaryArithmeticOperation(generateExpression(depth + 1), generateExpression(depth + 1), TiDBBinaryArithmeticOperator.getRandom());
8192
default:
@@ -131,4 +142,12 @@ public TiDBExpressionGenerator setColumns(List<TiDBColumn> columns) {
131142
this.columns = columns;
132143
return this;
133144
}
145+
146+
public List<TiDBExpression> generateExpressions(int nr) {
147+
return generateExpressions(0, nr);
148+
}
149+
150+
public TiDBExpression generateHavingClause() {
151+
return generateExpression(); // TODO: enable aggregate functions
152+
}
134153
}

‎src/sqlancer/tidb/TiDBProvider.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@
2727
import sqlancer.tidb.gen.TiDBInsertGenerator;
2828
import sqlancer.tidb.gen.TiDBSetGenerator;
2929
import sqlancer.tidb.gen.TiDBTableGenerator;
30-
import sqlancer.tidb.test.TiDBQueryPartitioningWhereTester;
30+
import sqlancer.tidb.test.TiDBQueryPartitioningHavingTester;
3131

3232
public class TiDBProvider implements DatabaseProvider<TiDBGlobalState> {
3333

@@ -138,7 +138,7 @@ public void generateAndTestDatabase(TiDBGlobalState globalState) throws SQLExcep
138138
});
139139
se.executeStatements();
140140
manager.incrementCreateDatabase();
141-
TestOracle oracle = new TiDBQueryPartitioningWhereTester(globalState);
141+
TestOracle oracle = new TiDBQueryPartitioningHavingTester(globalState);
142142
for (int i = 0; i < globalState.getOptions().getNrQueries(); i++) {
143143
try {
144144
oracle.check();

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@
33
import sqlancer.Randomly;
44
import sqlancer.ast.BinaryNode;
55

6-
public class TiDBBinaryArithmeticOperation extends BinaryNode<TiDBExpression> implements TiDBExpression {
6+
7+
public class TiDBBinaryArithmeticOperation extends BinaryNode<TiDBExpression> implements TiDBExpression {
78

89
private final TiDBBinaryArithmeticOperator op;
910

@@ -36,4 +37,4 @@ public String getOperatorRepresentation() {
3637
return op.textRepresentation;
3738
}
3839

39-
}
40+
}

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
package sqlancer.tidb.ast;
2+
3+
import sqlancer.Randomly;
4+
import sqlancer.ast.BinaryNode;
5+
6+
public class TiDBBinaryLogicalOperation extends BinaryNode<TiDBExpression> implements TiDBExpression {
7+
8+
private final TiDBBinaryLogicalOperator op;
9+
10+
public static enum TiDBBinaryLogicalOperator {
11+
AND("&"), //
12+
OR("|"), //
13+
XOR("^"), //
14+
LEFT_SHIFT("<<"), //
15+
RIGHT_SHIFT(">>");
16+
17+
String textRepresentation;
18+
19+
TiDBBinaryLogicalOperator(String textRepresentation) {
20+
this.textRepresentation = textRepresentation;
21+
}
22+
23+
public static TiDBBinaryLogicalOperator getRandom() {
24+
return Randomly.fromOptions(values());
25+
}
26+
}
27+
28+
public TiDBBinaryLogicalOperation(TiDBExpression left, TiDBExpression right, TiDBBinaryLogicalOperator op) {
29+
super(left, right);
30+
this.op = op;
31+
}
32+
33+
@Override
34+
public String getOperatorRepresentation() {
35+
return op.textRepresentation;
36+
}
37+
38+
}

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

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,21 @@ public class TiDBFunctionCall implements TiDBExpression {
1111

1212
public static enum TiDBFunction {
1313

14+
// https://pingcap.github.io/docs/stable/reference/sql/functions-and-operators/bit-functions-and-operators/
15+
BIT_COUNT(1),
16+
17+
// https://pingcap.github.io/docs/stable/reference/sql/functions-and-operators/information-functions/
18+
CONNECTION_ID(0),
19+
// CURRENT_USER(0), https://github.com/pingcap/tidb/issues/15789
20+
DATABASE(0),
21+
// FOUND_ROWS(0), <-- non-deterministic
22+
// LAST_INSERT_ID(0), <-- non-deterministic
23+
// ROW_COUNT(0), <-- non-deterministic
24+
// SCHEMA(0), https://github.com/pingcap/tidb/issues/15789
25+
// SESSION_USER(0), https://github.com/pingcap/tidb/issues/15789
26+
// SYSTEM_USER(0), https://github.com/pingcap/tidb/issues/15789
27+
// USER(0), https://github.com/pingcap/tidb/issues/15789
28+
// VERSION(0), https://github.com/pingcap/tidb/issues/15789
1429

1530
TIDB_VERSION(0),
1631

@@ -21,7 +36,8 @@ public static enum TiDBFunction {
2136
ASCII(1),
2237
BIN(1),
2338
BIT_LENGTH(1),
24-
CHAR(1),
39+
// https://github.com/pingcap/tidb/issues/15789
40+
// CHAR(1),
2541
CHAR_LENGTH(1),
2642
CHARACTER_LENGTH(1),
2743
// CONCAT(1, true),

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,8 @@ public class TiDBSelect implements TiDBExpression {
99
private List<TiDBExpression> fetchColumns;
1010
private List<TiDBExpression> from;
1111
private List<TiDBExpression> orderBy = new ArrayList<>();
12+
private List<TiDBExpression> groupBys = new ArrayList<>();
13+
private TiDBExpression havingClause;
1214

1315
public void setWhereCondition(TiDBExpression wherePredicate) {
1416
this.wherePredicate = wherePredicate;
@@ -41,5 +43,21 @@ public void setOrderBy(List<TiDBExpression> orderBy) {
4143
public List<TiDBExpression> getOrderBy() {
4244
return orderBy;
4345
}
46+
47+
public void setGroupByClause(List<TiDBExpression> groupBys) {
48+
this.groupBys = groupBys;
49+
}
50+
51+
public List<TiDBExpression> getGroupBys() {
52+
return groupBys;
53+
}
54+
55+
public void setHavingClause(TiDBExpression havingClause) {
56+
this.havingClause = havingClause;
57+
}
58+
59+
public TiDBExpression getHavingClause() {
60+
return havingClause;
61+
}
4462

4563
}

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

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,8 +9,10 @@ public class TiDBUnaryPrefixOperation extends UnaryNode<TiDBExpression> implemen
99

1010
public static enum TiDBUnaryPrefixOperator {
1111
NOT("NOT"), //
12+
INVERSION("~"), //
1213
PLUS("+"), //
13-
MINUS("-"); //
14+
MINUS("-"), //
15+
BINARY("BINARY"); //
1416

1517
private String s;
1618

‎src/sqlancer/tidb/gen/TiDBSetGenerator.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,8 @@ public class TiDBSetGenerator {
1212

1313
private enum Action {
1414

15+
// SQL_MODE("sql_mode", (r) -> Randomly.fromOptions("TRADITIONAL", "ANSI", "POSTGRESQL", "ORACLE")),
16+
1517
TIDB_OPT_AGG_PUSH_DOWN("tidb_opt_agg_push_down", (r) -> Randomly.fromOptions(0, 1)),
1618
TIDB_BUILD_STATS_CONCURRENCY("tidb_build_stats_concurrency", (r) -> Randomly.getNotCachedInteger(0, 500)),
1719
TIDB_DISTSQL_SCAN_CONCURRENCY("tidb_distsql_scan_concurrency", (r) -> Randomly.getNotCachedInteger(1, 500)),
@@ -28,6 +30,8 @@ private enum Action {
2830
// https://github.com/pingcap/tidb/issues/15751
2931
// https://github.com/pingcap/tidb/issues/15752
3032
// TIDB_ENABLE_FAST_ANALYZE("tidb_enable_fast_analyze", (r) -> Randomly.fromOptions(0, 1));
33+
// TODO: global
34+
// TIDB_SCATTER_REGION("tidb_scatter_region", (r) -> Randomly.fromOptions(0, 1));
3135

3236
private String name;
3337
private Function<Randomly, Object> prod;

‎src/sqlancer/tidb/test/TiDBQueryPartitioningHavingTester.java‎

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
package sqlancer.tidb.test;
2+
3+
import java.io.IOException;
4+
import java.sql.SQLException;
5+
import java.util.Arrays;
6+
import java.util.HashSet;
7+
import java.util.List;
8+
import java.util.Set;
9+
import java.util.stream.Collectors;
10+
11+
import sqlancer.DatabaseProvider;
12+
import sqlancer.Randomly;
13+
import sqlancer.TestOracle;
14+
import sqlancer.tidb.TiDBErrors;
15+
import sqlancer.tidb.TiDBExpressionGenerator;
16+
import sqlancer.tidb.TiDBProvider.TiDBGlobalState;
17+
import sqlancer.tidb.TiDBSchema;
18+
import sqlancer.tidb.TiDBSchema.TiDBTables;
19+
import sqlancer.tidb.ast.TiDBColumnReference;
20+
import sqlancer.tidb.ast.TiDBExpression;
21+
import sqlancer.tidb.ast.TiDBSelect;
22+
import sqlancer.tidb.ast.TiDBTableReference;
23+
import sqlancer.tidb.ast.TiDBUnaryPostfixOperation;
24+
import sqlancer.tidb.ast.TiDBUnaryPostfixOperation.TiDBUnaryPostfixOperator;
25+
import sqlancer.tidb.ast.TiDBUnaryPrefixOperation;
26+
import sqlancer.tidb.ast.TiDBUnaryPrefixOperation.TiDBUnaryPrefixOperator;
27+
import sqlancer.tidb.visitor.TiDBVisitor;
28+
29+
public class TiDBQueryPartitioningHavingTester implements TestOracle {
30+
31+
private final TiDBGlobalState state;
32+
private final Set<String> errors = new HashSet<>();
33+
34+
public TiDBQueryPartitioningHavingTester(TiDBGlobalState state) {
35+
this.state = state;
36+
TiDBErrors.addExpressionErrors(errors);
37+
TiDBErrors.addExpressionHavingErrors(errors);
38+
}
39+
40+
@Override
41+
public void check() throws SQLException {
42+
TiDBSchema s = state.getSchema();
43+
TiDBTables targetTables = s.getRandomTableNonEmptyTables();
44+
TiDBExpressionGenerator gen = new TiDBExpressionGenerator(state).setColumns(targetTables.getColumns());
45+
TiDBSelect select = new TiDBSelect();
46+
select.setColumns(Arrays.asList(new TiDBColumnReference(targetTables.getColumns().get(0))));
47+
List<TiDBExpression> tableList = targetTables.getTables().stream()
48+
.map(t -> new TiDBTableReference(t)).collect(Collectors.toList());
49+
// List<TiDBTableReference> from = TiDBCommon.getTableReferences(tableList);
50+
// if (Randomly.getBooleanWithRatherLowProbability()) {
51+
// select.setJoinList(TiDBNoRECTester.getJoins(from, state));
52+
// }
53+
select.setFromTables(tableList);
54+
// TODO order by?
55+
if (Randomly.getBoolean()) {
56+
select.setWhereCondition(gen.generateExpression());
57+
}
58+
select.setGroupByClause(gen.generateExpressions(Randomly.smallNumber() + 1));
59+
select.setHavingClause(null);
60+
String originalQueryString = TiDBVisitor.asString(select);
61+
if (state.getOptions().logEachSelect()) {
62+
state.getLogger().writeCurrent(originalQueryString);
63+
try {
64+
state.getLogger().getCurrentFileWriter().flush();
65+
} catch (IOException e) {
66+
// TODO Auto-generated catch block
67+
e.printStackTrace();
68+
}
69+
}
70+
List<String> resultSet = DatabaseProvider.getResultSetFirstColumnAsString(originalQueryString, errors, state.getConnection());
71+
72+
TiDBExpression predicate = gen.generateHavingClause();
73+
select.setHavingClause(predicate);
74+
String firstQueryString = TiDBVisitor.asString(select);
75+
select.setHavingClause(new TiDBUnaryPrefixOperation(predicate, TiDBUnaryPrefixOperator.NOT));
76+
String secondQueryString = TiDBVisitor.asString(select);
77+
select.setHavingClause(new TiDBUnaryPostfixOperation(predicate, TiDBUnaryPostfixOperator.IS_NULL));
78+
String thirdQueryString = TiDBVisitor.asString(select);
79+
String combinedString = firstQueryString + " UNION ALL " + secondQueryString + " UNION ALL " + thirdQueryString;
80+
if (state.getOptions().logEachSelect()) {
81+
state.getLogger().writeCurrent(combinedString);
82+
try {
83+
state.getLogger().getCurrentFileWriter().flush();
84+
} catch (IOException e) {
85+
// TODO Auto-generated catch block
86+
e.printStackTrace();
87+
}
88+
}
89+
List<String> secondResultSet = DatabaseProvider.getResultSetFirstColumnAsString(combinedString, errors, state.getConnection());
90+
if (resultSet.size() != secondResultSet.size()) {
91+
throw new AssertionError(originalQueryString + ";\n" + combinedString + ";");
92+
}
93+
}
94+
}

0 commit comments

Comments
 (0)