8

My SQL statement returns properly on my laptop's MySQL (Server version: 5.5.62-0ubuntu0.14.04.1 - (Ubuntu)) but on my server (Server version: 5.7.26-0ubuntu0.16.04.1 - (Ubuntu)) it returns an error.

SELECT * 
FROM `orders` 
WHERE `mail_sent`='No' 
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE) 
GROUP BY `contact_id` 
ORDER BY `datetime` ASC;

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.orders.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3
  • 1
    This query tries to select one RANDOM record from record group with the same contact_id. So the query makes no sense.
    – Akina
    Apr 30 '19 at 14:59
  • I'm only looking for unique contact_id
    – anjanesh
    Apr 30 '19 at 15:00
  • Then only ask for a contact id: SELECT contact_id FROM ...
    – Rick James
    May 2 '19 at 3:46
9

I think this problem due to strict mode enabled in your MySQL version. Kindly disable strict mode and try again.

To disable follow How to turn on/off MySQL strict mode in localhost (xampp)?

STRICT_TRANS_TABLES is responsible for setting MySQL strict mode.

To check whether strict mode is enabled or not run the below sql:

SHOW VARIABLES LIKE 'sql_mode';

If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave

+--------------+------------------------------------------+ 
|Variable_name |Value                                     |
+--------------+------------------------------------------+
|sql_mode      |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|
+--------------+------------------------------------------+

Hence strict mode is enabled in my case as one of the value is STRICT_TRANS_TABLES.

To disable strict mode run the below sql:

set global sql_mode='';

[or any mode except STRICT_TRANS_TABLES. Ex: set global sql_mode='NO_ENGINE_SUBSTITUTION';]

To again enable strict mode run the below sql:

set global sql_mode='STRICT_TRANS_TABLES';

If you don't what to disable strict_mode then you have to change your sql query to follow sql standard.

like this..

SELECT
   aggregated_column,
   aggregated_column,
   aggregation_function(column),
   aggregation_function(column),
   aggregation_function(column)
FROM
  TABLE
WHERE [CLAUSE]
GROUP BY [GROUP BY FIELD]
WHILE [CLAUSE]
ORDER BY 

for your query..

SELECT 
  contact_id,
  min(mail_sent),
  min(datetime),
  max(datetime),
FROM `orders` 
WHERE `mail_sent`='No' 
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE) 
GROUP BY `contact_id` 
ORDER BY `datetime` ASC;

Let me know if that helps.

1
  • 5
    Please edit the "Kindly disable strict mode" with "Kindly disable strict mode if you want this nonsense query to be allowed and potentially return insonsistent or non-existent results." Apr 30 '19 at 18:23
1

Put the remainder of the columns (except contact_id) inside aggregate functions. In my limited experience, MySQL isn't completely consistent in how it handles non-grouped columns outside of aggregate functions. Best case, it's not deterministic.

2
  • That was fixed in later versions
    – Philᵀᴹ
    Apr 30 '19 at 15:11
  • @Philᵀᴹ yes, and the new strict settings produce the error the OP gets when used with queries that can return inconsistent/nonsense data. Apr 30 '19 at 18:22
1

Another quick fix is to use ANY_VALUE(contact_id) as contact_id instead of only contact_id in your SELECT.

For example:

SELECT ANY_VALUE(contact_id) as contact_id, ANY_VALUE(mail_sent) as mail_sent, ANY_VALUE(datetime) AS datetime
    WHERE ...
    GROUP BY contact_id
    ORDER BY datetime DESC 

Note that the field you group by (e.g. contact_id) must be selected with ANY_VALUE(contact_id) AS contact_id.

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

Worth to read: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.