Duplicate count check: on Oracle datasource wrong query to select failed rows

[stelapo]

``Hello, I found a bug relative to selection of failed rows when executing checks on Oracle datasource.

Soda builds the query 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

[tools-soda]

SAS-3269

[dirkgroenen]

Hi @stelapo! Thanks for letting us know. We're happy to accept any contribution to the project, so if you're already aware of the solution, feel free to open up a PR and we'll help you through getting it merged.

If not we'll of course try to fix it at some point. :)

[m1n0]

Fixed in #2101

[stelapo]

Hi, this issue has been closed, but 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.

Thank you