Literal values character set introducer and COLLATE clause support.

[winkyao]

MySQL support bellow syntax but TiDB not:

[_charset_name]'string' [COLLATE collation_name]
[_charset_name] b'val' [COLLATE collation_name]
[_charset_name] X'val' [COLLATE collation_name]

Example:

SELECT _latin1'string';
SELECT _binary'string';
SELECT _utf8'string' COLLATE utf8_danish_ci;

SELECT _latin1 X'4D7953514C';
SELECT _utf8 0x4D7953514C COLLATE utf8_danish_ci;

SELECT _latin1 b'1000001';
SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;

and use N to create a string in the national character set. these statements are equivalent:

SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';

[morgo]

TiDB now supports most of these. The following still produce errors:

MySQL [test]> SELECT _latin1 X'4D7953514C';
ERROR 1105 (HY000): line 1 column 28 near "" (total length 28)
MySQL [test]> SELECT _utf8 0x4D7953514C COLLATE utf8_danish_ci;
ERROR 1105 (HY000): line 1 column 25 near " COLLATE utf8_danish_ci" (total length 48)
MySQL [test]> SELECT _latin1 b'1000001';
ERROR 1105 (HY000): line 1 column 25 near "" (total length 25)
MySQL [test]> SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;
ERROR 1105 (HY000): line 1 column 22 near " COLLATE utf8_danish_ci" (total length 45)

[wjhuang2016]

@morgo It seems that we can close this issue now.

[morgo]

Confirming all these cases are fixed:

mysql> SELECT _latin1'string';
+--------+
| string |
+--------+
| string |
+--------+
1 row in set (0.00 sec)

mysql> SELECT _binary'string';
+--------+
| string |
+--------+
| string |
+--------+
1 row in set (0.00 sec)

mysql> SELECT _utf8'string' COLLATE utf8_danish_ci;
+--------------------------------------+
| _utf8'string' COLLATE utf8_danish_ci |
+--------------------------------------+
| string                               |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT _latin1 X'4D7953514C';
+-----------------------+
| _latin1 X'4D7953514C' |
+-----------------------+
| MySQL                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT _utf8 0x4D7953514C COLLATE utf8_danish_ci;
+-------------------------------------------+
| _utf8 0x4D7953514C COLLATE utf8_danish_ci |
+-------------------------------------------+
| MySQL                                     |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT _latin1 b'1000001';
+--------------------+
| _latin1 b'1000001' |
+--------------------+
| A                  |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;
+----------------------------------------+
| _utf8 0b1000001 COLLATE utf8_danish_ci |
+----------------------------------------+
| A                                      |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1868-g1a9852f85
Edition: Community
Git Commit Hash: 1a9852f85f7b90b013cafdd136260367203a29c0
Git Branch: release-5.0-rc
UTC Build Time: 2020-12-19 13:37:36
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

I am going to close this now. Thanks!