SQL Prompt changes alias in order by clause

GregDodd

I'm not sure if this is the right place to report a bug, but I'm seeing SQL Prompt (version 9.4.14.8687) change the table alias in an order by clause (doesn't affect the column list in the select, or join parameters, or column list in group by clause, only the order by).

A trivial example, say I type in the following query:

<div>SELECT		*</div><div>FROM		Product</div><div>INNER JOIN	Product AS p2</div><div>		ON	Product.ProductItemID = p2.ProductItemID</div><div>WHERE		1 = 2</div><div>ORDER BY	product.ProductItemID</div><div>		,p2.ProductItemID</div><div></div>

When I format the code, SQL Prompt changes the product.ProductItemID in the Order By Clause to p2.ProductItemID:

SELECT		*
FROM		Product
INNER JOIN	Product AS p2
		ON	Product.ProductItemID = p2.ProductItemID
WHERE		1 = 2
ORDER BY	p2.ProductItemID
		,p2.ProductItemID

Additional note - if I put an alias on the first table (which is probably a good idea for readability), it works fine. It's only if I alias one table but not the other.

Russell D

I can't get that to parse as valid sql (in ssms, not Prompt), so can't reproduce that example. That said, this looks like SP-7229 which has been closed as won't fix because the workaround is to alias both.

Ah it was you that reported this a while ago. I'll update that thread as well.

GregDodd

I thought I'd logged it before but I couldn't find it. I still don't like the work around - I don't need an alias for the column list in the select, or the join conditions, or the group by, but I need to alias both for the order by.

Russell D

Aye, I'm afraid we see this as a perfectly valid workaround though so won't be prioritising any fix.