DacFx does not indicate existing problem in query if # or @ involved in statement

[IVNSTN]

• SqlPackage or DacFx Version:
  • SSDT - 16.0.62205.05200
  • SqlPackage - 162.0.52.1
• .NET Framework (Windows-only) or .NET Core:
• Environment (local platform and source/target platforms):
  • Windows 10
  • VS 2019

SQL71501 does not work if a statement includes temp table or table variable reference.

Steps to Reproduce:

1. Use script below to create new sqlproj
2. See warning SQL71501 unresolved reference to column on this SELECT t.missing_col in SP which is expected
3. Replace dbo.another_table in the select statement with #another_table or @another_table
4. The warning disappears
5. Change it back to dbo.another_table - the warning is back

It looks like a bug or feature limitation. But the statement is so trivial and column is qualified with table alias. It'd be great if the missing column reference warning worked even if # or @ involved at least in cases like described one where column belonging seems to be identifiable.

CREATE TABLE dbo.my_table (id INT);
GO
CREATE TABLE dbo.another_table (id INT);
GO
CREATE PROC dbo.my_proc
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #another_table (id INT);

    DECLARE @another_table TABLE (id INT);

    SELECT t.missing_col
    FROM dbo.my_table t
    INNER JOIN dbo.another_table a
    ON a.id = t.id
END;
GO

# and @ are underlined on screenshot because they are unused. These hints are not related to the issue.

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[namangupta211]

Hi, thanks for reaching out to us. One question, are you also not able to deploy the project, or it is just the warning?

[IVNSTN]

Sorry for misunderstanding

• When there are only "normal" tables involved in the broken query, DacFx shows SQL71501 error because t.missing_col really does not exist, build fails, I'm not able to deploy the project - this is good and expected behavior
• When there is a temp table # or table variable @ used in the same broken query, DacFx does not detect SQL71501, no error or warning shown, build succeeds, I'm able to deploy the project with this broken proc and the proc fails at runtime only - tihs is bad and unexpected behavior

Why doesn't DacFx detect missing column reference if temp table or table variable used in query? I think it should detect this broken reference.