tidb
tidb copied to clipboard
pingcap
Selection on merge join could not find column
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)
16cd0e260
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.
https://github.com/pingcap/tidb/pull/30967 solves this problem.
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)
