Strange formatting behaviour when parentheses are involved

allmhuran

Consider the following screenshots, where I have underlined what I consider to be formatting errors. The first screenshot includes my query as originally written.

In the first screenshot, I do not have the collapse option ticked. The formatter makes some very strange choices with respect to indenting. It doubly indents the nested parenthesized expression, but then unindents the "and" which is part of that expression, and also unindents the "or" which is part of the outer expression. This is obviously not the desired result.

In the second screenshot the nested indenting is no longer a problem because the expressions are collapsed, but note that it has now make a mistake regarding alignment. Specifically the alingment of the cross joined subquery is no longer aligned to the one above it.

Also, for some reason it has added two tabs after "select b" and before the "=" in the second subquery, but it did not do that for the first subquery, even though the structures are otherwise the same.

In both cases, the chosen settings on the "data (DML)" settings page are identical, as per the third screenshot.

---

Screenshot 1: No collapse

Screenshot 2: With collapse

Screenshot 3: DML settings

Tianjiao_Li

Would you be able to share your format style file?

Thanks.

allmhuran

Sure, attached.

( Edit:I  can't see my attempted attachement anywhere, so here it is on pastebin https://pastebin.com/P5Ds6A11 )

I think some of the problems with trying to set up formatting probably come up because there are two different formatting "regimes" in a SQL statement.

The first is the formatting for SQL clauses themselves.. select, from, where, and so on, the names of the relations being queried, joins, etc.

But a second regime exists, which is a formatting regime for expressions. That might mean case statements, but it might also mean logical constructs used in where clauses, or inside the join, or function calls for column value expressions, and things like that.

While SQL prompt does recognize case statements as an independently format-able construct in their own right, it doesn't seem to make that more general distinction between scalar-value-returning-expressions vs SQL clauses. For instance, the construct inside a where clause (or a join) is actually a scalar expression returning a boolean, and should fall under an "expression formatting regime" as opposed to a "clause formatting regime". You can think of the where clause as creating a sort of attachment point where an expression is about to go, and I should be able to set the "cursor start position" for that expression via SQL prompt, after which expression formatting should take over and act as if that chosen position was the left edge of the screen, so to speak.

Tianjiao_Li

Thanks. I've reproduced the issues and escalated to the development team. I'll keep you posted.

Tianjiao_Li

Hi @allmhuran

Thanks for your patience.

Sadly, it's not something the development team will look into in the near future. They'll try to review formatting and the UI, but it won't be this year unfortunately.

Our apologies for the inconvenience caused.