DDC-3986: [Paginator] Invalid generated query with usage of output walkers #4866

New Issue

admin · 2026-01-22T14:50:53+01:00

admin commented

2026-01-22 14:50:53 +01:00

Originally created by @doctrinebot on GitHub (Nov 8, 2015).

Originally assigned to: @lcobucci on GitHub.

Jira issue originally created by user egeloen:

Hey!

I have some troube when I use the paginator with the output walkers enabled on MySQL 5.7. Here is my query that I pass to the paginator:

SELECT shop, Distance(shop.location, PointFromText(:location)) AS HIDDEN distance FROM Shopper\Component\Shop\Shop shop WHERE shop.location IS NOT NULL ORDER BY distance ASC

It uses some geospatial feature but the point is not here. When using the output walkers, the generated query is the following:

SELECT DISTINCT id*4 FROM (SELECT s0_.name AS name_0, ST_AsText(s0_.location)  AS location_1, s0_.created_at AS created_at_2, s0_.updated_at AS updated_at_3, s0_.id AS id_4, ST_Distance(s0_.location, ST_PointFromText(?)) AS sclr_5 FROM shop s0_ WHERE s0_.location IS NOT NULL) dctrn_result ORDER BY sclr*5 ASC LIMIT 10 OFFSET 0

Then, Mysql complains the following:

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn*result.sclr*5' which is not in SELECT list; this is incompatible with DISTINCT"

Am I missing something or is it a bug since when I disable the output walkers, the query is executable.

Originally created by @doctrinebot on GitHub (Nov 8, 2015). Originally assigned to: @lcobucci on GitHub. Jira issue originally created by user egeloen: Hey! I have some troube when I use the paginator with the output walkers enabled on MySQL 5.7. Here is my query that I pass to the paginator: ``` java SELECT shop, Distance(shop.location, PointFromText(:location)) AS HIDDEN distance FROM Shopper\Component\Shop\Shop shop WHERE shop.location IS NOT NULL ORDER BY distance ASC ``` It uses some geospatial feature but the point is not here. When using the output walkers, the generated query is the following: ``` java SELECT DISTINCT id*4 FROM (SELECT s0_.name AS name_0, ST_AsText(s0_.location) AS location_1, s0_.created_at AS created_at_2, s0_.updated_at AS updated_at_3, s0_.id AS id_4, ST_Distance(s0_.location, ST_PointFromText(?)) AS sclr_5 FROM shop s0_ WHERE s0_.location IS NOT NULL) dctrn_result ORDER BY sclr*5 ASC LIMIT 10 OFFSET 0 ``` Then, Mysql complains the following: ``` java SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn*result.sclr*5' which is not in SELECT list; this is incompatible with DISTINCT" ``` Am I missing something or is it a bug since when I disable the output walkers, the query is executable.

admin added the Bug label 2026-01-22 14:50:53 +01:00

admin closed this issue

2026-01-22 14:50:55 +01:00

admin commented

2026-01-22 14:50:55 +01:00

@doctrinebot commented on GitHub (Nov 15, 2015):

Comment created by egeloen:

For the record, it seems related to http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmodeonly_full_groupby which is now enabled by default and break the paginator. Disabling it "fix" the issue but then, mysql can return invalid data according to this new mode.

@doctrinebot commented on GitHub (Nov 15, 2015): Comment created by egeloen: For the record, it seems related to http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode*only_full_group*by which is now enabled by default and break the paginator. Disabling it "fix" the issue but then, mysql can return invalid data according to this new mode.

admin commented

2026-01-22 14:50:56 +01:00

@mikemix commented on GitHub (May 2, 2016):

