group_concat section in select remains unformatted

[csonuryilmaz]

Hi, below is a working unformatted MySQL query written in two lines:

SELECT GROUP_CONCAT(DISTINCT TRIM(Concat(m.title, ' ', m.firstname, ' ', m.lastname)) ORDER BY p2m.manufacturer_type_id ASC SEPARATOR ' ') as manufacturer_name
FROM product_to_manufacturer p2m LEFT JOIN manufacturer m ON m.manufacturer_id = p2m.manufacturer_id WHERE p2m.product_id = 574768;

When I format the query, group_concat section in select remains unformatted with all its keywords:

select GROUP_CONCAT(DISTINCT TRIM(Concat(m.title, ' ', m.firstname, ' ', m.lastname)) ORDER BY p2m.manufacturer_type_id ASC SEPARATOR ' ') as manufacturer_name
from product_to_manufacturer p2m
  left join manufacturer m
    on m.manufacturer_id = p2m.manufacturer_id
where p2m.product_id = 574768
;

Here is my formatter settings:

---

Here is an example formatted query from an online formatter service: 😉

[manticore-projects]

Thank you for reporting, the AGGREGATE functions are not fully supported yet because there is also an issue pending in JSQLParser which I wont' close before then other pending PRs are accepted.

That said: Also your Online example fails on it, the GROUP_CONCAT is formatted somehow, but certainly not correctly.

But I will see what I can do today.

[manticore-projects]

You also will see some Warning like:

WARNING: Unhandled expression: net.sf.jsqlparser.expression.MySQLGroupConcat = GROUP_CONCAT(DISTINCT TRIM(Concat(m.title, ' ', m.firstname, ' ', m.lastname)) ORDER BY p2m.manufacturer_type_id ASC SEPARATOR ' ')

It indicates that this particular expression is not supported yet in JSQLFormatter.
Just send me a reminder whenever you any warning like that. I do not use MySQL and so far have focused on Oracle/MSSQL Server and H2. But of course we should support MySQL also as much as possible.

[manticore-projects]

How would you like to get it actually formatted? This MySQL specific expression looks a bit alien to me, so right now I would format it like that:

-- GROUP_CONCAT
SELECT Group_Concat(    DISTINCT Trim( Concat(  m.title, ' ', m.firstname
                                                , ' ', m.lastname ) )
                        ORDER BY p2m.manufacturer_type_id ASC
                        SEPARATOR' ' ) AS manufacturer_name
FROM product_to_manufacturer p2m
    LEFT JOIN manufacturer m
        ON m.manufacturer_id = p2m.manufacturer_id
WHERE p2m.product_id = 574768
;

Any objections or suggestions?

[manticore-projects]

Implemented, you can test it online and also download the Updated Netbeans Plugin

[csonuryilmaz]

.. How would you like to get it actually formatted? This MySQL specific expression looks a bit alien to me, so right now I would format it like that: ..

Hi, your proposed formatting seems to be fine. 👍

Now, I'm using latest .nbm and query is much better and readable compared to previous one-line style.

Thanks a lot. 🤗 🙏

.. You also will see some Warning like:

WARNING: Unhandled expression: net.sf.jsqlparser.expression.MySQLGroupConcat

I don't have any reported warning or exception on Netbeans "Notifications" window by now.

.. I do not use MySQL and so far have focused on Oracle/MSSQL Server and H2. But of course we should support MySQL also as much as possible. ..

👍 In the past I've also worked on MSSQL and Oracle databases. But now, in my current job, I'm only working on MySQL.