Bug #112917 The same query results in different results (INNODB and MYISAM)
Submitted: 1 Nov 2023 11:47 Modified: 3 Nov 2023 12:14
Reporter: Ying FU Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engines Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[1 Nov 2023 11:47] Ying FU 
Description:
Different from Bug #112913 (while the result of INNODB and MYISAM is the same), in this issue, when you execute the same query, the result of INNODB and MYISAM is inconsistent.

MYISAM:
+------+------+
| c0   | c1   |
+------+------+
| NULL | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.01 sec)

INNODB:
Empty set (0.00 sec)

How to repeat:
Server1:

create database test;
use test;
source create-MYISAM.sql;
source select.sql;

Server2:
create database test;
use test;
source create-INNODB.sql;
source select.sql;
[1 Nov 2023 11:48] Ying FU 
create-INNODB.sql

Attachment: create-INNODB.sql (application/octet-stream, text), 297.65 KiB.
[1 Nov 2023 11:49] Ying FU 
create-MYISAM.sql

Attachment: create-MYISAM.sql (application/octet-stream, text), 297.65 KiB.
[1 Nov 2023 11:49] Ying FU 
select.sql

Attachment: select.sql (application/octet-stream, text), 17.46 KiB.
[1 Nov 2023 14:05] Ying FU 
Because this is automatically generated sql, there may be problems. 
You just need to execute 
> source xxx.sql

Please execute according to my reproduction step.
[1 Nov 2023 14:11] Ying FU 
The main problem is that the two engines perform inconsistently after executing the same sql (there may be errors in the sql).
[2 Nov 2023 13:33] MySQL Verification Team 
Hi Mr. Fu,

Thank you for your report.

We can not repeat what you are reporting.

This is what we get:

source create-MYISAM.sql
Query OK, 0 rows affected, 35 warnings (0.05 sec)

Query OK, 1 row affected, 35 warnings (0.00 sec)

Query OK, 1 row affected, 35 warnings (0.00 sec)

ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR:
No query specified

continued in the next comment.
[2 Nov 2023 13:34] MySQL Verification Team 
source ./tmp/create-INNODB.sql
Query OK, 0 rows affected, 35 warnings (0.04 sec)

Query OK, 1 row affected, 35 warnings (0.00 sec)

Query OK, 1 row affected, 35 warnings (0.00 sec)

ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '0' for key 'table67.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1' for key 'table67.PRIMARY'
ERROR:
No query specified

Can't repeat.
[2 Nov 2023 14:26] MySQL Verification Team 
HI,

One more comment from us.

We have got only two rows wtih  both MyISAM and InnoDB engines.

However, there is a difference. Those two rows are the same, but in a different physical order.

Your query in select.sql is definitely  depending.  on the physical ordering of the rows. That is because you have nested queries that rely on the scanning of the entire table. And scanning depends on the physical ordering of the rows.

That is why you think that it is a bug, but it is not.

You simply have to add ORDER BY in many nested queries in your select.sql.

Can't repeat.
[2 Nov 2023 14:58] Ying FU 
Hi, thanks for your effort.

But I think you should at least follow the steps when reproducing. According to your comment, you only executed create.sql. As long as you execute select.sql immediately after executing create.sql, you can reproduce my problem.

My question is why the results returned by executing the same statement on the two engines are inconsistent.
I would like to ask, do you think 

Empty set

and 
+------+------+
| c0   | c1   |
+------+------+
| NULL | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.01 sec)

are the same?
[2 Nov 2023 15:01] Ying FU 
Hi, I think it's irresponsible of you to classify this bug as can't repeat. 

Because Percona's verifiers did reproduce it successfully. The description I gave is exactly the same.

https://jira.percona.com/browse/PS-8987
[2 Nov 2023 15:03] Ying FU 
Sorry, I mean https://jira.percona.com/browse/PS-8987 is same as https://bugs.mysql.com/bug.php?id=112913.

Bug #112917 is another inconsistent issue.
[3 Nov 2023 10:43] MySQL Verification Team 
Hi,

First of all, we only got two rows with all your test cases.

If some other product got more, that is their problem.

Let us inform you of some basic SQL facts.

As per the SQL standard a relation is a set of the unordered tuples, each tuple being a set of unordered attributes. 

If you specify a wildcard in your SELECT ,like SELECT * FROM table, you can get a select list in any order. The order is not guaranteed.

Also, the order of rows is not guaranteed unless you use ORDER BY some unique set of columns.

Your complex query depends a lot on the physical order of the rows, which is not allowed as per SQL standard.

Each storage engine orders columns and rows in different ways. That is OK according to SQL Standard, which we follow.

We would be happy to verify this bug if you would:

1. Send us CREATE  test cases which would not return any error nor any warning.

2. Send us SELECT that would have each SELECT have a list of columns specified in the SELECT list and  rows sorted by ORDER BY.

Then, if we can repeat the problem, we will definitely verify this bug as S2 bug !!!!!

We are eagerly waiting to get the proper test case, as described above.
[3 Nov 2023 12:02] MySQL Verification Team 
Hi,

These are our latest results.

With InnoDB:

source ./tmp/select.sql
Empty set (0.05 sec)

With MyISAM:

source ./tmp/select.sql
+------+------+
| c0   | c1   |
+------+------+
| NULL | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.01 sec)

With MEMORY:

source ./tmp/select.sql
+------+------+
| c0   | c1   |
+------+------+
| NULL | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

This is sufficient for us.

This bug is now verified.
[3 Nov 2023 12:12] Ying FU 
Thanks for your efforts. And I will improve my reproduction case next time. Thanks for your advice.

But I think Bug #112913 is different from this one, the inconsistent engines are different. Would you reopen Bug #112913?
[3 Nov 2023 12:14] Ying FU 
The contents of create.sql and select.sql are different in Bug #112917 and Bug #112913.