MySQL Bugs: #98014: Lossy implicit conversion in conditional breaks ONLY_FULL_GROUP

Bug #98014	Lossy implicit conversion in conditional breaks ONLY_FULL_GROUP_BY
Submitted:	18 Dec 2019 0:59	Modified:	8 Feb 2020 19:21
Reporter:	Joshua Varner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using a lossy implicit conversion in an equality conditional for a non-aggregated column should cause an ONLY_FULL_GROUP_BY error, but it doesn't.

How to repeat:
mysql> SELECT User, Host, COUNT(*) FROM mysql.user WHERE Host = 0 GROUP BY 1;
+---------------+-----------+----------+
| User          | Host      | COUNT(*) |
+---------------+-----------+----------+
| mysql.session | localhost |        1 |
| mysql.sys     | localhost |        1 |
| root          | ::1       |        3 |
+---------------+-----------+----------+
3 rows in set, 6 warnings (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> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.28-log |
+------------+
1 row in set (0.00 sec)

Hello Mr. Varner,

Thank you for your bug report.

However , this is definitely not a bug. You do not seem to have read entire contents of our 5.7 Reference Manual.

There, it clearly says:

"
MySQL 5.7 and later also permits a non-aggregate column not named in a GROUP BY clause when ONLY_FULL_GROUP_BY SQL mode is enabled, provided that this column is limited to a single value.

"

This is a clear case in your example.

Not a bug.

I agree that this is not a bug, at least ONLY_FULL_GROUP_BY works as it is supposed to.

The main problem is that even with strict mode enabled, the equality "host = 0" truncates the host values to a numeric value. This is also not a bug, because strict mode is not effective for SELECT statements, only for data change statements, and this is documented behaviour.

There is a simple workaround for this problem: Rewrite the condition to use consistent data types, ie.

  host = ""

This problem would also have been avoided if strict mode was in effect for SELECT statements.

Sinisa: I had read the manual that you referenced.

I understand that it's allowing the group by because of the single-value condition. The key part, though, is that it's not actually yielding a single-value group because of the implicit type conversion. This, to me, seems like a bug.

If Oracle doesn't consider this a bug, perhaps it's at LEAST worth updating the documentation to say that ONLY_FULL_GROUP_BY does not guarantee actual groupings in conditions like this.

Hi Mr. Varner,

All explanations that you need are documented in the above comment by oru esteemed developer, Mr. Lyseng.