MySQL Bugs: #110597: Different results from equivalent queries

Bug #110597	Different results from equivalent queries
Submitted:	4 Apr 2023 4:20	Modified:	4 Apr 2023 12:38
Reporter:	Yuxi Ling	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.32	OS:	MacOS (12.4)
Assigned to:		CPU Architecture:	Any

Description:
Different results from equivalent queries. The results before the `join` clause are the same. But after the `join` operation, they turn out to be different.

How to repeat:
Database Creation:
```
CREATE TABLE tb1 ( 
    c1 int, 
    c2 int
);
CREATE TABLE tb2 ( 
    c1 int, 
    c2 int
);
INSERT INTO tb1 (c1, c2) VALUES (1, 1);
INSERT INTO tb2 (c1, c2) VALUES (1, 1);
```
Test Cases and Results:
```
WITH table_1 AS (
  SELECT
    c1
  FROM
    tb1
),
table_2 AS (
  SELECT
    10 AS `a1`
  FROM
    table_1
    RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
)
SELECT
  `a1`
FROM
  table_2
ORDER BY `a1`
  LIMIT 1
```
```
+---------+
|    a1   |
+---------+
|    10   |
+---------+
1 row in set (0.00 sec)
```
```
WITH table_1 AS (
  SELECT
    10 AS `a1`,
    c1
  FROM
    tb1
),
table_2 AS (
  SELECT
    table_1.`a1`
  FROM
    table_1
    RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
)
SELECT
  `a1`
FROM
  table_2
ORDER BY `a1`
  LIMIT 1
```
```
+--------+
|   a1   |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)
```

Hello Yuxi Ling,

Thank you for the report and test case.
I'm not seeing any discrepancies in the provided test case. Is there anything else we are missing here(changing opt switch for second query etc)? Thank you.

