OrchardCore: SQL SELECT FROM subquery not available
Describe the bug
The current SQL parser does not allow subqueries in the FROM clause
To Reproduce
Run the following SQL Query (can be done via the Run SQL Query menu in the admin page):
SELECT Id FROM (
SELECT Id FROM Document
) AS Temp
Expected behavior
The query should run (it might take a while using the query above but that is only for illustration of the issue)
Screenshots
Below is the error encountered when running the above query:
Notes
The screenshot is from an OC v1.3 instance using SQL Server as the database. I’m not sure if subqueries in the FROM clause is part of the SQL92 standard, so this might be intended behavior. Subqueries in the WHERE clause works properly.
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 20 (17 by maintainers)
@Skrypt @hishamco @lampersky thank you for the updates. We will have to evaluate if creating the view separately will be applicable for our needs. The confirmation of the issue being a limitation of the SQL92 standard syntax should be enough for this issue.
This can be closed if needed. Thanks!
I though he means to create the VIEW inside the database, then you can easily query it as a normal table in the query editor
@alexandervelasco as it is mentioned above it is a parser/grammar limitation, if you really need to use subquery in from clause, as a workaround you could wrap that query inside a VIEW, and query a view instead
The current parser is using SQL92. We would need to implement a new parser that is compliant with new standards. Make it selectable as an option. Maybe we could also make the parsing optional. But the idea is to have SQL that will work with any of the databases that we use. Of course, MySQL and PostgreSQL have different allowed syntaxes for example. So, I don’t know what would be best, to be honest. You want to keep some parsing to return syntax errors to debug these still. I think that’s the idea with these. Else, I think the current parser has been modified over time. So, that would be just about to add the option in the parser.
Probably SQL92 limitation. This requires support for “temporal tables”.
See below without parentheses & alias: