[12.0.181 Pro] - Formatting of subqueries

[12.0.181 Pro] - Formatting of subqueries

I have the following rule for formatting SELECT statements:

	Code:
	SELECT ... AS ... ,... = ... FROM ... AS ... ,... JOIN ... ON ... = ... AND ... OR ... WHERE ... = ... AND ... OR ... GROUP BY ... ,... HAVING ... ,... ORDER BY ... ,... LIMIT ... INTO ... ,...;

and a SELECT statement with a subquery that (based on the rule above) I'd expect to be formatted like this:

Code:

SELECT sq.`whatever` AS `name1`
,sq.`ssp_name` AS `name2`
FROM (SELECT 'whatever' AS `whatever`
,ssp.`name` AS `ssp_name`
FROM `hkir_bd`.`call` AS c
INNER JOIN `hkir_bd`.`scheduled_stop_point` AS ssp ON ssp.`tid_scheduled_stop_point` = c.`tid_scheduled_stop_point`
INNER JOIN `hkir_bd`.`service_journey` AS sj ON sj.`tid_service_journey` = c.`tid_service_journey`
INNER JOIN `hkir_bd`.`line` AS l ON l.`tid_line` = sj.`tid_line`
WHERE 1 = 1
AND c.`tid_service_journey` = `lv_tid_service_journey`
) AS sq
WHERE 1 = 1
AND sq.`ssp_name` = `a_ssp_name`;

However, it gets formatted to this instead:

Code:

SELECT sq.`whatever` AS `name1`
,sq.`ssp_name` AS `name2`
FROM (SELECT 'whatever' AS `whatever`
,ssp.`name` AS `ssp_name`
FROM `hkir_bd`.`call` AS c
INNER JOIN `hkir_bd`.`scheduled_stop_point` AS ssp ON ssp.`tid_scheduled_stop_point`= c.`tid_scheduled_stop_point`
INNER JOIN `hkir_bd`.`service_journey` AS sj ON sj.`tid_service_journey`= c.`tid_service_journey`
INNER JOIN `hkir_bd`.`line` AS l ON l.`tid_line`= sj.`tid_line`
WHERE 1 = 1
AND c.`tid_service_journey` = `lv_tid_service_journey`
) AS sq
WHERE 1 = 1
AND sq.`ssp_name` = `a_ssp_name`;

Any hints on why? I recall earlier versions formatting subqueries properly.

Code:

SELECT sq.`whatever` AS `name1`
,sq.`ssp_name` AS `name2`
FROM (
SELECT 'whatever' AS `whatever`
,ssp.`NAME` AS `ssp_name`
FROM `hkir_bd`.`call` AS c
INNER JOIN `hkir_bd`.`scheduled_stop_point` AS ssp
ON ssp.`tid_scheduled_stop_point` = c.`tid_scheduled_stop_point`
INNER JOIN `hkir_bd`.`service_journey` AS sj
ON sj.`tid_service_journey` = c.`tid_service_journey`
INNER JOIN `hkir_bd`.`line` AS l
ON l.`tid_line` = sj.`tid_line`
WHERE 1 = 1
AND c.`tid_service_journey` = `lv_tid_service_journey`
) AS sq
WHERE 1 = 1
AND sq.`ssp_name` = `a_ssp_name`;

This is what I get when I format your query with my preferences.

It looks like the significant difference between your rules and factory default rules is one line JOIN which somehow leads to a collision with alias alignment and that appears to shift everything else

	Code:
	JOIN ... ON ... = ... AND ... OR ...

Strangely, it happens even if I disable all the alignments (datatypes, assignments, aliases). I could format them in multiple steps selecting parts of the query moving from the outside to inside but that is not perfect because when formatting a selection, SA always assumes the selection starts at the beginning of the line and when that assumption is false, it offsets the alignment of the first alias. Not a big deal but it looks ugly and "fixing" it takes time and effort and it does so at the expense of efficiency. Check this short video here.

Side note: I abandoned the concept of keeping the joined table and the join conditions on separate lines. It is useful when writing/analyzing the code but at the end of the day, it only wastes space. If/When I really need to see them in an aligned list to debug the join details I apply another formatting rule to expand those parts of the query. I have those compress/expand custom formatting rulesets for all the dialects I use. After I'm done with it I reformat to the more compact version which I use as default.