MySQL Bugs: #116415: Table union data error

Bug #116415	Table union data error
Submitted:	19 Oct 2024 6:27	Modified:	6 Nov 2024 22:52
Reporter:	John Jove	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	8.4.2, 8.0.40	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

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;

Hello John,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

-- 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

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.