You signed in with another tab or window.
Reload
to refresh your session.
You signed out in another tab or window.
Reload
to refresh your session.
You switched accounts on another tab or window.
Reload
to refresh your session.
By clicking “Sign up for GitHub”, you agree to our
terms of service
and
privacy statement
. We’ll occasionally send you account related emails.
Already on GitHub?
Sign in
to your account
Your browser and the version: Firefox 67.0
Your operating system: Windows 10
Your databases: Oracle Database 12c Enterprise Edition Release 12.1.0.2 - 64bit
Metabase version: 0.32.4
Metabase hosting environment: Windows Server 2012 R2
Metabase internal database: H2
When we use Query Builder and then filter the results using a date column for previous 6 months the query will fail with the following Oracle specific error:
java.sql.SQLDataException: ORA-01839: date not valid for month specified
I believe this is caused with how Metabase generates Oracle queries, I checked the debug log and saw that in order to select data for the previous months Metabase uses the numtoyminterval() function and then applies it to the sysdate.
So a query date filter is generated like this:
trunc((SYSDATE + numtoyminterval(-6, 'month')), 'month') , this would generate the date 31-NOV-2018 (since today's date is 31-MAY) which in fact doesn't exist. Maybe when doing month based filtering using the add_months() function would be safer?
Regards and thanks for the product!
⬇️ Please click the 👍 reaction instead of leaving a
+1
or
update?
comment
Hello again,
it seems this bug does not have enough priority for the developers.
I just wanted to say that we hit it today as well, so the Query Builder is generating 30.02.2020 when we filter with Previous Month.
Regards,
Sokol
"language": "fr-FR",
"platform": "Linux x86_64",
"userAgent": "Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:88.0) Gecko/20100101 Firefox/88.0",
"vendor": ""
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_292-b10",
"java.vendor": "Red Hat, Inc.",
"java.vendor.url": "https://www.redhat.com/",
"java.version": "1.8.0_292",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.292-b10",
"os.name": "Linux",
"os.version": "3.10.0-1160.25.1.el7.x86_64",
"user.language": "fr",
"user.timezone": "Europe/Paris"
"metabase-info": {
"databases": [
"oracle"
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MariaDB",
"version": "10.5.10-MariaDB"
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
"run-mode": "prod",
"version": {
"tag": "v0.39.3",
"date": "2021-05-27",
"branch": "release-x.39.x",
"hash": "598a112"
"settings": {
"report-timezone": "Europe/Paris"
I use well ojdbc8.jar
@Philippe-M
I know you're using ojdbc - otherwise Oracle wouldn't function. I'm asking for which version you're using.
https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
@Philippe-M
Try 19.3 or newer:
https://www.metabase.com/docs/latest/administration-guide/databases/oracle.html
Metabase cannot work for every version of every database forever. Oracle 9 was
EOL in 2007/2010
. If a newer ojdbc8.jar does not work, then it will not be supported, and you would have to create your own custom driver to support legacy databases.
@flamber
I've the same error with the 19.3 or 21.1 version.
I tested with the same filter but for a day and a week and in this case I have no error. But as soon as I chose this error for a month.
I have to use Oracle 9 again because of an old ERP which will evolve next year. But in the meantime we are looking for a BI solution.
With sqplus this requete it's ok
SELECT *
FROM (
SELECT "SDELIVERY"."SDHNUM_0" AS "SDHNUM_0", "SDELIVERY"."CREDAT_0" AS "CREDAT_0"
FROM "SDELIVERY"
WHERE (
trunc("SDELIVERY"."CREDAT_0", 'month') = trunc(SYSDATE, 'month') AND
trunc("SDELIVERY"."CREDAT_0", 'year') = trunc(SYSDATE, 'year')
SELECT *
FROM (SELECT "SDELIVERY"."SDHNUM_0" AS "SDHNUM_0", "SDELIVERY"."CREDAT_0" AS "CREDAT_0" FROM "SDELIVERY"
WHERE ("SDELIVERY"."CREDAT_0" >= trunc(SYSDATE, 'month')
AND "SDELIVERY"."CREDAT_0" < trunc((CAST(SYSDATE AS timestamp) + numtoyminterval(1, 'month')), 'month'))) WHERE rownum <= 1048575
generates the error 01839
It seems like the issue is when adding a month results in a day that doesn't exist, e.g. March 31st plus 3 months = June 31st, which doesn't exist. Only :oracle seems like it has a problem with this...
I'm not 100% sure what the right thing to do here is. Does March 31st plus 3 months = June 30th? Or July 1st? Who knows. Either way now that we've identified the problem we can fix
* Handle March 31st + 3 months for Oracle (#10072)
* Optimize out some casting in Oracle
* rx util support varargs inside `opt`
* hx/ math operators like + and - should propagate type information
* Some dox tweaks
* Fix SQLite busted behavior
* Avoid unneeded casting in Vertica when adding temporal intervals
* Lint error fixes
* BigQuery fix for #21969
* Add testing context for tests for #21968 and #21971
* Handle March 31st + 3 months for Oracle (#10072)
* Optimize out some casting in Oracle
* rx util support varargs inside `opt`
* hx/ math operators like + and - should propagate type information
* Some dox tweaks
* Fix SQLite busted behavior
* Avoid unneeded casting in Vertica when adding temporal intervals
* Lint error fixes
* BigQuery fix for #21969
* Add testing context for tests for #21968 and #21971
* Handle March 31st + 3 months for Oracle (#10072)
* Optimize out some casting in Oracle
* rx util support varargs inside `opt`
* hx/ math operators like + and - should propagate type information
* Some dox tweaks
* Fix SQLite busted behavior
* Avoid unneeded casting in Vertica when adding temporal intervals
* Lint error fixes
* BigQuery fix for #21969
* Add testing context for tests for #21968 and #21971
Co-authored-by: Cam Saul <[email protected]>
