DBAL-713: MSSQL: Wrong Placement of "ROW_NUMBER() OVER" when using Subqueries in SELECT part #1937

Closed
doctrinebot opened this Issue Dec 19, 2013 · 10 comments

2 participants

@doctrinebot

Jira issue originally created by user mklocke:

I'm trying to create a DQL query like that:

SELECT Task.id AS id, Task.date AS date, (
    SELECT COUNT(p.posNr)
    FROM Project\Entity\Position p
    WHERE Task.id=p.ref
) AS poscount
FROM Project\Entity\Task Task
WHERE Task.id <> 0 AND Task.status < 3
ORDER BY Task.date DESC

This works flawlessly on MSSQL until i try to apply a LIMIT/OFFSET by using setFirstResult() and setMaxResults().
Applying a Limit results in an invoke of "doModifyLimitQuery()" in "Doctrine\DBAL\Platforms\SQLServerPlatform".

The function implementation clearly states what's wrong:

//Replace only first occurrence of FROM with $over to prevent changing FROM also in subqueries.
$over  = 'ORDER BY ' . implode(', ', $overColumns);
$query = preg*replace('/\sFROM\s/i', ", ROW_NUMBER() OVER ($over) AS doctrine*rownum FROM ", $query, 1);

This breaks support with subqueries in SELECT statements.

@doctrinebot

Comment created by @deeky666:

Yeah this seems to be indeed wrong. This method is going one step forward and one step back with each adjustment :( I don't feel comfortable trying to fix that one as it is really sensitive. But you are welcome to provide a patch for this on github :) (or someone else).

@doctrinebot

Comment created by tomdrissen:

I ran into this bug today, which is really annoying. Is there any change this will be fixed shortly?
How about checking parenthesises to determine the 'base' FROM/table?

@doctrinebot

Comment created by @deeky666:

Patch supplied in PR: #512

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-512] was closed:
#512

@doctrinebot

Comment created by @deeky666:

Fixed in commit: c4d4c5f

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by mklocke:

Important Notice:

The usage of preg_replace() in this bugfix can cause Apache to abort the PHP execution because of a stack overflow.
Apache's default value for "ThreadStackSize" on Windows is 1MB. This is not sufficient if you use preg_replace() on long queries.
I had to increase the size to 8MB.
This should be mentioned somewhere.

@doctrinebot

Comment created by @ocramius:

[~mklocke] how long is the query with which you are experiencing this? I'd say it's more a PHP bug than problem of the DBAL

@doctrinebot

Comment created by @deeky666:

That might be an issue of using recursion in the regular expression maybe. I'm not sure if that is known limitation of PHP. But it seems weird to me you have to adjust apache config instead of PHP for this.

@doctrinebot

Comment created by mklocke:

My Query is 1275 characters long.

It's not a bug at all, just a misconfiguration in Apache. But it's really hard to debug this problem, because Apache just kills PHP and doesn't say a word about it. So it would be kind to document this somewhere, so that new Doctrine Users can configure their Apache correctly.

@doctrinebot doctrinebot added the Bug label Dec 7, 2015
@beberlei beberlei was assigned by doctrinebot Dec 7, 2015
@doctrinebot doctrinebot added this to the 2.5 milestone Dec 7, 2015
@doctrinebot doctrinebot closed this Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment