MySQL Bugs: #112378: Incorrect result

Bug #112378	Incorrect result
Submitted:	19 Sep 2023 2:03	Modified:	24 Sep 2023 0:57
Reporter:	Doris Li	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
set optimizer_switch='materialization=on,semijoin=off';
-- SQL query with two levels of nesting, "NOT IN," "IN," filtering a column with NULL values, and adding the structure "NOT IN () = column name"
SELECT b.Title
FROM Books b
WHERE b.Author IN (
    SELECT a.AuthorName
    FROM Authors a
    WHERE a.Birthdate IS NOT NULL
    AND (a.AuthorName NOT IN (
        SELECT c.Author
        FROM Books c
        WHERE c.PublicationYear < 2000
    )) = a.AuthorName
);
-- Empty set, 3 warnings (0.00 sec)
set optimizer_switch='materialization=on,semijoin=on';
-- SQL query with two levels of nesting, "NOT IN," "IN," filtering a column with NULL values, and adding the structure "NOT IN () = column name"
SELECT b.Title
FROM Books b
WHERE b.Author IN (
    SELECT a.AuthorName
    FROM Authors a
    WHERE a.Birthdate IS NOT NULL
    AND (a.AuthorName NOT IN (
        SELECT c.Author
        FROM Books c
        WHERE c.PublicationYear < 2000
    )) = a.AuthorName
);
-- +--------+
-- | Title  |
-- +--------+
-- | Book 1 |
-- | Book 2 |
-- | Book 5 |
-- +--------+
-- 3 rows in set (0.00 sec)

How to repeat:
CREATE DATABASE Library;

USE Library;

CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    PublicationYear INT,
    ISBN VARCHAR(13)
);

INSERT INTO Books (Title, Author, PublicationYear, ISBN) VALUES
    ('Book 1', 'Author 1', 2000, '1234567890'),
    ('Book 2', 'Author 2', NULL, '2345678901'),
    ('Book 3', 'Author 3', 1995, NULL),
    ('Book 4', 'Author 10', 2010, '3456789012'),
    ('Book 5', 'Author 5', NULL, NULL);

USE Library;

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY AUTO_INCREMENT,
    AuthorName VARCHAR(255) NOT NULL,
    Birthdate DATE
);

INSERT INTO Authors (AuthorName, Birthdate) VALUES
    ('Author 1', '1980-05-10'),
    ('Author 2', '1975-09-22'),
    ('Author 3', NULL),
    ('Author 4', NULL),
    ('Author 5', '1990-03-15');

Hi Mrs Li,

Thank you for your bug report.

However, we were not been able to repeat it with latest 8.0 and 8.1.

We have got the same results for both variants of the optimizer_switch:

Level	Code	Message
Warning	1292	Truncated incorrect DOUBLE value: 'Author 1'
Warning	1292	Truncated incorrect DOUBLE value: 'Author 2'
Warning	1292	Truncated incorrect DOUBLE value: 'Author 5'
Level	Code	Message
Warning	1292	Truncated incorrect DOUBLE value: 'Author 1'
Warning	1292	Truncated incorrect DOUBLE value: 'Author 2'
Warning	1292	Truncated incorrect DOUBLE value: 'Author 5'

Unlike your tests , here both queries produced zero results and exactly the same warnings.

It is expected behaviour that you can not expect to get a DOUBLE value from "Author X". Do note that DOUBLE values are used in expressions in some cases when a common denominator is required.

Can't repeat.

I run my test case on the docker, you may should try it again on the docker version of Mysql 8.0.28.

Hi,

We do not make special Docker versions of our product.

But, we do test with latest releases and your release is not latest available.