greatest() sql function uses connection collation and not collation of columns

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Enable the new collation framework first. Then use the following test case:

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
    id INT NOT NULL PRIMARY KEY auto_increment,
  `col_91` char(47) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `col_92` int(10) unsigned DEFAULT '2478966067',
  `col_97` char(32) COLLATE utf8mb4_bin NOT NULL
) collate utf8mb4_general_ci;

INSERT INTO `t2` VALUES 
(17,'UUtJeaV',497551109,'snRXXCZHBPW');

SET names utf8mb4 collate utf8mb4_bin;
-- correct:
SELECT
 id,
 greatest( col_92 , col_91 , col_97 ) as expr1
FROM t2 
ORDER BY id;

SET names utf8mb4 collate utf8mb4_general_ci;
-- uses collation from connection and not columns (incorrect)
SELECT
 id,
 greatest( col_92 , col_91 , col_97 ) as expr1
FROM t2 
ORDER BY id;

2. What did you expect to see? (Required)

mysql [localhost:8027] {msandbox} (test) > SELECT
    ->  id,
    ->  greatest( col_92 , col_91 , col_97 ) as expr1
    -> FROM t2 
    -> ORDER BY id;
+----+-------------+
| id | expr1       |
+----+-------------+
| 17 | snRXXCZHBPW |
+----+-------------+
1 row in set (0.00 sec)

mysql [localhost:8027] {msandbox} (test) > 
mysql [localhost:8027] {msandbox} (test) > SET names utf8mb4 collate utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8027] {msandbox} (test) > -- uses collation from connection and not columns (incorrect)
mysql [localhost:8027] {msandbox} (test) > SELECT
    ->  id,
    ->  greatest( col_92 , col_91 , col_97 ) as expr1
    -> FROM t2 
    -> ORDER BY id;
+----+-------------+
| id | expr1       |
+----+-------------+
| 17 | snRXXCZHBPW |
+----+-------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

tidb> -- correct:
tidb> SELECT
    ->  id,
    ->  greatest( col_92 , col_91 , col_97 ) as expr1
    -> FROM t2 
    -> ORDER BY id;
+----+-------------+
| id | expr1       |
+----+-------------+
| 17 | snRXXCZHBPW |
+----+-------------+
1 row in set (0.00 sec)

tidb> 
tidb> SET names utf8mb4 collate utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

tidb> -- uses collation from connection and not columns (incorrect)
tidb> SELECT
    ->  id,
    ->  greatest( col_92 , col_91 , col_97 ) as expr1
    -> FROM t2 
    -> ORDER BY id;
+----+---------+
| id | expr1   |
+----+---------+
| 17 | UUtJeaV |
+----+---------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.5.0-alpha-136-g50704075a
Edition: Community
Git Commit Hash: 50704075afa7c0e3f2aa1fc9a66f440884a8f3fe
Git Branch: master
UTC Build Time: 2022-01-18 00:29:17
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[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.