{"record":{"id":"15ba2df1ee14dc44","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-json-arrayagg-filter-clause-when-usi","errorCode":null,"errorMessage":"Can't emulate json_arrayagg filter clause when using 'null on null' clause.","messagePattern":"Can't emulate json_arrayagg filter clause when using 'null on null' clause\\.","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonArrayAggFunction.java","lineNumber":60,"sourceCode":"\t\tfinal JsonNullBehavior nullBehavior;\n\t\tif ( sqlAstArguments.size() > 1 ) {\n\t\t\tnullBehavior = (JsonNullBehavior) sqlAstArguments.get( 1 );\n\t\t}\n\t\telse {\n\t\t\tnullBehavior = JsonNullBehavior.ABSENT;\n\t\t}\n\t\tfinal SqlAstNode firstArg = sqlAstArguments.get( 0 );\n\t\tfinal Expression arg;\n\t\tif ( firstArg instanceof Distinct distinctArg) {\n\t\t\tsqlAppender.appendSql( \"distinct \" );\n\t\t\targ = distinctArg.getExpression();\n\t\t}\n\t\telse {\n\t\t\targ = (Expression) firstArg;\n\t\t}\n\t\tif ( caseWrapper ) {\n\t\t\tif ( nullBehavior != JsonNullBehavior.ABSENT ) {\n\t\t\t\tthrow new QueryException( \"Can't emulate json_arrayagg filter clause when using 'null on null' clause.\" );\n\t\t\t}\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WHERE );\n\t\t\tsqlAppender.appendSql( \"case when \" );\n\t\t\tfilter.accept( translator );\n\t\t\ttranslator.getCurrentClauseStack().pop();\n\t\t\tsqlAppender.appendSql( \" then \" );\n\t\t\trenderArgument( sqlAppender, arg, nullBehavior, translator );\n\t\t\tsqlAppender.appendSql( \" else null end)\" );\n\t\t}\n\t\telse {\n\t\t\trenderArgument( sqlAppender, arg, nullBehavior, translator );\n\t\t}\n\t\tif ( withinGroup != null && !withinGroup.isEmpty() ) {\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );\n\t\t\tsqlAppender.appendSql( \" order by \" );\n\t\t\twithinGroup.get( 0 ).accept( translator );\n\t\t\tfor ( int i = 1; i < withinGroup.size(); i++ ) {\n\t\t\t\tsqlAppender.appendSql( ',' );","sourceCodeStart":42,"sourceCodeEnd":78,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonArrayAggFunction.java#L42-L78","documentation":"SingleStore has no native json_arrayagg, so the dialect emulates a FILTER (WHERE ...) clause by wrapping the argument in CASE WHEN <filter> THEN arg ELSE NULL END. That wrapper can only reproduce ABSENT ON NULL semantics; with NULL ON NULL the filtered-out rows would have to contribute JSON nulls, which the CASE trick cannot express, so render() throws QueryException.","triggerScenarios":"HQL `json_arrayagg(e.tag null on null) filter (where e.active = true)` (or Criteria filter plus JsonNullBehavior.NULL) on SingleStoreDialect. filter with the default ABSENT ON NULL is fine, and `null on null` without filter is fine.","commonSituations":"Porting SQL/JSON queries from Oracle/PostgreSQL/MySQL where json_arrayagg with null on null plus filter works; generic aggregation code that always spells out null behavior.","solutions":["Drop `null on null` and rely on the default ABSENT ON NULL when using filter","Remove the filter clause and pre-filter rows with a WHERE clause, then use null on null","Aggregate the JSON array in Java instead","Fall back to a native SingleStore query with group_concat emulation"],"exampleFix":"// before\nselect json_arrayagg(e.tag null on null) filter (where e.active = true) from E e\n\n// after: pre-filter with WHERE, keep null on null\nselect json_arrayagg(e.tag null on null) from E e where e.active = true","handlingStrategy":"validation","validationCode":"// On SingleStore, filter() may combine only with the default ABSENT ON NULL\nstatic boolean arrayAggEmulatable(boolean filter, boolean nullOnNull, Dialect d) {\n    return !(d instanceof SingleStoreDialect) || !(filter && nullOnNull);\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).getResultList(); // json_arrayagg ... null on null filter ...\n} catch (QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"json_arrayagg filter\")) {\n        // drop 'null on null' or move the predicate into WHERE and retry\n    }\n    throw e;\n}","preventionTips":["On SingleStore, pre-filter rows with WHERE instead of filter() when nulls must be kept","Treat json_arrayagg null-behavior clauses as dialect-specific and centralize them","Test JSON aggregation combos (filter x null behavior) per backend"],"tags":["hibernate","singlestore","json-arrayagg","filter-clause","null-behavior"],"backgroundTag":"json-aggregate-emulation-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}