Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.21, 10.0(EOL), 10.1(EOL)
-
None
-
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 |
|
+--------+----------------+ |