window Function ROW_NUMBER() changes processing ORDER BY with variable RUNNING TOTAL

Question

I am seeing an order change when I add a column using the window function ROW_NUMBER().

The result is an incorrect running total. (@ONorderQTYrunner)

Can anyone explain why the ROW_NUMBER() is changing the sort order and resulting in the error in running total?

The query below (with ROW_NUMBER()) is returning an unexpected RUNNINGtotal value for the 1st and 2nd rows:

1  57   A-123   69  4000    2020-06-10  4500
2  32   A-123   67  500     2020-07-01  500
3  59   A-123   69  2000    2020-07-15  6500
4  60   A-123   69  2000    2020-08-15  8500

Without ROW_NUMBER() is returning this, as I would expect:

57  A-123   69  4000    2020-06-10  4000
32  A-123   67  500     2020-07-01  4500
59  A-123   69  2000    2020-07-15  6500
60  A-123   69  2000    2020-08-15  8500

CREATE TABLE IF NOT EXISTS `PURCHASEorders` (
  `UniKey` int(11) NOT NULL AUTO_INCREMENT,
  `PARTnumber` varchar(255) DEFAULT NULL,
  `POnumber` varchar(255) NOT NULL,
  `QTY` double DEFAULT 0,
  `DOCKdate` date DEFAULT NULL,
   PRIMARY KEY (`UniKey`),
   KEY `PartNumber` (`PARTnumber`),
   KEY `POnumber` (`POnumber`)
) DEFAULT CHARSET=utf8;
INSERT INTO `PURCHASEorders` (`UniKey`, `PARTnumber`,`POnumber`, `QTY`, `DOCKdate`) VALUES
  ('32', 'A-123', 67, 500, '2020-07-01 12:00:00'),
  ('57', 'A-123', 69, 4000,'2020-06-10 12:00:00'),
  ('59', 'A-123', 69, 2000,'2020-07-15 12:00:00'),
  ('60', 'A-123', 69, 2000,'2020-08-15 12:00:00');

SET @PARTnumber = 'A-123';
SET @ONorderQTYrunner =0;

SELECT DISTINCT 
    ROW_NUMBER() OVER(ORDER BY DOCKdate ASC) AS ONorderROWindex,
    PO.UniKey,
    PO.PARTnumber,
    PO.POnumber,
    PO.QTY,
    PO.DOCKdate,
    @ONorderQTYrunner:= @ONorderQTYrunner + PO.QTY AS RUNNINGtotal
FROM PURCHASEorders PO
WHERE PO.PARTnumber = @PARTnumber 
ORDER BY PO.DOCKdate ASC

EDIT: Following suggestions by Gordon Linoff using this in place of inline variable assignment has corrected the issue:

SUM(PO.QTY) OVER (ORDER BY PO.DOCKdate) as RUNNINGtotalfollowing 

has corrected RUNNING total Result:

1  57   A-123   69  4000    2020-06-10  4000
2  32   A-123   67  500     2020-07-01  4500
3  59   A-123   69  2000    2020-07-15  6500
4  60   A-123   69  2000    2020-08-15  8500

Result, Root Cause of Issue: Variable assignment in-line which as been deprecated. Note: This is an issue in MySql and MariaDB

Answer 1

Variable assignment in MySQL SELECT statements is now deprecated. Use a proper cumulative sum:

SELECT DISTINCT 
       ROW_NUMBER() OVER (ORDER BY DOCKdate ASC) AS ONorderROWindex,
       PO.UniKey, PO.PARTnumber, PO.POnumber, PO.QTY, PO.DOCKdate,
       SUM(PO.QTY) OVER (ORDER BY PO.DOCKdate) as RUNNINGtotal
FROM PURCHASEorders PO
WHERE PO.PARTnumber = @PARTnumber 
ORDER BY PO.DOCKdate ASC;

I doubt the SELECT DISTINCT is really needed, but that would be a different matter.