Wrong Query Result with LATERAL DERIVED (split_materialized=on)

CREATE TABLE product (

  `product_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

  `product_type_id` decimal(6,0) unsigned NOT NULL,

  PRIMARY KEY (`product_id`)

);

CREATE TABLE stock (

  `product_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

  `value` int(11) NOT NULL,

  `date` date NOT NULL,

  KEY `product_id` (`product_id`),

  CONSTRAINT `stock_product` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)

);

INSERT INTO

	`product` (`product_id`, `product_type_id`)

VALUES

	('one_product', 1),

	('another_product', 1);

INSERT INTO

	`stock` (`product_id`, `value`, `date`)

VALUES

	('one_product', 1, '2021-05-01'),

	('one_product', 2, '2021-05-02'),

	('one_product', 3, '2021-05-03'),

	('another_product', 2, '2021-05-01'),

	('another_product', 3, '2021-05-02'),

	('another_product', 4, '2021-05-03');

# Wrong result with split_materialized=on

set optimizer_switch='split_materialized=on';

SELECT

	product_id AS id,

	stock_data.stock_value

FROM

	product

JOIN (

	SELECT

		product_id

	FROM

		product p

	WHERE

		product_type_id = 1

	GROUP BY product_id

) product_data USING (product_id)

JOIN (

	SELECT

		product_id,

		SUM(stock.value) AS stock_value

	FROM

		stock

	JOIN

		product USING (product_id)

	WHERE

		stock.date BETWEEN '2021-05-01' AND '2021-05-03' AND

		product_type_id = 1

	GROUP BY

		product_id

) stock_data USING (product_id);

# Different but wrong result with a distinct alias in the second sub select

set optimizer_switch='split_materialized=on';

SELECT

	product_id AS id,

	stock_data.stock_value

FROM

	product

JOIN (

	SELECT

		product_id

	FROM

		product p

	WHERE

		product_type_id = 1

	GROUP BY product_id

) product_data USING (product_id)

JOIN (

	SELECT

		p2.product_id,

		SUM(stock.value) AS stock_value

	FROM

		stock

	JOIN

		product p2 USING (product_id)

	WHERE

		stock.date BETWEEN '2021-05-01' AND '2021-05-03' AND

		p2.product_type_id = 1

	GROUP BY

		p2.product_id

) stock_data USING (product_id);