SET sql_mode=DEFAULT is not setting the value for session , until global value is set to DEFAULT

[ramanich1]

Bug Report

---

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set sql_mode=DEFAULT;
select @@sql_mode;
set global sql_mode=DEFAULT;
select @@sql_mode;
set sql_mode=DEFAULT;
select @@sql_mode;
set sql_mode='DEFAULT';  
--- String 'DEFAULT' should not be allowed

2. What did you expect to see? (Required)

mysql> set sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set global sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode='DEFAULT';
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'DEFAULT'

3. What did you see instead (Required)

mysql> set sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)
mysql> set global sql_mode=DEFAULT;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
mysql> set sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode='DEFAULT';
Query OK, 0 rows affected (0.01 sec)

4. What is your TiDB version? (Required)

+-------------------------+--------------------------------------------------------------------------+
| Variable_name           | Value                                                                    |
+-------------------------+--------------------------------------------------------------------------+
| innodb_version          | 5.6.25                                                                   |
| protocol_version        | 10                                                                       |
| tidb_analyze_version    | 2                                                                        |
| tidb_row_format_version | 2                                                                        |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                                                    |
| version                 | 5.7.25-TiDB-v5.2.2                                                       |
| version_comment         | TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible |
| version_compile_machine | x86_64                                                                   |
| version_compile_os      | osx10.8                                                                  |
+-------------------------+--------------------------------------------------------------------------+

[morgo]

This looks to be a bug caused by a misunderstanding of this text:

They are actually two separate examples.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.