`VARCHAR IN (SELECT YEAR_col)` Produces Plan-Dependent Wrong Results on SINGLE Tables

Summary

This report shows a wrong-result issue for a query of the form VARCHAR_col IN (SELECT YEAR_col FROM ...) on PolarDB-X SINGLE tables. Two execution states are compared:

• S1: a stricter outer-table variant with UNIQUE KEY constraints and fewer rows
• S2: a relaxed outer-table variant without those UNIQUE KEY constraints and with more rows

S2 contains all rows relevant to S1 plus additional rows, so the result on S2 should contain the S1 result as a subset. Instead, S1 returns rows while S2 returns none. Additional diagnostics suggest that the same logical predicate is evaluated with different coercion behavior under different plan shapes.

Environment

Item	Value
Deployment	Single-node Docker (SINGLE group)
sql_mode	'' (empty, set via SET SESSION sql_mode = '')
OS	Ubuntu 20.04.4 LTS
CPU Architecture	x86 — Intel(R) Xeon(R) Platinum 8358P @ 2.60GHz (64 Cores)

MySQL [replay]> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.004 sec)

MySQL [replay]> 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.023 sec)

Steps to Reproduce

SET SESSION sql_mode = '';
DROP TABLE IF EXISTS vert_ref_516978e6_t3;
DROP TABLE IF EXISTS vert_s1_516978e6;
DROP TABLE IF EXISTS vert_s2_516978e6;
CREATE TABLE vert_ref_516978e6_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 GEOMETRY NULL,
    c12 TINYTEXT NULL,
    c13 TINYBLOB NULL,
    c14 SET('x','y','z') NULL,
    c15 TINYINT(1) NULL,
    PRIMARY KEY (c1)
);;
CREATE TABLE vert_s1_516978e6 (
    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,
    UNIQUE KEY `u_v_vert_s1_516978e6_0` (`c3`),
    UNIQUE KEY `u_v_vert_s1_516978e6_1` (`c2`),
    UNIQUE KEY `u_vc_vert_s1_516978e6_0` (`c4`, `c5`),
    PRIMARY KEY (c1)
);;
CREATE TABLE vert_s2_516978e6 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 VARCHAR(255) NULL,
    c3 VARCHAR(255) NULL,
    c4 INT NULL,
    c5 DATE NULL,
    c6 VARCHAR(10) NULL,
    PRIMARY KEY (c1)
);;
CREATE INDEX i_v_s1_516978e6_c2 ON vert_s1_516978e6 (`c2`);
CREATE INDEX i_v_s1_516978e6_c3 ON vert_s1_516978e6 (`c3`);
CREATE INDEX i_v_s1_516978e6_c4 ON vert_s1_516978e6 (`c4`);
CREATE INDEX i_v_s1_516978e6_c5 ON vert_s1_516978e6 (`c5`);
CREATE INDEX i_v_s1_516978e6_c6 ON vert_s1_516978e6 (`c6`);
CREATE INDEX i_v_s2_516978e6_c2 ON vert_s2_516978e6 (`c2`);
CREATE INDEX i_v_s2_516978e6_c3 ON vert_s2_516978e6 (`c3`);
CREATE INDEX i_v_s2_516978e6_c4 ON vert_s2_516978e6 (`c4`);
CREATE INDEX i_v_s2_516978e6_c5 ON vert_s2_516978e6 (`c5`);
CREATE INDEX i_v_s2_516978e6_c6 ON vert_s2_516978e6 (`c6`);
CREATE INDEX i_vg_s2_516978e6_0 ON vert_s2_516978e6 (`c3`);
CREATE INDEX i_vg_s2_516978e6_1 ON vert_s2_516978e6 (`c2`);
CREATE INDEX i_vg_s2_516978e6_2 ON vert_s2_516978e6 (`c4`, `c5`);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000000, 20230101, 20230101, NULL, '00:53:58', 1, 1, 1, 20230101, ' 1', NULL, '2023-01-01 00:00:00', NULL, 'z,y', 20230101);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000001, 0, 0, NULL, '17:30:26', 0, 20230101, 0, 0, 'y_ ssj-s', NULL, 'jtce53ghm9jysxw39', NULL, 'x', 1);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000002, 1, 0, 2023, '20:51:26', -1, 0, 20230101, 1, '__yvdz2xj50aiqv52c27s :zv9mh0', NULL, '1', NULL, 'y,z,x', 0);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000003, 0, 0, 1993, NULL, 0, 1, 1, 1, '0', NULL, '/ 7e4 ja:/', NULL, 'z,x,y', 20230101);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000004, 20230101, 1, NULL, '18:48:31', 0, 0, 1, 1, 'hscpvc5h:u2wb0x5dj8/da3', NULL, 'l/g1364qjcthk173', NULL, 'x,z,y', 0);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000005, 20230101, 0, NULL, '23:30:53', 20230101, -1, -1, -1, '0', NULL, '-1', NULL, 'x', 20230101);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000006, 0, 0, 2037, '09:11:17', -1, 1, -1, 20230101, 'wqi_2nt8ew33g4npk-j9v131h2qmhp98txl-:lcw9q1/hm4e7ny', NULL, '7qy2 0u2_io3zll6egt', NULL, 'x,y', 0);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (9000007, 0, -1, NULL, NULL, 1, -1, 20230101, 1, 'ygt203i2ss2:f_lcabq v/k89ze:/:wi urzxiq6nq1nge:hmjjnr3', NULL, 'v46t4ek-oey_t', NULL, 'y,z', -1);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000008, 56, 57, 2008, '19:57:13', 60, 61, 62, 63, '9999-12-31', NULL, 'v_266', X'01', 'x,y', 69);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000009, 87, 88, 2014, '02:30:50', 91, 92, 93, 94, 'v_295', NULL, 'v_297', X'01', 'vhv_178', 100);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000010, 118, 119, 2020, '09:03:27', 122, 123, 124, 125, 'v_326', NULL, 'v_328', X'01', '0', 131);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000011, 149, 150, 2001, '16:36:04', 153, 154, 155, 156, 'v_357', NULL, 'v_359', X'01', '2023-01-01 00:00:00', 162);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000012, 180, 181, 2007, '23:09:41', 184, 185, 186, 187, '01e0', NULL, 'v_390', X'01', 'x,y,z', 193);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000013, -190, -189, 2013, '06:42:18', -186, -185, -184, -183, 'v_419', NULL, 'v_421', X'01', '2023-01-01', -177);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000014, -159, -158, 2019, '13:15:55', -155, -154, -153, -152, 'v_450', NULL, '-7', X'01', 'x,y', -146);
INSERT IGNORE INTO vert_ref_516978e6_t3 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (8000015, -128, -127, 2000, '20:48:32', -124, -123, -122, -121, '-6', NULL, 'v_483', X'01', 'x,y,z', -115);
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000015, 'vhv_178', 'v_5550', -7, '1000-01-01', 'v_4968');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000015, 'vhv_178', 'v_5550', -7, '1000-01-01', 'v_4968');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000016, 'v_2165', 'v_9922', -6, '9999-12-31', 'v_4954');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000016, 'v_2165', 'v_9922', -6, '9999-12-31', 'v_4954');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000017, '01e0', 'v_2070', -182, '2024-02-29', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000017, '01e0', 'v_2070', -182, '2024-02-29', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000018, '01e0', 'v_7471', NULL, '2016-09-05', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000018, '01e0', 'v_7471', NULL, '2016-09-05', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000019, 'v_9805', 'v_6850', 84, '2006-11-11', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000019, 'v_9805', 'v_6850', 84, '2006-11-11', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000020, '01e0', 'v_3386', 49, '2001-06-22', 'v_1301');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000020, '01e0', 'v_3386', 49, '2001-06-22', 'v_1301');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000021, 'v_8111', 'v_7179', -127, '2009-06-06', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000021, 'v_8111', 'v_7179', -127, '2009-06-06', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000022, 'v_5538', 'v_3833', -6, '9999-12-31', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000022, 'v_5538', 'v_3833', -6, '9999-12-31', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000023, 'vhv_178', 'v_6855', -7, '2024-02-29', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000023, 'vhv_178', 'v_6855', -7, '2024-02-29', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000024, 'v_8326', 'v_197', -5, '2002-11-11', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000024, 'v_8326', 'v_197', -5, '2002-11-11', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000025, 'not-a-date', 'v_3982', 55, '2000-09-25', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000025, 'not-a-date', 'v_3982', 55, '2000-09-25', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000026, 'not-a-date', 'v_7851', -7, '9999-12-31', 'v_8911');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000026, 'not-a-date', 'v_7851', -7, '9999-12-31', 'v_8911');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000027, 'v_9745', '01e0', -147, '2010-07-27', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000027, 'v_9745', '01e0', -147, '2010-07-27', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000028, 'v_7449', 'v_850', -84, '1000-01-01', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000028, 'v_7449', 'v_850', -84, '1000-01-01', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000029, '01e0', 'vhv_598', NULL, '2011-12-04', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000029, '01e0', 'vhv_598', NULL, '2011-12-04', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000030, 'v_6293', 'v_4639', -33, '2019-04-24', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000030, 'v_6293', 'v_4639', -33, '2019-04-24', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000031, 'v_3787', 'v_2807', 103, '1000-01-01', 'v_3188');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000031, 'v_3787', 'v_2807', 103, '1000-01-01', 'v_3188');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000032, 'v_408', 'v_6646', -5, '2001-10-06', 'v_7831');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000032, 'v_408', 'v_6646', -5, '2001-10-06', 'v_7831');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000033, 'v_9179', '01e0', -147, '2015-08-12', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000033, 'v_9179', '01e0', -147, '2015-08-12', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000034, 'v_1975', 'vhv_598', 162, '9999-12-31', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000034, 'v_1975', 'vhv_598', 162, '9999-12-31', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000035, 'v_5107', 'v_1399', -6, '2015-12-24', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000035, 'v_5107', 'v_1399', -6, '2015-12-24', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000036, 'v_145', 'v_2850', 136, '2014-07-19', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000036, 'v_145', 'v_2850', 136, '2014-07-19', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000037, '01e0', 'v_490', 105, '1000-01-01', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000037, '01e0', 'v_490', 105, '1000-01-01', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000038, '01e0', 'v_6796', -117, '2010-03-07', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000038, '01e0', 'v_6796', -117, '2010-03-07', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000039, 'v_8553', 'vhv_598', 78, '2014-11-11', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000039, 'v_8553', 'vhv_598', 78, '2014-11-11', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000040, 'v_8012', 'not-a-date', 20, '2009-06-06', 'v_5700');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000040, 'v_8012', 'not-a-date', 20, '2009-06-06', 'v_5700');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000041, 'v_69', 'not-a-date', -7, '1000-01-01', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000041, 'v_69', 'not-a-date', -7, '1000-01-01', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000042, 'v_4458', 'v_9833', 157, '2001-06-06', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000042, 'v_4458', 'v_9833', 157, '2001-06-06', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000043, 'v_192', 'vhv_598', -7, '2008-09-21', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000043, 'v_192', 'vhv_598', -7, '2008-09-21', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000044, '01e0', 'v_3839', 2, '2012-01-21', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000044, '01e0', 'v_3839', 2, '2012-01-21', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000045, 'v_7152', 'v_328', 73, '2003-08-28', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000045, 'v_7152', 'v_328', 73, '2003-08-28', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000046, 'not-a-date', 'v_6197', -5, '2018-03-19', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000046, 'not-a-date', 'v_6197', -5, '2018-03-19', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000047, 'v_3274', 'v_8970', -90, '2011-04-20', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000047, 'v_3274', 'v_8970', -90, '2011-04-20', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000048, 'v_9040', 'v_9197', 18, '2017-06-14', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000048, 'v_9040', 'v_9197', 18, '2017-06-14', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000049, 'v_2139', 'vhv_598', 177, '2011-04-04', 'v_5299');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000049, 'v_2139', 'vhv_598', 177, '2011-04-04', 'v_5299');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000050, 'v_1677', 'v_212', 153, '2002-03-15', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000050, 'v_1677', 'v_212', 153, '2002-03-15', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000051, 'vhv_178', 'v_5055', -34, '2003-08-28', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000051, 'vhv_178', 'v_5055', -34, '2003-08-28', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000052, 'v_5246', 'v_1285', 134, '9999-12-31', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000052, 'v_5246', 'v_1285', 134, '9999-12-31', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000053, 'v_3969', 'v_871', -7, '2024-02-29', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000053, 'v_3969', 'v_871', -7, '2024-02-29', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000054, 'v_3656', 'v_3284', 20, '2005-02-06', 'v_5641');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000054, 'v_3656', 'v_3284', 20, '2005-02-06', 'v_5641');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000055, 'v_3043', 'v_9763', 111, '1000-01-01', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000055, 'v_3043', 'v_9763', 111, '1000-01-01', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000056, 'v_2860', 'not-a-date', -171, '2024-02-29', 'v_4596');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000056, 'v_2860', 'not-a-date', -171, '2024-02-29', 'v_4596');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000057, 'v_9218', 'v_6095', -6, '2018-07-11', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000057, 'v_9218', 'v_6095', -6, '2018-07-11', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000058, 'v_4126', 'v_3810', -151, '2003-08-12', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000058, 'v_4126', 'v_3810', -151, '2003-08-12', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000059, 'v_9559', 'vhv_598', 160, '2024-02-29', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000059, 'v_9559', 'vhv_598', 160, '2024-02-29', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000060, 'v_5525', 'v_3209', 87, '2018-07-19', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000060, 'v_5525', 'v_3209', 87, '2018-07-19', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000061, '01e0', 'not-a-date', -6, '2024-02-29', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000061, '01e0', 'not-a-date', -6, '2024-02-29', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000062, 'v_5529', '01e0', 1, '2018-11-19', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000062, 'v_5529', '01e0', 1, '2018-11-19', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000063, 'v_8975', 'v_8058', -48, '2004-09-13', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000063, 'v_8975', 'v_8058', -48, '2004-09-13', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000064, '01e0', 'v_9813', 36, '2004-05-17', 'v_5368');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000064, '01e0', 'v_9813', 36, '2004-05-17', 'v_5368');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000065, 'v_5970', '01e0', -6, '2010-03-11', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000065, 'v_5970', '01e0', -6, '2010-03-11', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000066, 'not-a-date', 'v_4928', -63, '2010-07-27', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000066, 'not-a-date', 'v_4928', -63, '2010-07-27', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000067, 'v_9624', '01e0', -7, '2024-02-29', 'v_7400');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000067, 'v_9624', '01e0', -7, '2024-02-29', 'v_7400');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000068, 'not-a-date', '01e0', -10, '2005-02-10', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000068, 'not-a-date', '01e0', -10, '2005-02-10', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000069, 'v_3667', 'vhv_598', -86, '2004-05-05', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000069, 'v_3667', 'vhv_598', -86, '2004-05-05', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000070, 'v_327', 'v_203', -5, '2024-02-29', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000070, 'v_327', 'v_203', -5, '2024-02-29', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000071, 'not-a-date', 'v_5977', -7, '2007-04-08', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000071, 'not-a-date', 'v_5977', -7, '2007-04-08', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000072, 'v_7407', '01e0', 81, '2012-01-09', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000072, 'v_7407', '01e0', 81, '2012-01-09', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000073, 'v_8991', 'v_5123', -56, '9999-12-31', 'v_4509');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000073, 'v_8991', 'v_5123', -56, '9999-12-31', 'v_4509');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000074, 'v_8021', 'vhv_598', -5, '2000-01-05', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000074, 'v_8021', 'vhv_598', -5, '2000-01-05', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000075, 'vhv_178', 'not-a-date', -182, '2015-08-08', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000075, 'vhv_178', 'not-a-date', -182, '2015-08-08', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000076, 'vhv_178', 'v_6852', -5, '2008-09-09', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000076, 'vhv_178', 'v_6852', -5, '2008-09-09', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000077, 'v_2999', 'v_1094', -6, '2024-02-29', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000077, 'v_2999', 'v_1094', -6, '2024-02-29', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000078, 'v_7098', 'vhv_598', 5, '9999-12-31', 'v_3448');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000078, 'v_7098', 'vhv_598', 5, '9999-12-31', 'v_3448');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000079, 'vhv_178', 'v_3401', 102, '2015-04-16', 'vhv_234');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000079, 'vhv_178', 'v_3401', 102, '2015-04-16', 'vhv_234');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000080, 'not-a-date', 'v_3446', -7, '2002-07-07', 'v_4990');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000080, 'not-a-date', 'v_3446', -7, '2002-07-07', 'v_4990');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000081, 'vhv_178', '01e0', -109, '2013-10-10', 'v_6697');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000081, 'vhv_178', '01e0', -109, '2013-10-10', 'v_6697');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000082, 'v_250', 'v_1733', -6, '2024-02-29', 'v_9153');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000082, 'v_250', 'v_1733', -6, '2024-02-29', 'v_9153');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000083, 'v_2499', '01e0', -155, '1000-01-01', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000083, 'v_2499', '01e0', -155, '1000-01-01', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000084, 'v_3899', 'v_3663', 174, '2016-05-21', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000084, 'v_3899', 'v_3663', 174, '2016-05-21', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000085, 'not-a-date', 'v_1000', -5, '9999-12-31', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000085, 'not-a-date', 'v_1000', -5, '9999-12-31', 'not-a-date');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000086, 'v_7595', 'v_1524', -6, '9999-12-31', 'v_180');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000086, 'v_7595', 'v_1524', -6, '9999-12-31', 'v_180');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000087, 'v_5788', '01e0', -6, '9999-12-31', '01e0');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000087, 'v_5788', '01e0', -6, '9999-12-31', '01e0');
INSERT INTO vert_s1_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000088, 'v_9432', 'v_2012', -6, '2014-07-11', 'not-a-date');
INSERT INTO vert_s2_516978e6 (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1000088, 'v_9432', 'v_2012', -6, '2014-07-11', 'not-a-date');
ANALYZE TABLE vert_s1_516978e6;
ANALYZE TABLE vert_s2_516978e6;

