A Database Error Occurred Error Number: 1055 , group by substring

0
votes

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

SELECT b.show_title as show_title FROM `shows` `b` 
WHERE `b`.`active` = 1 
GROUP BY SUBSTRING(show_title, 1, 1)

I'm naming all columns in select query so why the error?

I know the workaround of disabling ONLY_FULL_GROUP_BY from sql_mode but how do i solve the error with the query?

2
mysqlcodeigniter
Your query makes little sense. You want an effectively random title chosen for each letter of the alphabet that has an active title that starts with that letter? – Uueerdo

2 Answers

2
votes

Q: why the error?

Consider two rows with show_title values of 'True Detective' and 'True Lies'

The expression in the GROUP BY is going to return T for both of those rows, so those rows are going to be collapsed into a single row in the resultset.

The query can return only a single value for a column in the collapsed row, and it's indeterminate/ambiguous which of the two values to return. With ONLY_FULL_GROUP_BY in sql_mode, MySQL is adhering more closely to the ANSI SQL specification; the 1055 error is behavior similar to what we observe in other relational DBMS e.g. Oracle, Microsoft SQL Server, Teradata, DB2, et al.

Q: how do i solve the error with the query?

The normative pattern is to use an aggregate function to specify which value (out of a set of possible values) to return.

For example, MAX() or MIN(). Demonstration: 

SELECT SUBSTR(b.show_title,1,1)    AS s1 
     , MAX(b.show_title)           AS max_show_title
     , MIN(b.show_title)           AS min_show_title
  FROM shows b
 WHERE b.active = 1
 GROUP BY SUBSTR(b.show_title,1,1)

will avoid the 1055 error, and could return e.g. 

s1  max_show_title   min_show_title
--  ---------------  ---------------
T   True Lies        True Detective
0
votes

Simply use these two queries and run them as query. Your problem will be fixed asap. No need to restart mysql

solution : run this query :

  1. SET sql_mode = '';
  2. SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));