MySQL Bugs: #97967: Empty resultset for subquery with union and text column with constant expression

Bug #97967	Empty resultset for subquery with union and text column with constant expression
Submitted:	12 Dec 2019 12:11	Modified:	18 Dec 2019 17:39
Reporter:	Jörg Bender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.18	OS:	Windows
Assigned to:		CPU Architecture:	x86
Tags:	constant expression, empty resultset, text, UNION

Description:
MySql Server 8.0.18 return wrong (empty) result for query.
In 8.0.16 and ~ all previous versions this work correctly.

This happened in a subquery with an resultset column of type text and a constant expression false in a union.

How to repeat:
CREATE TABLE ttt (
  id INT NOT NULL,
  textCol text NULL,
  PRIMARY KEY (id)
);

INSERT INTO ttt (id) VALUES ('1');
INSERT INTO ttt (id) VALUES ('2');

        SELECT x1.*
        FROM xxx x1
        WHERE  x1.id IN (1)
        
#===> 1 row selected (OK)

SELECT * FROM (
        SELECT x1.*
        FROM xxx x1
        WHERE  x1.id IN (1)
     UNION
        SELECT x2.*
        FROM xxx x2
        WHERE 1 = 0
) allxxx

#===> 0 row selected (WRONG, should be also 1)

# if you change column type textCol from text to varchar(30) for example => 1 row selected (OK)

# or if you change constant expression WHERE 1 = 0 to WHERE x2.id IN (99) => 1 row selected (OK)

# or if your columns in select contains no text columns like SELECT x1.id and SELECT x2.id => 1 row selected (OK)

# reproducible with innoDb as well as with MyISAM

Thank you for the bug report.

Posted by developer:
 
Fixed in 8.0.20.

In unions of a const table and zero or more known-zero expressions,
derived tables of exactly one row could be read incorrectly as having
zero rows.