microsoft / microsoft/DacFx

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

Open
#389 5 comments 7 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- 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
```

![image](https://github.com/microsoft/DacFx/assets/13050317/f9edd600-f6e4-49df-bff9-ad468f2f09b6)

`#` 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)

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the issue with the SQL script in the report and verify whether SQL71501 appears for dbo.another_table but disappears for #another_table and @another_table. Investigate the DacFx/SqlPackage analysis path responsible for unresolved column references; done means the warning is preserved when the referenced table uses either prefix.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.