Misleading references

Reports ambiguous references in SQL code.

For example, when a name refer to both a table column and a routine parameter. The execution of such code might lead to errors or unexpected results due to counter-intuitive resolution logic. Usually, names with a more local scope have higher priority.

Example (PostgreSQL):

CREATE TABLE foo
(
    id   INT,
    name VARCHAR(5)
);
CREATE FUNCTION func(name VARCHAR(5)) RETURNS INT AS
$$
DECLARE
    b INT;
BEGIN
      -- `name` is ambiguous as it is used as a column name and a parameter
    SELECT COUNT(*) INTO b FROM foo t WHERE t.name = name;
    RETURN b;
END;
$$ LANGUAGE plpgsql;

In PostgreSQL, you can use the #variable_conflict directives to explicitly specify a correct reference. For example, use #variable_conflict use_column to refer to a column name, or #variable_conflict use_variable to refer to a parameter.

CREATE TABLE foo
(
    id   INT,
    name VARCHAR(5)
);
CREATE FUNCTION func(name VARCHAR(5)) RETURNS INT AS
$$
    #variable_conflict use_column
DECLARE
    b INT;
BEGIN
    SELECT COUNT(*) INTO b FROM foo t WHERE t.name = name;
    RETURN b;
END;
$$ LANGUAGE plpgsql;

Inspection Details
Available in:	AppCode 2023.3, CLion 2023.3, DataGrip 2023.3, DataSpell 2023.3, GoLand 2023.3, IntelliJ IDEA 2023.3, JetBrains Rider 2023.1, PhpStorm 2023.3, PyCharm 2023.3, Qodana for .NET 2023.1, Qodana for JVM 2023.3, Qodana for PHP 2023.3, Qodana for Ruby 2023.3, RubyMine 2023.3
Plugin:	Database Tools and SQL, 233.SNAPSHOT

Last modified: 13 July 2023