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

SonarQube Plugin Development
1

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?

2

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

3

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
4

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