Bug #118875 set optimizer_switch='block_nested_loop=off/on', result is different.

• Submitted: 21 Aug 12:42
• Modified: 21 Aug 13:16
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41, 8.0.43
• OS: Any
• CPU Architecture: Any

[21 Aug 12:42] Alice Alice

Description:
set optimizer_switch='block_nested_loop=off/on', result is different.

mysql> set optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT /*+ INL_JOIN(t0, t1)*/t1.c0, (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)),(((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) FROM  t1 RIGHT JOIN t0 ON t0.c0 WHERE (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)) GROUP BY t0.c0 HAVING ((((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) IS NULL);
Empty set, 31 warnings (0.00 sec)

mysql> set optimizer_switch='block_nested_loop=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT /*+ INL_JOIN(t0, t1)*/t1.c0, (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)),(((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) FROM  t1 RIGHT JOIN t0 ON t0.c0 WHERE (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)) GROUP BY t0.c0 HAVING ((((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) IS NULL);
+------+--------------------------------------------------+----------------------------------------+
| c0   | (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)) | (((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) |
+------+--------------------------------------------------+----------------------------------------+
|    1 |                                                1 |                                   NULL |
|    1 |                                                1 |                                   NULL |
|    1 |                                                1 |                                   NULL |
+------+--------------------------------------------------+----------------------------------------+
3 rows in set, 41 warnings (0.00 sec)

How to repeat:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `having2025082044` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `having2025082044`;

DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` char(1) NOT NULL DEFAULT '鷡',
  PRIMARY KEY (`c0`),
  KEY `i66` (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `t0` WRITE;
/*!40000 ALTER TABLE `t0` DISABLE KEYS */;
INSERT INTO `t0` VALUES ('-'),('0'),('2'),('6'),('9'),('B'),('E'),('J'),('q'),('R'),('t'),('V'),('w'),('홅');
/*!40000 ALTER TABLE `t0` ENABLE KEYS */;
UNLOCK TABLES;

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c0` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (0),(1);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

mysql> set optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT /*+ INL_JOIN(t0, t1)*/t1.c0, (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)),(((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) FROM  t1 RIGHT JOIN t0 ON t0.c0 WHERE (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)) GROUP BY t0.c0 HAVING ((((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) IS NULL);
Empty set, 31 warnings (0.00 sec)

mysql> set optimizer_switch='block_nested_loop=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT /*+ INL_JOIN(t0, t1)*/t1.c0, (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)),(((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) FROM  t1 RIGHT JOIN t0 ON t0.c0 WHERE (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)) GROUP BY t0.c0 HAVING ((((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) IS NULL);
+------+--------------------------------------------------+----------------------------------------+
| c0   | (((+ (((777071193)<<(t0.c0)))))>=(6.99772379E8)) | (((BIT_OR(t1.c0))AND(DEFAULT(t1.c0)))) |
+------+--------------------------------------------------+----------------------------------------+
|    1 |                                                1 |                                   NULL |
|    1 |                                                1 |                                   NULL |
|    1 |                                                1 |                                   NULL |
+------+--------------------------------------------------+----------------------------------------+
3 rows in set, 41 warnings (0.00 sec)

[21 Aug 13:16] MySQL Verification Team

Hello Alice Alice,

Thank you for the report and feedback.

regards,
Umesh