coercibility is wrong for subquery #30748
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE `t` (
`a` char(10) DEFAULT NULL
);
CREATE TABLE `t1` (
`a` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL
);
insert into t values ("a");
insert into t1 values ("A");
select * from t where t.a = all (select a collate utf8mb4_general_ci from t1);
select * from t where t.a != any (select a collate utf8mb4_general_ci from t1);
select a as a_col from t where t.a <= all (select a collate utf8mb4_general_ci from t1);
select a as a_col from t where t.a <= any (select a collate utf8mb4_general_ci from t1);
select * from t where t.a = (select a collate utf8mb4_general_ci from t1);2. What did you expect to see? (Required)
mysql> select * from t where t.a = all (select a collate utf8mb4_general_ci from t1);
+------+
| a |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql> select * from t where t.a != any (select a collate utf8mb4_general_ci from t1);
Empty set (0.00 sec)
mysql> select a as a_col from t where t.a <= all (select a collate utf8mb4_general_ci from t1);
+------+
| a |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql> select a as a_col from t where t.a <= any (select a collate utf8mb4_general_ci from t1);
+------+
| a |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql> select * from t where t.a = (select a collate utf8mb4_general_ci from t1);
+------+
| a |
+------+
| a |
+------+
1 row in set (0.00 sec)
3. What did you see instead (Required)
mysql> select * from t where t.a = all (select a collate utf8mb4_general_ci from t1);
Empty set (0.00 sec)
mysql> select * from t where t.a != any (select a collate utf8mb4_general_ci from t1);
+------+
| a |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql> select a as a_col from t where t.a <= all (select a collate utf8mb4_general_ci from t1);
Empty set (0.00 sec)
mysql> select a as a_col from t where t.a <= any (select a collate utf8mb4_general_ci from t1);
Empty set (0.01 sec)
mysql> select * from t where t.a = (select a collate utf8mb4_general_ci from t1);
Empty set (0.00 sec)4. What is your TiDB version? (Required)
master