DML and CTAS commands: Potential for wrong results when the RELY property is set (Preview)¶

When this behavior change bundle is enabled, an optimization on certain DML and CTAS statements might produce wrong results when the RELY property is set on the target table:

Before the change:

DML and CTAS statements on tables with UNIQUE and PRIMARY KEY constraints do not qualify for an optimization that is applied to DISTINCT and GROUP BY operations. (The optimizer prunes redundant grouping columns.)

Because the optimization in question is currently applied to SELECT statements only, the optimization presents no risk that incorrect data might be inserted if the RELY property is set for a constraint and a violation of referential integrity occurs.

After the change:

DML and CTAS statements on tables with UNIQUE and PRIMARY KEY constraints now qualify for the same optimization that is applied to SELECT statements.

If the RELY property is set for a constraint and a violation of referential integrity occurs, incorrect data might be inserted.

SELECT table_schema, table_name
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE RELY ='YES'
    AND (constraint_type = 'PRIMARY KEY' OR constraint_type = 'UNIQUE');