{"record":{"id":"ed18cf250a3df032","repo":"hibernate/hibernate-orm","slug":"field-type-not-supported-on-derby-unit-ed18cf","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/DerbyLegacyDialect.java","lineNumber":436,"sourceCode":"\tpublic String extractPattern(TemporalUnit unit) {\n\t\treturn switch (unit) {\n\t\t\tcase DAY_OF_MONTH -> \"day(?2)\";\n\t\t\tcase DAY_OF_YEAR -> \"({fn timestampdiff(sql_tsi_day,date(char(year(?2),4)||'-01-01'),?2)}+1)\";\n\t\t\t// Use the approach as outlined here: https://stackoverflow.com/questions/36357013/day-of-week-from-seconds-since-epoch\n\t\t\tcase DAY_OF_WEEK -> \"(mod(mod({fn timestampdiff(sql_tsi_day,{d '1970-01-01'},?2)}+4,7)+7,7)+1)\";\n\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// In SQL Server terms this is (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'17530101',@SomeDate)/7*7,'17530104'))+6)/7\n\t\t\tcase 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)\";\n\t\t\tcase QUARTER -> \"((month(?2)+2)/3)\";\n\t\t\tcase EPOCH -> \"{fn timestampdiff(sql_tsi_second,{ts '1970-01-01 00:00:00'},?2)}\";\n\t\t\tdefault -> \"?1(?2)\";\n\t\t};\n\t}\n\n\t@Override\n\tpublic String translateExtractField(TemporalUnit unit) {\n\t\treturn switch (unit) {\n\t\t\tcase WEEK, DAY_OF_YEAR, DAY_OF_WEEK -> throw new UnsupportedOperationException(\"field type not supported on Derby: \" + unit);\n\t\t\tcase DAY_OF_MONTH -> \"day\";\n\t\t\tdefault -> 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)\";\n\t\t\tcase DOUBLE:\n\t\t\t\treturn \"double(?1)\";","sourceCodeStart":418,"sourceCodeEnd":454,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacyDialect.java#L418-L454","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Move week / day-of-year / day-of-week computation into Java (WeekFields.ISO on the fetched date)","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","Replace the expression with a Derby-native calculation via native query or a registered user-defined function","Exclude Derby from tests that exercise these fields"],"exampleFix":"// before (HQL, throws on Derby legacy dialect)\nselect count(o) from Order o group by extract(week from o.orderDate)\n\n// after (compute week in Java, group by raw date or precomputed column)\n// add @Formula or mapped column weekOfYear maintained on save, then:\nselect count(o) from Order o group by o.weekOfYear","handlingStrategy":"validation","validationCode":"boolean isDerbyLegacy = sessionFactory.getJdbcServices().getDialect() instanceof DerbyLegacyDialect;\nif (isDerbyLegacy && (unit == TemporalUnit.WEEK || unit == TemporalUnit.DAY_OF_YEAR || unit == TemporalUnit.DAY_OF_WEEK)) {\n    // route to Java-side computation or native query\n}","typeGuard":"static boolean extractSupported(Dialect d, TemporalUnit unit) {\n    if (d instanceof DerbyLegacyDialect) {\n        return !(unit == TemporalUnit.WEEK || unit == TemporalUnit.DAY_OF_YEAR || unit == TemporalUnit.DAY_OF_WEEK);\n    }\n    return true;\n}","tryCatchPattern":"try {\n    q = session.createQuery(hql); // hql uses extract(day_of_week from ...)\n} catch (UnsupportedOperationException e) {\n    // compute day-of-week in Java: date.getDayOfWeek()\n}","preventionTips":["Treat WEEK/DAY_OF_WEEK/DAY_OF_YEAR as non-portable across dialects until proven otherwise","Maintain precomputed calendar columns (week_of_year, day_of_week) for reporting tables","Pin the Derby dialect version in tests so capability changes surface at build time"],"tags":["hibernate","derby","legacy-dialect","hql","extract","temporal"],"backgroundTag":"extract-field-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}