Bug #114884 Incorrect inner join with group by execution

• Submitted: 5 May 9:03
• Modified: 6 May 6:21
• Reporter: Xiu Tang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.2.0, 8.0.37
• OS: Any
• CPU Architecture: Any

[5 May 9:03] Xiu Tang

Description:
Inner join with group by execution produce wrong results

How to repeat:
CREATE TABLE `t0` (
  `c0` text
);

INSERT INTO `t0` VALUES (NULL),('2003'),('1959'),(NULL),('-692162217'),(NULL),(''),('-1898963317'),
(NULL),('-1303984332'),(NULL),('1726594266'),('-741978535'),(NULL),('-1'),('326357217'),(
'0^BtQYw');

CREATE TABLE `t1` (
  `c0` text 
);

INSERT INTO `t1` VALUES ('-1000391483'),('1966'),(NULL),('-556531167'),('1955'),('1987'),
(NULL),('1980'),('949792532'),('1420052172');

CREATE TABLE `t2` (
  `c0` decimal(10,0),
  UNIQUE KEY `c0` (`c0`)
);

INSERT INTO `t2` VALUES (0000001977),(0000000000),(NULL);

mysql> SET optimizer_switch='condition_fanout_filter=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t0.c0 FROM t2 INNER JOIN t0 ON t2.c0 = t0.c0 CROSS JOIN t1 GROUP BY t0.c0;

Result:
+------+
| c0   |
+------+
|      |
+------+
1 row in set, 2 warnings (0.00 sec)

Expected Result:
+---------+
| c0      |
+---------+
| 0^BtQYw |
|         |
+---------+

[6 May 6:21] MySQL Verification Team

Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh