mysqlgroup-bymysql-error-1055

Non Group by column in select working in one machine and not in another


I have two machines and I am trying to run the below-mentioned query in both of them.

SELECT 
    bdm.brand_id AS brandId,
    bdm.brand_name AS brandName,
    fse.seller_code AS dummySeller,
    bdm.feed_source AS feedSource
FROM
    `brand_distributor_mapping` bdm
        JOIN
    `feed_source` fse ON bdm.feed_source = fse.name
GROUP BY bdm.brand_id ,bdm.feed_source;

It's working on one machine and giving Error code 1055 in another.

Mysql version of both machine:

  1. Not working - mysql Ver 14.14 Distrib 5.6.19, for Linux (x86_64) using EditLine wrapper.
  2. Working - mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64) using readline 6.3

Solution

  • It must be due to SQL_MODE set to ONLY_FULL_GROUP_BY by default.

    Better you always practice a full group by aggregation in your queries. Otherwise though MySQL accepts and retrieves results based on the SQL_MODE set, they may not be correct.

    You may want to change your group by clause like below:

    GROUP BY
        bdm.brand_id,
        bdm.brand_name,
        fse.seller_code,
        bdm.feed_source
    

    Refer to: MySQL Documentation on ONLY_FULL_GROUP_BY