Autocomplete does not correctly prefix columns

HugoKornelis

See the code below for an absolutely useless query that I created just to repro in a simple AdventureWorks scenario:

UPDATE Person.Person<br>SET BusinessEntityID = (SELECT COUNT(*) FROM Person.Person AS p WHERE p.FirstName = FirstName);

When I type the FirstName column name I get two options, from the Person table aliased as p and from the non-aliased Person table; that is fine. When I select the first option I get p.FirstName, also correct.
But when I select the second option, autocomplete does not provide an alias. The code above is ambiguous and SQL Server can decide which of the two tables "FirstName" applies to. It favors the inner scope so this would effectively equate to "p.Firstname" - which is not the one I selected. I consider this a major bug - lots of people are confused about now unqualified column names in subqueries are resolved and if SQL Prompt generates the code they probably think it's okay.

If I let SQL Prompt reformat this code, it will then even add in the explicit p. qualifier that I never wanted.



If I do recognnize the error and explicitly type the table name myself, SQL Prompt automatically adds the schema as well, so now my query looks like this:

UPDATE Person.Person<br>SET BusinessEntityID = (SELECT COUNT(*) FROM Person.Person AS p WHERE p.FirstName = Person.Person.FirstName);

Though this syntax is accepted by SQL Server, it is not correct according to the official syntax diagrams.
(And, funny fact, SQL Prompt itself then marks this code as violating rule DEP026 - which is correct but then why generate the code like this?

Reformatting this code will not change this.


Using: SQL Prompt 9.1.2.4363