Bug #118788 When two tables are joined, the execution results are different in different join order.

• Submitted: 6 Aug 9:33
• Modified: 6 Aug 12:58
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41, 8.0.43
• OS: Linux
• CPU Architecture: x86

[6 Aug 9:33] Alice Alice

Description:
mysql> SELECT /*+ JOIN_ORDER(D, F) */ count(*)  FROM tbl_1_all_typeindex_desc D  JOIN tbl_1_all_type2index_desc F ON D.year_col <= F.smallint_col;
+----------+
| count(*) |
+----------+
|       41 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT /*+ JOIN_ORDER(F, D) */ count(*)  FROM tbl_1_all_typeindex_desc D  JOIN tbl_1_all_type2index_desc F ON D.year_col <= F.smallint_col;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41    |
+-----------+
1 row in set (0.00 sec)

How to repeat:
1.creaet  table and insert data
create database test;
use test;
source tbl_1_all_typeindex_desc.sql
source tbl_1_all_type2index_desc.sql

2.execute query
SELECT /*+ JOIN_ORDER(D, F) */ count(*)  FROM tbl_1_all_typeindex_desc D  JOIN tbl_1_all_type2index_desc F ON D.year_col <= F.smallint_col;
SELECT /*+ JOIN_ORDER(F, D) */ count(*)  FROM tbl_1_all_typeindex_desc D  JOIN tbl_1_all_type2index_desc F ON D.year_col <= F.smallint_col;

[6 Aug 12:58] MySQL Verification Team

Hello Alice Alice,

Thank you for the report and feedback.

regards,
Umesh

[21 Aug 8:59] Min Wang

HI, I would like to help on this issue. can you provide the sql?