{"record":{"id":"1bafb36f9e7fa0c3","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-1bafb3","errorCode":null,"errorMessage":"Insert conflict 'do update' clause with constraint name is not supported","messagePattern":"Insert conflict 'do update' clause with constraint name is not supported","errorType":"exception","errorClass":"IllegalQueryOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseASELegacySqlAstTranslator.java","lineNumber":123,"sourceCode":"\t}\n\n\t@Override\n\tprotected void renderFromClauseAfterUpdateSet(UpdateStatement statement) {\n\t\tif ( statement.getFromClause().getRoots().isEmpty() ) {\n\t\t\tappendSql( \" from \" );\n\t\t\trenderDmlTargetTableExpression( statement.getTargetTable() );\n\t\t\trenderTableReferenceIdentificationVariable( statement.getTargetTable() );\n\t\t}\n\t\telse {\n\t\t\tvisitFromClause( statement.getFromClause() );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void visitConflictClause(ConflictClause conflictClause) {\n\t\tif ( conflictClause != null ) {\n\t\t\tif ( conflictClause.isDoUpdate() && conflictClause.getConstraintName() != null ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Insert conflict 'do update' clause with constraint name is not supported\" );\n\t\t\t}\n\t\t}\n\t}\n\n\t// Sybase ASE does not allow CASE expressions where all result arms contain plain parameters.\n\t// At least one result arm must provide some type context for inference,\n\t// so we cast the first result arm if we encounter this condition\n\n\t@Override\n\tprotected void visitAnsiCaseSearchedExpression(\n\t\t\tCaseSearchedExpression caseSearchedExpression,\n\t\t\tConsumer<Expression> resultRenderer) {\n\t\tif ( getParameterRenderingMode() == SqlAstNodeRenderingMode.DEFAULT && areAllResultsParameters( caseSearchedExpression ) ) {\n\t\t\tfinal List<CaseSearchedExpression.WhenFragment> whenFragments = caseSearchedExpression.getWhenFragments();\n\t\t\tfinal Expression firstResult = whenFragments.get( 0 ).getResult();\n\t\t\tsuper.visitAnsiCaseSearchedExpression(\n\t\t\t\t\tcaseSearchedExpression,\n\t\t\t\t\te -> {","sourceCodeStart":105,"sourceCodeEnd":141,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseASELegacySqlAstTranslator.java#L105-L141","documentation":"SybaseASELegacySqlAstTranslator.visitConflictClause() throws IllegalQueryOperationException when an HQL insert statement carries an ON CONFLICT 'do update' clause that names a constraint ('on conflict for constraint <name> do update'). Sybase ASE has no native upsert, and while the translator can pass through a plain key-based conflict clause, the constraint-name variant is explicitly rejected during query translation. The check only fires when isDoUpdate() and getConstraintName() are both set - 'do nothing' with a constraint name and 'do update' keyed by columns are not rejected here.","triggerScenarios":"Building a MutationQuery with HQL like 'insert into Person(id, name) values(:id,:name) on conflict for constraint pk_person do update set name = excluded.name' against a Sybase ASE (legacy dialect) connection; Jakarta Persistence 3.2 style upsert statements reused across databases.","commonSituations":"An upsert statement written and tested on PostgreSQL (where naming the constraint is common) executed against Sybase ASE in another environment; shared repository code covering multiple databases; migration of batch import jobs to ASE.","solutions":["Drop the 'for constraint <name>' phrase and key the conflict by columns: 'on conflict (id) do update set ...'","Use 'on conflict do nothing' (optionally with the constraint name) if skipping duplicates is acceptable","On ASE, implement the upsert manually: try an UPDATE, then INSERT the rows that updated zero rows, inside a transaction","Catch IllegalQueryOperationException at query creation and fall back to the manual update-then-insert path"],"exampleFix":"// before\ninsert into Person(id, name) values(:id, :name)\non conflict for constraint pk_person do update set name = excluded.name\n\n// after\ninsert into Person(id, name) values(:id, :name)\non conflict (id) do update set name = excluded.name","handlingStrategy":"try-catch","validationCode":"static String stripConflictConstraintName(String hql) {\n    return hql.replaceAll('for constraint \\\\w+', '');\n}\n// use stripConflictConstraintName(hql) before createMutationQuery on Sybase ASE","typeGuard":null,"tryCatchPattern":"try {\n    factory.createMutationQuery(upsertHql).execute();\n}\ncatch (IllegalQueryOperationException e) {\n    if (String.valueOf(e.getMessage()).contains('do update')) {\n        // fallback: manual upsert on ASE\n        int updated = em.createQuery('update Person p set p.name = :n where p.id = :id')\n            .setParameter('n', name).setParameter('id', id).executeUpdate();\n        if (updated == 0) {\n            em.persist(new Person(id, name));\n        }\n    } else {\n        throw e;\n    }\n}","preventionTips":["Key ON CONFLICT clauses by columns, never by constraint name, for portable HQL","Keep database-specific upsert statements in per-dialect repositories","Test upsert queries against every target database in CI"],"tags":["hibernate","sybase-ase","sql-translator","upsert","on-conflict","hql"],"backgroundTag":"upsert-on-conflict-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}