| Bug #116415 | Table union data error | ||
|---|---|---|---|
| Submitted: | 19 Oct 6:27 | Modified: | 6 Nov 22:52 |
| Reporter: | John Jove | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 8.4.2, 8.0.40 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
[19 Oct 13:34]
MySQL Verification Team
Hello John, Thank you for the report and test case. Verified as described. regards, Umesh
[19 Oct 15:00]
MySQL Verification Team
-- 8.0.40 ( even in earlier GAs 5.6/5.7 ) ./mtr bug116415 --nocheck-testcases Logging: ./mtr bug116415 --nocheck-testcases MySQL Version 8.0.40 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/export/home/tmp/ushastry/mysql-8.0.40/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CREATE TABLE t1 ( c1 BOOL, c2 YEAR, unique_id SERIAL PRIMARY KEY ); INSERT INTO t1 (c1, c2) VALUES (TRUE, 1999); SELECT c1 FROM t1 WHERE c1 = 1; c1 1 SELECT c2 FROM t1 WHERE c2 < 2000; c2 1999 SELECT c1 FROM t1 WHERE c1 = 1 UNION SELECT c2 FROM t1 WHERE c2 < 2000; c1 1 127 SELECT c1 FROM t1 WHERE c1 = 1 UNION SELECT CAST(c2 AS UNSIGNED) FROM t1 WHERE c2 < 2000; c1 1 1999 Per manual - If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. https://dev.mysql.com/doc/refman/8.4/en/type-conversion.html
[6 Nov 22:52]
Jon Stephens
Documented fix as follows in the MySQL 9.2.0 changelog:
When taking the UNION of a YEAR column and a BOOL (TINYINT)
column, the resulting data type was TINYINT, due to a flaw in
the internal field_types_merge_rules array. We fix this by
adjusting the result data type in such cases to SMALLINT.
Closed.
Description: what you did: You don't get the right result when you union table data CREATE TABLE t1 ( c1 BOOL, c2 YEAR, unique_id SERIAL PRIMARY KEY ); INSERT INTO t1 (c1, c2) VALUES (TRUE, 1999); SELECT c1 FROM t1 WHERE c1 = 1; SELECT c2 FROM t1 WHERE c2 < 2000; SELECT c1 FROM t1 WHERE c1 = 1 UNION SELECT c2 FROM t1 WHERE c2 < 2000; The first and second SELECT queries return 1 and 1999, respectively, and the third SELECT statement is the UNION of the first and second SELECT statements. what you wanted to happen: The third SELECT query should return ((1,), (1999,)). what actually happened: The third SELECT query actually returns (1,), (127,)). How to repeat: CREATE TABLE t1 ( c1 BOOL, c2 YEAR, unique_id SERIAL PRIMARY KEY ); INSERT INTO t1 (c1, c2) VALUES (TRUE, 1999); SELECT c1 FROM t1 WHERE c1 = 1; SELECT c2 FROM t1 WHERE c2 < 2000; SELECT c1 FROM t1 WHERE c1 = 1 UNION SELECT c2 FROM t1 WHERE c2 < 2000;