[MDEV-8920] having variable assigned with order by field provides different result Created: 2015-10-08  Updated: 2021-02-15

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.21, 10.0, 10.1
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Anna Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: None
Environment:

ubuntu/trusty64 - vagrant base box,
$ uname -r
3.13.0-65-generic


 Description   

Not sure whether it's a bug or just specifics of mariaDB internals, but it affects calculations:

CREATE TABLE `test` (
 
  `col1` int(11) DEFAULT NULL
 
) ENGINE=InnoDB;
 
 
 
INSERT INTO test VALUES(1), (10), (2), (100), (5);
 
 
 
SET @rank=0;
 
 
 
SELECT IF(col1=2, col1+1, col1+2)  as result, @rank:=@rank+1 FROM test ORDER BY result;
 
#+--------+----------------+
 
#| result | @rank:=@rank+1 |
 
#+--------+----------------+
 
#|      3 |              1 |
 
#|      3 |              2 |
 
#|      7 |              3 |
 
#|     12 |              4 |
 
#|    102 |              5 |
 
#+--------+----------------+
 
 
 
 # however when variable is set with order by column
 
 
 
SET @rank=0, @prev_val_placeholder=0;
 
 
 
SELECT @prev_val_placeholder:=IF(col1=2, col1+1, col1+2)  as result, @rank:=@rank+1 FROM test ORDER BY result;
 
#+--------+----------------+
 
#| result | @rank:=@rank+1 |
 
#+--------+----------------+
 
#|      3 |              1 |
 
#|      3 |              3 |
 
#|      7 |              5 |
 
#|     12 |              2 |
 
#|    102 |              4 |
 
#+--------+----------------+

when running the same queries in mysql (5.5.44 ) getting results as expected:

SELECT @prev_val_placeholder:=IF(col1=2, col1+1, col1+2)  as result, @rank:=@rank+1 FROM test ORDER BY result;
 
+--------+----------------+
 
| result | @rank:=@rank+1 |
 
+--------+----------------+
 
|      3 |              1 |
 
|      3 |              2 |
 
|      7 |              3 |
 
|     12 |              4 |
 
|    102 |              5 |
 
+--------+----------------+



 Comments   
Comment by Elena Stepanova [ 2015-10-19 ]

ak, thanks for the report.

The effect is easily reproducible as described on 10.0 and 10.1, although I am far from sure there is a bug in here.

ORDER BY defines in which order the rows will be returned in the result set, not in which order they will be retrieved. The result set is ordered correctly, everything else here is non-deterministic. The fact that rank order coincides with the result order on some versions is a lucky coincidence which cannot be relied upon. 10.0 and 10.1 use a different execution plan for the second query, which is not a crime.

However, I will assign it to psergey for the expert opinion.

Not reproducible on MariaDB 5.5 and lower, or on MySQL 5.5-5.7.

Comment by Mark Punak [ 2017-11-16 ]

??The result set is ordered correctly, everything else here is non-deterministic. The fact that rank order coincides with the result order on some versions is a lucky coincidence which cannot be relied upon. ??

The issue is that variable assignment IS deterministic in MySQL (all versions), and always occurs after the order by has been applied to the result set. It is most definitely not a lucky coincidence.

We use this design pattern extensively for a variety of things like cumulative scoring within a result set. This incompatibility with mysql will likely prevent us from moving large portions of server side code to the Maria platform, as the work around of constructing preordered temp tables outside of the query would be a) time consuming and b) produce ugly and verbose code. Hoping this can be scheduled for repair.

Generated at Thu Feb 08 07:30:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.