-- 验证查询 --
SELECT /*implicit_conversion_in*/ `ver1`.`c6`, COALESCE(`ver1`.`c4`, 0) FROM `vert_s1_516978e6` ver1 WHERE `ver1`.`c6` = 'not-a-date' AND `ver1`.`c4` BETWEEN -7 AND 2 AND `ver1`.`c4` = -6 AND `ver1`.`c6` IN (SELECT `sq2`.`c4` FROM `vert_ref_516978e6_t3` sq2 WHERE COALESCE(`sq2`.`c8`, 0) >= 66);
SELECT /*implicit_conversion_in*/ `ver1`.`c6`, COALESCE(`ver1`.`c4`, 0) FROM `vert_s2_516978e6` ver1 WHERE `ver1`.`c6` = 'not-a-date' AND `ver1`.`c4` BETWEEN -7 AND 2 AND `ver1`.`c4` = -6 AND `ver1`.`c6` IN (SELECT `sq2`.`c4` FROM `vert_ref_516978e6_t3` sq2 WHERE COALESCE(`sq2`.`c8`, 0) >= 66);

Expected Behavior

Both vert_s1 and vert_s2 should satisfy the subset monotonicity property for this oracle construction:

|result(S1)| ≤ |result(S2)|

S2 is constructed from a relaxation of S1 and includes the S1 witness rows relevant to this predicate. Therefore, the result on S2 must be greater than or equal to the result on S1.

