support `COLLATE` clause to any of the arguments

[jetjinser]

Is your feature request related to a problem? Please describe.

support COLLATE clause to any of the arguments, e.g. literal const, column, expression, etc. The content of this issue is not the collation in create table.

Describe the solution you'd like

If we don't have plans to support collation yet, maybe we just need to simply handle collate in the frontend binder instead of throwing an error. https://github.com/risingwavelabs/risingwave/blob/7db34d516356d11ed2562e798cbb28f66108f715/src/frontend/src/binder/expr/mod.rs#L196-L200

Describe alternatives you've considered

No response

Additional context

https://www.postgresql.org/docs/16/sql-expressions.html#SQL-SYNTAX-COLLATE-EXPRS

Our user is using drmio, and he complained that risingwave can’t support collation.

dremio is a data lakehouse platform, the sql query it generates uses a lot of collate clause.

[BugenZhao]

maybe we just need to simply handle collate in the frontend binder instead of throwing an error

It seems not that easy. 😕 The collation affects the execution behavior like sorting and upper/lower. However, it seems we're following the behavior of COLLATE "C", perhaps we can only accept this value.

[jetjinser]

Well, this is really complicated... So we should be able to accept COLLATE "C" and COLLATE "POSIX" now? (AFAIK, the two are exactly the same but written in different ways) Dremio has not been observed using other collations, so we should be able to fix it by doing this first. 🙈

[fuyufjh]

Oh I didn't see this issue and created another one https://github.com/risingwavelabs/risingwave/issues/13171 . @jetjinser would you like to take it?

[jetjinser]

transfer to #13171.