Jira issue originally created by user @doctrinebot:
This issue is created automatically through a Github pull request on behalf of deeky666:
SELECT statements that contains subqueries in the SELECT clause do not get properly rewritten with a limit and/or offset applied to it resulting in wrong SQL.
SELECT foo.id, (SELECT COUNT(*) FROM bar) AS bar_count FROM foo
Expected with a limit of 10
SELECT ** FROM (SELECT foo.id, (SELECT COUNT(**) FROM bar) AS bar*count, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM foo) AS doctrine_tbl WHERE doctrine*rownum BETWEEN 1 AND 10
Actual with a limit of 10
SELECT ** FROM (SELECT foo.id, (SELECT COUNT(**), ROW*NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM bar) AS bar_count FROM foo) AS doctrine_tbl WHERE doctrine*rownum BETWEEN 1 AND 10
The ROW_NUMBER() OVER clause is misplaced into the subselect instead of into the main FROM clause.
What this PR does is recursively matching any (nested) parentheses inside the main SELECT clause to be able to identify the main FROM clause to add the ROW_NUMBER() OVER clause to.
This of course is far from perfect for matching all kinds of possible SELECT statement syntaxes but it fixes this particular issue, which is pretty common IMO.
Comment created by @doctrinebot:
A related Github Pull-Request [GH-512] was closed:
Issue was closed with resolution "Fixed"