Skip to content

Commit 41550d1

Browse files
mriggermrigger
authored
Extend the EET DML oracle to UPDATE support (#1357)
* Implement UPDATE support for EET DML oracle * Add row discrepancy information to test case logs for EET DML oracle * Remove hard-coding of EET DML rowId column being at index 0; derive it instead * Refactor EET DML statement generation to use dedicated methods for update and delete
2 parents 88d717e + 1ce0341 commit 41550d1

3 files changed

Lines changed: 344 additions & 73 deletions

File tree

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

Lines changed: 105 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22

33
import java.util.ArrayList;
44
import java.util.List;
5+
import java.util.Map;
56

67
import sqlancer.common.ast.newast.Expression;
78
import sqlancer.common.oracle.EETTransformer;
@@ -19,7 +20,8 @@
1920
* Adapted from the DQE oracle, state is observed with an auxiliary column ({@link EETDMLGenerator#ROW_ID_COLUMN}) which
2021
* uniquely identifies each row. The rows are stamped with identifiers once, before both executions of the statement run
2122
* (each in a rolled-back transaction), so both executions observe the same identifiers regardless of how they are
22-
* produced.
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.
2325
*
2426
* <p>
2527
* Most of these statements are standard SQL, likely common to most DBMSs, so are provided as {@code default} methods.
@@ -55,6 +57,15 @@ public interface EETDMLGenerator<E extends Expression<C>, T extends AbstractTabl
5557
*/
5658
E generateBooleanExpression();
5759

60+
/**
61+
* Generates a fresh set of {@code column = value} assignments over the current tables' columns, used as an UPDATE
62+
* statement's SET clause. The columns are a random non-empty subset and each value is a fresh random expression;
63+
* both the columns and their assigned expressions are transformed by the oracle.
64+
*
65+
* @return the assignments, as {@code (column, value expression)} pairs (at least one)
66+
*/
67+
List<Map.Entry<C, E>> generateSetAssignments();
68+
5869
/**
5970
* Creates a DBMS-specific {@link EETTransformer} backed by this generator, used to rewrite the statement's
6071
* expressions into semantically equivalent ones.
@@ -122,27 +133,48 @@ default String dropRowIdColumnStatement(T table) {
122133
}
123134

124135
/**
125-
* SQL that selects the {@link #ROW_ID_COLUMN} of every row of {@code table} (the surviving-row snapshot).
136+
* SQL that reads back the full post-image of {@code table}: the {@link #ROW_ID_COLUMN} identifier and every content
137+
* column of every surviving row, ordered by the (unique) identifier so the two statements' snapshots align
138+
* row-for-row.
139+
*
140+
* <p>
141+
* 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.
126144
*
127145
* @param table
128146
* the table to snapshot
129147
*
130-
* @return the SQL statement; its first result column must be the identifiers
148+
* @return the SQL statement; its result columns are those of {@link #postImageColumns}, in that order
131149
*/
132-
default String selectRowIdsStatement(T table) {
133-
return "SELECT " + ROW_ID_COLUMN + " FROM " + table.getName();
150+
default String selectPostImageStatement(T table) {
151+
return "SELECT " + String.join(", ", postImageColumns(table)) + " FROM " + table.getName() + " ORDER BY "
152+
+ ROW_ID_COLUMN;
134153
}
135154

136155
/**
137-
* SQL that deletes the rows of {@code table} matching {@code predicate}, optionally limited to the first
138-
* {@code limit} rows.
156+
* The columns a post-image row consists of, in the order {@link #selectPostImageStatement} returns them: the
157+
* {@link #ROW_ID_COLUMN} identifier followed by {@code table}'s content columns. This is the sole definition of the
158+
* post-image layout, so a consumer can find the identifier's position by looking up {@link #ROW_ID_COLUMN} here
159+
* rather than assuming one.
139160
*
140-
* <p>
141-
* When {@code limit} is non-null, the statement is ordered by {@code orderByColumns} followed by
142-
* {@link #ROW_ID_COLUMN} as a tiebreaker. Because the identifiers are unique, this is always a total order (even
143-
* when the ordering columns tie), so the "first {@code limit}" rows are identical for the original and transformed
144-
* statements. Varying the ordering columns exercises more access paths than the row id alone would. The caller must
145-
* pass the same {@code orderByColumns} and {@code limit} to both statements; neither is transformed.
161+
* @param table
162+
* the table being snapshot
163+
*
164+
* @return the post-image column names, in order
165+
*/
166+
default List<String> postImageColumns(T table) {
167+
List<String> columns = new ArrayList<>();
168+
columns.add(ROW_ID_COLUMN);
169+
for (C column : table.getColumns()) {
170+
columns.add(column.getName());
171+
}
172+
return columns;
173+
}
174+
175+
/**
176+
* SQL that deletes the rows of {@code table} matching {@code predicate}, optionally limited to the first
177+
* {@code limit} rows (see {@link #orderByLimitClause}).
146178
*
147179
* @param table
148180
* the table to delete from
@@ -157,16 +189,67 @@ default String selectRowIdsStatement(T table) {
157189
* @return the SQL statement
158190
*/
159191
default String deleteStatement(T table, E predicate, List<C> orderByColumns, Integer limit) {
160-
String statement = "DELETE FROM " + table.getName() + " WHERE " + asString(predicate);
161-
if (limit != null) {
162-
List<String> orderBy = new ArrayList<>();
163-
for (C column : orderByColumns) {
164-
orderBy.add(column.getName());
165-
}
166-
orderBy.add(ROW_ID_COLUMN); // unique tiebreaker: guarantees a total order regardless of the columns above
167-
statement += " ORDER BY " + String.join(", ", orderBy) + " LIMIT " + limit;
192+
return "DELETE FROM " + table.getName() + " WHERE " + asString(predicate)
193+
+ orderByLimitClause(orderByColumns, limit);
194+
}
195+
196+
/**
197+
* SQL that updates the rows of {@code table} matching {@code predicate}, setting each column in {@code assignments}
198+
* to its assigned value expression, optionally limited to the first {@code limit} rows (see
199+
* {@link #orderByLimitClause}).
200+
*
201+
* @param table
202+
* the table to update
203+
* @param assignments
204+
* the {@code (column, value expression)} pairs to assign; each value is rendered via {@link #asString}
205+
* @param predicate
206+
* the WHERE predicate; rendered via {@link #asString}
207+
* @param orderByColumns
208+
* the columns to order by before the row-id tiebreaker (may be empty); only used when {@code limit} is
209+
* non-null
210+
* @param limit
211+
* the maximum number of rows to update, or {@code null} for no limit
212+
*
213+
* @return the SQL statement
214+
*/
215+
default String updateStatement(T table, List<Map.Entry<C, E>> assignments, E predicate, List<C> orderByColumns,
216+
Integer limit) {
217+
List<String> setClauses = new ArrayList<>();
218+
for (Map.Entry<C, E> assignment : assignments) {
219+
setClauses.add(assignment.getKey().getName() + " = " + asString(assignment.getValue()));
220+
}
221+
return "UPDATE " + table.getName() + " SET " + String.join(", ", setClauses) + " WHERE " + asString(predicate)
222+
+ orderByLimitClause(orderByColumns, limit);
223+
}
224+
225+
/**
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.
228+
*
229+
* <p>
230+
* The rows are ordered by {@code orderByColumns} followed by {@link #ROW_ID_COLUMN} as a tiebreaker. Because the
231+
* identifiers are unique, this is always a total order (even when the ordering columns tie), so the "first
232+
* {@code limit}" rows are identical for the original and transformed statements. Varying the ordering columns
233+
* exercises more access paths than the row id alone would. The caller must pass the same {@code orderByColumns} and
234+
* {@code limit} to both statements; neither is transformed.
235+
*
236+
* @param orderByColumns
237+
* the columns to order by before the row-id tiebreaker (may be empty)
238+
* @param limit
239+
* the maximum number of rows, or {@code null} for no limit (yielding an empty clause)
240+
*
241+
* @return the {@code ORDER BY ... LIMIT n} clause, or the empty string when {@code limit} is null
242+
*/
243+
default String orderByLimitClause(List<C> orderByColumns, Integer limit) {
244+
if (limit == null) {
245+
return "";
246+
}
247+
List<String> orderBy = new ArrayList<>();
248+
for (C column : orderByColumns) {
249+
orderBy.add(column.getName());
168250
}
169-
return statement;
251+
orderBy.add(ROW_ID_COLUMN); // unique tiebreaker: guarantees a total order regardless of the columns above
252+
return " ORDER BY " + String.join(", ", orderBy) + " LIMIT " + limit;
170253
}
171254

172255
/**

0 commit comments

Comments
 (0)