string value change after removing except clause

Start by running the supplied SQL schema, data, and the two EXCEPT queries against the reported PolarDB-X version, then compare the differing col_1 output. Trace the query-processing path for nested EXCEPT operations and date type handling; done means removing the middle EXCEPT no longer changes col_1 from the full date to a year-month value.

after removing except clause the col_1's value unexpectedly change

mysql> SELECT end29.c5 AS col_1, end29.c4 AS col_2 FROM t1 AS end29 USE INDEX (idx_t1_c5) WHERE NOT end29.c1 IS NULL ORDER BY end29.c6 ASC EXCEPT SELECT kmg99.c6 AS col_1, kmg99.c4 AS col_2 FROM t1 AS kmg99;
+------------+-------+
| col_1      | col_2 |
+------------+-------+
| 2025-09-07 |    36 |
+------------+-------+
1 row in set (0.01 sec)

mysql> SELECT end29.c5 AS col_1, end29.c4 AS col_2 FROM t1 AS end29 USE INDEX (idx_t1_c5) WHERE NOT end29.c1 IS NULL ORDER BY end29.c6 ASC EXCEPT SELECT jjp59.c11 AS col_1, jjp59.c1 AS col_2 FROM t2 AS jjp59 EXCEPT SELECT kmg99.c6 AS col_1, kmg99.c4 AS col_2 FROM t1 AS kmg99;
+---------+-------+
| col_1   | col_2 |
+---------+-------+
| 2025-09 |    36 |
+---------+-------+
1 row in set (0.02 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 t2 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 INT NOT NULL,
    c3 DECIMAL(10,2) NOT NULL,
    c4 VARCHAR(50) NOT NULL,
    c5 DATE NOT NULL,
    c6 MEDIUMTEXT NULL,
    c7 LONGTEXT NULL,
    c8 MEDIUMBLOB NULL,
    c9 LONGBLOB NULL,
    c10 ENUM('value1','value2','value3') NULL,
    c11 SET('a','b','c','d') NULL,
    c12 BIT(8) NULL,
    c13 DATETIME NULL,
    c14 FLOAT(8,2) NULL,
    c15 DOUBLE(12,4) 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 t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (994, 3423, 181.05, 'sample_wfxp8dzD7XrFnPLzr', '2024-12-16', 'sample_pVtuVRHgHyCTAdMjTX5uDmgDFTTcRtYK4MA3VzWGhxyRAthAan095ltHnWC2wZqFzmNIz2BhrrY2h0fxo8UzijtqT3Qzyc7ydCZ760AbwzyWxKRhxPNIiXBNmglw9ylwoKoF0Q5QMAuWKKHLT07TyALEjZEq9SXDPPHZkG637npBmt2TKOxONpcXFVVrsW3jgaSAVhmwZMYON8YAyhzkhs0fsDZAohNdcrXeb43HI5de5EWnnY2lj8xyjRyfvWyVnBrhfeRiVnwUHuk0bcwoN3ueyVTRnXwQSF56ZbxUsw4hUmzUQ4GGRNJqlHOzrdMFLZHnsp5SamGZ0WPFJC1Vx1RuAnB5RdThXSIL8o2Fgebw5VIlETIWBvsuSQqrlbUxl0DbKa4xwcIiP23T8cVxdUgU4xKNvx6nab1kO47gdMcGoSGyXyvr23yKaVpjtw6XNe0uwE6zNt4nqZ1IMHxo', 'sample_oIxWhZ5PNoCnBUDNNG1t4uGQ80Jm3dOMyFRe0xsG8bqvWYFDuMqCy715ukwGqx4YD0tNg95vNkY49I1JPAbh5BBrcbTCYpkH1R37hDwaEo0Lzl91rc0tCeXUzOy258wzk7DX3Z11LNDgWU4awzLAAqwgg6l6QUVNdWbDUN3MQYXgivI8FcfivR8MVM40OIkt9fO8BNRyM2wTSSdov5M69ahQHUactc9lQyhXQfKFssG6OK2qKe2LIxK7FT1nHxPTNh7d41fB2bHcrjQ51tWpKJcLUFVVansOZC4fsvYKAamSAEMg3CsdHRNxw9dNCn5504tI75YCiAZT9DJEv7eTX7R07OJUaKE95P4VCs4XzftyMlVR4sPXkCsh7k7MYYBRd5d9iJdQLLOKXV3FUX5qjMRwF0oltoDkNGhVzUwlRuOptO8ROwzJLwCo5Zc7ce9l3bVoWQ3LsFymuRfVPQNPDkec3GZtAddEZBmIvzojWCpi0vhTzdYojxEdQwMHomRvrrYI7XWkrLrOOYwgMnPfHTeyO9oBFhEksiqIvbJHAEP2xCD2J0NvGcap1lZc2ueOUe6pJbZ95HmiFyRwwxMOxfLY4VCD5vgXNZTzeP0q8G0caUPPmLnaPcrw0vcnvR1gJnPdl8YyggslnNMOFmhPU2TK8EO5Xn6QVpvb6zPaM3Bw6sK3u10cw3i3jPuAYGSdMFNIdrU6L0zMUiI1LO4T7us2MUaXM', X'4DD6A6D687C6A9C08CDA93E9B48429DAA8ECBF93E891B67DDEBCDEBCE79C8A20', X'CF8CCBA2C880ED9DA9CF8644ECA5BD53E6B0AAED898E10C398D0AD', 'value1', 'a,c,b,d', b'01101010', '2025-04-25 18:28:30', 90.91, 53.96);

SELECT end29.c5 AS col_1, end29.c4 AS col_2 FROM t1 AS end29 USE INDEX (idx_t1_c5) WHERE NOT end29.c1 IS NULL ORDER BY end29.c6 ASC EXCEPT SELECT kmg99.c6 AS col_1, kmg99.c4 AS col_2 FROM t1 AS kmg99;
SELECT end29.c5 AS col_1, end29.c4 AS col_2 FROM t1 AS end29 USE INDEX (idx_t1_c5) WHERE NOT end29.c1 IS NULL ORDER BY end29.c6 ASC EXCEPT SELECT jjp59.c11 AS col_1, jjp59.c1 AS col_2 FROM t2 AS jjp59 EXCEPT SELECT kmg99.c6 AS col_1, kmg99.c4 AS col_2 FROM t1 AS kmg99;

Version

mysql> select polardb_version();
+---------+--------------+---------------------+
| TYPE    | VERSION      | RELEASE_DATE        |
+---------+--------------+---------------------+
| Product | PolarDB V2.0 | Distributed Edition |
| CN      | 2.4.0.5.4.19 | SNAPSHOT            |
| DN      | 2.4.0.8.4.19 | 20240430            |
| CDC     | 2.4.0.5.4.19 | 20240430            |
| GMS     | 2.4.0.8.4.19 | 20240430            |
+---------+--------------+---------------------+
5 rows in set (0.02 sec)

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.00 sec)