MySQL running total computed column message

Question

I am using the following query to compute a running balance for my credit card. My bank does not provide running balance column on its website, so I am using a SQL query to compute it myself. This code works for me, except my IDE shows the following warning message, "Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'".

SET @running_sum = 100;
   
SELECT
  post_date,
  category,
  amount,
  (@running_sum := @running_sum + amount) AS running_total
FROM credit_card;

I also noticed the following statement under MySQL version 8 section 9.4 User-Defined Variables, "Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL".

From these messages it appears to me that MySQL does not like that I am using this expression within my query (@running_sum := @running_sum + amount). However, I can't fully understand how am I supposed to re-write this query, so I can achieve the same result of computing running balance, and comply with MySQL version 8 code style.

Thank you in advance for your response.

Does this answer your question? [Cumulative sum over a set of rows in mysql](https://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql) — Ulrich Thomas Gabor, Dec 13 '20 at 00:04
Thank you Ulrich, I forgot to use ORDER BY clause in my original query, but it still worked because I populated table with entries in correct order. I still agree with you that it would be better to use ORDER BY clause to ensure that calculation is performed in the right order. I tested SUM window function, but as you stated it assumes that the initial value starts at 0, rather than at some arbitrary value. Is there a way to still use the SUM window function, but initialize it to some starting value first. — sambo9, Dec 14 '20 at 03:09
Also, do you know what is the general problem with using expression like this (@running_sum := @running_sum + amount) for calculated column? In my opinion, it is much easier to understand what this expression is doing compared to using SUM window function, which I still need to re-read in order to understand better. — sambo9, Dec 14 '20 at 03:14
The order of population*can* have an effect on the result set, but without `ORDER BY` the DBMS is allowed to return the result set in whatever order it deems appropriate. It will likely return the rows in an order it can compute most efficiently. This can be order of population, but it can also be anything else. — Ulrich Thomas Gabor, Dec 14 '20 at 09:17
No, I don't know how to start at 100 with a window SUM function. This also seems to me as something which can be done easily in the application. — Ulrich Thomas Gabor, Dec 14 '20 at 09:18
I added a link and some text to my answer where Oracle explains why they deprecated that functionality. — Ulrich Thomas Gabor, Dec 14 '20 at 09:22

Ulrich Thomas Gabor · Answer 1 · 2020-12-14T09:22:32.017

Without ORDER BY the order of the result set is unspecified and your query could return different results on each execution.

Assuming that the result set should be sorted by post_date and the sum should start at 0 (and not at 100) the following query based on window functions yields the same result set:

SELECT
  `post_date`,
  `category`,
  `amount`,
  SUM(`amount`) OVER (ORDER BY `post_date` ASC) AS `running_total`
FROM `credit_card`;

Why the functionality was deprecated:

Important Change: Setting user variables in statements other than SET is now deprecated due to issues that included those listed here:

The order of evaluation for expressions involving user variables was undefined.

The default result type of a variable is based on its type at the beginning of the statement, which could have unintended effects when a variable holding a value of one type at the beginning of a statement was assigned a new value of a different type in the same statement.

HAVING, GROUP BY, and ORDER BY clauses, when referring to a variable that was assigned a value in the select expression list, did not work as expected because the expression was evaluated on the client and so it was possible for stale column values from a previous row to be used.

MySQL running total computed column message

1 Answers1