MySQL Bugs: #105015: The variable declared by the current_date function does not respect the charset

Bug #105015	The variable declared by the current_date function does not respect the charset
Submitted:	23 Sep 2021 8:51	Modified:	24 Sep 2021 15:13
Reporter:	Marco Cristofanilli	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	current_date

Description:
The variable declared by the current_date function does not respect the charset configured in character_set_connection and collation_connection, but always uses the latin1 charset (implicit)

If the charset is declared in explicit mode, everything works properly.

How to repeat:
=== Configuration ===

mysql> show variables like "%char%";
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0,00 sec)

mysql> show variables like "%collation_connection%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
1 row in set (0,00 sec)

== String work well == 

mysql> set @string = "Test charset";
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT @string collate utf8mb4_unicode_ci;
+------------------------------------+
| @string collate utf8mb4_unicode_ci |
+------------------------------------+
| Test charset                       |
+------------------------------------+
1 row in set (0,00 sec)

== Current_date not work - problem ==

mysql> set @date = current_date;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT @date collate utf8mb4_unicode_ci;
ERROR 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'

== current_date work well with exclicit declaration ==

mysql> set @date = current_date collate utf8mb4_unicode_ci;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT @date collate utf8mb4_unicode_ci;
+----------------------------------+
| @date collate utf8mb4_unicode_ci |
+----------------------------------+
| 2021-09-23                       |
+-----------------------------

Hi Mr. Cristofanilli,

Thank you for your bug report.

However, this is not a bug.

Simply, current_date() returns the result in Latin1. And you can not collate Latin1 into UTF, after you have assigned that value to user-defined variable. Simply, user variable is defined fully by the output from the right hand side !!!!!

However, if you first collate the output from current_date() then assign it to user-defined variable it will work.

So, this is not a bug, but if you would like to make it a feature request, we would be very much willing to verify it as  a nice feature request.

Thanks, I turned the request into a feature request, it would definitely be useful for current_date() to return the result based on the character_set_connection and collation_connection variables

Hi Mr. Cristofanilli,

But, that definition of the feature request is unreal.

The only feature request that is acceptable is the one where the expression of this type:

 SELECT @date collate utf8mb4_unicode_ci;

would change a character set and a collation of the user variable of the DATE/DATETIME type.

If you agree with this, we can verify it as such.

I don't understand why the current_date() function always returns with Latin1 encoding even if all user variables have other chaersets defined.

This is due to the very specific set of characters that these functions are returning.

Okay, clear.

If you think it's interesting, it might be useful to impementation of

SELECT @date collate utf8mb4_unicode_ci;

Thanks

Hi Mr. Cristofanilli,

You are truly welcome.

Verified as a feature request.