SQL statement parsing broken when using procedures from package

kczx3 commented 2 weeks ago

Description

I'm not entirely sure how to describe this but will do my best. I was working on an adhoc SQL query today and needed to use the SUBSTR function from the DBMS_LOB package within an Oracle Database. Upon using this in the JOIN clause of the table in question, the parsing of the SQL statement in the editor seems broken. It results in an error stating that one of my columns does not exist. Commenting out the line with the function call from DBMS_LOB shows the query gets reparsed and everything is hunky dory then.

SELECT
    s.MODULE_NAME,
    to_char(trunc(a.begin_dt_tm), 'MM-DD-YYYY') AS "Date",
    count(*) AS "Count per day"
from
    eks_module em
join eks_modulestorage s on
    s.module_name = em.module_name
    and s.version = em.version
    -- s.EKM_INFO is a CLOB type
    AND dbms_lob.SUBSTR(s.EKM_INFO, instr(s.EKM_INFO, chr(4)) - 1, 1) = 'PATIENT_EVENT'
    and s.data_type = 7
JOIN eks_module_audit a ON
    a.module_name = em.MODULE_NAME
    AND a.begin_dt_tm BETWEEN SYSDATE - 5 AND SYSDATE
    AND a.end_dt_tm BETWEEN sysdate - 5 AND SYSDATE
where    
    em.active_flag = 'A'
    and em.maint_validation = 'PRODUCTION'
GROUP BY s.MODULE_NAME, trunc(a.begin_dt_tm)
order by s.module_name;

Screenshot with usage of DBMS_LOB.SUBSTR

Screenshot with DBMS_LOB.SUBSTR commented out

DBeaver Version

Community Edition 24.2.3.202410201725

Operating System

Windows 11 10.0.22631

Database and driver

Oracle Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.23.0.0.0

Driver: Oracle JDBC driver 23.2.0.0.0

Steps to reproduce

May be tricky to reproduce without the schema.

Additional context

No response

ShadelessFox commented 2 weeks ago

Hello @kczx3,

In the first screenshot, where does the a (supposedly an alias) come from? In the second screenshot, it seems that SYSDATE is not recognized as a function.

kczx3 commented 2 weeks ago

In the first screenshot, the a alias is for the EKS_MODULE_AUDIT table on line 14. In the second screenshot, I saw that but felt it was a different issue. However, since you've asked, SYSDATE is definitely a function in Oracle SQL. It does appear that adding parenthesis to the end of the keyword makes the error disappear. The Oracle docs do not ever show SYSDATE like its being called like a function. Perhaps that is just a limitation of DBeaver?

ShadelessFox commented 2 weeks ago

Thanks for the clarification. Regarding SYSDATE, it's a bug in DBeaver's query analizer.

dbeaver / dbeaver