Description:
When range scan is allowed, the query returns 12 rows; after disabling range scan by adding the `/*+ NO_RANGE_OPTIMIZATION(t2 PRIMARY) */` hint inside the derived table, the result changes and returns 16 rows. Merely changing the optimizer's range-optimization choice alters the result, indicating a result-consistency defect likely related to incorrect result filtering by range optimization.
How to repeat:
mysql> CREATE TABLE t2 ( c2 bit(1) DEFAULT NULL, c1 bit(46) NOT NULL, PRIMARY KEY (c1)) ;
Query OK, 0 rows affected (0.186 sec)
mysql> INSERT IGNORE INTO t2 (c2,c1) VALUES (b'1111111000011101100101111101001',b'01001001010110110011011110000011010111001111111000');
Query OK, 1 row affected, 2 warnings (0.053 sec)
mysql> UPDATE IGNORE t2 SET c2 = b'000101001000001110010100111110000010010010010001010100', c1 = b'00111100011101011111000011101010110';
Query OK, 1 row affected, 1 warning (0.032 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> INSERT IGNORE INTO t2 (c2,c1) VALUES (b'11000010110110110010101',b'100');
Query OK, 1 row affected, 1 warning (0.022 sec)
mysql> REPLACE INTO t2 (c2,c1) VALUES (b'001',b'111100001110100001000100001');
Query OK, 1 row affected (0.022 sec)
mysql> INSERT IGNORE INTO t2 (c2,c1) VALUES (b'10100101000011001000011101100101010011',b'1001110100101010111111001001111110011110001010111101000010');
Query OK, 1 row affected, 2 warnings (0.026 sec)
mysql> SELECT * FROM t2 AS ta1 INNER JOIN (SELECT ca10 AS ca8 FROM (SELECT c1 AS ca10 FROM t2) AS ta2) AS ta3 ON ((('H') <= (ca8)));
+------------+----------------+----------------+
| c2 | c1 | ca8 |
+------------+----------------+----------------+
| 0x01 | 0x000000000004 | 0x3FFFFFFFFFFF |
| 0x01 | 0x000000000004 | 0x0001E3AF8756 |
| 0x01 | 0x000000000004 | 0x000007874221 |
| 0x01 | 0x000007874221 | 0x3FFFFFFFFFFF |
| 0x01 | 0x000007874221 | 0x0001E3AF8756 |
| 0x01 | 0x000007874221 | 0x000007874221 |
| 0x01 | 0x0001E3AF8756 | 0x3FFFFFFFFFFF |
| 0x01 | 0x0001E3AF8756 | 0x0001E3AF8756 |
| 0x01 | 0x0001E3AF8756 | 0x000007874221 |
| 0x01 | 0x3FFFFFFFFFFF | 0x3FFFFFFFFFFF |
| 0x01 | 0x3FFFFFFFFFFF | 0x0001E3AF8756 |
| 0x01 | 0x3FFFFFFFFFFF | 0x000007874221 |
+------------+----------------+----------------+
12 rows in set, 2 warnings (0.008 sec)
-- the same query but with NO_RANGE_OPTIMIZATION hint
mysql> SELECT * FROM t2 AS ta1 INNER JOIN (SELECT ca10 AS ca8 FROM (SELECT /*+ NO_RANGE_OPTIMIZATION(t2 PRIMARY) */ c1 AS ca10 FROM t2) AS ta2) AS ta3 ON ((('H') <= (ca8)));
+------------+----------------+----------------+
| c2 | c1 | ca8 |
+------------+----------------+----------------+
| 0x01 | 0x000000000004 | 0x3FFFFFFFFFFF |
| 0x01 | 0x000000000004 | 0x0001E3AF8756 |
| 0x01 | 0x000000000004 | 0x000007874221 |
| 0x01 | 0x000000000004 | 0x000000000004 |
| 0x01 | 0x000007874221 | 0x3FFFFFFFFFFF |
| 0x01 | 0x000007874221 | 0x0001E3AF8756 |
| 0x01 | 0x000007874221 | 0x000007874221 |
| 0x01 | 0x000007874221 | 0x000000000004 |
| 0x01 | 0x0001E3AF8756 | 0x3FFFFFFFFFFF |
| 0x01 | 0x0001E3AF8756 | 0x0001E3AF8756 |
| 0x01 | 0x0001E3AF8756 | 0x000007874221 |
| 0x01 | 0x0001E3AF8756 | 0x000000000004 |
| 0x01 | 0x3FFFFFFFFFFF | 0x3FFFFFFFFFFF |
| 0x01 | 0x3FFFFFFFFFFF | 0x0001E3AF8756 |
| 0x01 | 0x3FFFFFFFFFFF | 0x000007874221 |
| 0x01 | 0x3FFFFFFFFFFF | 0x000000000004 |
+------------+----------------+----------------+
16 rows in set, 1 warning (0.005 sec)
Expected Result：Results should be the same under both plans (an optimizer hint should only affect the execution plan, not the query result).
© 2026, Oracle Corporation and/or its affiliates
Description: When range scan is allowed, the query returns 12 rows; after disabling range scan by adding the `/*+ NO_RANGE_OPTIMIZATION(t2 PRIMARY) */` hint inside the derived table, the result changes and returns 16 rows. Merely changing the optimizer's range-optimization choice alters the result, indicating a result-consistency defect likely related to incorrect result filtering by range optimization. How to repeat: mysql> CREATE TABLE t2 ( c2 bit(1) DEFAULT NULL, c1 bit(46) NOT NULL, PRIMARY KEY (c1)) ; Query OK, 0 rows affected (0.186 sec) mysql> INSERT IGNORE INTO t2 (c2,c1) VALUES (b'1111111000011101100101111101001',b'01001001010110110011011110000011010111001111111000'); Query OK, 1 row affected, 2 warnings (0.053 sec) mysql> UPDATE IGNORE t2 SET c2 = b'000101001000001110010100111110000010010010010001010100', c1 = b'00111100011101011111000011101010110'; Query OK, 1 row affected, 1 warning (0.032 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> INSERT IGNORE INTO t2 (c2,c1) VALUES (b'11000010110110110010101',b'100'); Query OK, 1 row affected, 1 warning (0.022 sec) mysql> REPLACE INTO t2 (c2,c1) VALUES (b'001',b'111100001110100001000100001'); Query OK, 1 row affected (0.022 sec) mysql> INSERT IGNORE INTO t2 (c2,c1) VALUES (b'10100101000011001000011101100101010011',b'1001110100101010111111001001111110011110001010111101000010'); Query OK, 1 row affected, 2 warnings (0.026 sec) mysql> SELECT * FROM t2 AS ta1 INNER JOIN (SELECT ca10 AS ca8 FROM (SELECT c1 AS ca10 FROM t2) AS ta2) AS ta3 ON ((('H') <= (ca8))); +------------+----------------+----------------+ | c2 | c1 | ca8 | +------------+----------------+----------------+ | 0x01 | 0x000000000004 | 0x3FFFFFFFFFFF | | 0x01 | 0x000000000004 | 0x0001E3AF8756 | | 0x01 | 0x000000000004 | 0x000007874221 | | 0x01 | 0x000007874221 | 0x3FFFFFFFFFFF | | 0x01 | 0x000007874221 | 0x0001E3AF8756 | | 0x01 | 0x000007874221 | 0x000007874221 | | 0x01 | 0x0001E3AF8756 | 0x3FFFFFFFFFFF | | 0x01 | 0x0001E3AF8756 | 0x0001E3AF8756 | | 0x01 | 0x0001E3AF8756 | 0x000007874221 | | 0x01 | 0x3FFFFFFFFFFF | 0x3FFFFFFFFFFF | | 0x01 | 0x3FFFFFFFFFFF | 0x0001E3AF8756 | | 0x01 | 0x3FFFFFFFFFFF | 0x000007874221 | +------------+----------------+----------------+ 12 rows in set, 2 warnings (0.008 sec) -- the same query but with NO_RANGE_OPTIMIZATION hint mysql> SELECT * FROM t2 AS ta1 INNER JOIN (SELECT ca10 AS ca8 FROM (SELECT /*+ NO_RANGE_OPTIMIZATION(t2 PRIMARY) */ c1 AS ca10 FROM t2) AS ta2) AS ta3 ON ((('H') <= (ca8))); +------------+----------------+----------------+ | c2 | c1 | ca8 | +------------+----------------+----------------+ | 0x01 | 0x000000000004 | 0x3FFFFFFFFFFF | | 0x01 | 0x000000000004 | 0x0001E3AF8756 | | 0x01 | 0x000000000004 | 0x000007874221 | | 0x01 | 0x000000000004 | 0x000000000004 | | 0x01 | 0x000007874221 | 0x3FFFFFFFFFFF | | 0x01 | 0x000007874221 | 0x0001E3AF8756 | | 0x01 | 0x000007874221 | 0x000007874221 | | 0x01 | 0x000007874221 | 0x000000000004 | | 0x01 | 0x0001E3AF8756 | 0x3FFFFFFFFFFF | | 0x01 | 0x0001E3AF8756 | 0x0001E3AF8756 | | 0x01 | 0x0001E3AF8756 | 0x000007874221 | | 0x01 | 0x0001E3AF8756 | 0x000000000004 | | 0x01 | 0x3FFFFFFFFFFF | 0x3FFFFFFFFFFF | | 0x01 | 0x3FFFFFFFFFFF | 0x0001E3AF8756 | | 0x01 | 0x3FFFFFFFFFFF | 0x000007874221 | | 0x01 | 0x3FFFFFFFFFFF | 0x000000000004 | +------------+----------------+----------------+ 16 rows in set, 1 warning (0.005 sec) Expected Result：Results should be the same under both plans (an optimizer hint should only affect the execution plan, not the query result).