PostgreSQL view generator can produce nondeterministic views #1355

Summary

PostgresRandomQueryGenerator.createRandomQuery() can emit SELECT DISTINCT ON (k) ... without an ORDER BY that determines which row survives each group. Used as a view body, such a query returns a different row under a different plan, and both results are correct. Oracles that compare results across plans report this as a discrepancy.

Ambiguity inside the view definition

The ambiguity sits one level below the query under test. Reduced from a run against postgres:18:

CREATE VIEW v0(c0, c1, c2) AS (
  SELECT DISTINCT ON ( <boolean expression> )   -- 2-3 groups
         t0.c0, ...
  FROM ONLY t1, ONLY t0
  WHERE t1.c1 >= t0.c1
);
-- no ORDER BY at all

SELECT DISTINCT v0.c0, v0.c1 FROM ONLY v0 WHERE CAST(CAST(v0.c1 AS INT) AS BOOLEAN);

The tested query has no LIMIT, no aggregate, and an explicit DISTINCT over two columns. Under SET enable_seqscan = off the two results differ symmetrically: one contains NULL and not 210.249.166.198, the other the reverse. No rows were lost. The group representative changed. A check applied to the text of the tested query cannot detect this.

Code path

PostgresViewGenerator.create() builds the view body with PostgresRandomQueryGenerator.createRandomQuery(), where the two relevant clauses are decided independently:

select.setSelectType(SelectType.getRandom());
if (select.getSelectOption() == SelectType.DISTINCT && Randomly.getBoolean()) {
    select.setDistinctOnClause(gen.generateExpression(0));
}
...
if (Randomly.getBooleanWithRatherLowProbability()) {
    select.setOrderByClauses(gen.generateOrderBys());
}

DISTINCT ON is added to half of all DISTINCT selects. ORDER BY is added only with a rather low probability, and generateOrderBys() is not constrained to cover the output columns. The combination that produces nondeterminism is therefore the default, not a rare corner.

The generator already expects SELECT DISTINCT ON expressions must match initial ORDER BY expressions, so an inconsistent ORDER BY is rejected by PostgreSQL and handled. Omitting ORDER BY entirely is legal SQL and passes silently.

Frequency

From one overnight run on postgres:18:

• DISTINCT ON appears in 11 of 20 generated view definitions
• at least one such view exists in 5 of 13 generated databases
• in 3 of the 4 observed cases the DISTINCT ON key was a constant expression, so every row falls into a single group and the view returns one arbitrary row of the join

Scope

This is not confined to one oracle. Any oracle that changes the plan of a query reading such a view is affected, including the merged QPG.

Value-level ambiguity is a related class, for example 0 against -0 under GROUP BY on MySQL, and canonicalizing result values handles it. Row-choice ambiguity is not reachable that way, because the value formats are identical and the row itself differs.

Proposal

When DISTINCT ON is generated, emit an ORDER BY covering the key followed by all output columns. This makes the row choice total without removing DISTINCT ON from the grammar, and it addresses the cause rather than each oracle separately.

Two alternatives look worse. Excluding views from oracle queries removes a large class of plans from testing. Filtering nondeterministic views by parsing pg_get_viewdef is a heuristic that errs in both directions.

LIMIT in view bodies

The same generator adds LIMIT to half of the view bodies without a total ORDER BY. This is a second channel for the same defect. It rarely binds in practice, because Randomly.getPositiveOrZeroNonCachedInteger() returns values far above the row count, 2618503569244999991 and 3491465572106883265 in observed runs.

I can open a PR for the proposal if the approach is right.