Expected:

Table	COUNT(*)
S1 (strict, ~87 rows)	2
S2 (relaxed, ~500 rows)	≥ 2

Actual Behavior

Table	COUNT(*)
S1 (strict, ~87 rows)	2
S2 (relaxed, ~500 rows)	0

S2 contains all rows of S1 plus additional rows, yet returns zero results — a direct violation of monotonicity.

Analysis

Query Shape

SELECT ver1.c6, COALESCE(ver1.c4, 0)
FROM <table> ver1
WHERE ver1.c6 = 'not-a-date'
  AND ver1.c4 BETWEEN -7 AND 2
  AND ver1.c4 = -6
  AND ver1.c6 IN (
      SELECT sq2.c4 FROM vert_ref_516978e6_t3 sq2
      WHERE COALESCE(sq2.c8, 0) >= 66
  );

The subquery returns a YEAR column (sq2.c4), while the outer predicate compares against a VARCHAR(10) column (ver1.c6). This cross-type IN subquery is the key trigger pattern.

Key Data

Additional diagnostic queries on the same replay state showed that the filtered subquery result set includes YEAR 0. They also showed that the outer value 'not-a-date' coerces to signed 0, so implicit comparison against YEAR 0 can succeed depending on coercion direction.

Execution Plan Divergence

