Bug #119394 Join order specified in the hint is different, the query results are incorrect.

• Submitted: 14 Nov 9:02
• Modified: 14 Nov 9:02
• Reporter: Go Yakult
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[14 Nov 9:02] Go Yakult

Description:
mysql> SELECT /*+ JOIN_ORDER(t1, t0)*/ DISTINCTROW  t1.vc8, t0.vc7, t1.vc9 FROM t0, t1 WHERE ((t0.vc7) IN (t1.vc9)) && (t1.vc8);
Empty set, 1 warning (0.00 sec)

mysql> 
mysql> SELECT /*+ JOIN_ORDER(t0, t1)*/ DISTINCTROW  t1.vc8, t0.vc7, t1.vc9 FROM t0, t1 WHERE ((t0.vc7) IN (t1.vc9)) && (t1.vc8);
+------+---------------------------------+--------------------------------------------+
| vc8  | vc7                             | vc9                                        |
+------+---------------------------------+--------------------------------------------+
|  127 | 18446744073709551488.0000000000 | 18446744073709552000.000000000000000000000 |
+------+---------------------------------+--------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` decimal(10,0) unsigned zerofill NOT NULL  COMMENT 'asdf',
  `c1` double unsigned zerofill  DEFAULT NULL COMMENT 'asdf',
  `c2` tinyint(1)   DEFAULT NULL COMMENT 'asdf',
  `c3` float(62,10)  DEFAULT NULL COMMENT 'asdf',
  `c4` varchar(500) DEFAULT NULL COMMENT 'asdf',
  `vc5` varchar(500) GENERATED ALWAYS AS (cast(`c4` as char(30) charset utf8mb4)) STORED,
  `vc6` varchar(500) GENERATED ALWAYS AS (cast(`c3` as char(30) charset utf8mb4)) STORED,
  `vc7` decimal(30,10) unsigned zerofill GENERATED ALWAYS AS (cast(`c2` as unsigned)) STORED,
  `vc8` decimal(10,0) GENERATED ALWAYS AS (cast(`vc6` as unsigned)) STORED,
  `vc9` decimal(17,17) unsigned zerofill GENERATED ALWAYS AS (cast(`vc6` as unsigned)) STORED,
  PRIMARY KEY (`c0`),
  UNIQUE KEY `i6` (`vc7`,`c3`,`vc9`,`c4`(3),`vc5`,`c0`),
  KEY `i0` (`c1`),
  KEY `i41` (`c1`,`c2`,`c3`) USING BTREE,
  KEY `i2` (`c1`,`vc9`,`c4`(2),`c3`,`c2`,`vc8` DESC,`vc6`(4),`c0`,`vc7` DESC,`vc5`),
  KEY `i4` (`c4` DESC,`vc6`(2) DESC,`c1` DESC,`c0`,`c3`,`vc7` DESC),
  KEY `i5` (`vc8`,`vc5`(3) DESC,`c0`,`c4`,`c2`,`vc7` DESC,`c1`,`vc9`) USING BTREE
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` (`c0`, `c1`, `c2`, `c3`, `c4`) VALUES (1928139211,NULL,-128,0.9804951549,'UodW');

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c0` tinyint DEFAULT NULL,
  `c1` decimal(10,0) DEFAULT NULL,
  `c2` tinyint(1) NOT NULL   COMMENT 'asdf',
  `vc3` varchar(500) GENERATED ALWAYS AS (cast(`c1` as char(30) charset utf8mb4)) STORED,
  `c4` mediumint(8) unsigned zerofill  DEFAULT NULL COMMENT 'asdf',
  `vc5` tinyint(1) GENERATED ALWAYS AS (cast(`c1` as unsigned)) STORED,
  `vc6` decimal(45,14) GENERATED ALWAYS AS (cast(`c4` as unsigned)) STORED,
  `c7` smallint(8) unsigned zerofill DEFAULT NULL,
  `vc8` tinyint(1) GENERATED ALWAYS AS (cast(`c0` as unsigned)) STORED,
  `vc9` double(51,21) GENERATED ALWAYS AS (cast(`c2` as unsigned)) STORED,
  PRIMARY KEY (`c2`),
  UNIQUE KEY `i1` (`vc5` DESC,`vc9` DESC,`c7`,`vc3`) USING BTREE,
  UNIQUE KEY `i7` (`vc5` DESC,`vc9` DESC,`vc6`,`vc3`,`c0`,`c7`,`vc8`,`c2`,`c1`,`c4`) USING BTREE,
  UNIQUE KEY `i3` (`vc5`,`c1`,`c7`,`c2`,`vc3`(3),`vc9`),
  KEY `i8` (`vc5`,`vc6`,`c2` DESC)
)  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t1` (`c0`, `c1`, `c2`, `c4`, `c7`) VALUES (-124,1,-42,00000000,00000069);

SELECT /*+ JOIN_ORDER(t1, t0)*/ DISTINCTROW  t1.vc8, t0.vc7, t1.vc9 FROM t0, t1 WHERE ((t0.vc7) IN (t1.vc9)) && (t1.vc8);

SELECT /*+ JOIN_ORDER(t0, t1)*/ DISTINCTROW  t1.vc8, t0.vc7, t1.vc9 FROM t0, t1 WHERE ((t0.vc7) IN (t1.vc9)) && (t1.vc8);

[14 Nov 9:02] Go Yakult

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41    |
+-----------+