{"record":{"id":"2c0da22b5ed2cba8","repo":"hibernate/hibernate-orm","slug":"optional-table-insert-is-not-supported","errorCode":null,"errorMessage":"Optional table insert is not supported","messagePattern":"Optional table insert is not supported","errorType":"exception","errorClass":"IllegalQueryOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":8684,"sourceCode":"\t\treturn dialect.getFromDualForSelectOnly();\n\t}\n\n\tprotected enum LockStrategy {\n\t\tCLAUSE,\n\t\tFOLLOW_ON,\n\t\tNONE\n\t}\n\n\tprivate T translateTableMutation(TableMutation<?> mutation) {\n\t\tmutation.accept( this );\n\t\t//noinspection unchecked\n\t\treturn (T) mutation.createMutationOperation( getSql(), parameterBinders );\n\t}\n\n\t@Override\n\tpublic void visitStandardTableInsert(TableInsertStandard tableInsert) {\n\t\tif ( tableInsert instanceof OptionalTableInsert ) {\n\t\t\tthrow new IllegalQueryOperationException( \"Optional table insert is not supported\" );\n\t\t}\n\t\tgetCurrentClauseStack().push( Clause.INSERT );\n\t\ttry {\n\t\t\trenderInsertInto( tableInsert );\n\n\t\t\tif ( tableInsert.getNumberOfReturningColumns() > 0 ) {\n\t\t\t\tvisitReturningColumns( tableInsert::getReturningColumns );\n\t\t\t}\n\t\t}\n\t\tfinally {\n\t\t\tgetCurrentClauseStack().pop();\n\t\t}\n\t}\n\n\tprotected void renderInsertInto(TableInsertStandard tableInsert) {\n\t\tapplySqlComment( tableInsert.getMutationComment() );\n\n\t\tif ( tableInsert.getNumberOfValueBindings() == 0 ) {","sourceCodeStart":8666,"sourceCodeEnd":8702,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L8666-L8702","documentation":"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.","triggerScenarios":"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.","commonSituations":"@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.","solutions":["Set optional = false on the @SecondaryTable so a normal insert is planned","Ensure the secondary table always gets a row for the entity (no optional semantics needed)","Run against PostgreSQL/CockroachDB where the upsert path is supported","Upgrade Hibernate in case more dialects gained optional-table insert support"],"exampleFix":"// before\n@SecondaryTable(name = \"cust_details\", pkJoinColumns = @PrimaryKeyJoinColumn(name = \"id\"), optional = true)\n\n// after\n@SecondaryTable(name = \"cust_details\", pkJoinColumns = @PrimaryKeyJoinColumn(name = \"id\"))","handlingStrategy":"validation","validationCode":"// When booting on a non-PostgreSQL/Cockroach dialect, reject optional secondary tables\nboolean upsertCapable = dialect instanceof PostgreSQLDialect || dialect instanceof CockroachDialect;\nif (!upsertCapable && mappingHasOptionalSecondaryTable(entityClasses)) {\n    // fail fast at startup with a clear config message\n}","typeGuard":null,"tryCatchPattern":"try {\n    session.persist(entity);\n} catch (IllegalQueryOperationException e) {\n    if (\"Optional table insert is not supported\".equals(e.getMessage())) {\n        // surface a config-level error: optional secondary tables need a supported dialect\n    } else throw e;\n}","preventionTips":["Avoid @SecondaryTable(optional = true) unless you run on PostgreSQL/CockroachDB","Ensure secondary tables always receive rows so optional semantics are unnecessary","Align test-database (H2/MySQL) with production dialect capabilities"],"tags":["hibernate","secondary-table","insert","dialect-support","entity-mapping"],"backgroundTag":"optional-secondary-table-insert","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}