Bug #107385 Different result of GT expression for binary type

• Submitted: 24 May 2022 9:41
• Modified: 24 May 2022 13:21
• Reporter: jiangtao guo
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.28
• OS: Any
• CPU Architecture: Any

[24 May 2022 9:41] jiangtao guo

Description:
Results are different when cast expr is added.(a is char type, and b is binary(20))

drop table if exists t1;drop table if exists t2;
create table t1(a char(20));
create table t2(b binary(20), c binary(20));
insert into t1 value('-1');
insert into t2 value(0x2D31, 0x67);
insert into t2 value(0x2D31, 0x73);
MySQL [test]> select a from t where a >= b;
Empty set (0.000 sec)

MySQL [test]> select a from t where cast(a as binary(20)) >= b;
+------+
| a    |
+------+
| -1   |
| -1   |
+------+
2 rows in set (0.000 sec)

How to repeat:
drop table if exists t1;drop table if exists t2;
create table t1(a char(20));
create table t2(b binary(20), c binary(20));
insert into t1 value('-1');
insert into t2 value(0x2D31, 0x67);
insert into t2 value(0x2D31, 0x73);
select a from t where a >= b;
select a from t where cast(a as binary(20)) >= b;

[24 May 2022 13:03] MySQL Verification Team

Hi Mr. guo,

Thank you for your bug report.

However, we are not able to repeat what you are reporting.

Simply, table `t` does not exist and the WHERE expressions and results  that you are reporting as wrong, are not found all in both tables,  t1 and t2.

Hence, we do not know how to repeat it.

We are waiting on your feedback.

[24 May 2022 13:08] jiangtao guo

Sorry, i pasted the wrong SQL. It should be:

drop table if exists t;
create table t(a char(20), b binary(20), c binary(20));
insert into t value('-1', 0x2D31, 0x67);
insert into t value('-1', 0x2D31, 0x73);
select a from t where a >= b;  // I think the where filter should be ture, just like the following one
select a from t where cast(a as binary(20)) >= b;

[24 May 2022 13:21] MySQL Verification Team

Hi Mr. guo,

Your last comment made much more sense then your initial report.

However, what you experience is expected behaviour. BINARY data type is not a CHAR-related data type. When two data types are totally incompatible, like BINARY and CHAR, then a common denominator is used, which in this case is a floating , real number data type. That is why your first condition is not matched.

However, if you convert CHAR type to BINARY then, comparison is made with BINARY to BINARY. When you convert '-1' to BINARY, you get 0x23D1, which satisfies >= (greater or equal) criteria.

Not a bug.