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.

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.

Appendix: 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)