on Oracle datasource wrong query to select failed rows: LIMIT keyword doesn't exist

[stelapo]

Hi,
I re-open the issue #2060 because the problem is still present.

I tried with the last version 3.3.9.

Soda builds the query to select failed rows samples using LIMIT keyword, but it doesn't exist in Oracle.

Query error: oracle_beneficiario_with_connstring_dev.MYTABLE.duplicate_count[MYCOLUMN].failed_rows.aggregated: ORA-00933: SQL command not properly ended

WITH frequencies AS (
    SELECT MYCOLUMN, COUNT(*) AS frequency
    FROM MYTABLE
    WHERE MYCOLUMN IS NOT NULL
    GROUP BY MYCOLUMN)
SELECT *
FROM frequencies
WHERE frequency > 1
ORDER BY frequency DESC
LIMIT 10

The correct query should use the ROWNUM keyword:

WITH frequencies AS (
    SELECT MYCOLUMN, COUNT(*) AS frequency
    FROM MYTABLE
    WHERE MYCOLUMN IS NOT NULL
    GROUP BY MYCOLUMN)
SELECT *
FROM frequencies
WHERE frequency > 1
  AND ROWNUM <= 10
ORDER BY frequency DESC

Thank you

[tools-soda]

CLOUD-8027

[benjamin-pirotte]

@m1n0 didn't we fix this recently? Or did we only fix it in Soda Library and not Soda Core?

[m1n0]

@stelapo could you please share the full log output showing the version? This is where that query is constructed and it uses the right keyword https://github.com/sodadata/soda-core/blob/main/soda/oracle/soda/data_sources/oracle_data_source.py#L350

[stelapo]

Hi @benjamin-pirotte and @m1n0 ,
thank you for your reply.

I tried again today, but now it works. I honestly don't understand why; I had checked the installed library version using pip show.

Anyway, better that way! :)

I am closing this thread.

Finally, I would like to point out this other thread #2125; I have also checked this problem again and confirm that it exists.

Thank you very much

[stelapo]

I understand why :) .... now there's the new version 3.3.10; when I tried there was the version 3.3.9.

Thank you