0

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

  • 1
    ... for a certain value of properly ;) – ypercubeᵀᴹ Apr 30 at 14:58
  • 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 at 14:59
  • 1
    @ypercubeᵀᴹ That is the funniest thing I've seen all week! – Cowthulhu Apr 30 at 14:59
  • I'm only looking for unique contact_id – anjanesh Apr 30 at 15:00
  • Then only ask for a contact id: SELECT contact_id FROM ... – Rick James May 2 at 3:46
1

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
    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." – ypercubeᵀᴹ Apr 30 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.

  • That was fixed in later versions – Philᵀᴹ Apr 30 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. – ypercubeᵀᴹ Apr 30 at 18:22

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.