Bug #119722 Inconsistant result when executing functionally equivalent queries

• Submitted: 19 Jan 18:11
• Reporter: Jiyuan Li
• Status: Open
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 9.3.0-cluster
• OS: Ubuntu
• CPU Architecture: Any

[19 Jan 18:11] Jiyuan Li

Description:
Functionally equivalent queries return different results. 

How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL  NULL COMMENT 'asdf'  UNIQUE KEY  STORAGE DISK) ;
ALTER TABLE t0 FORCE, ENABLE KEYS, PACK_KEYS 0, RENAME t2, CHECKSUM 0, STATS_AUTO_RECALC 1, DELAY_KEY_WRITE 0, ALGORITHM DEFAULT;

INSERT HIGH_PRIORITY IGNORE INTO t2(c0) VALUES(0.7592617935932494);
INSERT IGNORE INTO t2(c0) VALUES(NULL);

SELECT t2.c0 AS ref0 FROM t2;
+------+
| ref0 |
+------+
| NULL |
|    1 |
+------+
2 rows in set (0.00 sec)

SELECT ALL t2.c0 AS ref0 FROM t2 WHERE (COALESCE(t2.c0, NULL, t2.c0, NULL)) && ((+ ((0.8148988380593897) IN (t2.c0)))) UNION ALL SELECT ALL t2.c0 AS ref0 FROM t2 WHERE (! ((COALESCE(t2.c0, NULL, t2.c0, NULL)) && ((+ ((0.8148988380593897) IN (t2.c0)))))) UNION ALL SELECT ALL t2.c0 AS ref0 FROM t2 WHERE ((COALESCE(t2.c0, NULL, t2.c0, NULL)) && ((+ ((0.8148988380593897) IN (t2.c0))))) IS UNKNOWN;
+------+
| ref0 |
+------+
|    1 |
|    1 |
| NULL |
+------+
3 rows in set, 4 warnings (0.01 sec)