plsql:S3651 False positive for left JOINs

Clean Code Report False-positive / False-negative...
1

When a table is LEFT JOINed, the fact that a joined column is NOT NULL is not relevant. When the LEFT JOIN finds no rows, the result is still NULL.

SELECT CASE WHEN other_table.not_null_column IS NOT NULL THEN 1 END
  FROM some_table
  LEFT JOIN other_table ON (other_table.id = some_table.id);

Based on the definition of “NOT_NULL_COLUMN”, this condition is always “TRUE”.
Individual “WHERE” clause conditions should not be unconditionally true or false plsql:S3651

I guess the same false positive would happen with WHERE not_null_column IS NULL.

  • Rule: plsql:S3651
  • Language: Oracle PL/SQL
  • SonarQube 9.9.1
4

Hello @Peter0,

Thank you for reporting this issue.
I confirm that this is a false positive.

I have added a ticket in our back to tackle it in the future.

I am guessing here you are referring to the CASE WHEN condition.
Please correct me if I am wrong.

Have a nice day!