MySQL Bugs: #112354: Incorrect result

Bug #112354	Incorrect result
Submitted:	15 Sep 2023 7:52	Modified:	19 Oct 2023 6:35
Reporter:	Doris Li	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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
);

-- Inserting data into the Authors table
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');

-- Triggering a potentially unexpected behavior (similar to the reported bug)
SELECT b.Title
FROM Books b
WHERE b.PublicationYear IN (
    SELECT a.PublicationYear
    FROM Books a
    WHERE (
        a.PublicationYear NOT IN (
            SELECT DISTINCT b.PublicationYear
            FROM Books b
            WHERE b.Title IS NOT NULL
        )
    ) = a.PublicationYear
);

How to repeat:
set optimizer_switch='materialization=on,semijoin=off';
SELECT b.Title
FROM Books b
WHERE b.PublicationYear IN (
    SELECT a.PublicationYear
    FROM Books a
    WHERE (
        a.PublicationYear NOT IN (
            SELECT DISTINCT b.PublicationYear
            FROM Books b
            WHERE b.Title IS NOT NULL
        )
    ) = a.PublicationYear
);
Empty Set

mysql> set optimizer_switch='materialization=on,semijoin=on';
Query OK, 0 rows affected (0.00 sec)
SELECT b.Title
FROM Books b
WHERE b.PublicationYear IN (
    SELECT a.PublicationYear
    FROM Books a
    WHERE (
        a.PublicationYear NOT IN (
            SELECT DISTINCT b.PublicationYear
            FROM Books b
            WHERE b.Title IS NOT NULL
        )
    ) = a.PublicationYear
);
+--------+
| Title  |
+--------+
| Book 1 |
| Book 3 |
| Book 4 |
+--------+
3 rows in set (0.00 sec)

These two sql are the same, but they don't return the same result.

Hello Doris Li,

Thank you for the report and feedback.
This issue is no longer seen in the latest GA version and most likely fixed. Could you please check and confirm at your end? 

##
 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE Library;
    ('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
);

-- Inserting data into the Authors table
INSERT INTO Authors (AuthorName, Birthdate) VALUES
    ('Author 1', '1980-05-10'),
    ('Author 2', '1975-09-22'),
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> USE Library;
Database changed
mysql>
mysql> CREATE TABLE Books (
    ->     BookID INT PRIMARY KEY AUTO_INCREMENT,
    ->     Title VARCHAR(255) NOT NULL,
    ->     Author VARCHAR(255) NOT NULL,
    ->     PublicationYear INT,
    ->     ISBN VARCHAR(13)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> 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);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> USE Library;
Database changed
mysql>
mysql> CREATE TABLE Authors (
    ->     AuthorID INT PRIMARY KEY AUTO_INCREMENT,
    ->     AuthorName VARCHAR(255) NOT NULL,
    ->     Birthdate DATE
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> -- Inserting data into the Authors table
mysql> 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');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> set optimizer_switch='materialization=on,semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT b.Title
    -> FROM Books b
    -> WHERE b.PublicationYear IN (
    ->     SELECT a.PublicationYear
    ->     FROM Books a
    ->     WHERE (
    ->         a.PublicationYear NOT IN (
    ->             SELECT DISTINCT b.PublicationYear
    ->             FROM Books b
    ->             WHERE b.Title IS NOT NULL
    ->         )
    ->     ) = a.PublicationYear
    -> );
Empty set (0.00 sec)

mysql> set optimizer_switch='materialization=on,semijoin=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT b.Title
    -> FROM Books b
    -> WHERE b.PublicationYear IN (
    ->     SELECT a.PublicationYear
    ->     FROM Books a
    ->     WHERE (
    ->         a.PublicationYear NOT IN (
    ->             SELECT DISTINCT b.PublicationYear
    ->             FROM Books b
    ->             WHERE b.Title IS NOT NULL
    ->         )
    ->     ) = a.PublicationYear
    -> );
Empty set (0.00 sec)

mysql>

regards,
Umesh

I try it on my docker version mysql 8.0.28. There is a bug like description.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".