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

[12 Dec 2019 12:11] Jörg Bender
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
[12 Dec 2019 12:38] MySQL Verification Team
Thank you for the bug report.
[18 Dec 2019 17:39] Paul DuBois
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.