Description:
The same query returns an empty set without a hint, but returns 6 rows when `/*+ SET_VAR(optimizer_switch='materialization=off') */` is added. Merely toggling an optimizer switch changes both the result set , indicating a result inconsistency likely related to evaluation differences of `SOUNDS LIKE`, `IN`, and `>= SOME` under the subquery materialization path.
How to repeat:
mysql> CREATE TABLE `t1` ( `c1` decimal(10,0) DEFAULT NULL, `c2` varbinary(10) DEFAULT NULL, `c3` bit(1) DEFAULT NULL, UNIQUE KEY `c1` (`c1`,`c2`), KEY `i2` (`c3`));
Query OK, 0 rows affected (0.263 sec)
mysql> INSERT IGNORE INTO t1 (c1,c2,c3) VALUES (-9.183,null,b'11010110011000011100001011011111111100111110001');
Query OK, 1 row affected, 2 warnings (0.029 sec)
mysql> INSERT IGNORE INTO t1 (c1,c2,c3) VALUES (1.009,'A',b'0110100010000101110000001111011');
Query OK, 1 row affected, 2 warnings (0.031 sec)
mysql> INSERT IGNORE INTO t1 (c1,c2,c3) VALUES (-9.183,'C',b'111111');
Query OK, 1 row affected, 2 warnings (0.030 sec)
mysql> SELECT ca4 AS ca1, ca4 AS ca2, c1 AS ca3 FROM (SELECT ca7 AS ca4 FROM (SELECT c2 AS ca7, c3 AS ca8, c3 AS ca9 FROM t1) AS ta1 WHERE ((ca8 & ca7) IN (SELECT c3 AS ca10 FROM t1)) SOUNDS LIKE ((EXISTS (SELECT c3 AS ca11 FROM t1)) >= SOME (SELECT c3 AS ca12 FROM t1))) AS ta2 CROSS JOIN t1;
Empty set, 5 warnings (0.010 sec)
-- Run the same query but with hint:
mysql> SELECT /*+ SET_VAR(optimizer_switch='materialization=off') */ ca4 AS ca1, ca4 AS ca2, c1 AS ca3 FROM (SELECT ca7 AS ca4 FROM (SELECT c2 AS ca7, c3 AS ca8, c3 AS ca9 FROM t1) AS ta1 WHERE ((ca8 & ca7) IN (SELECT c3 AS ca10 FROM t1)) SOUNDS LIKE ((EXISTS (SELECT c3 AS ca11 FROM t1)) >= SOME (SELECT c3 AS ca12 FROM t1))) AS ta2 CROSS JOIN t1;
+------------+------------+------+
| ca1 | ca2 | ca3 |
+------------+------------+------+
| 0x43 | 0x43 | -9 |
| 0x41 | 0x41 | -9 |
| 0x43 | 0x43 | -9 |
| 0x41 | 0x41 | -9 |
| 0x43 | 0x43 | 1 |
| 0x41 | 0x41 | 1 |
+------------+------------+------+
6 rows in set, 2 warnings (0.008 sec)
Expected Result：Results should be the same under both optimizer switch settings
