Skip to content

Commit a00e636

Browse files
mriggermrigger
authored
Merge pull request #1358 from sqlancer/feature/eet-insert
Extend the EET DML oracle to INSERT support
2 parents 41550d1 + f46934e commit a00e636

4 files changed

Lines changed: 162 additions & 16 deletions

File tree

‎src/sqlancer/common/gen/EETDMLGenerator.java‎

Lines changed: 75 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -19,9 +19,9 @@
1919
* <p>
2020
* Adapted from the DQE oracle, state is observed with an auxiliary column ({@link EETDMLGenerator#ROW_ID_COLUMN}) which
2121
* uniquely identifies each row. The rows are stamped with identifiers once, before both executions of the statement run
22-
* (each in a rolled-back transaction), so both executions observe the same identifiers regardless of how they are
23-
* produced. The resulting state is compared as a full post-image (each surviving row's identifier and content column
24-
* values), which covers every DML statement: a DELETE removes rows from it, an UPDATE changes values in it.
22+
* (each in a rolled-back transaction), so both executions observe the same identifiers. The resulting state is compared
23+
* as a full post-image (each surviving row's identifier and content column values), which covers any of the three DML
24+
* statements (DELETE, UPDATE, INSERT).
2525
*
2626
* <p>
2727
* Most of these statements are standard SQL, likely common to most DBMSs, so are provided as {@code default} methods.
@@ -66,6 +66,15 @@ public interface EETDMLGenerator<E extends Expression<C>, T extends AbstractTabl
6666
*/
6767
List<Map.Entry<C, E>> generateSetAssignments();
6868

69+
/**
70+
* Generates a fresh value expression for each content column of the current table, used as an INSERT statement's
71+
* inserted values. The returned expressions are positionally aligned with {@link AbstractTable#getColumns()}, and
72+
* each is transformed by the oracle.
73+
*
74+
* @return one fresh random value expression per content column, in {@link AbstractTable#getColumns()} order
75+
*/
76+
List<E> generateInsertValues();
77+
6978
/**
7079
* Creates a DBMS-specific {@link EETTransformer} backed by this generator, used to rewrite the statement's
7180
* expressions into semantically equivalent ones.
@@ -106,6 +115,17 @@ public interface EETDMLGenerator<E extends Expression<C>, T extends AbstractTabl
106115
*/
107116
String rowIdColumnType();
108117

118+
/**
119+
* A SQL expression, evaluated once per source row of an {@code INSERT ... SELECT}, that derives the inserted row's
120+
* {@link #ROW_ID_COLUMN} value from the source row's identifier. It must be deterministic (so both the original and
121+
* transformed statements assign the same identifiers), unique per source row, and distinct from every existing
122+
* identifier (so an inserted row never collides with the source row it was derived from in the post-image). DBMS-
123+
* specific because it names a suitable derivation function (e.g. a hash of the source identifier).
124+
*
125+
* @return the SQL expression deriving an inserted row's identifier from the source row's {@link #ROW_ID_COLUMN}
126+
*/
127+
String insertedRowIdExpression();
128+
109129
// --- Standard-SQL statements (override only where the DBMS's dialect differs) ---
110130

111131
/**
@@ -139,8 +159,9 @@ default String dropRowIdColumnStatement(T table) {
139159
*
140160
* <p>
141161
* This single value-level snapshot is the comparison surface for all DML statements: a DELETE removes rows from it,
142-
* an UPDATE changes column values in it. Row identity alone (which the identifier already captures) would suffice
143-
* for DELETE, but not for UPDATE, where the two runs could touch the same rows yet write different values.
162+
* an UPDATE changes column values in it, an INSERT adds rows to it. Row identity alone (which the identifier
163+
* already captures) would suffice for DELETE, but not for UPDATE, where the two runs could touch the same rows yet
164+
* write different values.
144165
*
145166
* @param table
146167
* the table to snapshot
@@ -223,8 +244,55 @@ default String updateStatement(T table, List<Map.Entry<C, E>> assignments, E pre
223244
}
224245

225246
/**
226-
* Renders the trailing {@code ORDER BY ... LIMIT n} clause shared by {@link #deleteStatement} and
227-
* {@link #updateStatement}, or the empty string when {@code limit} is null.
247+
* SQL that inserts a new row into {@code table} for each source row (optionally filtered by {@code predicate}),
248+
* setting each content column to its corresponding value in {@code values}, optionally limited to the first
249+
* {@code limit} source rows (see {@link #orderByLimitClause}).
250+
*
251+
* <p>
252+
* The {@code INSERT ... SELECT} form is used rather than {@code INSERT ... VALUES} because the transformed value
253+
* expressions reference the table's columns (the transformer injects column references into its equivalent
254+
* sub-expressions), which are legal in a {@code SELECT} but not in a {@code VALUES} clause. Each inserted row's
255+
* {@link #ROW_ID_COLUMN} is derived from its source row via {@link #insertedRowIdExpression()}, giving it a
256+
* deterministic identifier that is unique and distinct from every existing one, so the two statements' post-images
257+
* align (and inserted rows never collide with their source rows).
258+
*
259+
* @param table
260+
* the table to insert into
261+
* @param values
262+
* one value expression per content column, positionally aligned with {@link AbstractTable#getColumns()};
263+
* each is rendered via {@link #asString}
264+
* @param predicate
265+
* the WHERE predicate filtering the source rows, or {@code null} to insert from every source row;
266+
* rendered via {@link #asString}
267+
* @param orderByColumns
268+
* the columns to order the source rows by before the row-id tiebreaker (may be empty); only used when
269+
* {@code limit} is non-null
270+
* @param limit
271+
* the maximum number of source rows to insert from, or {@code null} for no limit
272+
*
273+
* @return the SQL statement
274+
*/
275+
default String insertStatement(T table, List<E> values, E predicate, List<C> orderByColumns, Integer limit) {
276+
List<String> columnNames = new ArrayList<>();
277+
columnNames.add(ROW_ID_COLUMN);
278+
List<String> selectItems = new ArrayList<>();
279+
selectItems.add(insertedRowIdExpression());
280+
List<C> columns = table.getColumns();
281+
for (int i = 0; i < columns.size(); i++) {
282+
columnNames.add(columns.get(i).getName());
283+
selectItems.add(asString(values.get(i)));
284+
}
285+
String statement = "INSERT INTO " + table.getName() + " (" + String.join(", ", columnNames) + ") SELECT "
286+
+ String.join(", ", selectItems) + " FROM " + table.getName();
287+
if (predicate != null) {
288+
statement += " WHERE " + asString(predicate);
289+
}
290+
return statement + orderByLimitClause(orderByColumns, limit);
291+
}
292+
293+
/**
294+
* Renders the trailing {@code ORDER BY ... LIMIT n} clause shared by {@link #deleteStatement},
295+
* {@link #updateStatement} and {@link #insertStatement}, or the empty string when {@code limit} is null.
228296
*
229297
* <p>
230298
* The rows are ordered by {@code orderByColumns} followed by {@link #ROW_ID_COLUMN} as a tiebreaker. Because the

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

Lines changed: 62 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -37,13 +37,15 @@
3737
* statements can be compared against the same starting state without permanently modifying the database. The state is
3838
* captured as a full post-image: each surviving row's identifier together with its content column values, ordered by
3939
* the identifier. This single value-level surface covers every DML statement — a DELETE removes rows from it, an UPDATE
40-
* changes values in it (row identity alone would suffice for DELETE, but not for UPDATE, which also transforms the
41-
* written values). Because rolling back a statement requires a transactional storage engine, the DBMS-specific setup
42-
* must ensure only such engines are used while this oracle is active.
40+
* changes values in it, an INSERT adds rows to it (row identity alone would suffice for DELETE, but not for UPDATE,
41+
* which also transforms the written values). Because rolling back a statement requires a transactional storage engine,
42+
* the DBMS-specific setup must ensure only such engines are used while this oracle is active.
4343
*
4444
* <p>
45-
* DELETE and UPDATE are currently supported (one is chosen at random per check). Statement reduction is not yet
46-
* implemented (there is no {@link sqlancer.Reproducer Reproducer}), so the finding is reported without database
45+
* DELETE, UPDATE and INSERT are currently supported (one is chosen at random per check). INSERT uses the
46+
* {@code INSERT ... SELECT} form so its transformed value expressions may reference columns; each inserted row is given
47+
* a deterministic identifier derived from its source row so the two runs' post-images align. Statement reduction is not
48+
* yet implemented (there is no {@link sqlancer.Reproducer Reproducer}), so the finding is reported without database
4749
* reduction.
4850
*
4951
* @param <E>
@@ -104,9 +106,11 @@ public void check() throws SQLException {
104106
orderByColumns = Randomly.subset(table.getColumns());
105107
}
106108

107-
StatementPair statements = Randomly.getBoolean()
108-
? generateUpdateStatements(table, predicate, transformedPredicate, orderByColumns, limit)
109-
: generateDeleteStatements(table, predicate, transformedPredicate, orderByColumns, limit);
109+
// Generators for the different kinds of statement this oracle supports. One is chosen at random per check
110+
List<DMLStatementGenerator<E, T, C>> statementGenerators = List.of(this::generateDeleteStatements,
111+
this::generateUpdateStatements, this::generateInsertStatements);
112+
StatementPair statements = Randomly.fromList(statementGenerators).generate(table, predicate,
113+
transformedPredicate, orderByColumns, limit);
110114
String originalStatement = statements.original;
111115
String transformedStatement = statements.transformed;
112116
generatedQueryString = originalStatement;
@@ -137,6 +141,22 @@ public void check() throws SQLException {
137141
}
138142
}
139143

144+
/**
145+
* Generates a DML statement of one kind together with its transformed counterpart. The kinds share this signature
146+
* so the oracle can pick one of them at random per check.
147+
*
148+
* @param <E>
149+
* the DBMS-specific expression class
150+
* @param <T>
151+
* the DBMS-specific table class
152+
* @param <C>
153+
* the DBMS-specific column class
154+
*/
155+
@FunctionalInterface
156+
private interface DMLStatementGenerator<E, T, C> {
157+
StatementPair generate(T table, E predicate, E transformedPredicate, List<C> orderByColumns, Integer limit);
158+
}
159+
140160
/**
141161
* A DML statement and its transformed counterpart, which must leave the database in the same state.
142162
*/
@@ -201,6 +221,40 @@ private StatementPair generateDeleteStatements(T table, E predicate, E transform
201221
gen.deleteStatement(table, transformedPredicate, orderByColumns, limit));
202222
}
203223

224+
/**
225+
* Generates an {@code INSERT ... SELECT} and its transformed counterpart. Besides the WHERE predicate, which
226+
* filters the source rows and is optional here, INSERT also transforms each inserted value in a scalar context.
227+
*
228+
* <p>
229+
* The ordering and limit cap the source rows the statement reads, so it inserts one row per source row kept.
230+
*
231+
* @param table
232+
* the table being modified
233+
* @param predicate
234+
* the WHERE predicate of the original statement
235+
* @param transformedPredicate
236+
* the transformed WHERE predicate, used by the transformed statement
237+
* @param orderByColumns
238+
* the columns ordering the source rows, empty if the statement is not capped by a limit
239+
* @param limit
240+
* the maximum number of source rows to insert from, or {@code null} for no limit
241+
*
242+
* @return the original statement together with its transformed counterpart
243+
*/
244+
private StatementPair generateInsertStatements(T table, E predicate, E transformedPredicate, List<C> orderByColumns,
245+
Integer limit) {
246+
List<E> values = gen.generateInsertValues();
247+
List<E> transformedValues = new ArrayList<>();
248+
for (E value : values) {
249+
transformedValues.add(transformer.transform(value, false));
250+
}
251+
boolean withPredicate = Randomly.getBoolean();
252+
return new StatementPair(
253+
gen.insertStatement(table, values, withPredicate ? predicate : null, orderByColumns, limit),
254+
gen.insertStatement(table, transformedValues, withPredicate ? transformedPredicate : null,
255+
orderByColumns, limit));
256+
}
257+
204258
/**
205259
* Executes {@code statement} inside a transaction that is always rolled back, and returns the resulting post-image:
206260
* the surviving rows' identifier and content column values, ordered by identifier (the resulting database state). A

‎src/sqlancer/mysql/gen/MySQLExpressionGenerator.java‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -268,6 +268,14 @@ public List<Map.Entry<MySQLColumn, MySQLExpression>> generateSetAssignments() {
268268
return assignments;
269269
}
270270

271+
@Override
272+
public List<MySQLExpression> generateInsertValues() {
273+
// One value per content column, in schema order (aligned with the INSERT column list). As with the normal
274+
// INSERT workload, each value is an arbitrary expression (not type-matched to the column); any resulting
275+
// type/range/constraint error is on the oracle's expected-error allow-list.
276+
return columns.stream().map(c -> generateExpression()).collect(Collectors.toList());
277+
}
278+
271279
@Override
272280
public MySQLSelect generateSelect() {
273281
return new MySQLSelect();
@@ -408,4 +416,12 @@ public String rowIdColumnType() {
408416
// Holds a 36-character UUID string produced by stampRowIdsStatement.
409417
return "VARCHAR(36)";
410418
}
419+
420+
@Override
421+
public String insertedRowIdExpression() {
422+
// The source row's identifier with its dashes removed: deterministic (identical across both runs) and unique
423+
// per
424+
// source row. Fits the identifier column's VARCHAR(36).
425+
return String.format("REPLACE(%s, '-', '')", ROW_ID_COLUMN);
426+
}
411427
}

‎src/sqlancer/mysql/gen/MySQLTableGenerator.java‎

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -164,7 +164,15 @@ public static List<TableOptions> getRandomTableOptions() {
164164
}
165165

166166
private void appendTableOptions() {
167-
List<TableOptions> tableOptions = TableOptions.getRandomTableOptions();
167+
List<TableOptions> tableOptions = new ArrayList<>(TableOptions.getRandomTableOptions());
168+
// The EET DML oracle rolls back each statement to compare database states, which requires a transactional
169+
// engine. The ENGINE option already forces InnoDB when the oracle is active (see the ENGINE case below), but it
170+
// is only emitted when randomly chosen; otherwise the table would inherit the server's default engine, which is
171+
// not guaranteed transactional. Force the option to always be present so the engine is never left to the
172+
// server default.
173+
if (globalState.usesEETDML() && !tableOptions.contains(TableOptions.ENGINE)) {
174+
tableOptions.add(TableOptions.ENGINE);
175+
}
168176
int i = 0;
169177
for (TableOptions o : tableOptions) {
170178
if (i++ != 0) {

0 commit comments

Comments
 (0)