{"record":{"id":"50931b601d5e90dd","repo":"hibernate/hibernate-orm","slug":"unit-is-not-a-legal-field","errorCode":null,"errorMessage":"{unit} is not a legal field","messagePattern":"(.+?) is not a legal field","errorType":"exception","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacyDialect.java","lineNumber":683,"sourceCode":"\t\t\t\tbreak;\n\t\t\tcase DAY:\n\t\t\t\tif ( temporalType == TemporalType.DATE ) {\n\t\t\t\t\tpattern.append( \"(?3+(?2))\" );\n\t\t\t\t\tbreak;\n\t\t\t\t}\n\t\t\tcase HOUR:\n\t\t\tcase MINUTE:\n\t\t\tcase SECOND:\n\t\t\t\tpattern.append( \"(?3+numtodsinterval(?2,'?1'))\" );\n\t\t\t\tbreak;\n\t\t\tcase NANOSECOND:\n\t\t\t\tpattern.append( \"(?3+numtodsinterval((?2)/1e9,'second'))\" );\n\t\t\t\tbreak;\n\t\t\tcase NATIVE:\n\t\t\t\tpattern.append( \"(?3+numtodsinterval(?2,'second'))\" );\n\t\t\t\tbreak;\n\t\t\tdefault:\n\t\t\t\tthrow new SemanticException( unit + \" is not a legal field\" );\n\t\t}\n\t\treturn pattern.toString();\n\t}\n\n\t@Override\n\tpublic String timestampdiffPattern(TemporalUnit unit, TemporalType fromTemporalType, TemporalType toTemporalType) {\n\t\tfinal StringBuilder pattern = new StringBuilder();\n\t\tfinal boolean hasTimePart = toTemporalType != TemporalType.DATE || fromTemporalType != TemporalType.DATE;\n\t\tswitch ( unit ) {\n\t\t\tcase YEAR:\n\t\t\t\textractField( pattern, YEAR, unit );\n\t\t\t\tbreak;\n\t\t\tcase QUARTER:\n\t\t\tcase MONTH:\n\t\t\t\tpattern.append( \"(\" );\n\t\t\t\textractField( pattern, YEAR, unit );\n\t\t\t\tpattern.append( \"+\" );\n\t\t\t\textractField( pattern, MONTH, unit );","sourceCodeStart":665,"sourceCodeEnd":701,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacyDialect.java#L665-L701","documentation":"OracleLegacyDialect.timestampaddPattern() translates HQL datetime arithmetic (timestampadd() and 'date + n unit') into Oracle numtoyminterval/numtodsinterval expressions. It has patterns only for year, month, day, hour, minute, second, nanosecond and NATIVE; any other TemporalUnit (WEEK, QUARTER, DAY_OF_WEEK, EPOCH) reaches the default branch and fails query translation with a SemanticException before any SQL is generated.","triggerScenarios":"HQL such as 'select timestampadd(week, 2, o.shipDate) from Order o' or 'o.shipDate + 1 week', or Criteria temporal additions built with an unsupported TemporalUnit, executed on OracleLegacyDialect.","commonSituations":"Report or dashboard queries ported from PostgreSQL/H2 where week or quarter arithmetic worked; dynamically assembled interval units from a user-selected granularity dropdown (daily/weekly/monthly toggles).","solutions":["Rewrite the unit in supported terms: week -> '* 7 day', quarter -> '* 3 month'","Precompute the shifted datetime in Java and bind it as a query parameter","Switch to the non-legacy org.hibernate.dialect.OracleDialect and verify its unit coverage","Fall back to a native query with Oracle interval arithmetic"],"exampleFix":"-- before (HQL)\nselect timestampadd(week, :n, o.shipDate) from Order o\n\n-- after (HQL)\nselect timestampadd(day, :n * 7, o.shipDate) from Order o","handlingStrategy":"validation","validationCode":"private static final Set<TemporalUnit> ORACLE_TIMESTAMPADD_UNITS = EnumSet.of(\n        TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.DAY,\n        TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,\n        TemporalUnit.NANOSECOND, TemporalUnit.NATIVE );\n\nif ( !ORACLE_TIMESTAMPADD_UNITS.contains( unit ) ) {\n    throw new IllegalArgumentException(\n        \"Oracle timestampadd: convert \" + unit + \" first (e.g. week -> 7 day)\" );\n}","typeGuard":"static boolean oracleTimestampAddSupports(TemporalUnit unit) {\n    return EnumSet.of( TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.DAY,\n                       TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,\n                       TemporalUnit.NANOSECOND, TemporalUnit.NATIVE ).contains( unit );\n}","tryCatchPattern":"try {\n    return em.createQuery( hql ).getResultList();\n}\ncatch ( SemanticException e ) {\n    // translation-time failure: an unsupported temporal unit is baked into the HQL\n    throw new IllegalArgumentException( \"Rewrite week/quarter units as day/month\", e );\n}","preventionTips":["Whitelist TemporalUnit values before composing dynamic HQL date arithmetic on Oracle","Express weeks as 7 days and quarters as 3 months","Bind precomputed dates as parameters instead of asking the dialect to do interval math"],"tags":["oracle","hql","temporal-unit","query-translation"],"backgroundTag":"unsupported-temporal-unit","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}