Description:
A query that uses an IN clause to compare a CHARACTER type column against a subquery returning a BIT type column yields inconsistent results between the optimized execution plan and the unoptimized row count calculation. The optimizer correctly evaluates the implicit type conversion, resulting in a TRUE condition, while the unoptimized path incorrectly evaluates it as FALSE.
How to repeat:
CREATE TABLE t1529 (c1 CHARACTER, c2 BIT NOT NULL);
INSERT HIGH_PRIORITY IGNORE INTO t1529 (c1,c2) VALUES ('r',b'1');
INSERT INTO t1529 (c1,c2) VALUES ('v',b'1');
INSERT HIGH_PRIORITY IGNORE INTO t1529 (c1,c2) VALUES ('c',b'1');
INSERT IGNORE INTO t1529 (c1,c2) VALUES ('a',b'0');
SELECT ca6 FROM (SELECT c1 ca4, c1, c2 ca6 FROM t1529) ta1 WHERE (((((c1)) IN (SELECT c2 FROM t1529))));
-- Returned Row Count: 4
SELECT SUM(count) FROM (SELECT ((((((c1)) IN (SELECT c2 FROM t1529))))) IS TRUE AS count FROM (SELECT c1 ca4, c1, c2 ca6 FROM t1529) ta1) AS ta_norec;
-- Returned Row Count: 0
Description: A query that uses an IN clause to compare a CHARACTER type column against a subquery returning a BIT type column yields inconsistent results between the optimized execution plan and the unoptimized row count calculation. The optimizer correctly evaluates the implicit type conversion, resulting in a TRUE condition, while the unoptimized path incorrectly evaluates it as FALSE. How to repeat: CREATE TABLE t1529 (c1 CHARACTER, c2 BIT NOT NULL); INSERT HIGH_PRIORITY IGNORE INTO t1529 (c1,c2) VALUES ('r',b'1'); INSERT INTO t1529 (c1,c2) VALUES ('v',b'1'); INSERT HIGH_PRIORITY IGNORE INTO t1529 (c1,c2) VALUES ('c',b'1'); INSERT IGNORE INTO t1529 (c1,c2) VALUES ('a',b'0'); SELECT ca6 FROM (SELECT c1 ca4, c1, c2 ca6 FROM t1529) ta1 WHERE (((((c1)) IN (SELECT c2 FROM t1529)))); -- Returned Row Count: 4 SELECT SUM(count) FROM (SELECT ((((((c1)) IN (SELECT c2 FROM t1529))))) IS TRUE AS count FROM (SELECT c1 ca4, c1, c2 ca6 FROM t1529) ta1) AS ta_norec; -- Returned Row Count: 0