SqlParser can't parse SQL queries with CTEs in them

[fuzl-llc]

Describe the bug

There is a line in SqlQuerySource that tries to parse a SQL query:

SqlParser.TryParse(tokenizedQuery, dialect, _session.Store.Configuration.TablePrefix, parameters, out var rawQuery, out var messages)

If you try to use a query with a CTE (common table expression), the parse fails with the following error:

Syntax error, expected: SELECT at line:0, col:0

I'm not sure if this is a bug or by design, given Orchard supports storage in different database providers and I'm not sure they all support CTEs. If so, perhaps this could be moved to a feature request for those of us who want to use CTEs and are ok with a requirement that our sites must run on a database that supports them.

Expected behavior

Ideally, we could use CTEs in SQL queries. Note: I'm not suggesting necessarily that the Orchard UI should support that, but if we use a database connection directly, it would be nice if we could use CTEs (and get the benefits of the SqlParser.TryParse method).

[hishamco]

Please supply a simple query to test, if it's fail this will conclude that such expression is not supported yet (if the syntax is correct)

[lampersky]

@fuzl-llc it is Orchard Core parser and grammar limitation, CTE is not supported yet

[fuzl-llc]

Thanks @lampersky. Is there a way this can be converted to a feature request? Or, should I close this and create one separately?

[hishamco]

Is there a way this can be converted to a feature request? Or, should I close this and create one separately?

You can create a PR and wait for the team to review it or you can subclass the SqlParser to make this happen in your project

[ns8482e]

@fuzl-llc I guess you can execute sql queries directly to db without needing to use OC's parser see. https://docs.orchardcore.net/en/latest/docs/reference/core/Data/#example

[fuzl-llc]

Thanks @ns8482e! Yes, that is exactly what I'm doing for now. I like to do things the standard Orchard way as much as possible but it does seem to work just fine to execute my CTE directly using the connection object.

[sebastienros]

Looks like all dbs we support support this syntax. Maybe worth adding to the grammar then. Shouldn't be hard to do, unless we really can only return one statement. For instance the same concern could apply to having two SELECT statements in a query.

[sebastienros]

I checked the grammar and we already return a statement list but these are unions only for now.