Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 关于sql_mode里的ONLY_FULL_GROUP_BY
[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] 7.1
[Encountered Problem: Problem Phenomenon and Impact]
Reference
SQL Mode | PingCAP Documentation Center
By using select @@sql_mode, you can find
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
The documentation states
ONLY_FULL_GROUP_BY If the columns appearing in GROUP BY do not appear in SELECT, HAVING, ORDER BY, this SQL is invalid because the columns not in GROUP BY being queried and displayed do not conform to normal phenomena (supported)
Tested several cases and found all returned normally, such as:
select loc_name, MAX(loc_type) from loc l group by l.loc_id;
select l.loc_id, loc_name, MAX(loc_type) from loc l group by l.loc_id;
Which one is considered invalid?
Do I need to reconnect after changing the variable for it to take effect?
I encountered an error when executing SQL similar to yours below.
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'battleLog.battlelog.datetime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Is this a session parameter or a global parameter?
I haven’t changed it, it’s just the default.
By default, it hasn’t been changed.
I also think it’s illegal, but it doesn’t seem to be effective.
This parameter can be turned on and off, right?
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
What you queried should be enabled by default, it should be at the GLOBAL level. Check the SESSION level again?
The configuration is fine.
In production, I have also encountered this issue. If the corresponding column does not appear in the group by clause, it will report an error indicating sql_mode=only_full_group_by.
I’ll try it in my environment.
I got an error prompt here.
What version are you using?
I’m on 5.7.25-TiDB-v7.1.1
In my case, it’s the opposite. I have this configuration but no errors are reported.
Tested 5.7.25-TiDB-v7.1.0
Testing with 5.7.25-TiDB-v7.1.0 will result in an error.
Your table doesn’t just have loc_id and loc_name corresponding one-to-one, right? Try grouping by a different field.
It will definitely report an error. You can try setting it before the query statement.
Mine also reported an error, it’s better to write it more standard.
The image you provided is not accessible. Please provide the text content that needs to be translated.
|
username: zhanggame1
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: