DapperLib / DapperLib/DapperAOT

SqlServer SQL Analyser false positives in update from into statement

Open
#136 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
472
Forks
43
Avg merge
1d 4h
Merged PRs (30d)
18

Description

Describe the bug

False positives of the T-Sql Analyser in a update ... output into ... from statement, see below.

Where are you seeing this?

  • what Dapper/Dapper.StrongName version?
    • <PackageReference Include="Dapper" Version="2.1.35" />
  • what Dapper.AOT/Dapper.Advisor version?
    • <PackageReference Include="Dapper.AOT" Version="1.0.31" />
  • if relevant: what database backend?
    • Microsoft Sql Server, <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.2" />

To Reproduce

SqlConnection connection = .....
await connection.ExecuteAsync(@"
declare @t1 table(id int, val int)
declare @t2 table(id int, val int)
declare @t3 table(resultid int)
insert into @t1(id,val) values(1,1)
insert into @t2(id,val) values(1,20)

update t set
	val = 2
output inserted.id into @t3(resultid)
from @t1 t
join @t2 t2 on t.id = t2.id
where t2.val > 10
");

Expected behavior
I expected no warnings, but got three, which at least in my opinion could all be considered false positives:

  1. update t set produced warning DAP225 FROM expressions with multiple elements should use aliases

I didn't get this one in my production code, only in this minimal example, but I did update a regular table not a table variable, maybe that is the difference.

  1. val = 2 produced warning DAP226 FROM expressions with multiple elements should qualify all columns; it is unclear where 'val' is located

This warning could possibly be considered valid, but I don't think it makes sense, because the update t set already specifies, that only columns of t can be updated...

  1. into @t3(resultid) produced warning DAP226 FROM expressions with multiple elements should qualify all columns; it is unclear where 'resultid' is located

This does not make sense, as resultid is not a column of any of the tables in the from clause.

Screenshots
grafik

Additional context
I just wanted to mention that I really like the analysers made available by Dapper.AOT, only very few false positives when I switched to AOT and great to find errors in SQL Queries!

Contributor guide

No contributing guide indexed for this repository

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

Start with the Dapper.AOT T-SQL analyser and reproduce the update ... output into ... from example using the package versions listed. Check why DAP225 and DAP226 are emitted for the update target, assigned value, and output table; done means the valid statement produces no false-positive warnings while other qualification warnings remain covered.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
tooling
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
40/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.