MySQL Bugs: #79134: SQL statement can't run on MySQL 5.7 CentOS 7

Bug #79134	SQL statement can't run on MySQL 5.7 CentOS 7
Submitted:	5 Nov 2015 9:09	Modified:	12 Dec 2015 13:31
Reporter:	weixing zou	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7	OS:	CentOS (7.1)
Assigned to:		CPU Architecture:	Any
Tags:	SQL statement can't run on MySQL 5.7 for CentOS 7

Description:
run a not complex sql:
SELECT
        DISTINCT banner1_.id AS id1_38_,
        banner1_.create_date AS create_d2_38_,
        banner1_.modify_date AS modify_d3_38_,
        banner1_.banner_type AS banner_t4_38_,
        banner1_.color_id AS color_i12_38_,
        banner1_.detail AS detail5_38_,
        banner1_.display_name AS display_6_38_,
        banner1_.extname AS extname7_38_,
        banner1_.icon AS icon8_38_,
        banner1_.lcaid AS lcaid9_38_,
        banner1_.status AS status10_38_,
        banner1_.type AS type11_38_ 
    FROM
        xx_banner_scheduled_publish bannersche0_ 
    INNER JOIN
        xx_banner banner1_ 
            ON bannersche0_.banner_id=banner1_.id CROSS 
    JOIN
        xx_banner banner2_ 
    WHERE
        bannersche0_.banner_id=banner2_.id 

        AND banner2_.status=1 

    ORDER BY
        bannersche0_.order_list;

[Err] 3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'game.bannersche0_.order_list' which is not in SELECT list; this is incompatible with DISTINCT

But this statement can run under MySQL 5.6 and MySQL 5.7 for centos 6. why?

How to repeat:
run it on MySQL 5.7 for CentOS 7

Suggested fix:
any difference between MySQL 5.7 for rhel 6 and rhel 7?

Thank you for the bug report. Please provide complete test case (Create table statement). Thanks.

This is documented behaviour. 

From 5.7.5 SQL_MODE now by default contains: ONLY_FULL_GROUP_BY
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

You can fix your SELECTs (as I'm doing) or

SET SQL_MODE=""

or

SET SQL_MODE=(SELECT REPLACE(@@SQL_MODE,'ONLY_FULL_GROUP_BY',''));

Or whatever floats your boat. Beware, you'll probably be bitten by other things too. You may want to read http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

Please try:

SET SQL_MODE=""

as suggested in prior comment. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".