Unexpected values appeared in select query statements that should logically imply each other

[Okakura-Tenshin]

Bug Report

In theory,the result of sql2(distinct) ∈ the result of sql1

however,in sql2 any return couldn't we find after adding >,which seems like a logical bug

1. Minimal reproduce step (Required)

’‘’create table

CREATE TABLE table_3_utf8_undef (
  `col_decimal(40, 20)_key_unsigned` DECIMAL(40, 20) UNSIGNED,
  `col_char(20)_key_signed` CHAR(20),
  PRIMARY KEY (`col_decimal(40, 20)_key_unsigned`),
  KEY (`col_char(20)_key_signed`)
) CHARACTER SET utf8;
INSERT INTO table_3_utf8_undef (`col_decimal(40, 20)_key_unsigned`, `col_char(20)_key_signed`) VALUES
(1, '3	'),
(12.991, '1'),
(47.1515, 'w');

'''sql1

SELECT 
      (!`f5`) AS `f1`
    FROM 
      (SELECT `col_decimal(40, 20)_key_unsigned` AS `f5`
       FROM `table_3_utf8_undef` 
      ) AS `t1`
    HAVING 
       ((`f1`) = ANY (SELECT `col_char(20)_key_signed` FROM `table_3_utf8_undef` )); 

'''sql2

SELECT 
      (!`f5`) AS `f1`
    FROM 
      (SELECT `col_decimal(40, 20)_key_unsigned` AS `f5`
       FROM `table_3_utf8_undef` 
      ) AS `t1`
    HAVING 
      ((`f1`) >= ANY (SELECT `col_char(20)_key_signed` FROM `table_3_utf8_undef` ));    

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

'''sql1

f1 
----
  0 
  0 
  0 
(3 rows)

'''sql2
some value instead of nothing

3. What did you see instead (Required)

'''sql2

(0 rows)

4. What is your TiDB version? (Required)

8.0.11-TiDB-v8.0.0

Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue?

[yibin87]

Duplicated with #50785

[yibin87]

/close

[ti-chi-bot]

@yibin87: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.