ORDER_BY placement in GROUP_CONCAT vs STRING_AGG: MYSQL vs TSQL

[Antusqus]

Hi,

I'm trying to transpile an sql statement from MYSQL to TSQL, containing GROUP_CONCAT and ORDER_BY.
In MYSQL, GROUP_CONCAT has the ORDER BY token within the statement of GROUP_CONCAT.
e.g GROUP_CONCAT(_colb_ ORDER BY _colb_ ASC separator _','_)

In TSQL, ORDER BY should come AFTER the statement.
e.g STRING_AGG(_colb, ','_) ORDER BY _colb_ ASC
How would you go about creating a transpiling method for this?

Below is the expected syntax for both languages.
MySQL – GROUP_CONCAT()

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

T-SQL – STRING_AGG()

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

I'm also very curious to learn more, so that I can solve these issues myself and perhaps contribute.
This is an awesome library.
Would you be able to give me some pointers? Or do you have any use-case type diagrams available?
Things that show kind of the workflow of the library. I think it would really help with contributions.

Thank you!

P.S
The things I have tried are (but to no avail):

• Inspect other languages for similar situations
• Adding an ordered token to GroupConcat
• Changing the expression transformation

in tsql.py
Parser.FUNCTION_PARSERS
"GROUP_CONCAT": lambda self: self.expression( exp.GroupConcat, this=self._parse_lambda(), separator=self._match(TokenType.SEPARATOR) and self._parse_field(), ordered=self._match(TokenType.ORDER_BY) and self._parse_field() ),
Generator.Expressions
class GroupConcat(Func): arg_types = {"this": True, "separator": False, "ordered": False}
Generator.Transforms
exp.GroupConcat: lambda self, e: f"""STRING_AGG({self.sql(e, "this")}, {self.sql(e, "separator") or "','"}) {self.sql(e, "ordered")}""",

I assume the main problem is that the parser looks for L_PAREN and R_PAREN, to formulate the "this" expression, so it can't find the "ORDER_BY" token for the "ordered" expression. But I'm not sure. Any help would be appreciated.

[georgesittas]

Hello,

This looks a bit tricky at first glance. I think it'll involve creating a separate parsing method for STRING_AGG for TSQL, and also finding a way to transpile a list of comma-separated expressions (MySQL) to the equivalent TSQL version; maybe converting these expressions into a CONCAT expression could work. However, I'm not sure about the exact semantics right now, so it'll need a bit of investigation.

I'll try to solve this for you and ping you when a commit is ready so you can inspect the changes. Thanks for the report!

Would you be able to give me some pointers? Or do you have any use-case type diagrams available?
Things that show kind of the workflow of the library. I think it would really help with contributions.

There's no documentation about the contribution workflow right now, though maybe we'll work on this in the future. For now, you can inspect commits / PRs and see how the workflow looks. For simple issues, it's usually a matter of changing the parser / generator and posssibly adding some new classes in expressions.py.

[georgesittas]

By the way, I'm reading the TSQL docs and it mentions that:

Only one order_by_expression is allowed per query. The default sort order is ascending.

This is a bit confusing, since the syntax form says order_by_expression_list. Does this mean that you can only have a single expression after an ORDER BY clause? I couldn't find any examples that had a "list" of expressions with a quick search.

[Antusqus]

Thanks for the response! Then for now, I'll leave it to the experts while I try to study your library more, using past commits / PRs :))

By the way, I'm reading the TSQL docs and it mentions that:

Only one order_by_expression is allowed per query. The default sort order is ascending.

This is a bit confusing, since the syntax form says order_by_expression_list. Does this mean that you can only have a single expression after an ORDER BY clause? I couldn't find any examples that had a "list" of expressions with a quick search.

That is a pretty confusing statement. I think it means to imply that the expression can be from a list, which forms a single expression?
E.g: WITHIN GROUP (ORDER BY _col.amount DESC, col.price ASC_) where _col.amount DESC, col.price ASC_ counts as a single expression? I'm not sure.
I tried playing around with the syntax in sql server and it DOES allow for a list of expressions in the ORDER BY clause.
as in: The query executed normally.
It's hard to think of an example where we could test if it has any effect though. I tried testing it using some sample database, which has first_names, last_names aggregated, so it's not something I could actually test the ASC,DESC ordering on properly, since it just took whatever first argument got ordered.

I'll try to see if I can find some better data to try it on.

[georgesittas]

@Antusqus I'm not sure if it's the most elegant way to do this, but I tried to tackle the problem you mentioned here: d5846b6.

[Antusqus]

Looks good to me! Thank you 👍

I couldn't find any data to test on unfortunately.
Again, thank you :)

[georgesittas]

No worries. You're welcome. :)