SQL Prompt incorrectly expanding *

Matthew_Sontum

This is an edge case, but it is reproducable. In some code that I ran the SQL Prompt formatter on recently there was a common table expression that referenced a temporary table, and then another common table expression that referenced the first common table expression. An example would be:

WITH CTE1 AS (

SELECT *, a+b as ab FROM #TempTable), CTE2 AS (

SELECT *, c+d as cd FROM CTE1)

SELECT * FROM CTE2

SQL Prompt formats that as:

WITH CTE1 AS (SELECT *, a + b AS ab FROM #TempTable),

CTE2 AS (SELECT CTE1.ab, c + d AS cd FROM CTE1)

SELECT CTE2.ab, CTE2.cd FROM CTE2;

Which is a significant problem, since there are many columns in the original #TempTable that are now omitted from the final and intermediary SELECTs

Tianjiao_Li

Hi @Matthew_Sontum

I couldn't reproduce it with the default format style in 9.5.15. Please kindly try it again and share your format style file with us if problem persists.

Thanks.

Tianjiao_Li

I can see it now when I expand the wildcard. I've escalated it to the development team.

Tianjiao_Li

Hi @Matthew_Sontum

Thanks for your patience.

The development team has triaged this issue and recognized this is a bug. However, they are not able to prioritize it at the moment. This doesn't mean it will not be looked at in the future. Here is the reference number SP-7769 should you need an update again.

Our apologies for the inconvenience caused.

Matthew_Sontum

The reason I feel it deserves to be prioritized is that it causes existing working code not to work. As a first step in editing code (code I did not write myself) I run it through the SQL Prompt Formatter. I can no longer trust that the code will work after expanding wildcards, so I cannot use this feature at all until the issue is resolved.

And yes, this has already caused an issue. I commit the code in source control after running it through the formatter, so that formatting changes can be reviewed separately from functional changes in code. If the SQL Prompt formatter is making functional changes this methodology breaks down.

jds84

Any updates on this issue?

ssharif

Any updates on this? I am having the same issues

edwillis

The latest update and possibly prior one now if you highlight the #temp it used to give the table definition and no longer does it used to and any idea when this will be fixed still works fine with non-temp tables?
 select * into #temp from dbo.tablea 

Henning_Beywl_I

This bug has apparently been an issue since 2019 and it's still not even in consideration? I'm encountering it using the aforementioned CTE+temp, but also when I chain temporary tables like source -> temp1 -> temp2 -> target. in target, both temp1 and temp2 are missing columns in wildcard extension...

Victoria W

Hi all

Thank you for your patience with this issue.

The development team have reviewed the bug in a prioritization of open issues and unfortunately they are unable to fix it.

When reviewing bugs the development team take into account the impact it has and the number of users affected and unfortunately we are unable to fix all bugs and have to prioritize fixing the ones that cause the greatest impact.

I'm sorry that this isn't what you want to hear but thank you for your understanding.