CODEWITHSUNDEEP
mysqlmysql-error-1055
Thomas Williams
Thomas Williams

Reputation: 1548

How do I make my query mysql 5.7 compliant without changing my.ini file

I have just upgraded to mysql 5.7.14 and it has caused a problem with one of my queries, and I get the error.

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

Now the solution apparently was to edit the my.ini file and add this line

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Now this works, and now my query runs fine, but I would rather make my query mysql 5.7 compliant rather than having to add lines to my ini file, which might or might not work on our server. I have no access to the ini file on the server, but I have in my local server running wampserver 3.06.

Here is my query

SELECT DATE_FORMAT(`InvoiceDate`,'%Y-%m') as InvoiceDate,
       ROUND((SUM(`Unit_Cost`*`Quantity`)*`ExchangeRate`)+`VATValue`,2) as amount 
FROM `salesinvoice`
LEFT JOIN `salesinvoice_products`
  on `salesinvoice`.`SalesInvoice_id`=`salesinvoice_products`.`SalesInvoice_id`
WHERE `InvoiceDate` < Now()
  and `InvoiceDate` > DATE_ADD(Now(), INTERVAL- 6 MONTH) 
GROUP BY Month(`InvoiceDate`)

How do I change my query to be mysql 5.7 compliant, and what does the error message really mean?

Upvotes: 1

Views: 260

Answers (4)

user2560539
user2560539

Reputation:

From MySQL Documentation

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.

To tell MySQL to accept the query, you can use the ANY_VALUE() function on the field which produced the error.

SELECT ANY_VALUE(DATE_FORMAT(`InvoiceDate`,'%Y-%m')) as `formatedInvoiceDate`,
       ROUND((SUM(`Unit_Cost`*`Quantity`)*`ExchangeRate`)+`VATValue`,2) as `amount` 
FROM `salesinvoice`
LEFT JOIN `salesinvoice_products`
  on `salesinvoice`.`SalesInvoice_id`=`salesinvoice_products`.`SalesInvoice_id`
WHERE `InvoiceDate` < Now()
  and `InvoiceDate` > DATE_ADD(Now(), INTERVAL- 6 MONTH) 
GROUP BY Month(`InvoiceDate`).

Alternatively, disable ONLY_FULL_GROUP_BY.

A little more about ANY_VALUE() function can be found in MySQL Documentation on Miscellaneous Functions (make sure to read the example).

Synoptically, ANY_VALUE() function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

Your ActInvoiceDate is ambygous..

You can't use alias in where and you should change the name for alias (and you should use the same column in group by )

Your query worked fine before because previous version don't check if there are columns in select not corresponding in group by clause .. and for this columns use the firts value dound when needed

so you have ExchangeRate, VATValue column not in group by for this you could use a (fake) aggregagation function (in this case i use min) because probably these columns have constant value 

SELECT 
      DATE_FORMAT(`InvoiceDate`,'%Y-%m') as ActInvoiceDate
      ,ROUND((SUM(`Unit_Cost`*`Quantity`)*min(`ExchangeRate`)+min(`VATValue`),2) as amount 
      FROM `salesinvoice`
LEFT JOIN `salesinvoice_products` on `salesinvoice`.`SalesInvoice_id`=`salesinvoice_products`.`SalesInvoice_id`
WHERE `InvoiceDate` < Now() and `InvoiceDate` > DATE_ADD(Now(), INTERVAL- 6 MONTH) 
GROUP BY DATE_FORMAT(`InvoiceDate`,'%Y-%m')

or 

  SELECT 
        DATE_FORMAT(`InvoiceDate`,'%Y-%m') as ActInvoiceDate
        ,ROUND((SUM(`Unit_Cost`*`Quantity`)*min(`ExchangeRate`))+min(`VATValue`),2) as amount 
        FROM `salesinvoice`
  LEFT JOIN `salesinvoice_products` on `salesinvoice`.`SalesInvoice_id`=`salesinvoice_products`.`SalesInvoice_id`
  WHERE `InvoiceDate` < Now() and `InvoiceDate` > DATE_ADD(Now(), INTERVAL- 6 MONTH) 
  GROUP BY ActInvoiceDate

Upvotes: 1

Sateesh
Sateesh

Reputation: 1336

You can use this statement to set sql_mode

SET GLOBAL sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Upvotes: 0

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146490

From FAQ: Server SQL Mode:

  • Is the mode dependent on the database or connection?

  • A mode is not linked to a particular database. Modes can be set locally to the session (connection), or globally for the server. you can change these settings using SET [GLOBAL|SESSION] sql_mode='modes'.

In many platforms it's possible to run default queries on connection.

Said that, default mode got stricter and no longer allows incomplete GROUP BY clauses because it's normally a sign that the query is wrong and results are incorrect.

Upvotes: 0

Related Questions