==
 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.32 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 test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE tb1 (
    ->     c1 int,
    ->     c2 int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE tb2 (
    ->     c1 int,
    ->     c2 int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tb1 (c1, c2) VALUES (1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tb2 (c1, c2) VALUES (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> WITH table_1 AS (
    ->   SELECT
    ->     c1
    ->   FROM
    ->     tb1
    -> ),
    -> table_2 AS (
    ->   SELECT
    ->     10 AS `a1`
    ->   FROM
    ->     table_1
    ->     RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
    -> )
    -> SELECT
    ->   `a1`
    -> FROM
    ->   table_2
    -> ORDER BY `a1`
    ->   LIMIT 1;
+----+
| a1 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)

mysql> WITH table_1 AS (
    ->   SELECT
    ->     10 AS `a1`,
    ->     c1
    ->   FROM
    ->     tb1
    -> ),
    -> table_2 AS (
    ->   SELECT
    ->     table_1.`a1`
    ->   FROM
    ->     table_1
    ->     RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
    -> )
    -> SELECT
    ->   `a1`
    -> FROM
    ->   table_2
    -> ORDER BY `a1`
    ->   LIMIT 1;
+------+
| a1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

regards,
Umesh

database for reproduction

Attachment: test.sql (application/octet-stream, text), 67.51 KiB.

Sorry for my bad example. I think the reason is that the given table creation example is too simple. Please find the attached file, test.sql, and import it to your database. It helps you create two tables for testing, corresponding to tables of `tb1` and `tb2` in the test cases. After that, you run two equivalent queries. If everything goes well, the error should occur.

Thanks.

Thank you for the details.
Verified as described.

regards,
Umesh

- 8.0.32

bin/mysql -uroot -S /tmp/mysql.sock test < test.sql
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 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> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> WITH table_1 AS (
    ->   SELECT
    ->     c1
    ->   FROM
    ->     tb1
    -> ),
    -> table_2 AS (
    ->   SELECT
    ->     10 AS `a1`
    ->   FROM
    ->     table_1
    ->     RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
    -> )
    -> SELECT
    ->   `a1`
    -> FROM
    ->   table_2
    -> ORDER BY `a1`
    ->   LIMIT 1;
+----+
| a1 |
+----+
| 10 |
+----+
1 row in set (0.01 sec)

mysql> WITH table_1 AS (
    ->   SELECT
    ->     10 AS `a1`,
    ->     c1
    ->   FROM
    ->     tb1
    -> ),
    -> table_2 AS (
    ->   SELECT
    ->     table_1.`a1`
    ->   FROM
    ->     table_1
    ->     RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
    -> )
    -> SELECT
    ->   `a1`
    -> FROM
    ->   table_2
    -> ORDER BY `a1`
    ->   LIMIT 1;
+------+
| a1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> explain WITH table_1 AS (
    ->   SELECT
    ->     c1
    ->   FROM
    ->     tb1
    -> ),
    -> table_2 AS (
    ->   SELECT
    ->     10 AS `a1`
    ->   FROM
    ->     table_1
    ->     RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
    -> )
    -> SELECT
    ->   `a1`
    -> FROM
    ->   table_2
    -> ORDER BY `a1`
    ->   LIMIT 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | tb2   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL                                       |
|  1 | SIMPLE      | tb1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain WITH table_1 AS (
    ->   SELECT
    ->     10 AS `a1`,
    ->     c1
    ->   FROM
    ->     tb1
    -> ),
    -> table_2 AS (
    ->   SELECT
    ->     table_1.`a1`
    ->   FROM
    ->     table_1
    ->     RIGHT JOIN tb2 ON table_1.c1 = tb2.c1
    -> )
    -> SELECT
    ->   `a1`
    -> FROM
    ->   table_2
    -> ORDER BY `a1`
    ->   LIMIT 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | tb2   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using temporary; Using filesort            |
|  1 | SIMPLE      | tb1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

Hi, Yuxi Ling:

I do not think that your two queries are equivalent.

mysql> CREATE TABLE tb1 (c1 int, c2 int);

mysql> CREATE TABLE tb2 (c1 int, c2 int);

mysql> INSERT INTO tb1 (c1, c2) VALUES (1, 1);

mysql> INSERT INTO tb2 (c1, c2) VALUES (1, 1);

mysql> INSERT INTO tb2 (c1, c2) VALUES (2, 2);

mysql> INSERT INTO tb1 (c1, c2) VALUES (3, 3);

mysql> WITH table_1 AS (SELECT c1 FROM tb1),
    -> table_2 AS (SELECT  10 AS `a1`  FROM  table_1 RIGHT JOIN tb2 ON table_1.c1 = tb2.c1)
    -> SELECT `a1` FROM table_2 ORDER BY `a1` LIMIT 1;
+----+
| a1 |
+----+
| 10 |
+----+

mysql> WITH table_1 AS ( SELECT 10 AS `a1`, c1 FROM tb1),
    -> table_2 AS (SELECT table_1.`a1` FROM table_1 RIGHT JOIN tb2 ON table_1.c1 = tb2.c1)
    -> SELECT  `a1`  FROM table_2 ORDER BY `a1` LIMIT 1;
+------+
| a1   |
+------+
| NULL |
+------+

mysql> WITH table_1 AS ( SELECT 10 AS `a1`, c1 FROM tb1),
    -> table_2 AS (SELECT table_1.`a1` FROM table_1 RIGHT JOIN tb2 ON table_1.c1 = tb2.c1)
    -> SELECT  `a1`  FROM table_2 ORDER BY `a1`;
+------+
| a1   |
+------+
| NULL |
|   10 |
+------+

mysql> WITH table_1 AS (SELECT c1 FROM tb1),
    -> table_2 AS (SELECT  10 AS `a1`  FROM  table_1 RIGHT JOIN tb2 ON table_1.c1 = tb2.c1)
    -> SELECT `a1` FROM table_2 ORDER BY `a1`;
+----+
| a1 |
+----+
| 10 |
| 10 |
+----+