[MDEV-23292] Computed column using variables shows invalid values Created: 2020-07-25  Updated: 2020-07-28  Resolved: 2020-07-28

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.5.1, 10.2.32, 10.3.23, 10.4.13, 10.5.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Marcos Alejandro Gallardo Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Platforms:

  • Windows Pro 10 - version 2004
  • CloudLinux 7.8


 Description   

Use this schema as an 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 I run this query:

  SELECT
 
    P.code,
 
    O.state,
 
    @ordered := OP.quantity AS ordered,
 
    @invoiced := 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,
 
    @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold,
 
    @avgOrdQty := P.avgOrderQuantity AS avgOrdQty,
 
    IF(@sold > @avgOrdQty, @avgOrdQty, @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 10
COD2 confirmed 2 2 2 1 1

This is not the expected result, because on the second row at column `avgSold` prints `10`, but it should be `3`

Now if I move this into a derived table an get the sumarized `avgSold` by `code` like this:

SELECT T.code, SUM(T.avgSold)
 
FROM (
 
  SELECT
 
    P.code,
 
    O.state,
 
    @ordered := OP.quantity AS ordered,
 
    @invoiced := 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,
 
    @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold,
 
    @avgOrdQty := P.avgOrderQuantity AS avgOrdQty,
 
    IF(@sold > @avgOrdQty, @avgOrdQty, @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 6
COD2 1

This is the expected result, but how is this possible?

Here you can find a working demo

 Comments   
Comment by Alice Sherepa [ 2020-07-27 ]

From https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

"If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type.

To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it."

So while first SELECT treats variables as strings - the result is incorrect, but second SELECT counts them as numbers and returns expected results.

MariaDB [test]> set @ordered=0,@invoiced=0,@sold=0,@avgOrdQty=0;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]>  SELECT P.code,O.state,
 
    ->     @ordered := OP.quantity AS ordered,
 
    ->     @invoiced := 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,
 
    ->     @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold,
 
    ->     @avgOrdQty := P.avgOrderQuantity AS avgOrdQty,
 
    ->     IF(@sold > @avgOrdQty, @avgOrdQty, @sold) AS avgSold
 
    -> FROM order_products AS OP
 
    ->      JOIN orders AS O ON O.order_id = OP.order_id
 
    ->      JOIN products AS P ON P.product_id = OP.product_id
 
    -> WHERE O.state != 'pending';
 
+------+-----------+---------+----------+------+-----------+---------+
 
| 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 |
 
+------+-----------+---------+----------+------+-----------+---------+
 
3 rows in set (0.002 sec)
 
 
 
MariaDB [test]> SELECT T.code, T.sold, SUM(T.avgSold)
 
    -> FROM (
 
    ->   SELECT
 
    ->     P.code,
 
    ->     O.state,
 
    ->     @ordered := OP.quantity AS ordered,
 
    ->     @invoiced := 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,
 
    ->     @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold,
 
    ->     @avgOrdQty := P.avgOrderQuantity AS avgOrdQty,
 
    ->     IF(@sold > @avgOrdQty, @avgOrdQty, @sold) AS avgSold
 
    ->   FROM order_products AS OP
 
    ->      JOIN orders AS O
 
    ->       ON O.order_id = OP.order_id
 
    ->      JOIN products AS P
 
    ->       ON P.product_id = OP.product_id
 
    ->   WHERE O.state != 'pending'
 
    -> ) AS T
 
    -> GROUP BY T.code;
 
+------+-------------------------------------------+----------------+
 
| code | sold                                      | SUM(T.avgSold) |
 
+------+-------------------------------------------+----------------+
 
| cod1 | 30.00000000000000000000000000000000000000 |              6 |
 
| cod2 |  2.00000000000000000000000000000000000000 |              1 |
 
+------+-------------------------------------------+----------------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]> set @ordered='',@invoiced='',@sold='',@avgOrdQty='';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> SELECT T.code, T.sold, SUM(T.avgSold)
 
    -> FROM (
 
    ->   SELECT
 
    ->     P.code,
 
    ->     O.state,
 
    ->     @ordered := OP.quantity AS ordered,
 
    ->     @invoiced := 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,
 
    ->     @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold,
 
    ->     @avgOrdQty := P.avgOrderQuantity AS avgOrdQty,
 
    ->     IF(@sold > @avgOrdQty, @avgOrdQty, @sold) AS avgSold
 
    ->   FROM order_products AS OP
 
    ->      JOIN orders AS O
 
    ->       ON O.order_id = OP.order_id
 
    ->      JOIN products AS P
 
    ->       ON P.product_id = OP.product_id
 
    ->   WHERE O.state != 'pending'
 
    -> ) AS T
 
    -> GROUP BY T.code;
 
+------+------+----------------+
 
| code | sold | SUM(T.avgSold) |
 
+------+------+----------------+
 
| cod1 | 30   |             13 |
 
| cod2 | 2    |              1 |
 
+------+------+----------------+
 
2 rows in set (0.002 sec)
 
 
 
MariaDB [test]>  set @ordered='',@invoiced='',@sold='',@avgOrdQty='';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]>  SELECT P.code,O.state,
 
    ->     @ordered := OP.quantity AS ordered,
 
    ->     @invoiced := 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,
 
    ->     @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold,
 
    ->     @avgOrdQty := P.avgOrderQuantity AS avgOrdQty,
 
    ->     IF(@sold > @avgOrdQty, @avgOrdQty, @sold) AS avgSold
 
    -> FROM order_products AS OP
 
    ->      JOIN orders AS O ON O.order_id = OP.order_id
 
    ->      JOIN products AS P ON P.product_id = OP.product_id
 
    -> WHERE O.state != 'pending';
 
+------+-----------+---------+----------+------+-----------+---------+
 
| code | state     | ordered | invoiced | sold | avgOrdQty | avgSold |
 
+------+-----------+---------+----------+------+-----------+---------+
 
| cod1 | confirmed |      30 |       11 | 30   |         3 | 3       |
 
| cod1 | confirmed |      10 |       20 | 10   |         3 | 10      |
 
| cod2 | confirmed |       2 |        2 | 2    |         1 | 1       |
 
+------+-----------+---------+----------+------+-----------+---------+
 
3 rows in set (0.001 sec)

And in general KB says "It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined."

Generated at Thu Feb 08 09:21:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.