The result of association sub query is not correct

[lilinghai]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1.txt
collation is on
download and source the 1.txt
execute the sql

select count(*) from table_75_latin1_undef as t1 where t1. `col_binary(20)_key_signed` <= any (select `col_varbinary(20)_key_signed` from table_100_utf8mb4collate_utf8mb4_general_ci_undef as t2 where (case when t1. `col_varbinary(20)_key_signed` not between t1. `col_char(20)_undef_signed` and t2. `col_varchar(20)_key_signed` then t1. `col_varbinary(20)_key_signed` end) < (if(t1. `col_varchar(20)_undef_signed` not between "B" and t2. `col_varchar(20)_key_signed`,"Abc",t2. `col_char(20)_key_signed`))) order by t1. `col_binary(20)_key_signed`;
/*
+----------------------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows | task      | access object | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14                          | 1.00    | root      |               | Column#31                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| └─Sort_15                              | 1.00    | root      |               | rs2.table_75_latin1_undef.col_binary(20)_key_signed                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|   └─HashAgg_17                         | 1.00    | root      |               | funcs:count(1)->Column#31, funcs:firstrow(rs2.table_75_latin1_undef.col_binary(20)_key_signed)->rs2.table_75_latin1_undef.col_binary(20)_key_signed                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     └─Apply_20                         | 60.00   | root      |               | CARTESIAN inner join, other cond:or(le(rs2.table_75_latin1_undef.col_binary(20)_key_signed, Column#27), if(ne(Column#28, 0), NULL, 0))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|       ├─TableReader_23(Build)          | 60.00   | root      |               | data:Selection_22                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|       │ └─Selection_22                 | 60.00   | cop[tikv] |               | if(isnull(rs2.table_75_latin1_undef.col_binary(20)_key_signed), NULL, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       │   └─TableFullScan_21           | 75.00   | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|       └─Selection_24(Probe)            | 0.80    | root      |               | ne(Column#29, 0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|         └─StreamAgg_39                 | 1.00    | root      |               | funcs:max(Column#37)->Column#27, funcs:sum(Column#38)->Column#28, funcs:count(Column#39)->Column#29                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           └─TableReader_40             | 1.00    | root      |               | data:StreamAgg_28                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|             └─StreamAgg_28             | 1.00    | cop[tikv] |               | funcs:max(rs2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varbinary(20)_key_signed)->Column#37, funcs:sum(isnull(rs2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varbinary(20)_key_signed))->Column#38, funcs:count(1)->Column#39                                                                                                                                                                                                                                                                                                                                                                                                        |
|               └─Selection_38           | 80.00   | cop[tikv] |               | lt(case(not(and(ge(rs2.table_75_latin1_undef.col_varbinary(20)_key_signed, rs2.table_75_latin1_undef.col_char(20)_undef_signed), le(rs2.table_75_latin1_undef.col_varbinary(20)_key_signed, rs2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varchar(20)_key_signed))), rs2.table_75_latin1_undef.col_varbinary(20)_key_signed), if(not(and(ge(rs2.table_75_latin1_undef.col_varchar(20)_undef_signed, "B"), le(rs2.table_75_latin1_undef.col_varchar(20)_undef_signed, rs2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varchar(20)_key_signed))), "Abc", rs2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_char(20)_key_signed)) |
|                 └─TableFullScan_37     | 100.00  | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+----------------------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
/*

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

23

3. What did you see instead (Required)

24

4. What is your TiDB version? (Required)

Release Version: v5.2.0-alpha-586-g1c90b8e0b
Edition: Community
Git Commit Hash: 1c90b8e
Git Branch: master
UTC Build Time: 2021-08-11 10:18:44
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[XuHuaiyu]

The result differs between tidb and mysql when collate is disabled is because of that:
The default collate of tidb is case-sensitive, but the default collate of mysql is case-insensitive.
After we changing the CHARSET=latin1 COLLATE=latin1_bin to CHARSET=utf8mb4 COLLATE=utf8mb4_bin, we can get the same result from tidb and mysql.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions