Description:
Hi, I found a logic bug in MySQL 9.6.0.
A query that should have returned an empty set actually returned 1.
This query will return an empty set when executed in other databases, such as MariaDB.
How to repeat:
-- create table
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` ( `c0` longtext COMMENT 'asdf' ) CHARSET=utf8mb4;
DROP TABLE IF EXISTS `t4`;
CREATE TABLE `t4` ( `c0` longtext COMMENT 'asdf' ) CHARSET=utf8mb4;
INSERT INTO `t4` VALUES ('0.8937270726098927'),(NULL);
-- query, expect:empty set, actual:{1}
SELECT DISTINCT
1 AS c0
FROM t2 AS ref_0
RIGHT JOIN t4 AS ref_1
ON ref_0.c0 = ref_1.c0
WHERE JSON_LENGTH(JSON_ARRAY(ref_0.c0, ref_1.c0))
MOD NULLIF(-(JSON_CONTAINS(JSON_OBJECT('k', ref_1.c0), JSON_OBJECT('k', ref_0.c0))), 0)
< -(CAST(-(COALESCE((-53) & (-82), CAST(-27 AS SIGNED))) AS SIGNED))
ORDER BY c0 DESC;
Description: Hi, I found a logic bug in MySQL 9.6.0. A query that should have returned an empty set actually returned 1. This query will return an empty set when executed in other databases, such as MariaDB. How to repeat: -- create table DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `c0` longtext COMMENT 'asdf' ) CHARSET=utf8mb4; DROP TABLE IF EXISTS `t4`; CREATE TABLE `t4` ( `c0` longtext COMMENT 'asdf' ) CHARSET=utf8mb4; INSERT INTO `t4` VALUES ('0.8937270726098927'),(NULL); -- query, expect:empty set, actual:{1} SELECT DISTINCT 1 AS c0 FROM t2 AS ref_0 RIGHT JOIN t4 AS ref_1 ON ref_0.c0 = ref_1.c0 WHERE JSON_LENGTH(JSON_ARRAY(ref_0.c0, ref_1.c0)) MOD NULLIF(-(JSON_CONTAINS(JSON_OBJECT('k', ref_1.c0), JSON_OBJECT('k', ref_0.c0))), 0) < -(CAST(-(COALESCE((-53) & (-82), CAST(-27 AS SIGNED))) AS SIGNED)) ORDER BY c0 DESC;