So, I updated my ubuntu box and mysql came along for the ride to the glorious 5.7 that brings about doom:
[:error] [pid 9211] [client 0.0.0.0:1] PHP Fatal error:
Uncaught PDOException: SQLSTATE: Syntax error or access violation:
1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains
nonaggregated column 'a.Z'; this is incompatible with sql_mode=only_full_group_by
$s = $d->prepare("SELECT a.Z,a.Y,a.X,MAX(b.F) as `G` FROM `a`,`b` WHERE (a.P=:va1 && b.R:va2)");
P_Id int NOT NULL UNIQUE
Yes. Non-aggregated columns are any column that don't use an aggregation function like
a is functionally dependent on column
b if the value of
b implies a particular value of
a. This generally means that
b is a unique key for the table, and
a is some other column in that table.
Uniquely determined by is the same as functional dependency.
The alternative would be to list all the non-aggregated columns in the
GROUP BY list:
GROUP BY a.Z, a.Y, a.X.
The reason for all this is that when you select columns that aren't in the
GROUP BY list, they will come from arbitrary rows in the grouped rows. This leads to many common errors. For instance, a common mistake is to write:
SELECT user_id, MAX(timestamp), ip_address FROM user_logins GROUP BY user_id
ip_address to contain the address of the most recent login for each user. But it will actually contain any of the addresses they logged in from, not the one from the row with
MAX(timestamp). See SQL Select only rows with Max Value on a Column for the correct way to do that.
The functional dependency exception is typically useful with joins.
SELECT u.user_id, u.user_name, MAX(l.timestamp) FROM users AS u JOIN user_logins AS l ON u.user_id = l.user_id GROUP BY u.user_id
user_id is the primary key of the
users table, it uniquely determines
user_name, so it's not necessary to list that explicitly in