| Bug #101702 | Calculations based on backreference to subquery results in NULL | ||
|---|---|---|---|
| Submitted: | 20 Nov 2020 16:58 | Modified: | 20 Nov 2020 18:25 |
| Reporter: | Billy Sullivan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.21 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
[20 Nov 2020 18:18]
MySQL Verification Team
Thank you for the bug report. I couldn't repeat with 8.0.22:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS `customer_transaction`;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> CREATE TABLE `customer_transaction` (
-> `customer_transaction_id` int NOT NULL AUTO_INCREMENT,
-> `customer_id` int NOT NULL,
-> `order_id` int NOT NULL,
-> `amount` decimal(12,2) NOT NULL,
-> `credit_order_id` int NOT NULL,
-> `refund` int NOT NULL,
-> PRIMARY KEY (`customer_transaction_id`),
-> KEY `customer_id` (`customer_id`),
-> KEY `order_id` (`order_id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql>
mysql> INSERT INTO `customer_transaction` VALUES (1,200,1000,200.00,500,1),(2,200,2000,-200.00,0,0),(3,200,0,0.00,0,2833);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT credit_order_id,
-> (
-> SELECT IFNULL(SUM(amount),0) AS total_refunds
-> FROM customer_transaction ctr
-> WHERE ctr.credit_order_id = ct.credit_order_id
-> ) AS refunds_issued,
-> SUM(ct.amount) AS total_amount,
-> SUM(ct.amount) - (SELECT refunds_issued) AS refundable,
-> SUM(ct.amount) - (
-> SELECT IFNULL(SUM(amount),0) AS total_refunds
-> FROM customer_transaction ctr
-> WHERE ctr.credit_order_id = ct.credit_order_id
-> ) AS refundable_2
-> FROM customer_transaction ct
-> WHERE ct.credit_order_id > 0
-> AND ct.amount > 0
-> GROUP BY credit_order_id;
+-----------------+----------------+--------------+------------+--------------+
| credit_order_id | refunds_issued | total_amount | refundable | refundable_2 |
+-----------------+----------------+--------------+------------+--------------+
| 500 | 200.00 | 200.00 | 0.00 | 0.00 |
+-----------------+----------------+--------------+------------+--------------+
1 row in set (0.06 sec)
mysql>
================================================================================
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE test
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS `customer_transaction`;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE `customer_transaction` (
-> `customer_transaction_id` int NOT NULL AUTO_INCREMENT,
-> `customer_id` int NOT NULL,
-> `order_id` int NOT NULL,
-> `amount` decimal(12,2) NOT NULL,
-> `credit_order_id` int NOT NULL,
-> `refund` int NOT NULL,
-> PRIMARY KEY (`customer_transaction_id`),
-> KEY `customer_id` (`customer_id`),
-> KEY `order_id` (`order_id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql>
mysql> INSERT INTO `customer_transaction` VALUES (1,200,1000,200.00,500,1),(2,200,2000,-200.00,0,0),(3,200,0,0.00,0,2833);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT credit_order_id,
-> (
-> SELECT IFNULL(SUM(amount),0) AS total_refunds
-> FROM customer_transaction ctr
-> WHERE ctr.credit_order_id = ct.credit_order_id
-> ) AS refunds_issued,
-> SUM(ct.amount) AS total_amount,
-> SUM(ct.amount) - (SELECT refunds_issued) AS refundable,
-> SUM(ct.amount) - (
-> SELECT IFNULL(SUM(amount),0) AS total_refunds
-> FROM customer_transaction ctr
-> WHERE ctr.credit_order_id = ct.credit_order_id
-> ) AS refundable_2
-> FROM customer_transaction ct
-> WHERE ct.credit_order_id > 0
-> AND ct.amount > 0
-> GROUP BY credit_order_id;
+-----------------+----------------+--------------+------------+--------------+
| credit_order_id | refunds_issued | total_amount | refundable | refundable_2 |
+-----------------+----------------+--------------+------------+--------------+
| 500 | 200.00 | 200.00 | NULL | 0.00 |
+-----------------+----------------+--------------+------------+--------------+
1 row in set (0.00 sec)
[20 Nov 2020 18:25]
Billy Sullivan
Thanks, I guess it's fixed! Now just have to wait for the new version to make it into our repo. By any chance can I get a reference for the change that addresses this?
Description: Using referenced results from a subquery in a mathematical calculation sometimes results in a NULL value. How to repeat: DROP TABLE IF EXISTS `customer_transaction`; CREATE TABLE `customer_transaction` ( `customer_transaction_id` int NOT NULL AUTO_INCREMENT, `customer_id` int NOT NULL, `order_id` int NOT NULL, `amount` decimal(12,2) NOT NULL, `credit_order_id` int NOT NULL, `refund` int NOT NULL, PRIMARY KEY (`customer_transaction_id`), KEY `customer_id` (`customer_id`), KEY `order_id` (`order_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO `customer_transaction` VALUES (1,200,1000,200.00,500,1),(2,200,2000,-200.00,0,0),(3,200,0,0.00,0,2833); SELECT credit_order_id, ( SELECT IFNULL(SUM(amount),0) AS total_refunds FROM customer_transaction ctr WHERE ctr.credit_order_id = ct.credit_order_id ) AS refunds_issued, SUM(ct.amount) AS total_amount, SUM(ct.amount) - (SELECT refunds_issued) AS refundable, SUM(ct.amount) - ( SELECT IFNULL(SUM(amount),0) AS total_refunds FROM customer_transaction ctr WHERE ctr.credit_order_id = ct.credit_order_id ) AS refundable_2 FROM customer_transaction ct WHERE ct.credit_order_id > 0 AND ct.amount > 0 GROUP BY credit_order_id; The value `refundable` should be the same as `refundable_2` however it results in NULL on my version. Note: I was unable to reproduce this on any DB Fiddle using a different version so it's possibly fixed in 8.0.22.