SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.355 Pro] - Formatting issues (UPDATE on MariaDB)

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.355 Pro] - Formatting issues (UPDATE on MariaDB)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1981

Post [11.5.355 Pro] - Formatting issues (UPDATE on MariaDB) Reply with quote
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?
Mon Jan 18, 2021 10:45 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7555

Post Reply with quote
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.
Mon Jan 18, 2021 11:30 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1981

Post Reply with quote
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.
Mon Jan 18, 2021 2:11 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1981

Post Reply with quote
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
Tue May 10, 2022 9:29 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7555

Post Reply with quote
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.
Wed May 11, 2022 8:31 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1981

Post Reply with quote
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;

Wed May 11, 2022 8:37 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7555

Post Reply with quote
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.
Thu May 12, 2022 11:28 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1981

Post Reply with quote
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!
Fri May 13, 2022 8:27 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.