[11.5.355 Pro] - Formatting issues (UPDATE on MariaDB)

[11.5.355 Pro] - Formatting issues (UPDATE on MariaDB)

I noticed on a MariaDB server that there's a serious discrepancy in how the formatting for the SELECT and UPDATE rules happen. The results of the formatting differ significantly for those statements. That's sort of expected, as they are two separate types of statements with different formatting rules, however, it's worrying that difference is at a part of the rules that matches in both rules.

This is the formatting rule for SELECT

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

and this one is for the UPDATE:

	Code:
	UPDATE ... AS ... ,... JOIN ... ON ... = ... AND ... OR ... SET ... = ... ,... WHERE ... = ... AND ... OR ... ORDER BY ... ,... LIMIT ...;

The first rule formats a SELECT statement to look like this:

	Code:
	SELECT t1.* ,t2.* ,t3.* FROM `table_1` AS t1 INNER JOIN `table_2` AS t2 ON t2.`table_1_id` = tsrw.`table_1_id` LEFT OUTER JOIN `table_3` AS t3 ON t3.`table_2_id` = t2.`table_2_id` WHERE 1 = 1 AND t3.`table_3_id` IS NULL;

That's fine.

Now, based on that and the rule for UPDATE, I'd expect the UPDATE statement to become like this:

	Code:
	UPDATE `table_1` AS t1 INNER JOIN `table_2` AS t2 ON t2.`table_1_id` = tsrw.`table_1_id` LEFT OUTER JOIN `table_3` AS t3 ON t3.`table_2_id` = t2.`table_2_id` SET t2.`arbitrary_1` = 1 ,t2.`arbitrary_2` = 2 WHERE 1 = 1 AND t3.`table_3_id` IS NULL;

But it doesn't. It gets completely wrecked:

	Code:
	UPDATE `table_1` AS t1 INNER JOIN `table_2` AS t2 ON t2.`table_1_id` = tsrw.`table_1_id` LEFT OUTER JOIN `table_3` AS t3 ON t3.`table_2_id` = t2.`table_2_id` SET t2.`arbitrary_1` = 1 ,t2.`arbitrary_2` = 2 WHERE 1 = 1 AND t3.`table_3_id` IS NULL;

The joins are marred and even the parts of them that end up on the correct line (2nd line INNER keyword and 4th line LEFT OUTER keywords) are at the wrong column.

Any hints on why the application of that formatting rule goes awry so spectacularly?

Thank you. I think this part ",... JOIN" is where it stops working, because a pattern like ",..." expects repeating elements of the same type, like columns or conditions, and as a result, the following JOIN gets mistreated. You may also have a separate LEFT OUTER JOIN formatting pattern that possibly adds to that too.

I'm going to experiment with it to see if it's possible to achieve what you want.

The rule does not contain LEFT OUTER JOIN formatting pattern, only the generic JOIN pattern found everywhere. I guess that ",..." is a remnant fossil from when people didn't use JOINs but specified the tables separated by commas and then handled everything in the WHERE section. I never used that and it has been ages last time I saw it. The rule for SELECT does not hiccup even though it has that same ",..." pattern in its FROM part. But it does not really matter, even after tearing every occurrence of that ",..." away, the remaining rule

	Code:
	UPDATE ... AS ... JOIN ... ON ... = ... AND ... OR ... SET ... = ... WHERE ... = ... AND ... OR ... ORDER BY ... LIMIT ...;

still ruins the code. Besides, those ",..." chunks in other places (SET, ORDER BY) are legit.

I'm still struggling with this one. My ultimate goal would be to format the UPDATE statements so that they look like this:

	Code:
	UPDATE `schema_1`.`table_1` AS t1 INNER JOIN ``temp_t` AS tt ON tt.`tid_table_1` = t1.`tid_table_1` INNER JOIN `schema_2`.`table_2` AS t2 ON t2.`tid_table_2` = m.`tid_temp_t` LEFT OUTER JOIN `schema_2`.`table_3` AS t3 ON t3.`tid_table_2` = t2.`tid_table_2` AND t3.`private_code` = 'whatever' SET t1.`herehere` = `lv_some_variable` WHERE t1.`has_some_condition` = 1 AND t2.`and_also_some_others` = 1;

But it eludes me completely. No matter what I do, having JOIN-s in the statement will utterly ruin the code after formatting. Even the factory default formatting rule turns the JOIN part into an amorphous splotch. Currently, I'm formatting those as SELECT and then trying to restore them to an UPDATE but it takes too much time and is quite error-prone. Any ideas on what else I could try?

EDIT: fixed a typo, missing ` from table_1

