How can we help you today? How can we help you today?

SQL Prompt incorrectly expanding *

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
Matthew_Sontum
0

Comments

9 comments

Sort by
  • 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
    0
  • Tianjiao_Li
    I can see it now when I expand the wildcard. I've escalated it to the development team.
    Tianjiao_Li
    0
  • 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.
    Tianjiao_Li
    0
  • 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.
    Matthew_Sontum
    0
  • jds84
    Any updates on this issue?
    jds84
    0
  • ssharif
    Any updates on this? I am having the same issues
    ssharif
    0
  • 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
    edwillis
    0
  • 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...
    Henning_Beywl_I
    0
  • 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.

    Victoria W
    0

Add comment

Please sign in to leave a comment.