{"record":{"id":"83411c06fc0026a5","repo":"hibernate/hibernate-orm","slug":"field-type-not-supported-on-derby-unit","errorCode":null,"errorMessage":"field type not supported on Derby: \" + unit","messagePattern":"field type not supported on Derby: \" \\+ unit","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyDialect.java","lineNumber":443,"sourceCode":"\t\t\t\t// Use the approach as outlined here: https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number\n\t\t\t\t// In SQL Server terms this is (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'17530101',@SomeDate)/7*7,'17530104'))+6)/7\n\t\t\t\treturn \"(({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)\";\n\t\t\tcase QUARTER:\n\t\t\t\treturn \"((month(?2)+2)/3)\";\n\t\t\tcase EPOCH:\n\t\t\t\treturn \"{fn timestampdiff(sql_tsi_second,{ts '1970-01-01 00:00:00'},?2)}\";\n\t\t\tdefault:\n\t\t\t\treturn \"?1(?2)\";\n\t\t}\n\t}\n\n\t@Override\n\tpublic String translateExtractField(TemporalUnit unit) {\n\t\tswitch (unit) {\n\t\t\tcase WEEK:\n\t\t\tcase DAY_OF_YEAR:\n\t\t\tcase DAY_OF_WEEK:\n\t\t\t\tthrow new UnsupportedOperationException(\"field type not supported on Derby: \" + unit);\n\t\t\tcase DAY_OF_MONTH:\n\t\t\t\treturn \"day\";\n\t\t\tdefault:\n\t\t\t\treturn super.translateExtractField(unit);\n\t\t}\n\t}\n\n\t/**\n\t * Derby does have a real {@link Types#BOOLEAN}\n\t * type, but it doesn't know how to cast to it. Worse,\n\t * Derby makes us use the {@code double()} function to\n\t * cast things to its floating point types.\n\t */\n\t@Override\n\tpublic String castPattern(CastType from, CastType to) {\n\t\tswitch ( to ) {\n\t\t\tcase FLOAT:\n\t\t\t\treturn \"cast(double(?1) as real)\";","sourceCodeStart":425,"sourceCodeEnd":461,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyDialect.java#L425-L461","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Compute the value in Java after fetching the raw date, instead of extracting it in the query","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","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)","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"],"exampleFix":"// before (HQL, throws on Derby)\nselect extract(week from o.orderDate) from Order o\n\n// after (fetch raw date, compute week in Java)\nList<LocalDate> dates = session.createQuery(\"select o.orderDate from Order o\", LocalDate.class).list();\nint week = dates.get(0).get(WeekFields.ISO.weekOfWeekBasedYear());","handlingStrategy":"validation","validationCode":"static final Set<TemporalUnit> UNSUPPORTED_ON_DERBY =\n    EnumSet.of(TemporalUnit.WEEK, TemporalUnit.DAY_OF_YEAR, TemporalUnit.DAY_OF_WEEK);\n\nboolean isDerby = sessionFactory.getJdbcServices().getDialect() instanceof DerbyDialect;\nif (isDerby && UNSUPPORTED_ON_DERBY.contains(unit)) {\n    // compute the field in Java instead of extract() in HQL\n}","typeGuard":"static boolean extractFieldSupported(Dialect d, TemporalUnit unit) {\n    if (d instanceof DerbyDialect) {\n        return !(unit == TemporalUnit.WEEK || unit == TemporalUnit.DAY_OF_YEAR || unit == TemporalUnit.DAY_OF_WEEK);\n    }\n    return true;\n}","tryCatchPattern":"try {\n    result = session.createQuery(\"select extract(week from o.date) from Order o\", Integer.class).list();\n} catch (UnsupportedOperationException e) {\n    // fall back: fetch dates, compute ISO week in Java\n    log.warn(\"extract field unsupported on Derby; computing in Java\", e);\n}","preventionTips":["Lint shared HQL/JPQL for extract(week|day_of_year|day_of_week ...) when Derby is in the test matrix","Prefer computing calendar fields in Java (WeekFields.ISO) for portable code","Cover each dialect in CI with the queries production uses, not just H2"],"tags":["hibernate","derby","hql","extract","temporal","date-functions"],"backgroundTag":"extract-field-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}