paginator incompatibility with MySQL 5.7 #5144

New Issue

admin · 2026-01-22T14:59:37+01:00

admin commented

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

Originally created by @2binfree on GitHub (Jun 8, 2016).

Originally assigned to: @2binfree on GitHub.

i think its related to MySQL 5.7 change, can't have order by that isn't in select for aggregations, group or distincts included.
this code don't work on Mysql 5.7 :

  $query = $advert->createQueryBuilder('a')
            ->orderBy('a.date', 'DESC')
            ->getQuery();

        $query
            ->setFirstResult(1)
            ->setMaxResults(2);

Exception:

An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT DISTINCT id_0 FROM (SELECT o0_.id AS id_0, o0_.date AS date_1, o0_.title AS title_2, o0_.author AS author_3, o0_.content AS content_4, o0_.published AS published_5, o0_.updated_at AS updated_at_6, o0_.nb_applications AS nb_applications_7, o0_.slug AS slug_8 FROM oc_advert o0_) dctrn_result ORDER BY date_1 DESC LIMIT 2 OFFSET 1':

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.date_1' which is not in SELECT list; this is incompatible with DISTINCT") in OCPlatformBundle:Advert:test.html.twig at line 12.

you must set some parameters to your mysql server to fix this problem :

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Originally created by @2binfree on GitHub (Jun 8, 2016). Originally assigned to: @2binfree on GitHub. i think its related to MySQL 5.7 change, can't have order by that isn't in select for aggregations, group or distincts included. this code don't work on Mysql 5.7 : ``` $query = $advert->createQueryBuilder('a') ->orderBy('a.date', 'DESC') ->getQuery(); $query ->setFirstResult(1) ->setMaxResults(2); ``` Exception: > An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT DISTINCT id_0 FROM (SELECT o0_.id AS id_0, o0_.date AS date_1, o0_.title AS title_2, o0_.author AS author_3, o0_.content AS content_4, o0_.published AS published_5, o0_.updated_at AS updated_at_6, o0_.nb_applications AS nb_applications_7, o0_.slug AS slug_8 FROM oc_advert o0_) dctrn_result ORDER BY date_1 DESC LIMIT 2 OFFSET 1': > > SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.date_1' which is not in SELECT list; this is incompatible with DISTINCT") in OCPlatformBundle:Advert:test.html.twig at line 12. you must set some parameters to your mysql server to fix this problem : `SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';`

admin added the Improvement Duplicate labels 2026-01-22 14:59:37 +01:00

admin closed this issue

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

admin commented

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

@mvrhov commented on GitHub (Jun 8, 2016):

This has already been reported.

@mvrhov commented on GitHub (Jun 8, 2016): This has already been reported.

admin commented

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

@teohhanhui commented on GitHub (Jul 21, 2016):

Duplicate of #4846