{"record":{"id":"f234aaf5b4ca2102","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-order-preserving-row-constructor-thr","errorCode":null,"errorMessage":"Can't emulate order preserving row constructor through string concatenation for numeric expression [%s] without precision or scale","messagePattern":"Can't emulate order preserving row constructor through string concatenation for numeric expression \\[(.+?)\\] without precision or scale","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":3318,"sourceCode":"\t * This is because the translation from the types to strings is not guaranteed to result in the same ordering.\n\t */\n\tprivate SqlAstNode wrapRowComponentAsOrderPreservingConcatArgument(Expression expression) {\n\t\tfinal JdbcMapping jdbcMapping = expression.getExpressionType().getSingleJdbcMapping();\n\t\treturn switch ( jdbcMapping.getCastType() ) {\n\t\t\tcase STRING -> expression;\n\t\t\tcase BOOLEAN, INTEGER_BOOLEAN, TF_BOOLEAN, YN_BOOLEAN -> castToString( expression );\n\t\t\tcase INTEGER, LONG -> castNumberToString( expression, 19, 0 );\n\t\t\tcase FIXED -> {\n\t\t\t\tif ( expression.getExpressionType() instanceof SqlTypedMapping sqlTypedMapping ) {\n\t\t\t\t\tif ( sqlTypedMapping.getPrecision() != null && sqlTypedMapping.getScale() != null ) {\n\t\t\t\t\t\tyield castNumberToString(\n\t\t\t\t\t\t\t\texpression,\n\t\t\t\t\t\t\t\tsqlTypedMapping.getPrecision(),\n\t\t\t\t\t\t\t\tsqlTypedMapping.getScale()\n\t\t\t\t\t\t);\n\t\t\t\t\t}\n\t\t\t\t}\n\t\t\t\tthrow new IllegalArgumentException(\n\t\t\t\t\t\tString.format(\n\t\t\t\t\t\t\t\t\"Can't emulate order preserving row constructor through string concatenation for numeric expression [%s] without precision or scale\",\n\t\t\t\t\t\t\t\texpression\n\t\t\t\t\t\t)\n\t\t\t\t);\n\t\t\t}\n\t\t\tdefault -> throw new IllegalArgumentException(\n\t\t\t\t\tString.format(\n\t\t\t\t\t\t\t\"Can't emulate order preserving row constructor through string concatenation for expression [%s] which is of type [%s]\",\n\t\t\t\t\t\t\texpression,\n\t\t\t\t\t\t\tjdbcMapping.getCastType()\n\t\t\t\t\t)\n\t\t\t);\n\t\t};\n\t}\n\n\tprivate int wrapRowComponentAsOrderPreservingConcatArgumentSizeEstimate(Expression expression) {\n\t\tfinal JdbcMapping jdbcMapping = expression.getExpressionType().getSingleJdbcMapping();","sourceCodeStart":3300,"sourceCodeEnd":3336,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L3300-L3336","documentation":"wrapRowComponentAsOrderPreservingConcatArgument converts each search/cycle key to a string whose lexicographic order matches the value's numeric order; for that it must left-pad fixed-point numbers to a known width, which requires precision and scale from a SqlTypedMapping. When the expression's cast type is FIXED (numeric/decimal) but its mapping exposes no precision or scale — typically a computed expression, function result, or untyped cast — Hibernate cannot build the padding and throws IllegalArgumentException.","triggerScenarios":"A recursive CTE SEARCH (or CYCLE) specification that searches by a decimal/numeric expression whose type carries no precision+scale — e.g. an arithmetic expression like (a+b), a function return like coalesce/avg, or an attribute mapped without @Column(precision/scale) — rendered through emulateSearchClauseOrderWithString's order-preserving concat on a dialect without row/array emulation.","commonSituations":"MySQL-family targets (string-concat path) where the search key is a computed BigDecimal; entities with numeric attributes missing precision/scale in the mapping (relying on database defaults Hibernate never sees); queries ported from PostgreSQL (row/array emulation path) that never exposed the missing metadata; dynamically built cast() expressions dropping the SqlTypedMapping.","solutions":["Declare precision and scale on the numeric mapping used in the search specification: @Column(precision = 19, scale = 2) — and regenerate/verify the schema matches.","Search by a plain, mapped numeric column (or its integral/string projection) instead of a computed expression like a+b or function results.","Cast the computed key to a concrete typed literal via a typed parameter or tuple-cast so the mapping retains precision/scale.","Run the query as native SQL, or switch to a dialect with row/array emulation where the padded-string encoding is unnecessary."],"exampleFix":"// before: BigDecimal attribute without precision/scale used as search key\n@Column(name = \"sort_key\")\nprivate BigDecimal sortKey;\n// ...\n// \"with recursive t as (...) search depth first by sortKey set ord\"\n\n// after: give the mapping precision and scale so the order-preserving\n// string encoding can left-pad correctly\n@Column(name = \"sort_key\", precision = 19, scale = 4)\nprivate BigDecimal sortKey;","handlingStrategy":"validation","validationCode":"// Before running the query: verify the search-by mapping can be order-preservingly encoded\nfor (SingularAttribute<?, ?> attr : searchByAttributes) {\n    if ( attr.getJavaType() == BigDecimal.class ) {\n        org.hibernate.mapping.Property p = sessionFactory.getMetamodel()\n            .entityPersister(attr.getDeclaringType().getJavaType()).getProperty(attr.getName());\n        // simpler: check the column directly\n    }\n}\n// Practical check on the JDBC metadata side:\nDatabaseMetaData md = connection.getMetaData();\n// ensure columns used in SEARCH BY have NUMERIC(p,s) not bare DECIMAL/DOUBLE without precision\nif ( !dialect.supportsRecursiveSearchClause() && usesDecimalSearchKey && !hasPrecisionAndScale ) {\n    throw new IllegalStateException(\"Decimal SEARCH BY key needs precision/scale or a different key\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, ResultDto.class).getResultList();\n} catch (IllegalArgumentException e) {\n    if ( e.getMessage() != null && e.getMessage().contains(\"without precision or scale\") ) {\n        // switch the search key to a plain mapped column or a cast with explicit type, or use native SQL\n        return session.createNativeQuery(nativeSql, ResultDto.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Always declare precision and scale on BigDecimal mappings used in recursive CTE SEARCH/CYCLE clauses.","Search by plain mapped columns, not computed expressions (a+b, function results) whose types lose precision/scale metadata.","Prefer integral or string keys for search ordering; they encode without precision metadata.","Verify the physical column type (NUMERIC(p,s)) matches the mapping, since Hibernate reads precision from the mapping/SqlTypedMapping."],"tags":["hibernate","recursive-cte","search-clause","numeric-precision","type-mapping"],"backgroundTag":"missing-numeric-precision","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}