search

felipebz / zpa

Parser and static code analysis tool for PL/SQL and Oracle SQL.
https://zpa.felipebz.com
GNU Lesser General Public License v3.0
211 stars 77 forks source link

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

Closed felipebz closed 1 year ago

felipebz commented 1 year ago

Originally from https://community.sonarsource.com/t/pl-sql-zpa-plugin-fails-during-scanning-code-with-sql-analytic-functions/85363

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?

felipebz commented 1 year ago

Hi,

Thank you for reporting this bug. I was able to reproduce the issue using the latest version of the plugin.

I've fixed the issue and the early-access release with the fix is available at https://github.com/felipebz/zpa/releases/tag/early-access

Another thing I noticed is that the stacktrace of the error you provided indicates that you're probably also using an older version of the plugin, because it shows:

at org.sonar.plsqlopen.checks.ToCharInOrderByCheck.visitNode(ToCharInOrderByCheck.kt:55)

I get this exact same error on ZPA 2.4.0, released 3 years ago. On newer versions, it shows:

at org.sonar.plsqlopen.checks.ToCharInOrderByCheck.visitNode(ToCharInOrderByCheck.kt:54)

So I also recommend that you update to the latest version to ensure that you have access to the latest features and bug fixes.

Again, thanks for your help in identifying this issue. Let me know if you have any more questions or feedback!