SA 12.1.279: Order by Formatting inconsistency

SA 12.1.279: Order by Formatting inconsistency

If I have two order by criteria and one is ASC and one is DESC, the clauses don't line up when hitting F11. If the clause is the same direction, they do.

video example:

https://www.screencast.com/t/5uhDMW1g4

I'm unable so far to reproduce the same result. Do you see this happening for all queries?

	SysOp wrote:
	I'm unable so far to reproduce the same result. Do you see this happening for all queries?

I will play more with it in the morning, but I don't think I've seen this occur before so I sort of figured it was a regression.

Ok, it seems table (and possibly column aliases) with the 'AS' keyword seems to screw it up no matter what (there's no alignment happening)

Then if you REMOVE the 'AS' keyword in aliases, the mixed desc/asc bug as mentioned above seems to occur.

It also seems that no alignment is done regardless of AS or no AS if there is not a join involved but once you add the join the formatting goes whacky.

With AS:

Without AS but mixed order:

Without AS but same order:

In the case of the above examples this is on SQL Assistant on my laptop at home, which does NOT have any formatting customization other than leading commas. No new keywords or rules. But the alignment behavior is consistent with the customized formatting I have at work as far as the bug is concerned.

Here is what I get using factory default settings

Just in case, I get identical result in the SQL Editor

Okay so:

a) what happens when you remove the 'as' keyword for table aliases?
b) why don't the orders align with the default settings or maybe the better question is why isn't the behavior of the alignment consistent?

Here is my result for the same statement without AS keywords. The table aliases don't get aligned just as in your case. The formatting engine isn't related to SQL Intellisense, it operates on text patterns around anchored keywords. Without AS, it doesn't really know where aliases are, it aligns text prefixed by AS

I'm not able to explain why it impacts the DESC/ASC, apparently without AS keywords it's finding a different set of keyword anchors and patterns to match and that makes DESC shift, but what exactly leads to that I can't tell. The pattern based formatting engine has some constrains. Of course there a room for improvements. I will log this as an issue that needs to be investigated.

Last edited by SysOp on Thu May 19, 2022 8:29 am; edited 1 time in total

I would suspect it's anchoring somewhere on the INNER JOIN or ON keywords, since the ASC/DESC alignment issue doesn't seem to manifest itself the same way with single table selects (no joins)

HTH narrow it down.