Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25843

Wrong Query Result with LATERAL DERIVED (split_materialized=on)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.5.10
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
    • Environment:
      Debian Linux

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              sbeining Sascha Beining
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration