coercibility is wrong for subquery

[wjhuang2016]

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

[yudongusa]

Assign this to @wjhuang2016 who is working on #30750

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.