hibernate/hibernate-orm · error · IllegalQueryOperationException

Optional table insert is not supported

Error message

Optional table insert is not supported

What it means

OptionalTableInsert is produced when Hibernate upserts an entity row into an optional secondary table (it must decide at runtime whether a row exists there). Only dialect translators that override visitStandardTableInsert for this case (PostgreSQL, CockroachDB via their upsert handling) can render it; the base AbstractSqlAstTranslator throws IllegalQueryOperationException for any other dialect.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java:8684

		return dialect.getFromDualForSelectOnly();
	}

	protected enum LockStrategy {
		CLAUSE,
		FOLLOW_ON,
		NONE
	}

	private T translateTableMutation(TableMutation<?> mutation) {
		mutation.accept( this );
		//noinspection unchecked
		return (T) mutation.createMutationOperation( getSql(), parameterBinders );
	}

	@Override
	public void visitStandardTableInsert(TableInsertStandard tableInsert) {
		if ( tableInsert instanceof OptionalTableInsert ) {
			throw new IllegalQueryOperationException( "Optional table insert is not supported" );
		}
		getCurrentClauseStack().push( Clause.INSERT );
		try {
			renderInsertInto( tableInsert );

			if ( tableInsert.getNumberOfReturningColumns() > 0 ) {
				visitReturningColumns( tableInsert::getReturningColumns );
			}
		}
		finally {
			getCurrentClauseStack().pop();
		}
	}

	protected void renderInsertInto(TableInsertStandard tableInsert) {
		applySqlComment( tableInsert.getMutationComment() );

		if ( tableInsert.getNumberOfValueBindings() == 0 ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Set optional = false on the @SecondaryTable so a normal insert is planned
2. Ensure the secondary table always gets a row for the entity (no optional semantics needed)
3. Run against PostgreSQL/CockroachDB where the upsert path is supported
4. Upgrade Hibernate in case more dialects gained optional-table insert support

Example fix

// before
@SecondaryTable(name = "cust_details", pkJoinColumns = @PrimaryKeyJoinColumn(name = "id"), optional = true)

// after
@SecondaryTable(name = "cust_details", pkJoinColumns = @PrimaryKeyJoinColumn(name = "id"))

// When booting on a non-PostgreSQL/Cockroach dialect, reject optional secondary tables
boolean upsertCapable = dialect instanceof PostgreSQLDialect || dialect instanceof CockroachDialect;
if (!upsertCapable && mappingHasOptionalSecondaryTable(entityClasses)) {
    // fail fast at startup with a clear config message
}

try {
    session.persist(entity);
} catch (IllegalQueryOperationException e) {
    if ("Optional table insert is not supported".equals(e.getMessage())) {
        // surface a config-level error: optional secondary tables need a supported dialect
    } else throw e;
}

When it happens

Trigger: An entity maps an optional secondary table (@SecondaryTable(optional=true)) and its insert/update path goes through OptionalTableUpdateWithUpsertOperation, which builds an OptionalTableInsert - on a dialect other than PostgreSQL/CockroachDB the base translator refuses it.

Common situations: @SecondaryTable(optional = true) mappings that worked on PostgreSQL failing when the same app runs/tests on H2/MySQL/Oracle; multi-tenant or test-dialect (H2) setups diverging from production; Hibernate 6.x where optional secondary table upsert support is dialect-gated.

Related errors

• Insert conflict 'do update' clause with constraint name is n
• The INSERT statement for table [%s] contains no column, and
• Insert conflict 'do update' clause with constraint name is n
• Insert conflict 'do update' clause with constraint name is n
• Insert conflict 'do update' clause with constraint name is n

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/2c0da22b5ed2cba8. Report an issue: GitHub.