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

[doctrinebot]

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.

[doctrinebot]

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.

[mikemix]

Still not working for MySQL 5.7 :(

[WubbleWobble]

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.

[skonsoft]

Hello,

Any news ?

Thank you

[doctoome]

Yes, any news ?

[Ocramius]

See #5622 #5973

[binarious]

Is this fixed via #6143?

[lcobucci]

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]

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