2

I am upgrading some old SS 2.4 code to SS4. I am getting the following error when executing the below query:

Error:

Expression #1 of SELECT list is not in GROUP BY clause and contains non-aggregated column 't.Total' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by.

How can I update this query to be compatible with mysql strict mode? Editing my.cnf mysql_mode doesn't seem to take effect

SELECT SalesRepRegion,COUNT(DISTINCT(SalesRepID)) as Total FROM Sale s
 WHERE SalesRepRegion <> 'NULL' AND YEARWEEK( SalesDate, 1 ) = ".$yearweek." AND Status <> 'Void'
 GROUP BY SalesRepRegion
wmk
  • 4,598
  • 1
  • 20
  • 37
LiveSource
  • 6,230
  • 4
  • 21
  • 20
  • Include SalesRepID in the group by clause: `GROUP BY SalesRepRegion, SalesRepID` – marekful Jan 09 '18 at 04:30
  • 1
    Error doesnt seem related to this query. – Mihai Jan 09 '18 at 05:02
  • Hi. What does the manual say about group by & only_full_group_by? Why do you think that query has a problem? In cases where a query needs to change, ie it used to be nondeterministic and was likely just wrong, how are we to know what you want now, necessarily differently, if you don't use words to explain, or even show example input & output? Also please read & act on [mcve]. – philipxy Jan 09 '18 at 06:09
  • Possible duplicate of https://stackoverflow.com/q/43951723/3404097 – philipxy Jan 09 '18 at 06:40

3 Answers3

1

Turns out SilverStripe 4 sets mysql_mode to ANSI in MysqlDatabase.php. To fix I just put the following above the query I was having trouble with. This removes the only_full_group_by mode

DB::get_conn()->setSQLMode('REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE');
LiveSource
  • 6,230
  • 4
  • 21
  • 20
  • 1
    Hi. Read your question. This doesn't answer it. If you are trying to ask a different question, please edit your question to ask what you mean to ask. Also maybe the other answer is a good answer for what you meant to ask? Also note that as we have commented that query did not generate that error. And do you understand that queries generating that error are nondeterministic, having random values chosen by the DBMS, and are consquently likely just wrong? – philipxy Jan 09 '18 at 06:44
1

You will need to add all items in your SELECT list to the GROUP BY statement.

SELECT SalesRepRegion,COUNT(DISTINCT(SalesRepID)) as Total FROM Sale s
 WHERE SalesRepRegion <> 'NULL' AND YEARWEEK( SalesDate, 1 ) = ".$yearweek." AND Status <> 'Void'
 GROUP BY SalesRepRegion, Total

As others have mentioned, this is due to the way MySQL 5.7.5+ changed GROUP BY behaved in order to be SQL99 compliant.

Brett Tasker
  • 219
  • 1
  • 3
  • That caused another error, I can't remember what the error was – LiveSource Jan 10 '18 at 06:54
  • @SheaDawson if this caused another error then you potentially have multiple errors in your SQL statement. Are you able to re-test and let me know what error you got? – Brett Tasker Jan 11 '18 at 02:56
  • This is the correct answer. Both CWP and the blog module have had this problem recently, and ensuring your group items are in the select is the correct way to structure your query. – scrowler Jan 12 '18 at 14:17
0

You can add the following line to your _config.php:

DB::get_conn()->setSQLMode('REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE');
Meysam Zarei
  • 419
  • 2
  • 14