|
Only tested with MariaDB 10.5.10
Example schema and data:
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');
|
Example queries:
# 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);
|
Result:
| id |
stock_value |
| one_product |
30 |
# 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);
|
Result:
| id |
stock_value |
| another_product |
15 |
| one_product |
15 |
Expected result (with set optimizer_switch='split_materialized=off';)
| id |
stock_value |
| another_product |
9 |
| one_product |
6 |
|