The "varchar" type column flags are not compatible with MySQL

[zimulala]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

CREATE TABLE t (
    ->  c varchar(120) DEFAULT NULL
    -> ) CHARSET=utf8 COLLATE=utf8_bin;

 select c from t;

2. What did you expect to see?

3. What did you see instead?

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   Release Version: v2.1.18-32-g8ddd007c7-dirty
   Git Commit Hash: 8ddd007
   Git Branch: heads/zimuxia/drop-col

[ghost]

Confirming that I can still reproduce this in master:

DROP TABLE IF EXISTS t;
CREATE TABLE t (
 c varchar(120) DEFAULT NULL
) CHARSET=utf8 COLLATE=utf8_bin;

SELECT c FROM t;

..

mysql> SELECT c FROM t;
Field   1:  `c`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  `t`
Type:       VAR_STRING
Collation:  utf8_bin (83)
Length:     360
Max_length: 0
Decimals:   0
Flags:      

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-771-gca41972fb
Edition: Community
Git Commit Hash: ca41972fbac068c8a5de107d9075f09ac68842ac
Git Branch: master
UTC Build Time: 2020-07-14 02:41:21
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)


vs mysql:

mysql [localhost:5731] {msandbox} (test) > SELECT c FROM t;
Field   1:  `c`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  `t`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     360
Max_length: 0
Decimals:   0
Flags:      BINARY 

[blacktear23]

The simple fix is if we meet collation in [charset.CollationUTF8, charset.CollationUTF8MB4, charset.CollationASCII, charset.CollationLatin1], just add mysql.BinaryFlag to column's Flag. But due to #9689 client will get NOT_NULL BINARY as column type result. And this is also not compatible with MySQL.

[wjhuang2016]

MySQL's behavior is strange, and we decide not to fix it unless it really affect the usage.