5 Replies Latest reply on Apr 11, 2018 12:39 PM by Lukas Eder

    Parser reports errors and warnings when using legacy outer join in row value expressions

    Lukas Eder

      Consider this query using, admittedly, a bit edge case syntax:

       

      WITH

        a(x, y) AS (SELECT 1, 2 FROM dual),

        b(y, z) AS (SELECT 1, 2 FROM dual UNION ALL SELECT 2, 3 FROM dual),

        c(z)    AS (SELECT 1    FROM dual UNION ALL SELECT 2    FROM dual UNION ALL SELECT 3 FROM dual)

      SELECT

        a1.x, COUNT(c1.z)

      FROM a a1, b b1, c c1

      WHERE (a1.y, b1.z) = ((b1.y(+), c1.z(+)))

      GROUP BY a1.x

      ;

       

      The syntax is perfectly fine and the query runs as expected. But SQL Developer reports a couple of wrong warnings and errors

       

      sqldeveloper.PNG

       

      • Line 6, warning that the select list is inconsistent with the group by clause. Fixing the warning yields wrong SQL. The warning itself is wrong, too
      • Line 7, warning that the tables a, b, and c are unknown. This is obviously incorrect
      • Line 8, error that the syntax cannot be parsed, but it is perfectly fine.