Community Conundrum 25: Feature Visualization is now live! Read More

Difference SQL Dataiku / external

Level 1
Difference SQL Dataiku / external

Hello,

I'm trying to understand a difference in behavior on Dataiku and my external tool Dbeaver. I'm runing the same queries on the same database (PostGreSQL), one on Dataiku SQL recipe the other on Dbeaver.

Here is the first request:

select extract(day from date1-date2) as delai

On Dbeaver this syntax is not accepted it says : ERROR: function pg_catalog.date_part(unknown, integer) does not exist.

On Dataiku, it works.

 

I tried this second request:

select  date1-date2 as delai

and further in the script try to compare delai with an integer

On Dbeaver, it works and makes the comparison.

On Dataiku, the logger returns: ERROR: operator does not exist: interval <= integer Hint: No operator matches the given name and argument types. 

 

Why such different behavior since I'm working on the same database ?

0 Kudos
2 Replies
Dataiker
Dataiker
Hello,
 
I was unable to reproduce this behavior. For me, DBevaer and DSS behavior was identical. I’m using PostgreSQL 12, DBeaver 7.2.0 with postgresql-42.2.5.jar:
Screenshot 2020-09-07 at 16.06.37.png
and DSS 7.0.3 (on MacOS) with default postgresql-9.1-901.jdbc4.jar (/Applications/DataScienceStudio.app/Contents/Resources/kit/lib/ivy/backend-run/).
 
According to PostgreSQL 12 documentation "The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.)”
 
Apparently, your “date1” and “date2” columns can be timestamps but not dates because result of subtracting date from date is integer according to the doc. You can use dates if you do EXTRACT(DAY from submit_date) though.
 
Please double-check your settings and columns types and if you still experience the problem provide more details to reproduce the issue. 
 
P.s. Generally speaking, both DSS and DBeaver rely solely on JDBC driver to communicate with target DB. So, as long as identical JDBC drivers are used you shouldn't experience any differences in the output.
0 Kudos
Level 1
Author

Thanks

By "check your settings", what settings do you mean ? For column types they are indeed the same.

0 Kudos