Bug #119474 Result error caused by the subquery_to_derived optimizer switch

• Submitted: 29 Nov 9:29
• Modified: 1 Dec 4:16
• Reporter: Go Yakult
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any
• Tags: 8.0.41

[29 Nov 9:29] Go Yakult

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

mysql> SELECT c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t0 WHERE c0 > 0 AND c0 < 1000 ORDER BY c0 LIMIT 1 OFFSET 2);
Empty set (0.00 sec)

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

mysql> SELECT c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t0 WHERE c0 > 0 AND c0 < 1000 ORDER BY c0 LIMIT 1 OFFSET 2);
+----------------------------------------+
| c0                                     |
+----------------------------------------+
| 0x313E46513E4D                         |
| 0x6C723E5EE184B97E76                   |
| 0x302E36323635383831303036383332393631 |
| 0x302E35353736323032313536333130333636 |
+----------------------------------------+
4 rows in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` longblob
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` VALUES (_binary '1>FQ>M'),(_binary 'lr>^ᄹ~v'),(_binary '0.6265881006832961'),(_binary '0.5576202156310366');

set optimizer_switch='subquery_to_derived=on';
SELECT c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t0 WHERE c0 > 0 AND c0 < 1000 ORDER BY c0 LIMIT 1 OFFSET 2);

set optimizer_switch='subquery_to_derived=off';
SELECT c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t0 WHERE c0 > 0 AND c0 < 1000 ORDER BY c0 LIMIT 1 OFFSET 2);

[29 Nov 9:31] Go Yakult

Changing the field type to INT also results in an error.

DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` VALUES (1),(2),(3),(4);

[1 Dec 4:16] Chaithra Marsur Gopala Reddy

Hi Go Yakult,

Thank you for the test case. Verified as described.