Bug #114884 Incorrect inner join with group by execution

Submitted:	5 May 2024 9:03	Modified:	6 May 2024 6:21
Reporter:	Xiu Tang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.2.0, 8.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any

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 |
|         |
+---------+

Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.