Be the star of a SQL Prompt case study. Learn more.

Breaking bug in SQL Prompt

<p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">      </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">*<br><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">FROM</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">        dbo</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">.</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">Catalog<br><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">ORDER</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">BY</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">    </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">CASE<br><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">                </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">WHEN</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">Type</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">=</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> 4<br><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">                    </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">THEN</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">COALESCE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">((</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">   Catalog2</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">.</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">Name<br></span></span></span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">                                   </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">FROM</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">     dbo</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">.</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">Catalog</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">AS</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> Catalog2<br><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">                                   </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">WHERE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">    Catalog2</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">.</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">ItemID </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">=</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">Catalog</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">.</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">LinkSourceID</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">),<br></span></span></span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">                                  </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">'Z'</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)<br><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">                </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">ELSE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">Name<br></span></span></span></span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">            </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">END</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></p>


Hi!

I just ran into a dangerous bug in SQL Prompt, where it changes my code instead of merely reformatting it. I am running 9.5.12.10845, which I think is the latest build. The repro code below runs on the "ReportServer" database that is standard installed for every SQL instance that has SSRS configured.

First, copy/paste the code above (the repro is simplified as much as possible, the original query is more useful). The idea is to ensure that linked reports are sorted immediately after the report they link to, even if they have a different name. Linked reports that, somehow, got orphaned are sorted last. (And yes, I know that reports with a name starting with Z would break this; not relevant for this issue).

Next, hit Ctrl-Y - Ctrl-Z to activate the magic of SQL Prompt. It will (in my case) not change the formatting since the snippet above already uses my preferred formatting guidelines. But ... it DOES change the WHERE clause in the subquery. After reformatting, it reads WHERE Catelog2.Item = Catalog2.LinkSourceID. Instead of a correlated subquery, it is now a normal subquery. And it returns nothing, since LinkSourceID can never be equal to ItemID.

End result: All linked reports sort last, as if they are all orphaned (which they are not).

--
Hugo Kornelis
(SQL Server MVP, 2006-2016 // Friend of Red Gate)

Comments

  • Alex BAlex B Posts: 1,052 Diamond 4
    Hi @HugoKornelis,

    It's not breaking for me.  When I format using default options and default formatting style with nothing else in a new query window, it doesn't change the "catalog.LinkSourceID" portion.

    Do you have anything else in the query window?  I can also reach out via a support ticket to get the style and settings you are using to see if there's something in there that is causing the issue.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Alex BAlex B Posts: 1,052 Diamond 4
    Oh and also what SSMS and SQL Server version is this with?
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.