Description:
When I run the following SQL script, the number of rows of results returned by MySQL is 75, while the same script executed in MariaDB, OceanBase, and TiDB returns 70 rows of results.
database11> SELECT DISTINCT t36.c0, t0.c0 FROM t0 INNER JOIN t36 ON ((t0.c0)AND(t0.c0))
[2025-07-18 22:40:19] 75 rows retrieved starting from 1 in 1 s 254 ms (execution: 1 s 226 ms, fetching: 28 ms)
database11> (SELECT DISTINCT t36.c0, t0.c0 FROM t0 LEFT JOIN t36 ON ((t0.c0)AND(t0.c0)))
INTERSECT
(SELECT DISTINCT t36.c0, t0.c0 FROM t0 RIGHT JOIN t36 ON ((t0.c0)AND(t0.c0)))
[2025-07-18 22:40:21] 75 rows retrieved starting from 1 in 545 ms (execution: 505 ms, fetching: 40 ms)
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.2.0 |
+-----------+
1 row in set (0.00 sec)
MariaDB [database5]> SELECT VERSION();
+--------------------------+
| VERSION() |
+--------------------------+
| 10.11.11-MariaDB-ubu2204 |
+--------------------------+
1 row in set (0.000 sec)
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.00 sec)
obclient(root@test)[database11]> select version();
+-------------------------------+
| version() |
+-------------------------------+
| 5.7.25-OceanBase_CE-v4.2.1.10 |
+-------------------------------+
1 row in set (0.002 sec)
------------------------------SQL-----------------------------------------
DROP DATABASE IF EXISTS database11;
CREATE DATABASE database11;
USE database11;
CREATE TABLE t0(c0 TEXT(176) NOT NULL );
CREATE TABLE t36 LIKE t0;
REPLACE INTO t0(c0) VALUES ('Z\nXFNB');
UPDATE t0 SET c0=t0.c0 WHERE 1903745906;
REPLACE INTO t36 VALUES (''), ('jG6)us9\''), ('0.18739477836362717');
INSERT IGNORE INTO t36 VALUES ('I2'), ('UY');
REPLACE INTO t0(c0) VALUES (')<');
INSERT INTO t0(c0) VALUES ('0.6204876460364737');
DELETE FROM t0 WHERE ((false)=((((CASE t0.c0 WHEN t0.c0 THEN -300558496 WHEN -1142662442 THEN t0.c0 ELSE t0.c0 END ))AND(-2.102737568E9))));
INSERT IGNORE INTO t36(c0) VALUES ('-1824796372') ON DUPLICATE KEY UPDATE c0=t36.c0;
REPLACE INTO t36 VALUES ('*W'), ('');
INSERT INTO t0(c0) VALUES ('BX') ON DUPLICATE KEY UPDATE c0=((-1.831121922E9)OR(t0.c0));
INSERT HIGH_PRIORITY INTO t0 VALUES ('*DP M]y*');
REPLACE INTO t36 VALUES ('[z7ﱍ');
INSERT INTO t0 VALUES ('1t');
INSERT IGNORE INTO t36 VALUES ('\'-');
ALTER TABLE t36 CHANGE c0 c0 TEXT NOT NULL ;
ALTER TABLE t36 CHANGE c0 c0 TEXT NOT NULL ;
INSERT IGNORE INTO t36(c0) VALUES ('\\\'L{'), ('-.'), (NULL);
INSERT INTO t0(c0) VALUES ('潟kN<u&1'), ('k4lsσ- '), ('-125271933');
REPLACE INTO t0(c0) VALUES ('Ydd|^ \rfg');
INSERT INTO t36(c0) VALUES ('DIQ崤Qxߣ)<');
INSERT INTO t0(c0) VALUES ('k4lsσ- ');
REPLACE INTO t36 VALUES ('iqn?');
INSERT INTO t36(c0) VALUES ('7R');
REPLACE INTO t36(c0) VALUES (' ');
REPLACE INTO t0 VALUES ('-1035263628'), ('1335458656');
INSERT INTO t0 VALUES ('doi7⨅3*'), (''), ('TUg}6') ON DUPLICATE KEY UPDATE c0=NULL;
REPLACE INTO t0 VALUES ('XM&^!');
SELECT DISTINCT t36.c0, t0.c0 FROM t0 INNER JOIN t36 ON ((t0.c0)AND(t0.c0));
(SELECT DISTINCT t36.c0, t0.c0 FROM t0 LEFT JOIN t36 ON ((t0.c0)AND(t0.c0)))
INTERSECT
(SELECT DISTINCT t36.c0, t0.c0 FROM t0 RIGHT JOIN t36 ON ((t0.c0)AND(t0.c0)));
Description: When I run the following SQL script, the number of rows of results returned by MySQL is 75, while the same script executed in MariaDB, OceanBase, and TiDB returns 70 rows of results. database11> SELECT DISTINCT t36.c0, t0.c0 FROM t0 INNER JOIN t36 ON ((t0.c0)AND(t0.c0)) [2025-07-18 22:40:19] 75 rows retrieved starting from 1 in 1 s 254 ms (execution: 1 s 226 ms, fetching: 28 ms) database11> (SELECT DISTINCT t36.c0, t0.c0 FROM t0 LEFT JOIN t36 ON ((t0.c0)AND(t0.c0))) INTERSECT (SELECT DISTINCT t36.c0, t0.c0 FROM t0 RIGHT JOIN t36 ON ((t0.c0)AND(t0.c0))) [2025-07-18 22:40:21] 75 rows retrieved starting from 1 in 545 ms (execution: 505 ms, fetching: 40 ms) How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.2.0 | +-----------+ 1 row in set (0.00 sec) MariaDB [database5]> SELECT VERSION(); +--------------------------+ | VERSION() | +--------------------------+ | 10.11.11-MariaDB-ubu2204 | +--------------------------+ 1 row in set (0.000 sec) mysql> select version(); +--------------------+ | version() | +--------------------+ | 8.0.11-TiDB-v7.5.1 | +--------------------+ 1 row in set (0.00 sec) obclient(root@test)[database11]> select version(); +-------------------------------+ | version() | +-------------------------------+ | 5.7.25-OceanBase_CE-v4.2.1.10 | +-------------------------------+ 1 row in set (0.002 sec) ------------------------------SQL----------------------------------------- DROP DATABASE IF EXISTS database11; CREATE DATABASE database11; USE database11; CREATE TABLE t0(c0 TEXT(176) NOT NULL ); CREATE TABLE t36 LIKE t0; REPLACE INTO t0(c0) VALUES ('Z\nXFNB'); UPDATE t0 SET c0=t0.c0 WHERE 1903745906; REPLACE INTO t36 VALUES (''), ('jG6)us9\''), ('0.18739477836362717'); INSERT IGNORE INTO t36 VALUES ('I2'), ('UY'); REPLACE INTO t0(c0) VALUES (')<'); INSERT INTO t0(c0) VALUES ('0.6204876460364737'); DELETE FROM t0 WHERE ((false)=((((CASE t0.c0 WHEN t0.c0 THEN -300558496 WHEN -1142662442 THEN t0.c0 ELSE t0.c0 END ))AND(-2.102737568E9)))); INSERT IGNORE INTO t36(c0) VALUES ('-1824796372') ON DUPLICATE KEY UPDATE c0=t36.c0; REPLACE INTO t36 VALUES ('*W'), (''); INSERT INTO t0(c0) VALUES ('BX') ON DUPLICATE KEY UPDATE c0=((-1.831121922E9)OR(t0.c0)); INSERT HIGH_PRIORITY INTO t0 VALUES ('*DP M]y*'); REPLACE INTO t36 VALUES ('[z7ﱍ'); INSERT INTO t0 VALUES ('1t'); INSERT IGNORE INTO t36 VALUES ('\'-'); ALTER TABLE t36 CHANGE c0 c0 TEXT NOT NULL ; ALTER TABLE t36 CHANGE c0 c0 TEXT NOT NULL ; INSERT IGNORE INTO t36(c0) VALUES ('\\\'L{'), ('-.'), (NULL); INSERT INTO t0(c0) VALUES ('潟kN<u&1'), ('k4lsσ- '), ('-125271933'); REPLACE INTO t0(c0) VALUES ('Ydd|^ \rfg'); INSERT INTO t36(c0) VALUES ('DIQ崤Qxߣ)<'); INSERT INTO t0(c0) VALUES ('k4lsσ- '); REPLACE INTO t36 VALUES ('iqn?'); INSERT INTO t36(c0) VALUES ('7R'); REPLACE INTO t36(c0) VALUES (' '); REPLACE INTO t0 VALUES ('-1035263628'), ('1335458656'); INSERT INTO t0 VALUES ('doi7⨅3*'), (''), ('TUg}6') ON DUPLICATE KEY UPDATE c0=NULL; REPLACE INTO t0 VALUES ('XM&^!'); SELECT DISTINCT t36.c0, t0.c0 FROM t0 INNER JOIN t36 ON ((t0.c0)AND(t0.c0)); (SELECT DISTINCT t36.c0, t0.c0 FROM t0 LEFT JOIN t36 ON ((t0.c0)AND(t0.c0))) INTERSECT (SELECT DISTINCT t36.c0, t0.c0 FROM t0 RIGHT JOIN t36 ON ((t0.c0)AND(t0.c0)));