Selection on merge join could not find column

[tangenta]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1, t2;
create table t1 ( id bigint primary key, a datetime);
create table t2 (id bigint primary key, b datetime);
insert into t1 values(1,'2022-02-06 00:03:35');
insert into t2 values(1,'2021-02-06 00:03:35');
insert into t2 values(3,'2021-02-07 00:03:35');
select * from( select ifnull(t2.b,t1.a) abc from t1 left join t2 on t2.id=t1.id) a where a.abc >='2022-02-07 00:00:00';

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

Empty set.

3. What did you see instead (Required)

ERROR 1105 (HY000): Can't find column Column#5 in schema Column: [test.t1.a,test.t2.b] Unique key: []

4. What is your TiDB version? (Required)

16cd0e2

[tangenta]

The problem happens when PDD(predicate-push-down) optimizer trying to substitute a.abc in where clause with ifnull(t2.b,t1.a), for example:

select * from (select ifnull(t2.b,t1.a) abc from t1 left join t2 on t2.id=t1.id) a where a.abc >='2022-02-07 00:00:00';

is optimized to

select * from (select ifnull(t2.b,t1.a) from t1 left join t2 on t2.id=t1.id where ifnull(t2.b,t1.a) >= '2022-02-07 00:00:00') tmp;

When the new collation is enabled, the strictness of the collation should be also considered before the substitution(See checkCollationStrictness). Because the derived charset of ifnull([datetime], [datetime]) is utf8mb4 instead of binary, this check does not passed. Thus, the substitution is failed. However, this failure is not aware by PDD optimizer, the predicate is still pushed down.

#30967 solves this problem.

[River2000i]

maybe found a similar case

CREATE TABLE t (a MEDIUMINT NOT NULL AUTO_INCREMENT primary key,b varbinary(20));
select a from t  group by a having not ifnull(min(null) = max(null), min(b));

sql exec

mysql> CREATE TABLE t (a MEDIUMINT NOT NULL AUTO_INCREMENT primary key,b varbinary(20));
Query OK, 0 rows affected (0.60 sec)

mysql> select a from t  group by a having not ifnull(min(null) = max(null), min(b));
ERROR 1105 (HY000): Can't find column Column#3 in schema Column: [aggregation1.t.a,aggregation1.t.b] Unique key: [[aggregation1.t.a]]

when i change column b type varbinary(20), such as varbinary(20) ->double or int
there is no error

mysql> CREATE TABLE t1 (a MEDIUMINT NOT NULL AUTO_INCREMENT primary key,b int);
Query OK, 0 rows affected (0.58 sec)

mysql> select a from t1  group by a having not ifnull(min(null) = max(null), min(b));
Empty set (0.05 sec)

[qw4990]

Fixed by #37992