Stability and Compatibility issue on SELECT MICROSECOND(CURRENT_TIMESTAMP(6))=MICROSECOND(UTC_TIMESTAMP(6))

[espresso98]

Bug Report

1. Minimal reproduce step (Required)

SELECT MICROSECOND(CURRENT_TIMESTAMP(6))=MICROSECOND(UTC_TIMESTAMP(6));

2. What did you expect to see?

mysql> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+

mysql> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+

mysql> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+

mysql> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+

3. What did you see instead

tidb> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+

tidb> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+

tidb> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+

tidb> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+

4. What is your TiDB version?

tidb_version(): Release Version: v5.5.0-alpha-154-gc589ee547
Edition: Community
Git Commit Hash: c589ee5471e05430f7f888190780a27fddb9ce7a
Git Branch: master
UTC Build Time: 2022-01-19 21:56:30
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[morgo]

In MySQL this is stable because these functions use the sysvar TIMESTAMP which is cached for statement context duration. TiDB partially supports this:

tidb> SET timestamp=UNIX_TIMESTAMP('2021-01-01 01:01:01.000010');
Query OK, 0 rows affected (0.00 sec)

tidb> select current_timestamp(6), utc_timestamp(6);
+----------------------------+----------------------------+
| current_timestamp(6)       | utc_timestamp(6)           |
+----------------------------+----------------------------+
| 2021-01-01 01:01:01.000010 | 2021-01-01 08:01:01.000010 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

tidb> SELECT MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6));
+------------------------------------------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP(6))= MICROSECOND(UTC_TIMESTAMP(6)) |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

But It looks when the variable is not set, it's not caching for statement context correctly.