Issue on Join order and MySQL 5.7+

[e-onux]

I'm using knp paginator
And we have order issue about join query
we get error like this:

Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.

then we use this parameter:
'wrap-queries' => true
After that we get this error:

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.value_37' which is not in SELECT list; this is incompatible with DISTINCT

I am using MySQL 5.7.10

[Adambean]

+1, this paginator component is manipulating the query in a way that is not compatible with MySQL 5.7+.

Example situation: Entities "Product" and "Taxonomy" are linked by bridge entity "TaxonomyToProduct", because a custom sort order is needed. This sort order is an integer inside the "TaxonomyToProduct".

Then when it comes to displaying products in a taxonomy one would use the following repository function:

public function findByTaxonomySorted(Taxonomy $taxonomy, $siteId = 0)
{

   $qb = $this->createQueryBuilder("p")
       ->select("p")
       ->leftJoin("p.product_to_taxonomies", "p2t")
       ->leftJoin("p2t.taxonomy", "t")
       ->leftJoin("t.children", "tc")
       ->andWhere("t = :taxonomy OR tc.parent = :taxonomy")
       ->setParameter("taxonomy", $taxonomy)
       ->andWhere("p.status = :statusEnabled")
       ->setParameter("statusEnabled", Product::STATUS_ENABLED)
   ;

   if (($siteId = intval($siteId)) >= 1) {
       $qb
           ->leftJoin("p.sites", "s")
           ->andWhere("s.id = :siteId")
           ->setParameter("siteId", $siteId)
       ;
   }

   $qb
       ->addOrderBy("p2t.sort", "ASC")
   ;

   $query = $qb->getQuery();
   return $query;

}

This works fine when being called by a controller. However the paginator is doing some DISTINCT stuff around the query preventing it from working with the sort order. I've also tried sorting products by their created date via $qb->addOrderBy("p.created", "DESC"); instead but even that causes the same error as OP's message.

SQL query from repository function's getSql() call before paginator fiddled with it:

SELECT
p0_.id AS id_0,
p0_.name AS name_1,
p0_.slug AS slug_2,
p0_.description AS description_3,
p0_.meta_keywords AS meta_keywords_4,
p0_.meta_description AS meta_description_5,
p0_.model AS model_6,
p0_.sku AS sku_7,
p0_.retail_price AS retail_price_8,
p0_.wholesale_price AS wholesale_price_9,
p0_.sort AS sort_10,
p0_.quantity AS quantity_11,
p0_.image AS image_12,
p0_.heavy_or_large AS heavy_or_large_13,
p0_.status AS status_14,
p0_.created AS created_15,
p0_.modified AS modified_16,
p0_.tax_rate_id AS tax_rate_id_17
FROM
product p0_
LEFT JOIN product_site p2_ ON p0_.id = p2_.product_id
LEFT JOIN site s1_ ON s1_.id = p2_.site_id
LEFT JOIN taxonomy_to_product t3_ ON p0_.id = t3_.product_id
LEFT JOIN taxonomy t4_ ON t3_.taxonomy_id = t4_.id
LEFT JOIN taxonomy t5_ ON t4_.id = t5_.parent_id
WHERE
(
t4_.id = ? OR t5_.parent_id = ?
) AND p0_.status = ? AND s1_.id = ?
ORDER BY
t3_.sort ASC

SQL query after the paginator fiddled with it:

SELECT DISTINCT
id_0
FROM
(
SELECT
p0_.id AS id_0,
p0_.name AS name_1,
p0_.slug AS slug_2,
p0_.description AS description_3,
p0_.meta_keywords AS meta_keywords_4,
p0_.meta_description AS meta_description_5,
p0_.model AS model_6,
p0_.sku AS sku_7,
p0_.retail_price AS retail_price_8,
p0_.wholesale_price AS wholesale_price_9,
p0_.sort AS sort_10,
p0_.quantity AS quantity_11,
p0_.image AS image_12,
p0_.heavy_or_large AS heavy_or_large_13,
p0_.status AS status_14,
p0_.created AS created_15,
p0_.modified AS modified_16,
t1_.id AS id_17,
s2_.id AS id_18,
t3_.sort AS sort_19
FROM
product p0_
LEFT JOIN product_site p4_ ON p0_.id = p4_.product_id
LEFT JOIN site s2_ ON s2_.id = p4_.site_id
LEFT JOIN taxonomy_to_product t3_ ON p0_.id = t3_.product_id
LEFT JOIN taxonomy t1_ ON t3_.taxonomy_id = t1_.id
LEFT JOIN taxonomy t5_ ON t1_.id = t5_.parent_id
WHERE
(
t1_.id = ? OR t5_.parent_id = ?
) AND p0_.status = ? AND s2_.id = ?
ORDER BY
t3_.sort ASC
) dctrn_result
ORDER BY
sort_19 ASC
LIMIT 12 OFFSET 0

For some reason the paginator adding an extra ORDER BY condition that isn't possible because it hasn't been selected, and also completely redundant what with the inner query already handling the sort.

[Adambean]

Oh yeah -- As per #358 I've also tried setting options "distinct" to false, and also removed the addOrderBy() from the repository function and as "defaultSortFieldName" => "p2t.sort" instead.

We've also always been using "wrap-queries" => true otherwise we get

Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.

[nicolasmure]

Related to #477 .

[cybernet]

anything new on this ?

[spolischook]

got this bug on mysql but on MariaDb ver 10.1.38 it works well

[cybernet]

still open ...