Here is the problem from leetcode:

Table: Users

 --------------- --------- 
| Column Name   | Type    |
 --------------- --------- 
| id            | int     |
| name          | varchar |
 --------------- --------- 

id is the primary key for this table. name is the name of the user.

Table: Rides

 --------------- --------- 
| Column Name   | Type    |
 --------------- --------- 
| id            | int     |
| user_id       | int     |
| distance      | int     |
 --------------- --------- 

id is the primary key for this table. user_id is the id of the user who traveled the distance "distance".

Write an SQL query to report the distance traveled by each user.

Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order.

The query result format is in the following example.

Example 1:

Input: Users table:

 ------ ----------- 
| id   | name      |
 ------ ----------- 
| 1    | Alice     |
| 2    | Bob       |
| 3    | Alex      |
| 4    | Donald    |
| 7    | Lee       |
| 13   | Jonathan  |
| 19   | Elvis     |
 ------ ----------- 

Rides table:

 ------ ---------- ---------- 
| id   | user_id  | distance |
 ------ ---------- ---------- 
| 1    | 1        | 120      |
| 2    | 2        | 317      |
| 3    | 3        | 222      |
| 4    | 7        | 100      |
| 5    | 13       | 312      |
| 6    | 19       | 50       |
| 7    | 7        | 120      |
| 8    | 19       | 400      |
| 9    | 7        | 230      |
 ------ ---------- ---------- 

Output:

 ---------- -------------------- 
| name     | travelled_distance |
 ---------- -------------------- 
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  |
 ---------- -------------------- 

Explanation: Elvis and Lee traveled 450 miles, Elvis is the top traveler as his name is alphabetically smaller than Lee. Bob, Jonathan, Alex, and Alice have only one ride and we just order them by the total distances of the ride. Donald did not have any rides, the distance traveled by him is 0.

Below is my MySQL query:

select
    u.name,
    coalesce(sum(r.distance), 0) as travelled_distance
from users u
left join rides r on u.id = r.user_id
group by u.id
order by travelled_distance desc, u.name asc; 

I ran this query successfully. But I find that the u.name column is neither an aggregation function nor in the group by clause, which is required when using the group by clause. Why there is no error here? I searched online and learnt that some versions of MySQL can still run this scenario without an error. Can someone clarify this for me? (P.S. The reason for using group by u.id is because there can be duplicate names in the users table in some test cases).

CodePudding user response：

There are two reasons you may not be seeing an error when selecting u.name, one good and one bad.

The bad reason would be if you do not have the ONLY_FULL_GROUP_BY sql_mode enabled; this is on by default but if you have upgraded an old server that had sql_mode set in a config file or chose to set sql_mode it may not be. You can do select @@SESSION.sql_mode to see if it is enabled in your current session. If this is the case, unless you have a lot of existing queries that would need to be updated, you should enable ONLY_FULL_GROUP_BY. You would then need to modify this query (either adding u.name to the group by list, which should be harmless if u.id is a primary key, or selecting ANY_VALUE(u.name) or MAX(u.name) or the like.

The good reason would be if you are using a recent mysql version and u.id is a unique or primary key; then, since you are grouping by u.id, there can only be one u.name value and there is no ambiguity in what is selected. This is called u.name being "functionally dependent" on u.id, and is allowed in current mysql versions. (It is not yet allowed in mariadb, see https://jira.mariadb.org/browse/MDEV-11588.)

CodePudding user response：

you can MAX the u.name or add it to the GROUP By

select
    u.name,
    coalesce(sum(r.distance), 0) as travelled_distance
from users u
left join rides r on u.id = r.user_id
group by u.id,u.name
order by travelled_distance desc, u.name asc; 

select
    MAX(u.name) name,
    coalesce(sum(r.distance), 0) as travelled_distance
from users u
left join rides r on u.id = r.user_id
group by u.id
order by travelled_distance desc, name asc; 

fiddle