Last edited by gemisigo on Fri May 13, 2022 8:14 am; edited 1 time in total

My hypothesis there is a collision with the alias alignment feature, which wants aliases to be at the right end of their lines rather than taking place in a middle of the string. And because of that collision it would be difficult to achieve a predictable result in queries with aliases, the number of spaces inserted to align the aliases is calculated based on multiple factors. It might be easier to add a post formatting text processor rule with a regex replacing text like white space with line breaks followed by ON then followed by white space with a simple " ON
" which would make it look as you want. The text processor action can be added as a custom menu or toolbar icon.

To be clear, the alias alignment is a second-class citizen here. If it worked, it would be great. But there are more urgent issues than the aliases not being aligned. The JOINs get ruined. If I try to format the above code, this will happen:

	Code:
	UPDATE `schema_1`.`table_1 AS t1 INNER JOIN ``temp_t` AS tt ON tt.`tid_table_1` = t1.`tid_table_1` INNER JOIN `schema_2`.`table_2` AS t2 ON t2.`tid_table_2` = m.`tid_temp_t` LEFT OUTER JOIN `schema_2`.`table_3` AS t3 ON t3.`tid_table_2` = t2.`tid_table_2` AND t3.`private_code` = 'whatever' SET t1.`herehere` = `lv_some_variable` WHERE t1.`has_some_condition` = 1 AND t2.`and_also_some_others` = 1;

After playing with your example I modified the UPDATE formatting rule to what is a bit counter-intuitive to me yet returning the result you want

	Code:
	UPDATE ... AS ... INNER JOIN ... ON ... = ... AND ... OR ... LEFT JOIN ... ON ... = ... AND ... OR ... SET ... = ... WHERE ... = ... AND ... OR ... ORDER BY ... LIMIT ...;

I tried it with a few different variations of UPDATE statements with different joins and it worked fine. Whether it would work in most cases or not is to be seen.

Not exactly what I had in mind, the indentations are different. If the UPDATE keyword is at indentation level 0 (I0), then my format would be I0 for UPDATE, I2 for JOINs, I1 for SET and WHERE, and I2 for AND/OR in WHERE. When I apply the rule provided to a slightly more complicated statement, eg. this:

Code:

UPDATE `schema_1`.`table_1` AS t1
INNER JOIN `temp_t` AS tt ON tt.`tid_table_1` = t1.`tid_table_1`
INNER JOIN `schema_2`.`table_2` AS t2 ON t2.`tid_table_2` = m.`tid_temp_t`
INNER JOIN `schema_3`.`table_3` AS t3 ON t3.`tid_table_3` = m.`tid_temp_t`
LEFT OUTER JOIN `schema_2`.`table_4` AS t4 ON t4.`tid_table_2` = t2.`tid_table_2` AND t4.`private_code` = 'whatever'
LEFT OUTER JOIN `schema_2`.`table_4` AS t4 ON t4.`tid_table_2` = t2.`tid_table_2` AND t4.`private_code` = 'whatever'
LEFT OUTER JOIN `schema_2`.`table_4` AS t4 ON t4.`tid_table_2` = t2.`tid_table_2` AND t4.`private_code` = 'whatever'
SET t1.`herehere`= `lv_some_variable`
WHERE t1.`has_some_condition`= 1 AND t2.`and_also_some_others` = 1;

the result is this:

Code:

UPDATE `schema_1`.`table_1` AS t1
INNER JOIN `temp_t` AS tt ON tt.`tid_table_1` = t1.`tid_table_1`
INNER JOIN `schema_2`.`table_2` AS t2 ON t2.`tid_table_2` = m.`tid_temp_t`
INNER JOIN `schema_3`.`table_3` AS t3 ON t3.`tid_table_3` = m.`tid_temp_t`
LEFT OUTER JOIN `schema_2`.`table_4` AS t4 ON t4.`tid_table_2` = t2.`tid_table_2` AND t4.`private_code` = 'whatever'
LEFT OUTER JOIN `schema_2`.`table_4` AS t4 ON t4.`tid_table_2` = t2.`tid_table_2` AND t4.`private_code` = 'whatever'
LEFT OUTER JOIN `schema_2`.`table_4` AS t4 ON t4.`tid_table_2` = t2.`tid_table_2` AND t4.`private_code` = 'whatever'
SET t1.`herehere`= `lv_some_variable`
WHERE t1.`has_some_condition`= 1 AND t2.`and_also_some_others` = 1;

It still does not come near the desired goal but it is definitely much better than my attempts. Thanks!