different types compare error

[ChenPeng2013]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

# case 1
use test;
drop table if exists t;
create table t(b enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') DEFAULT NULL, c decimal(40,20));
insert into t values('z', 19.18040000000000000000);
select * from t where t.b > t.c;

# case 2
drop table if exists t;
create table t(a bit(64), b double);
insert into t values(-21172, -11623);
select * from t where a < b;

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

# case 1
mysql> create table t(b enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') DEFAULT NULL, c decimal(40,20));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values('z', 19.18040000000000000000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t where t.b > t.c;
+------+-------------------------+
| b    | c                       |
+------+-------------------------+
| z    | 19.18040000000000000000 |
+------+-------------------------+
1 row in set (0.00 sec)

# case 2
mysql> drop table if exists t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t(a bit(64), b double);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(-21172, -11623);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t where a < b;
+----------+--------+
| a        | b      |
+----------+--------+
| �������L        | -11623 |
+----------+--------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

# case 1
mysql> create table t(b enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') DEFAULT NULL, c decimal(40,20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values('z', 19.18040000000000000000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t where t.b > t.c;
Empty set, 1 warning (0.00 sec)

# case 2
mysql> drop table if exists t;
Query OK, 0 rows affected (0.19 sec)

mysql> create table t(a bit(64), b double);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values(-21172, -11623);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t where a < b;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

master f6d445c and release-4.0 0fbe796

SIG slack channel

#sig-exec

Score

• 900

Mentor

• @XuHuaiyu

[lzmhhh123]

Duplicate of #17994

[newcworld]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@pengdaqian2020 You did not submit PR within 7 days, so give up automatically.

[zhaoxugang]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@zhaoxugang You did not submit PR within 7 days, so give up automatically.

[zhaoxugang]

/pick-up

[ti-challenge-bot]

Pick up success.

[wshwsh12]

I have a try case 2 in mysql8.0.22, the result is same with tidb. Should we keep the behavior same with the new version Mysql? @ChenPeng2013

MySQL [test]> select version();
+-----------+
| version() |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.000 sec)

MySQL [test]> drop table if exists t;
Query OK, 0 rows affected (0.006 sec)

MySQL [test]> create table t(a bit(64), b double);
Query OK, 0 rows affected (0.008 sec)

MySQL [test]> insert into t values(-21172, -11623);
Query OK, 1 row affected (0.001 sec)

MySQL [test]> select * from t where a < b;
Empty set (0.000 sec)

[ti-challenge-bot]

@zhaoxugang You did not submit PR within 7 days, so give up automatically.