the query results for enum column is wrong

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
source  [test.txt](https://github.com/pingcap/tidb/files/8285072/test.txt);

alter table IDT_MULTI15863STROBJSTROBJ cache;
# please execute many times to reporduce
 select col1 from IDT_MULTI15863STROBJSTROBJ t1 where (select count(*) from IDT_MULTI15863STROBJSTROBJ t2 where t2.col1 in (t1.col1, "none")) > 1;

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

MySQL [test]> select col1 from IDT_MULTI15863STROBJSTROBJ t1 where (select count(*) from IDT_MULTI15863STROBJSTROBJ t2 where t2.col1 in (t1.col1, "none")) > 1;
+------+
| col1 |
+------+
...
+------+
58 rows in set (0.03 sec)

3. What did you see instead (Required)

MySQL [test]> select col1 from IDT_MULTI15863STROBJSTROBJ t1 where (select count(*) from IDT_MULTI15863STROBJSTROBJ t2 where t2.col1 in (t1.col1, "none")) > 1;
+------+
| col1 |
+------+
...
+------+
60 rows in set (0.03 sec)

4. What is your TiDB version? (Required)

Release Version: v6.0.0-alpha-122-g46c43fe
Edition: Community
Git Commit Hash: 46c43febcac0e24bc9f0872e93cf6125db69d93d
Git Branch: master
UTC Build Time: 2022-03-17 01:02:30
GoVersion: go1.17.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[tiancaiamao]

I've found the root cause.
When there is a Apply executor, it will Open / Close its children executor several times.
And when UnionScan->TableReader open and close for reuse, it's not fully clean the state.

This bug affects only table cache and temporary table.

[tiancaiamao]

The simplified reproduce case:

create table t (col enum('a', 'b', 'c')) default null;
insert into t values ('a'), ('b'), ('c'), (null), ('c');
alter table t cache;
select col from t t1 where (select count(*) from t t2 where t2.col = t1.col or t2.col =  'sdf') > 1;

Expect:

mysql> select col from t t1 where (select count(*) from t t2 where t2.col = t1.col or t2.col =  'sdf') > 1;
+------+
| col  |
+------+
| c    |
| c    |
+------+
2 rows in set (0.00 sec)

But got when repeat several times:

mysql> select col from t t1 where (select count(*) from t t2 where t2.col = t1.col or t2.col =  'sdf') > 1;
+------+
| col  |
+------+
| b    |
| c    |
| c    |
+------+
2 rows in set (0.00 sec)