paginator incompatibility with MySQL 5.7

[2binfree]

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';

[mvrhov]

This has already been reported.

[teohhanhui]

Duplicate of #4846

[cordoval]

after running the sql you indicated it works.