PL/SQL ZPA plugin fails during scanning code with SQL-analytic functions

Hi all,

SonarQube (PL/SQL ZPA plugin) fails during scanning code with SQL-analytic functions.

We are using SonarQube Community EditionVersion 9.9 (build 65466)

Query (successfully executed in DB Oracle 12c):
select /*+ parallel(4) */
client_id
,ntile(8) over (order by 0) as sqoop_chunk
,discount_name
,discount_percent
,discount_start_dt
from (
select t.client_id
,t.discount_name
,t.discount as discount_percent
,t.date_start as discount_start_dt
,row_number() over (partition by t.client_id, t.discount_name order by t.date_start desc) as rn
from REPORT.TARIFF_FEE_DISCOUNT_STATE t
where t.discount_name = ‘CMS Discount’
)
where rn = 1;

Fails with:
ERROR: Error during SonarQube Scanner execution
java.lang.ArrayIndexOutOfBoundsException: -1
at java.util.ArrayList.elementData(ArrayList.java:422)
at java.util.ArrayList.get(ArrayList.java:435)
at org.sonar.plsqlopen.checks.ToCharInOrderByCheck.visitNode(ToCharInOrderByCheck.kt:55)
at org.sonar.plsqlopen.squid.PlSqlAstWalker.visitNode(PlSqlAstWalker.kt:94)
at org.sonar.plsqlopen.squid.PlSqlAstWalker.visit(PlSqlAstWalker.kt:60)
at org.sonar.plsqlopen.squid.PlSqlAstWalker.visitChildren(PlSqlAstWalker.kt:74)

The problem is function “ntile(8) over (order by 0) as sqoop_chunk”
Also issues appear with another analytic function “lag() over(partition by XXX order by YYY))”

Have anyone faced with such issues, or has any ideas how ti fix it?

Hi,

Welcome to the community!

PL/SQL support starts in Developer Edition($). You’re using an unsupported plugin to analyze PL/SQL. You should talk to its maintainers.

 
Ann

Hi, I’m the maintainer of this plugin. I’ve just moved your question to PL/SQL ZPA plugin fails during scanning code with SQL-analytic functions · Issue #165 · felipebz/zpa · GitHub

1 Like

Hi Felipe,

I found another issue with analytic SQL-analytic functions.
It’s more tricky than previous one.

Please, find 3cattached files explaining bug, possibly relarted to lag function.

lag_bug.txt (5.4 KB)
lag_no_bug.txt (5.4 KB)
lag_no_bug_short.txt (522 Bytes)

lag_bug.txt - SonarQube goes to endless cycle with “INFO: 2/3 files analyzed, current file: lag_bug.sql” message repeated, until timeout. I use .sql extention, and renamed sources for be able to add it to this topic.

The problem was code section:
select
nvl(d_date, lag(d_date ignore nulls) over(partition by ack order by startdate)) last_ref_date,
nvl(bal_bef_first, lag(bal_bef_first ignore nulls) over(partition by ack order by startdate)) bef_ref_bal,
nvl(ref_amount, lag(ref_amount ignore nulls) over(partition by ack order by startdate)) last_ref_amounts,

lag_no_bug.txt - I removed that section, and no error was given.

But if I put problem code section into simple sql:
lag_no_bug_short.txt - no error was given.

Felipe, could you please, check is your latest plugin version works fine with lag function