Difference SQL Dataiku / external

vmar
vmar Dataiku DSS Core Designer, Registered Posts: 9 ✭✭✭

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 ?

Answers

  • dima_naboka
    dima_naboka Dataiker, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts Posts: 28 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.
  • vmar
    vmar Dataiku DSS Core Designer, Registered Posts: 9 ✭✭✭

    Thanks

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

Setup Info
    Tags
      Help me…