hibernate/hibernate-orm · error · SemanticException

{unit} is not a legal field

Error message

{unit} is not a legal field

What it means

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.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacyDialect.java:683

				break;
			case DAY:
				if ( temporalType == TemporalType.DATE ) {
					pattern.append( "(?3+(?2))" );
					break;
				}
			case HOUR:
			case MINUTE:
			case SECOND:
				pattern.append( "(?3+numtodsinterval(?2,'?1'))" );
				break;
			case NANOSECOND:
				pattern.append( "(?3+numtodsinterval((?2)/1e9,'second'))" );
				break;
			case NATIVE:
				pattern.append( "(?3+numtodsinterval(?2,'second'))" );
				break;
			default:
				throw new SemanticException( unit + " is not a legal field" );
		}
		return pattern.toString();
	}

	@Override
	public String timestampdiffPattern(TemporalUnit unit, TemporalType fromTemporalType, TemporalType toTemporalType) {
		final StringBuilder pattern = new StringBuilder();
		final boolean hasTimePart = toTemporalType != TemporalType.DATE || fromTemporalType != TemporalType.DATE;
		switch ( unit ) {
			case YEAR:
				extractField( pattern, YEAR, unit );
				break;
			case QUARTER:
			case MONTH:
				pattern.append( "(" );
				extractField( pattern, YEAR, unit );
				pattern.append( "+" );
				extractField( pattern, MONTH, unit );

View on GitHub (pinned to fad1729dce)

Solutions

1. Rewrite the unit in supported terms: week -> '* 7 day', quarter -> '* 3 month'
2. Precompute the shifted datetime in Java and bind it as a query parameter
3. Switch to the non-legacy org.hibernate.dialect.OracleDialect and verify its unit coverage
4. Fall back to a native query with Oracle interval arithmetic

Example fix

-- before (HQL)
select timestampadd(week, :n, o.shipDate) from Order o

-- after (HQL)
select timestampadd(day, :n * 7, o.shipDate) from Order o

private static final Set<TemporalUnit> ORACLE_TIMESTAMPADD_UNITS = EnumSet.of(
        TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.DAY,
        TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,
        TemporalUnit.NANOSECOND, TemporalUnit.NATIVE );

if ( !ORACLE_TIMESTAMPADD_UNITS.contains( unit ) ) {
    throw new IllegalArgumentException(
        "Oracle timestampadd: convert " + unit + " first (e.g. week -> 7 day)" );
}

static boolean oracleTimestampAddSupports(TemporalUnit unit) {
    return EnumSet.of( TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.DAY,
                       TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,
                       TemporalUnit.NANOSECOND, TemporalUnit.NATIVE ).contains( unit );
}

try {
    return em.createQuery( hql ).getResultList();
}
catch ( SemanticException e ) {
    // translation-time failure: an unsupported temporal unit is baked into the HQL
    throw new IllegalArgumentException( "Rewrite week/quarter units as day/month", e );
}

When it happens

Trigger: 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.

Common situations: 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).

Related errors

• unrecognized field: {unit}
• unrecognized field: {unit}
• {unit} is not a legal field
• Insert conflict 'do update' clause with constraint name is n
• Unrecognized field:

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/50931b601d5e90dd. Report an issue: GitHub.