Description:
In MySQL, when querying a materialized table and a view using the CHAR function and complex logical conditions, the results are inconsistent.
How to repeat:
1. Set up table t0:
CREATE TABLE t0 (c0 MEDIUMTEXT, c1 ENUM('val0','val1'), c2 TINYBLOB);
INSERT INTO t0 (c0, c1, c2) VALUES ('y1i9F9xtt4EsejUxw7IJZQ2xrkNYzFNuyqM2tFSYqaR0bZexoSSirskRn2vKDk7gFDfpToYYe391ntb4Y4fzKinvpsPx1yLjBmK3mgiZ8RvLfN315sRPa', '0', 'j3w');
INSERT INTO t0 (c0, c1, c2) VALUES ('eIZVJsCLWMTJlsRpcARadMCHUFK7cff90mCVo0SEZZMpdlOrZYBsFNUvqx0QKnv2ZHa4FY4wlsIlWVSu50XWlzNQKhXKTFOeuF8qDFTTKn5E3hn2DxfaqMpmE17c9hu9WWOQ2km63Tq4rXmj3NEbEhcJn8fWa1WizmWSC9F9oxzGavd6Rj53XQ52oNYIgSWtk41Rrs4iBJkTdOoP0TqD7eaCWoZJXOyqtJoD81fzly6SBrJ5vzmWu', '0', 'bcz0KILBuzhLHiwoKBBHnobGS5ZweT97IdZ7BBsd5A7lLvWF5ZsgkFZ2AkLe4DbUEbeLwjDj9YbgemtotyWBtZL9ubGj8m1M92BPdLNPSWi0D5ACX9rH6heewUr5aKJWlaiOAmnew2dPGFfTV53O7fWwdPFSoaGBijgQkf0BvmigBXG4Iba0Z4jSHIglP4N7Ox4BLHwG');
INSERT INTO t0 (c0, c1, c2) VALUES ('JpG06z5w8trWYPztjNYiW8CxWdtSbq6gniM5T10Svtn8juaH2KaTbPgTDf4gwwODX0lj8CuLUu97Ujb5Z4Ss0izUlcGUWXlzi4uDRdrVhyjprSwxgUUvbSFNCpZfH8yaWLXT2oYGKW40M7Ni9w6gGe6q86SQtX3hEpQFY5R3s', 'V', 'BIb8Kek2ngiMzw2dNXT1ciMBjir3H5ewnxgMJHmMwwy9MjKGxNriF95nKNo6AERuQa7c1xVMF4vNFi58scCAcrPepky6Jn76PA8kL3qtqdruVfw3oXUfDW6YlWkHIdDSZslrwC1NqYU4SZm5YIbN');
INSERT INTO t0 (c0, c1, c2) VALUES ('kXPaC8P08xoqYATlves8HCubfF1CKB36AFgdCdcVa', 'V', 'gTyA8nJXf0vPDmxxHk1OI2TRs8HpnUuwkb4Eko8x2uqEGlArAYBt5n7yWVYN7rruXmC47e0oWhFh7wrFkyvoA19Aegsnpn6aB9BOseojFQzh7U68ZKWO80myShr61XEO4cpZOhewbtPGOLqtoaOlSPU6swnDtyXLJUekwCSYFyWS90HkJZ');
2. Create materialized table t1 and query it:
CREATE TABLE t1 AS (SELECT (CHAR(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 );
SELECT c1, (c0) FROM t1 WHERE (('5qXh0PMNtJSHkJ8P44CBCadEkbb4pdrX6sK41atCWKCNcqBk4jVghaUMHAoKuvG8Wcy9dOesAHTCaqkO54mT7ebBjpKgtDJtJT3axCNtai2xU0fUiwFvUnJBcC5ncPvSTKlSETtpYE9Ut5TGxtwoo' OR 0xC9577507C4) AND (c1 != (c0))) ORDER BY (c0) ASC;
+------+------------+
| c1 | c0 |
+------+------------+
| | 0x00 |
| | 0x00 |
+------+------------+
2 rows in set, 1 warning (0.00 sec)
3. Create view t1 instead and query it:
CREATE VIEW t1 AS (SELECT (CHAR(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 );
SELECT c1, (c0) FROM t1 WHERE (('5qXh0PMNtJSHkJ8P44CBCadEkbb4pdrX6sK41atCWKCNcqBk4jVghaUMHAoKuvG8Wcy9dOesAHTCaqkO54mT7ebBjpKgtDJtJT3axCNtai2xU0fUiwFvUnJBcC5ncPvSTKlSETtpYE9Ut5TGxtwoo' OR 0xC9577507C4) AND (c1 != (c0))) ORDER BY (c0) ASC;
Empty set, 1 warning (0.00 sec)
Expected Behavior:
Both the materialized table and the view should return consistent results based on the query conditions.
Description: In MySQL, when querying a materialized table and a view using the CHAR function and complex logical conditions, the results are inconsistent. How to repeat: 1. Set up table t0: CREATE TABLE t0 (c0 MEDIUMTEXT, c1 ENUM('val0','val1'), c2 TINYBLOB); INSERT INTO t0 (c0, c1, c2) VALUES ('y1i9F9xtt4EsejUxw7IJZQ2xrkNYzFNuyqM2tFSYqaR0bZexoSSirskRn2vKDk7gFDfpToYYe391ntb4Y4fzKinvpsPx1yLjBmK3mgiZ8RvLfN315sRPa', '0', 'j3w'); INSERT INTO t0 (c0, c1, c2) VALUES ('eIZVJsCLWMTJlsRpcARadMCHUFK7cff90mCVo0SEZZMpdlOrZYBsFNUvqx0QKnv2ZHa4FY4wlsIlWVSu50XWlzNQKhXKTFOeuF8qDFTTKn5E3hn2DxfaqMpmE17c9hu9WWOQ2km63Tq4rXmj3NEbEhcJn8fWa1WizmWSC9F9oxzGavd6Rj53XQ52oNYIgSWtk41Rrs4iBJkTdOoP0TqD7eaCWoZJXOyqtJoD81fzly6SBrJ5vzmWu', '0', 'bcz0KILBuzhLHiwoKBBHnobGS5ZweT97IdZ7BBsd5A7lLvWF5ZsgkFZ2AkLe4DbUEbeLwjDj9YbgemtotyWBtZL9ubGj8m1M92BPdLNPSWi0D5ACX9rH6heewUr5aKJWlaiOAmnew2dPGFfTV53O7fWwdPFSoaGBijgQkf0BvmigBXG4Iba0Z4jSHIglP4N7Ox4BLHwG'); INSERT INTO t0 (c0, c1, c2) VALUES ('JpG06z5w8trWYPztjNYiW8CxWdtSbq6gniM5T10Svtn8juaH2KaTbPgTDf4gwwODX0lj8CuLUu97Ujb5Z4Ss0izUlcGUWXlzi4uDRdrVhyjprSwxgUUvbSFNCpZfH8yaWLXT2oYGKW40M7Ni9w6gGe6q86SQtX3hEpQFY5R3s', 'V', 'BIb8Kek2ngiMzw2dNXT1ciMBjir3H5ewnxgMJHmMwwy9MjKGxNriF95nKNo6AERuQa7c1xVMF4vNFi58scCAcrPepky6Jn76PA8kL3qtqdruVfw3oXUfDW6YlWkHIdDSZslrwC1NqYU4SZm5YIbN'); INSERT INTO t0 (c0, c1, c2) VALUES ('kXPaC8P08xoqYATlves8HCubfF1CKB36AFgdCdcVa', 'V', 'gTyA8nJXf0vPDmxxHk1OI2TRs8HpnUuwkb4Eko8x2uqEGlArAYBt5n7yWVYN7rruXmC47e0oWhFh7wrFkyvoA19Aegsnpn6aB9BOseojFQzh7U68ZKWO80myShr61XEO4cpZOhewbtPGOLqtoaOlSPU6swnDtyXLJUekwCSYFyWS90HkJZ'); 2. Create materialized table t1 and query it: CREATE TABLE t1 AS (SELECT (CHAR(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 ); SELECT c1, (c0) FROM t1 WHERE (('5qXh0PMNtJSHkJ8P44CBCadEkbb4pdrX6sK41atCWKCNcqBk4jVghaUMHAoKuvG8Wcy9dOesAHTCaqkO54mT7ebBjpKgtDJtJT3axCNtai2xU0fUiwFvUnJBcC5ncPvSTKlSETtpYE9Ut5TGxtwoo' OR 0xC9577507C4) AND (c1 != (c0))) ORDER BY (c0) ASC; +------+------------+ | c1 | c0 | +------+------------+ | | 0x00 | | | 0x00 | +------+------------+ 2 rows in set, 1 warning (0.00 sec) 3. Create view t1 instead and query it: CREATE VIEW t1 AS (SELECT (CHAR(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 ); SELECT c1, (c0) FROM t1 WHERE (('5qXh0PMNtJSHkJ8P44CBCadEkbb4pdrX6sK41atCWKCNcqBk4jVghaUMHAoKuvG8Wcy9dOesAHTCaqkO54mT7ebBjpKgtDJtJT3axCNtai2xU0fUiwFvUnJBcC5ncPvSTKlSETtpYE9Ut5TGxtwoo' OR 0xC9577507C4) AND (c1 != (c0))) ORDER BY (c0) ASC; Empty set, 1 warning (0.00 sec) Expected Behavior: Both the materialized table and the view should return consistent results based on the query conditions.