hibernate/hibernate-orm · error · UnsupportedOperationException

field type not supported on Derby: " + unit

Error message

field type not supported on Derby: " + unit

What it means

DerbyDialect.translateExtractField maps HQL extract() fields to Derby SQL. Derby's JDBC {fn extract} escape has no representation for ISO week (WEEK), day-of-year (DAY_OF_YEAR) or day-of-week (DAY_OF_WEEK), so the dialect throws UnsupportedOperationException whenever the SQL AST for a query contains one of those three fields.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyDialect.java:443

				// 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
				return "(({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:
				return "((month(?2)+2)/3)";
			case EPOCH:
				return "{fn timestampdiff(sql_tsi_second,{ts '1970-01-01 00:00:00'},?2)}";
			default:
				return "?1(?2)";
		}
	}

	@Override
	public String translateExtractField(TemporalUnit unit) {
		switch (unit) {
			case WEEK:
			case DAY_OF_YEAR:
			case DAY_OF_WEEK:
				throw new UnsupportedOperationException("field type not supported on Derby: " + unit);
			case DAY_OF_MONTH:
				return "day";
			default:
				return 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)";

View on GitHub (pinned to fad1729dce)

Solutions

  1. Compute the value in Java after fetching the raw date, instead of extracting it in the query
  2. Replace the field with a supported combination, e.g. compute week number from day-of-year arithmetic in Java, or use {fn timestampdiff} via a native query
  3. Register a user-defined Derby function (CREATE FUNCTION ... PARAMETER STYLE JAVA) that returns ISO week / day-of-week and call it through HQL function('my_week', d)
  4. If Derby is only the test database, exclude these queries from the Derby profile or run the suite on a database that supports the fields

Example fix

// before (HQL, throws on Derby)
select extract(week from o.orderDate) from Order o

// after (fetch raw date, compute week in Java)
List<LocalDate> dates = session.createQuery("select o.orderDate from Order o", LocalDate.class).list();
int week = dates.get(0).get(WeekFields.ISO.weekOfWeekBasedYear());
Defensive patterns

Strategy: validation

Validate before calling

static final Set<TemporalUnit> UNSUPPORTED_ON_DERBY =
    EnumSet.of(TemporalUnit.WEEK, TemporalUnit.DAY_OF_YEAR, TemporalUnit.DAY_OF_WEEK);

boolean isDerby = sessionFactory.getJdbcServices().getDialect() instanceof DerbyDialect;
if (isDerby && UNSUPPORTED_ON_DERBY.contains(unit)) {
    // compute the field in Java instead of extract() in HQL
}

Type guard

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

Try / catch

try {
    result = session.createQuery("select extract(week from o.date) from Order o", Integer.class).list();
} catch (UnsupportedOperationException e) {
    // fall back: fetch dates, compute ISO week in Java
    log.warn("extract field unsupported on Derby; computing in Java", e);
}

Prevention

When it happens

Trigger: HQL 'extract(week from d)', 'extract(day_of_year from d)', 'extract(day_of_week from d)' (also the legacy 'd.week' / 'd.dayOfWeek' field syntax, and date_trunc with those units) executed against a Derby database. YEAR/MONTH/DAY and all timestampdiff-based units work; only these three fields throw.

Common situations: JPQL/HQL written and tested on H2/PostgreSQL that runs in a Derby-based CI matrix; report queries using extract(week from ...) for weekly grouping; upgrading Hibernate to a version where unsupported Derby fields throw instead of silently producing wrong SQL.

Related errors

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