Bug #119398 rows become less after removing Except clause

• Submitted: 15 Nov 6:56
• Reporter: cl hl
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[15 Nov 6:56] cl hl

Description:
after removing EXCEPT SELECT edt2.c1 AS col_1, edt2.c1 AS col_2, edt2.c1 AS col_3 FROM t1 AS edt2 USE INDEX (idx_t1_c3_c4_c2), the result rows unexpectedly become less.

mysql> WITH cte_936 AS (SELECT fgf56.c15 AS col_1, SIN(fgf56.c2) AS col_2, 1 AS col_3 FROM t3 AS fgf56 WHERE ((LOWER(fgf56.c14) <> 'sample_70') AND (fgf56.c12 <> 'sample_74'))) SELECT bqw51.col_3 AS col_1, bqw51.col_2 AS col_2, bqw51.col_3 AS col_3 FROM cte_936 AS bqw51 EXCEPT SELECT edt2.c1 AS col_1, edt2.c1 AS col_2, edt2.c1 AS col_3 FROM t1 AS edt2 USE INDEX (idx_t1_c3_c4_c2) EXCEPT SELECT gmx10.col_1 AS col_1, gmx10.col_2 AS col_2, gmx10.col_1 AS col_3 FROM cte_936 AS gmx10;
+-------+--------------------+-------+
| col_1 | col_2              | col_3 |
+-------+--------------------+-------+
|     1 | 0.8903801443283409 |     1 |
+-------+--------------------+-------+
1 row in set (0.00 sec)

mysql> WITH cte_936 AS (SELECT fgf56.c15 AS col_1, SIN(fgf56.c2) AS col_2, 1 AS col_3 FROM t3 AS fgf56 WHERE ((LOWER(fgf56.c14) <> 'sample_70') AND (fgf56.c12 <> 'sample_74'))) SELECT bqw51.col_3 AS col_1, bqw51.col_2 AS col_2, bqw51.col_3 AS col_3 FROM cte_936 AS bqw51 EXCEPT SELECT gmx10.col_1 AS col_1, gmx10.col_2 AS col_2, gmx10.col_1 AS col_3 FROM cte_936 AS gmx10;
Empty set (0.00 sec)

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 VARCHAR(255) NOT NULL,
    c3 VARCHAR(255) NULL,
    c4 INT NULL,
    c5 DATE NOT NULL,
    c6 VARCHAR(10) NOT NULL,
    PRIMARY KEY (c1)
);

CREATE TABLE t3 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 INT NOT NULL,
    c3 INT NOT NULL,
    c4 YEAR NOT NULL,
    c5 TIME NULL,
    c6 TINYINT NULL,
    c7 SMALLINT NULL,
    c8 MEDIUMINT NULL,
    c9 BIGINT NULL,
    c10 LONGTEXT NULL,
    c11 VARCHAR(255) NULL,
    c12 TINYTEXT NULL,
    c13 TINYBLOB NULL,
    c14 SET('x','y','z') NULL,
    c15 TINYINT(1) NULL,
    PRIMARY KEY (c1)
);

INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3584, 'sample_AQJP3FCb7PX46hF2d3VoKsPIHtIgzieVgOK1Ue7iSbNProjjh', 'sample_v', 36, '2025-09-07', 'sample_J');

INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (8417, 9828, 4869, 2023, '2023-01-01 21:39:34', 14, 68, 41, 89, 'sample_etX2AhIU3JkLkyrEOwJTBqpBStqOrUPbskI8gL9NI0mwroTZBFHdHYljUuEHNlUbh5hyij8jKGC30KpbQ1LxgLF3m1cLuhezhgFbet0FnWTwJErW2Qd64AWovLqIOmfMKcJrXPMb405Gro9bXX0ewJlrXrzl383yHWXzgpu1Tb9SGfjW21HzuYBp50VLzsuEtkTKnFouWhIF6m4rT46enkICMZ8vOF9qUanlkKZ8aPFJSjPJX5O8pnDC1BiPX77tUJojj286KVV5Av6I9B9Itw8A0V2gZumzouD06KPaMkQlHsphSpBT7VpjPpneb3ebwLvj73Y15sCv5iwcbaDKFGTuZRdqtmCwx2l37zcGJgU6Ch8fbCNAgmFdYNJ0sveP7uiAvefZUWmlnFmQo6ASVSR5utAX2ha7bflNpNvwT6pbanbj49lcD00bY3DYwRWuMh2d8dVb1p9allRD9Tie2mJJO2wW5qmI3uHz9MzFBCRrkAwOhczPgT6gPtnleQXwtsdSU0zzeUjTYdu23ISbFdfCk9bgfRtIZkXa4faYbiRfy8jPkwjLNK9swCMcwxAtNe1LjK0DqRcGuVbTX5E2vpfDlfk60euiq1RO4TLEUuMaBpptJ7hAGQbTQrfD7rQr5s2QzKUNgM7jY3Pz0NtNRCDtousLLEzfvEmQkM4H95WGf4v24A564PdHfyY3xYflHU3pID5XyBUNi1XC2vga2Kq8ADaM5VKsE3o4BWIKpJcuESY0bOUcp1SOhSaqFiYjf3mmm9eUqo4elRwMqf37uOeNQ1TdvdvJlKRNHTi4umf6TYZr57zdtS4RuQ7HHW2NE5wQBBhmLcpoQyTquda7JCVSmmKo8G0h1C4VXe46bDTQvjJmrCw7JKMLncGbm7TEMsWMdiNxFPoXUDpxkXUSkjRAe0Uc9tPmeTUwq5uauperTQaR', 'sample_weZoLCxaYRuEbIZSAcw', 'sample_8eD325FzN79CIA1lx3hcxnIVFhKIlbppGhso8TzRvfqzVgovF4texBWt2e15D4TE8uXMELQHhSBEC2wHYIIOfKH8XCNMYbzkX06yuiY1u41mec1TesgBYKV3PW', X'D2A6D881052BECAA90E6BDA5DBAF542830D084', 'y,z', NULL);

CREATE  INDEX idx_t1_c3_c4_c2 ON t1 (c3(63), c4, c2(63));
WITH cte_936 AS (SELECT fgf56.c15 AS col_1, SIN(fgf56.c2) AS col_2, 1 AS col_3 FROM t3 AS fgf56 WHERE ((LOWER(fgf56.c14) <> 'sample_70') AND (fgf56.c12 <> 'sample_74'))) SELECT bqw51.col_3 AS col_1, bqw51.col_2 AS col_2, bqw51.col_3 AS col_3 FROM cte_936 AS bqw51 EXCEPT SELECT edt2.c1 AS col_1, edt2.c1 AS col_2, edt2.c1 AS col_3 FROM t1 AS edt2 USE INDEX (idx_t1_c3_c4_c2) EXCEPT SELECT gmx10.col_1 AS col_1, gmx10.col_2 AS col_2, gmx10.col_1 AS col_3 FROM cte_936 AS gmx10;
WITH cte_936 AS (SELECT fgf56.c15 AS col_1, SIN(fgf56.c2) AS col_2, 1 AS col_3 FROM t3 AS fgf56 WHERE ((LOWER(fgf56.c14) <> 'sample_70') AND (fgf56.c12 <> 'sample_74'))) SELECT bqw51.col_3 AS col_1, bqw51.col_2 AS col_2, bqw51.col_3 AS col_3 FROM cte_936 AS bqw51 EXCEPT SELECT gmx10.col_1 AS col_1, gmx10.col_2 AS col_2, gmx10.col_1 AS col_3 FROM cte_936 AS gmx10;