{"record":{"id":"892c180010597060","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-on-empty-clause-on-mariadb","errorCode":null,"errorMessage":"Can't emulate on empty clause on MariaDB","messagePattern":"Can't emulate on empty clause on MariaDB","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/json/MariaDBJsonValueFunction.java","lineNumber":43,"sourceCode":" */\npublic class MariaDBJsonValueFunction extends JsonValueFunction {\n\n\tpublic MariaDBJsonValueFunction(TypeConfiguration typeConfiguration) {\n\t\tsuper( typeConfiguration, true, false );\n\t}\n\n\t@Override\n\tprotected void render(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tJsonValueArguments arguments,\n\t\t\tReturnableType<?> returnType,\n\t\t\tSqlAstTranslator<?> walker) {\n\t\tif ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonValueErrorBehavior.NULL ) {\n\t\t\t// MariaDB reports the error 4038 as warning and simply returns null\n\t\t\tthrow new QueryException( \"Can't emulate on error clause on MariaDB\" );\n\t\t}\n\t\tif ( arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonValueEmptyBehavior.NULL ) {\n\t\t\tthrow new QueryException( \"Can't emulate on empty clause on MariaDB\" );\n\t\t}\n\t\t\tfinal JdbcType jdbcType = arguments.returningType() == null\n\t\t\t\t\t? null\n\t\t\t\t\t: arguments.returningType().getJdbcMapping().getJdbcType();\n\t\t\tif ( jdbcType != null ) {\n\t\t\t\tswitch ( jdbcType.getDefaultSqlTypeCode() ) {\n\t\t\t\t\tcase BOOLEAN:\n\t\t\t\t\t\tsqlAppender.append( \"case \" );\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase BINARY:\n\t\t\t\t\tcase VARBINARY:\n\t\t\t\t\tcase LONG32VARBINARY:\n\t\t\t\t\t\t// We encode binary data as hex, so we have to decode here\n\t\t\t\t\t\tsqlAppender.append( \"unhex(json_unquote(\" );\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase UUID:\n\t\t\t\t\t\tif ( jdbcType.isBinary() ) {\n\t\t\t\t\t\t\tsqlAppender.append( \"unhex(replace(json_unquote(\" );","sourceCodeStart":25,"sourceCodeEnd":61,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/MariaDBJsonValueFunction.java#L25-L61","documentation":"MariaDB returns null when a json_value path matches nothing, so only NULL ON EMPTY is emulatable. The MariaDB json_value renderer rejects every other ON EMPTY behavior with this QueryException during SQL generation.","triggerScenarios":"An HQL json_value call on MariaDB uses DEFAULT <expr> ON EMPTY or ERROR ON EMPTY. The check emptyBehavior() != null && != JsonValueEmptyBehavior.NULL fires.","commonSituations":"Queries written for Oracle or PostgreSQL that supply a DEFAULT value for empty results. Optional JSON fields where the path may be absent in some rows.","solutions":["Remove the ON EMPTY clause: json_value already returns null on empty on MariaDB.","Use NULL ON EMPTY explicitly when you want the intent visible.","Wrap the call in coalesce() to supply the default value.","Fall back to a native query when ERROR ON EMPTY semantics are required."],"exampleFix":"// before\nselect json_value(e.doc, '$.name' default 'n/a' on empty) from Entity e\n\n// after\nselect coalesce(json_value(e.doc, '$.name' null on empty), 'n/a') from Entity e","handlingStrategy":"fallback","validationCode":"boolean mariadb = session.getJdbcServices().getDialect() instanceof org.hibernate.dialect.MariaDBDialect;\nif (mariadb && emptyBehavior != null && emptyBehavior != NULL_ON_EMPTY) {\n    // Plan: remove the clause and supply the default with coalesce in the final query.\n    hql = hql.replace(\" default \" + defaultExpr + \" on empty\", \"\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, String.class).getSingleResult();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"on empty clause on MariaDB\")) {\n        // Fallback: NULL ON EMPTY plus coalesce for the default value.\n        return session.createQuery(fallbackHql, String.class).getSingleResult();\n    }\n    throw e;\n}","preventionTips":["Wrap json_value in coalesce() instead of DEFAULT ... ON EMPTY for portable queries.","Expect null for missing paths on MariaDB and handle it in code.","Keep optional clause usage per dialect documented in the team wiki."],"tags":["hibernate","mariadb","json","json-value","on-empty-clause","dialect-emulation"],"backgroundTag":"json-on-empty-clause-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}