Bug #120265 Query returns with UNION, INTERSECT ALL and Derived SELECT DISTINCT

• Submitted: 14 Apr 8:24
• Modified: 14 Apr 9:47
• Reporter: Guo Yuxiao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.6.0
• OS: Any
• CPU Architecture: Any

[14 Apr 8:24] Guo Yuxiao

Description:
Hi, I found a logic bug in MySQL 9.6.0.
A query that should have returned {3.141593} actually returned an empty set.

How to repeat:
-- create table
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` ( `c0` decimal(10,0) unsigned NOT NULL  COMMENT 'asdf', PRIMARY KEY (`c0`) ) CHARSET=utf8mb4;
INSERT INTO `t0` VALUES (0000000000);
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` ( `c0` decimal(10,0) unsigned NOT NULL  COMMENT 'asdf', PRIMARY KEY (`c0`), UNIQUE KEY `c0` (`c0`) ) CHARSET=utf8mb4;
INSERT INTO `t1` VALUES (0000000000),(0000000001),(1433300933),(9999999999);

-- query, expect:{3.141593}, actual:empty set
SELECT PI() AS c1
    FROM t1 AS t1
    INTERSECT ALL
    SELECT t_restored_0.c1 AS c1
    FROM (
    SELECT
        0 AS c0,
        0 AS c1

    UNION

    SELECT *
    FROM (
        SELECT DISTINCT
        125 AS c0,
        PI() AS c1
        FROM t1 AS t1
    ) AS t_filter_sub_1
    ) AS t_restored_0;

[14 Apr 9:47] Chaithra Marsur Gopala Reddy

Hi Guo Yuxiao,

Thank you for the test case. Verified as described.