hibernate/hibernate-orm · error · UnsupportedOperationException

field type not supported on Derby: " + unit

Error message

field type not supported on Derby: " + unit

What it means

DerbyLegacyDialect.translateExtractField maps HQL extract() fields onto Derby's JDBC escape syntax. Derby cannot express ISO week (WEEK), day-of-year (DAY_OF_YEAR) or day-of-week (DAY_OF_WEEK) as extract fields, so the legacy dialect throws UnsupportedOperationException for those units instead of emitting wrong SQL.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacyDialect.java:436

	public String extractPattern(TemporalUnit unit) {
		return switch (unit) {
			case DAY_OF_MONTH -> "day(?2)";
			case DAY_OF_YEAR -> "({fn timestampdiff(sql_tsi_day,date(char(year(?2),4)||'-01-01'),?2)}+1)";
			// Use the approach as outlined here: https://stackoverflow.com/questions/36357013/day-of-week-from-seconds-since-epoch
			case DAY_OF_WEEK -> "(mod(mod({fn timestampdiff(sql_tsi_day,{d '1970-01-01'},?2)}+4,7)+7,7)+1)";
			// Use the approach as outlined here: https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
			// In SQL Server terms this is (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'17530101',@SomeDate)/7*7,'17530104'))+6)/7
			case WEEK -> "(({fn timestampdiff(sql_tsi_day,date(char(year(?2),4)||'-01-01'),{fn timestampadd(sql_tsi_day,{fn timestampdiff(sql_tsi_day,{d '1753-01-01'},?2)}/7*7,{d '1753-01-04'})})}+7)/7)";
			case QUARTER -> "((month(?2)+2)/3)";
			case EPOCH -> "{fn timestampdiff(sql_tsi_second,{ts '1970-01-01 00:00:00'},?2)}";
			default -> "?1(?2)";
		};
	}

	@Override
	public String translateExtractField(TemporalUnit unit) {
		return switch (unit) {
			case WEEK, DAY_OF_YEAR, DAY_OF_WEEK -> throw new UnsupportedOperationException("field type not supported on Derby: " + unit);
			case DAY_OF_MONTH -> "day";
			default -> super.translateExtractField(unit);
		};
	}

	/**
	 * Derby does have a real {@link Types#BOOLEAN}
	 * type, but it doesn't know how to cast to it. Worse,
	 * Derby makes us use the {@code double()} function to
	 * cast things to its floating point types.
	 */
	@Override
	public String castPattern(CastType from, CastType to) {
		switch ( to ) {
			case FLOAT:
				return "cast(double(?1) as real)";
			case DOUBLE:
				return "double(?1)";

View on GitHub (pinned to fad1729dce)

Solutions

1. Move week / day-of-year / day-of-week computation into Java (WeekFields.ISO on the fetched date)
2. Switch to the non-legacy DerbyDialect if your Derby version allows - behavior is the same for these fields but the rest of the translator is current
3. Replace the expression with a Derby-native calculation via native query or a registered user-defined function
4. Exclude Derby from tests that exercise these fields

Example fix

// before (HQL, throws on Derby legacy dialect)
select count(o) from Order o group by extract(week from o.orderDate)

// after (compute week in Java, group by raw date or precomputed column)
// add @Formula or mapped column weekOfYear maintained on save, then:
select count(o) from Order o group by o.weekOfYear

boolean isDerbyLegacy = sessionFactory.getJdbcServices().getDialect() instanceof DerbyLegacyDialect;
if (isDerbyLegacy && (unit == TemporalUnit.WEEK || unit == TemporalUnit.DAY_OF_YEAR || unit == TemporalUnit.DAY_OF_WEEK)) {
    // route to Java-side computation or native query
}

static boolean extractSupported(Dialect d, TemporalUnit unit) {
    if (d instanceof DerbyLegacyDialect) {
        return !(unit == TemporalUnit.WEEK || unit == TemporalUnit.DAY_OF_YEAR || unit == TemporalUnit.DAY_OF_WEEK);
    }
    return true;
}

try {
    q = session.createQuery(hql); // hql uses extract(day_of_week from ...)
} catch (UnsupportedOperationException e) {
    // compute day-of-week in Java: date.getDayOfWeek()
}

When it happens

Trigger: HQL 'extract(week from d)', 'extract(day_of_year from d)', 'extract(day_of_week from d)' (or the legacy 'd.week' / 'd.dayOfYear' / 'd.dayOfWeek' syntax, and date_trunc with those units) against Derby while the legacy dialect is in use. Units like YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND and EPOCH render fine.

Common situations: Applications pinned to the DerbyLegacyDialect after a Hibernate 6/7 upgrade; JPQL written against richer databases being validated on a Derby compatibility profile; CI running Derby where weekly aggregation queries fail only on that node.

Related errors

• field type not supported on Derby: " + unit
• format() function not supported on Derby
• format() function not supported on Derby
• Can't render offset and fetch clause for subquery
• Unrecognized field:

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