In the full diagnostic run, both queries were pushed down to TEST_SINGLE_GROUP, but EXPLAIN showed different plan shapes for S1 and S2. This does not by itself prove the root cause, but it strongly suggests that plan choice affects how the predicate is evaluated.

S1 plan (UNIQUE KEY present → const lookup):

id | select_type | table | type  | key  | Extra
 1 | SIMPLE      | ver1  | const | u_c6 | NULL
 1 | SIMPLE      | r     | ALL   | NULL | Using where; FirstMatch(ver1)

Type coercion direction: VARCHAR → SIGNED
→ CAST('not-a-date' AS SIGNED) = 0 = YEAR(0000) → match ✓

S2 plan (no UNIQUE KEY → materialized hash join):

id | select_type  | table       | type | key  | Extra
 1 | SIMPLE       | <subquery2> | ALL  | NULL | NULL
 1 | SIMPLE       | ver1        | ALL  | NULL | Using where; Using join buffer (hash join)
 2 | MATERIALIZED | r           | ALL  | NULL | Using where

Type coercion direction: YEAR → CHAR
→ 'not-a-date' vs '0000' → no match ✗

The Bug

At minimum, this is a wrong-result bug: the same logical predicate returns rows on S1 but no rows on S2, even though the S1 witness rows are still present in S2.

The most likely explanation is that ver1.c6 IN (SELECT sq2.c4 ...) is being evaluated with different implicit coercion behavior under different plan shapes. Result correctness should not depend on that plan choice.

The following diagnostic query supports this interpretation:

SELECT
    CAST('not-a-date' AS SIGNED)       AS varchar_to_signed,  -- 0  (S1 path: matches YEAR 0)
    ('not-a-date' = CAST(0 AS CHAR))   AS year_to_char;       -- 0  (S2 path: '0000' ≠ 'not-a-date')

Start by running the supplied SQL reproduction with sql_mode set to an empty string on SINGLE tables, then compare the S1 and S2 results and execution plans. Trace the VARCHAR IN (SELECT YEAR_col) predicate and its coercion behavior across the differing plan shapes. Done means S2 returns the S1 result as a subset and the predicate produces consistent results regardless of constraints or plan choice.