Bug #119821 MySQL and TiDB return different results

• Submitted: 31 Jan 16:25
• Modified: 1 Feb 12:58
• Reporter: Chunling Qin
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[31 Jan 16:25] Chunling Qin

Description:
Results from mysql is not collect:

mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE (t2.c0) || (LEAST(0.6082575586348781, 'i_t헺', "6oSE]o", t2.c0, t2.c0, t2.c0));
+----------------------------------------------------------------------+
| ref0                                                                 |
+----------------------------------------------------------------------+
| 00000000000000000000000000000000000000000000000000000000000000000000 |
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> select version();
+--------------+
| version()    |
+--------------+
| 8.0.45-debug |
+--------------+
1 row in set (0.00 sec)

Results from Tidb:

mysql> 
mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE (t2.c0) || (LEAST(0.6082575586348781, 'i_t헺', "6oSE]o", t2.c0, t2.c0, t2.c0));
Empty set (0.00 sec)

mysql> 
mysql> select version();
+------------------------------------------+
| version()                                |
+------------------------------------------+
| 8.0.11-TiDB-v8.4.0-this-is-a-placeholder |
+------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  c0 TINYINT(68) ZEROFILL NULL
);

REPLACE DELAYED INTO t2(c0) VALUES(0.3470436006470644); 

SELECT t2.c0 AS ref0 FROM t2;

SELECT t2.c0 AS ref0 FROM t2 WHERE (t2.c0) || (LEAST(0.6082575586348781, 'i_t헺', "6oSE]o", t2.c0, t2.c0, t2.c0));

[31 Jan 16:42] Chunling Qin

The following case seems hitting the same issue.
drop table t2;
CREATE TABLE t2 (c0 BIGINT ZEROFILL);
-- INSERT IGNORE INTO t2(c0) VALUES(-8.88542785E8);
INSERT IGNORE INTO t2(c0) VALUES('.0*');

-- 基础查询
SELECT t2.c0 AS ref0 FROM t2;

SELECT t2.c0 AS ref0 FROM t2 WHERE (((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0);

[31 Jan 21:57] Roy Lyseng

The result here is correct, so this is not a bug.
Note that if at least one argument to LEAST is a string, all arguments are converted to string.

[1 Feb 12:58] Chunling Qin

drop table t2;
CREATE TABLE t2 (c0 BIGINT ZEROFILL);
-- INSERT IGNORE INTO t2(c0) VALUES(-8.88542785E8);
INSERT IGNORE INTO t2(c0) VALUES('.0*');

--query1:

SELECT t2.c0 AS ref0 FROM t2;

--query2:
SELECT t2.c0 AS ref0 FROM t2 WHERE (((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0);

--query3:
SELECT t2.c0 AS ref0 FROM t2 WHERE not ((((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0));

--query4
SELECT t2.c0 AS ref0 FROM t2 WHERE ((((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0)) is null;

From SQLancer's TLP testing principle, the results of query1 should be identical to the combined results of query2, query3, and query4.
In mysql, query1 returns 1 row, but query2, query3 and query4 all return null.

```
mysql> SELECT t2.c0 AS ref0 FROM t2;
+----------------------+
| ref0                 |
+----------------------+
| 00000000000000000000 |
+----------------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE (((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0);
Empty set, 1 warning (0.00 sec)

mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE not ((((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0));
Empty set, 1 warning (0.00 sec)

mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE ((((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0)) is null;
Empty set, 1 warning (0.00 sec)

```

Result from tidb:
```
mysql> SELECT t2.c0 AS ref0 FROM t2;
+------+
| ref0 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE (((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0);
+------+
| ref0 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE not ((((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0));
Empty set (0.00 sec)

mysql> SELECT t2.c0 AS ref0 FROM t2 WHERE ((((CASE t2.c0 WHEN t2.c0 THEN t2.c0 ELSE 1108750281 END)) LIKE (CAST(t2.c0 AS SIGNED))) || (t2.c0)) is null;
Empty set (0.00 sec)

```