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

SUM column from a derived table returns invalid values

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Stalled (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.1.45, 10.2.32, 10.3.23, 10.4.13, 10.4, 10.5, 10.5.4, 10.0
    • Fix Version/s: 10.1, 10.2, 10.3, 10.4, 10.5, 10.0
    • Labels:
      None
    • Environment:
      Platforms:
       - Windows 10 Pro - version 2004
       - CloudLinux 7.8

      Description

      Use this schema as example:

      CREATE TABLE `products` (
        `product_id` INT,
        `code` VARCHAR(24),
        `price` DECIMAL(10,2),
        `avgOrderQuantity` INT
      );
       
      CREATE TABLE `orders` (
        `order_id` INT,
        `date` DATETIME DEFAULT CURRENT_TIMESTAMP,
        `state` ENUM('pending','confirmed','cancelled','completed')
      );
       
      CREATE TABLE `order_products` (
        `order_id` INT,
        `product_id` INT,
        `quantity` INT,
        `price` DECIMAL(10,2)
      );
       
      CREATE TABLE `invoices` (
        `invoice_id` INT,
        `order_id` INT,
        `date` DATETIME DEFAULT CURRENT_TIMESTAMP
      );
       
      CREATE TABLE `invoice_products` (
        `invoice_id` INT,
        `product_id` INT,
        `quantity` INT,
        `price` DECIMAL(10,2)
      );
       
      --
       
      INSERT INTO `products` (`product_id`, `code`, `price`, `avgOrderQuantity`) VALUES
      (1, 'COD1', 12, 3), (2, 'COD2', 11, 1);
       
      INSERT INTO `orders` (`order_id`, `state`) VALUES
      (1, 'confirmed'),
      (2, 'confirmed'),
      (3, 'pending');
       
      INSERT INTO `order_products` (`order_id`, `product_id`, `quantity`, `price`) VALUES
      (1, 1, 30, 10.2),
      (2, 1, 10, 12), (2, 2, 2, 11),
      (3, 1, 10, 12);
       
      INSERT INTO `invoices` (`invoice_id`, `order_id`) VALUES
      (1, 1),
      (2, 2),
      (3, 1);
       
      INSERT INTO `invoice_products` (`invoice_id`, `product_id`, `quantity`, `price`) VALUES
      (1, 1, 10, 10.2),
      (2, 1, 20, 12), (2, 2, 2, 11),
      (3, 1, 1, 10.2);
      

      If we run this query:

        SELECT
          P.code,
          O.state,
          OP.quantity AS ordered,
          IFNULL((
            SELECT SUM(IP.quantity)
            FROM invoices AS I
              LEFT JOIN invoice_products AS IP
                ON IP.invoice_id = I.invoice_id
            WHERE I.order_id = O.order_id
              AND IP.product_id = OP.product_id
          ), 0) AS invoiced,
          IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold,
          P.avgOrderQuantity AS avgOrdQty,
          IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold
        FROM order_products AS OP
          INNER JOIN orders AS O
            ON O.order_id = OP.order_id
          INNER JOIN products AS P
            ON P.product_id = OP.product_id
        WHERE O.state != 'pending';
      

      It returns:

      code state ordered invoiced sold avgOrdQty avgSold
      COD1 confirmed 30 11 30 3 3
      COD1 confirmed 10 20 10 3 3
      COD2 confirmed 2 2 2 1 1

      Now If move this into a derived table to get the sumarized `avgSold` grouped by `code` like this

      SELECT T.code, SUM(T.avgSold)
      FROM (
        SELECT
          P.code,
          O.state,
          OP.quantity AS ordered,
          IFNULL((
            SELECT SUM(IP.quantity)
            FROM invoices AS I
              LEFT JOIN invoice_products AS IP
                ON IP.invoice_id = I.invoice_id
            WHERE I.order_id = O.order_id
              AND IP.product_id = OP.product_id
          ), 0) AS invoiced,
          IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold,
          P.avgOrderQuantity AS avgOrdQty,
          IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold
        FROM order_products AS OP
          INNER JOIN orders AS O
            ON O.order_id = OP.order_id
          INNER JOIN products AS P
            ON P.product_id = OP.product_id
        WHERE O.state != 'pending'
      ) AS T
      GROUP BY T.code
      

      It returns:

      code SUM(T.avgSold)
      COD1 null
      COD2 null

      This is NOT the expected result.

      If I add a numeric column (YES it MUST be a numeric column) like this:

      SELECT T.code, T.sold, SUM(T.avgSold)
      FROM (
        SELECT
          P.code,
          O.state,
          OP.quantity AS ordered,
          IFNULL((
            SELECT SUM(IP.quantity)
            FROM invoices AS I
              LEFT JOIN invoice_products AS IP
                ON IP.invoice_id = I.invoice_id
            WHERE I.order_id = O.order_id
              AND IP.product_id = OP.product_id
          ), 0) AS invoiced,
          IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold,
          P.avgOrderQuantity AS avgOrdQty,
          IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold
        FROM order_products AS OP
          INNER JOIN orders AS O
            ON O.order_id = OP.order_id
          INNER JOIN products AS P
            ON P.product_id = OP.product_id
        WHERE O.state != 'pending'
      ) AS T
      GROUP BY T.code
      

      It returns:

      code sold SUM(T.avgSold)
      COD1 30 6
      COD2 2 1

      NOW this is the expected result

      Here you can find a working demo

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              socramg Marcos Alejandro Gallardo
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: