G-3120 Problems with query with EXTRACT function

[mmarquezvacas]

Hi, I'm using Trivadis plugin for SQL Developer.
The correct code for guideline G-3120 is:

SELECT emp.last_name
,emp.first_name
,dept.department_name
FROM
employees
emp
JOIN departments dept ON (emp.department_id = dept.department_id)
WHERE EXTRACT(MONTH FROM emp.hire_date) = EXTRACT(MONTH FROM SYSDATE);

but if I add the EXTRACT function, G-3120 appears, but I think this is not a rule violation.

SELECT emp.last_name
,emp.first_name
,dept.department_name
, EXTRACT(MONTH FROM SYSDATE) as datesample
FROM
employees
emp
JOIN departments dept ON (emp.department_id = dept.department_id)
WHERE EXTRACT(MONTH FROM emp.hire_date) = EXTRACT(MONTH FROM SYSDATE);

¿What can I do to fix the code?

[mmarquezvacas]

The same happens with TRUNC(); SUM() functions that are inside the SELECT statement

[PhilippSalvisberg]

Thanks @mmarquezvacas for reporting this issue.

I've opened an issue in our internal ticket system for PL/SQL. In this GitHub repository we handle just the guidelines. And this is not a guideline issue. Hence, I'm going to close this issue.

The issue is related to SYSDATE and not the EXTRACT, TRUNC or SUM function. SYSDATE is treated as a column. In the following example the line with current_month reports a G-3120 violation, but there is no violation for the line with hire_date_month.

SELECT emp.last_name
      ,emp.first_name
      ,dept.department_name
      ,EXTRACT(MONTH FROM emp.hire_date) AS hire_date_month
      ,EXTRACT(MONTH FROM SYSDATE) AS current_month
  FROM      employees   emp
       JOIN departments dept ON (emp.department_id = dept.department_id)
 WHERE EXTRACT(MONTH FROM emp.hire_date) = EXTRACT(MONTH FROM SYSDATE);

To fix the issue in the validator, we have to identify some pseudo columns and standalone functions. Since PL/SQL Cop relies on parsing only without semantic analysis (no database connection is required for the static code analysis) we cannot avoid false positives for user-defined functions or global variables. However, we can deal with common functions defined in the STANDARD package.

To answer your question. There is nothing you need to fix your code. If you want to suppress the false positive you have two options:

a) disable the check of the rule 3120 in the preferences dialog

b) disable the check for the line with the false positive

SELECT emp.last_name
      ,emp.first_name
      ,dept.department_name
      ,EXTRACT(MONTH FROM emp.hire_date) AS hire_date_month
      ,EXTRACT(MONTH FROM SYSDATE) AS current_month -- NOSONAR: G-3120 false positive
  FROM      employees   emp
       JOIN departments dept ON (emp.department_id = dept.department_id)
 WHERE EXTRACT(MONTH FROM emp.hire_date) = EXTRACT(MONTH FROM SYSDATE);

[mmarquezvacas]

@PhilippSalvisberg As always thanks a lot for your detailed help. I will communicate this to my team. Good to know we can also skip the false positive using --NOSONAR.
Thanks again for your great support.