MySQL select from view with user variables - Unexpected result

Question

I have 2 tables and a view. In product_oper I have some products that I receive (when id_dest is 1) and that I sell (when id_src is 1). The table product_doc contains the date when the operation took place.

CREATE TABLE product_doc (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  doc_date date NOT NULL,
  doc_no char(16) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO product_doc (id,doc_date,doc_no) VALUES 
 (1,'2009-10-07','1'),
 (2,'2009-10-14','2'),
 (3,'2009-10-28','4'),
 (4,'2009-10-21','3');

CREATE TABLE product_oper (
  id bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  id_document bigint(20) unsigned NOT NULL,
  prod_id bigint(12) unsigned NOT NULL DEFAULT '0',
  prod_quant decimal(16,4) NOT NULL DEFAULT '1.0000',
  prod_value decimal(18,2) NOT NULL DEFAULT '0.00',
  id_dest bigint(20) unsigned NOT NULL,
  id_src bigint(20) unsigned NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO product_oper (id,id_document,prod_id,prod_quant,prod_value,id_dest,id_src) 
  VALUES 
  (10,1,1,'2.0000', '5.00',1,0),
  (11,3,1,'0.5000', '1.20',0,1),
  (12,1,2,'3.0000','26.14',1,0),
  (13,2,2,'0.5000','10.20',0,1),
  (14,3,2,'0.3000', '2.60',0,1),
  (15,4,2,'1.0000', '0.40',1,0);

In the view I want to see all the operations and the dates.

CREATE VIEW product_oper_view AS
 SELECT product_oper.*, product_doc.doc_date AS doc_date, product_doc.doc_no AS doc_no
 FROM product_oper JOIN product_doc ON product_oper.id_document = product_doc.id
 WHERE 1;

Now I want to see the operations of a single product, and the amount and value at a specific date.

SET @amount=0.000, @balance=0.00;

SELECT product_oper_view.*,
  IF(id_dest<>0, prod_quant, NULL) AS q_in,
  IF(id_dest<>0, prod_value, NULL) AS v_in,
  IF(id_src<>0, prod_quant, NULL) AS q_out,
  IF(id_src<>0, prod_value, NULL) AS v_out,
  @amount:=@amount + IF(id_dest<>0, 1, -1)*prod_quant AS q_amount,
  @balance:=@balance + IF(id_dest<>0, 1, -1)*prod_value AS v_balance
FROM product_oper_view 
WHERE prod_id=2 AND (id_dest=1 OR id_src=1)
ORDER BY doc_date;

The result I get is strange:

id, id_ prod_ prod_  id_ id_    doc_date,   q_in,   v_in,                 q_      v_
   doc, quant,value,dest,src,                              q_out, v_out, amount,  balance
12, 1, 3.0000, 26.14, 1,  0, '2009-10-07', 3.0000, 26.14,  NULL ,  NULL,  3.000,  26.14
13, 2, 0.5000, 10.20, 0,  1, '2009-10-14',  NULL ,  NULL, 0.5000, 10.20,  2.500,  15.94
15, 4, 1.0000,  0.40, 1,  0, '2009-10-21', 1.0000,  0.40,  NULL ,  NULL,  3.200,  13.74
14, 3, 0.3000,  2.60, 0,  1, '2009-10-28',  NULL ,  NULL, 0.3000,  2.60,  2.200,  13.34

The amount starts from zero,
at row 1: +3 => 3 (ok)
at row 2: -0.5 => 2.5 (ok)
at row 3: +1 => 3.2 (???)
at row 4: -0.3 => 2.2 (???)

It seems that MySQL doesn't take the order of rows specified in the ORDER BY clause when executing the statement, and it looks after the id: See that document with id 4 is before document with id 3 ('2009-10-21' < '2009-10-28')

Am I doing something wrong, or is it a bug of MySQL?

Answer 1

If I'm not totally wrong the ORDER-operation is one of the last things done when preparing the result set. Therefore your calculations are done before ordering the results. The correct way to circumvent this problem should be to use a subselect:

SET @amount=0.000, @balance=0.00;

SELECT p.*,
    @amount:=@amount + IF(p.id_dest <> 0, 1, -1) * p.prod_quant AS q_amount,
    @balance:=@balance + IF(p.id_dest <> 0, 1, -1) * p.prod_value AS v_balance
FROM (
    SELECT product_oper_view.*,
        IF(product_oper_view.id_dest <> 0, product_oper_view.prod_quant, NULL) AS q_in,
        IF(product_oper_view.id_dest <> 0, product_oper_view.prod_value, NULL) AS v_in,
        IF(product_oper_view.id_src <> 0, product_oper_view.prod_quant, NULL) AS q_out,
        IF(product_oper_view.id_src <> 0, product_oper_view.prod_value, NULL) AS v_out
    FROM product_oper_view 
    WHERE product_oper_view.prod_id = 2 
        AND (product_oper_view.id_dest = 1 OR product_oper_view.id_src = 1)
    ORDER BY product_oper_view.doc_date
) AS p