Is there a way to show a WHERE clause just for one field in MySQL?

by

Prior to MySQL 5.7 the default was to allow non FULL group by. Which means you can have a group by (that uses aggregate functions like sum and max and count and group_concat) with other non-aggregated columns (let’s call them NON AGGS) like your first 3 shown not all part of your group by clause. It allowed it but the results would typically work out like this:

  • It worked great because you know your data well and are trying to achieve a distinct

  • It worked out awful because it was a snafu

Prior to 5.7, ONLY_FULL_GROUP_BY existed but it was turned OFF by default.

So in MySQL 5.7 along comes the ONLY_FULL_GROUP_BY defaulted ON. As such if you attempt a group by, but with not all the NON AGGS in the group by clause, you would get an Error.

Consider the following problem in 5.6 below:

create table thing
(   col1 int not null,
    col2 int not null,
    age int not null
);
insert thing(col1,col2,age) values 
(1,2,10),
(1,3,20),
(2,3,20),
(2,2,10);

select col1,col2,max(age) from thing group by col1;
+------+------+----------+
| col1 | col2 | max(age) |
+------+------+----------+
|    1 |    2 |       20 |
|    2 |    3 |       20 |
+------+------+----------+

What happens above is not all the NON AGGS are in the group by. It returns the max(age) by col1. But since col2 was not in the group by, it used the Cluster Index or Physical Ordering and brought it, inadvertently perhaps (a snafu, a mistake), the wrong value for col2. Depending on your intentions or knowing your data or even caring. The engine didn’t care; perhaps you do.

To avoid these common mistakes or inadvertent data return, MySQL 5.7 turns on ONLY_FULL_GROUP_BY by default.

In your case, the wrong rows are making up your results presumably for columns 2 and 3.

See the Manual Page entitled MySQL Handling of GROUP BY.

Example 2

-- drop table if exists person;
create table person
(   id int auto_increment primary key,
    firstName varchar(100) not null,
    lastName varchar(100) not null
);

-- drop table if exists fruitConsumed;
create table fruitConsumed
(   id int auto_increment primary key,
    theDate date not null,
    fruitId int not null, -- does not really matter. Say, 1=apple, 2=orange from some other table
    personId int not null,
    qty int not null
);

-- truncate table person;
insert person (firstName,lastName) values 
('Dirk','Peters'),
('Dirk','Smith'),
('Jane','Billings');

-- truncate table fruitConsumed;
insert fruitConsumed (theDate,fruitId,personId,qty) values
('2016-10-31',1,1,2),
('2016-10-31',2,1,5),
('2016-10-31',2,2,12),
('2016-11-02',2,2,3);

Query:

select p.firstName,p.lastName,sum(fc.qty) 
from person p 
join fruitConsumed fc 
on fc.personId=p.id 
group by p.firstName,p.lastName; 
+-----------+----------+-------------+
| firstName | lastName | sum(fc.qty) |
+-----------+----------+-------------+
| Dirk      | Peters   |           7 |
| Dirk      | Smith    |          15 |
+-----------+----------+-------------+

The above works great on MySQL 5.6 and 5.7 regardless of the setting for ONLY_FULL_GROUP_BY

now consider

select p.firstName,p.lastName,sum(fc.qty) 
from person p 
join fruitConsumed fc 
on fc.personId=p.id 
group by p.firstName; 

+-----------+----------+-------------+
| firstName | lastName | sum(fc.qty) |
+-----------+----------+-------------+
| Dirk      | Peters   |          22 |
+-----------+----------+-------------+

The above is often acceptable on MySQL 5.6 without ONLY_FULL_GROUP_BY enabled and fails on 5.7 with ONLY_FULL_GROUP_BY enabled (error 1055). The above output is basically gibberish. But below it is explained somewhat:

We know that Dirk, a Dirk, just one Dirk, is the only one to survive the inner join. There are 2 Dirks. But because of the group by p.firstName, we are left with just one Dirk. We need a lastName. Because of the non-conformity to
the SQL Standard, MySQL can allow this with ONLY_FULL_GROUP_BY turned off. So it just picks any old lastName. Well, the first one it finds, and that is either in cache or the one in the physical ordering.

And it went with Peters. The fruit count sum is for all Dirks.

So if you code like this, the non-conforming non-ONLY_FULL_GROUP_BY gives you gibberish.

And as stated, MySQL 5.7 ships defaulted to not allowing this. But it is tweakable to the old way if you choose.

It is highly recommended that you fix your queries and leave ONLY_FULL_GROUP_BY as enabled.

Leave a Comment

tech