Different query plans on machines with different disk types

One of our customers, found that changing the atomic_write_supported (to either 0 or 1) in the file "storage/innobase/os/os0file", getting different explain plan for the same query. This test is carried out on the MariaDB 10.4.13.

This is reproducible, we got the obfuscated data from the customer and steps in repeating locally.

This issue is reproducible on a machine with rotational disk type.

Here are the steps to reproduce the issue:

• Assuming mysqld server is already running
• drop table if exists table1
• drop table if exists table2
• Stop mysqld server
• Set the value of atomic_write_supported (to either 0 or 1) in the file "storage/innobase/os/os0file.cc" on line 7655
• Compile and build MariaDB
• Start mysqld server
• Load data files (as shown below for ex)

  mysql -u root test < table1_obfuscated.sql &

  mysql -u root test < table2_obfuscated.sql &

• Restart server (optional)
• Run the query from "ObfuscatedQuery.rtf"

NOTE: It is very important to drop tables and reload data everytime atomic_write_supported variable is modified to reproduce the issue consistently.

I will share obfuscated data and query separately.