Still not working for MySQL 5.7 :(

@mikemix commented on GitHub (May 2, 2016): Still not working for MySQL 5.7 :(

admin commented

2026-01-22 14:50:59 +01:00

@WubbleWobble commented on GitHub (Jun 9, 2016):

Not tested and no guarantees of completeness, but a quick fix looks like so:

// LimitSubqueryOutputWalker.php:
private function preserveSqlOrdering(array $sqlIdentifier, $innerSql, $sql, $orderByClause)
{
    // If the sql statement has an order by clause, we need to wrap it in a new select distinct
    // statement
    if (! $orderByClause instanceof OrderByClause) {
        return $sql;
    }

    // Rebuild the order by clause to work in the scope of the new select statement
    /* @var array $orderBy an array of rebuilt order by items */
    $orderBy = $this->rebuildOrderByClauseForOuterScope($orderByClause);
    $orderByFields = str_replace([' DESC', ' ASC'], ['', ''], $orderBy);

    // Build the select distinct statement
    $sql = sprintf(
        'SELECT DISTINCT %s, %s FROM (%s) dctrn_result ORDER BY %s',
        implode(', ', $sqlIdentifier),
        implode(', ', $orderByFields),
        $innerSql,
        implode(', ', $orderBy)
    );

    return $sql;
}

The two lines added (the ones that mention $orderByFields) add the order-by fields mentioned in the subquery into the select on the outer query as per MySQL's complaint.

@WubbleWobble commented on GitHub (Jun 9, 2016): Not tested and no guarantees of completeness, but a quick fix looks like so: ``` // LimitSubqueryOutputWalker.php: private function preserveSqlOrdering(array $sqlIdentifier, $innerSql, $sql, $orderByClause) { // If the sql statement has an order by clause, we need to wrap it in a new select distinct // statement if (! $orderByClause instanceof OrderByClause) { return $sql; } // Rebuild the order by clause to work in the scope of the new select statement /* @var array $orderBy an array of rebuilt order by items */ $orderBy = $this->rebuildOrderByClauseForOuterScope($orderByClause); $orderByFields = str_replace([' DESC', ' ASC'], ['', ''], $orderBy); // Build the select distinct statement $sql = sprintf( 'SELECT DISTINCT %s, %s FROM (%s) dctrn_result ORDER BY %s', implode(', ', $sqlIdentifier), implode(', ', $orderByFields), $innerSql, implode(', ', $orderBy) ); return $sql; } ``` The two lines added (the ones that mention $orderByFields) add the order-by fields mentioned in the subquery into the select on the outer query as per MySQL's complaint.

admin commented

2026-01-22 14:51:00 +01:00

@skonsoft commented on GitHub (Sep 24, 2016):

Hello,

Any news ?

Thank you

@skonsoft commented on GitHub (Sep 24, 2016): Hello, Any news ? Thank you

admin commented

2026-01-22 14:51:00 +01:00

@doctoome commented on GitHub (Oct 9, 2016):

Yes, any news ?

@doctoome commented on GitHub (Oct 9, 2016): Yes, any news ?

admin commented

2026-01-22 14:51:00 +01:00

@Ocramius commented on GitHub (Nov 3, 2016):

See #5622 #5973

@Ocramius commented on GitHub (Nov 3, 2016): See #5622 #5973

admin commented

2026-01-22 14:51:00 +01:00

@binarious commented on GitHub (Sep 6, 2017):

Is this fixed via https://github.com/doctrine/doctrine2/pull/6143?

@binarious commented on GitHub (Sep 6, 2017): Is this fixed via https://github.com/doctrine/doctrine2/pull/6143?

admin commented

2026-01-22 14:51:00 +01:00

@lcobucci commented on GitHub (Nov 26, 2017):

Is this fixed via #6143?

@binarious it should be, but we don't have a stable release for v2.6.0 so you'll have to use v2.6.x-dev to use this feature for now.

@lcobucci commented on GitHub (Nov 26, 2017): > Is this fixed via #6143? @binarious it should be, but we don't have a stable release for `v2.6.0` so you'll have to use `v2.6.x-dev` to use this feature for now.

admin commented

2026-01-22 14:51:03 +01:00

@lcobucci commented on GitHub (Nov 26, 2017):

I'll close this issue since it was handled via #6143, please reopen if you feel it's needed.

@lcobucci commented on GitHub (Nov 26, 2017): I'll close this issue since it was handled via #6143, please reopen if you feel it's needed.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4866