[MDEV-27745] order by with incremented variable behaviour is different in 10.6 Created: 2022-02-04  Updated: 2022-10-21  Resolved: 2022-10-06

Status: Closed
Project: MariaDB Server
Component/s: Variables
Affects Version/s: 10.6
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Richard Stracke Assignee: Michael Widenius
Resolution: Not a Bug Votes: 0
Labels: order-by-optimization, variable

Issue Links:
Duplicate
is duplicated by MDEV-29408 Calculation error in recent version o... Closed
Relates
relates to MDEV-24089 support oracle syntax: rownum Closed

 Description   

Testcase:

use d1;
 
 
 
drop table if exists t1 ;
 
 
 
create table t1 (id1 int, id2 int, id3 float);
 
 
 
INSERT INTO `t1` (
 
with recursive series as (
 
select 1 as id union all
 
select id +1 as id from series
 
where id < 10)
 
select FLOOR(RAND()*(3-1+1))+1,FLOOR(RAND()*(2-1+1))+1 ,(FLOOR(RAND()*(100-1+1))+1)/10 from series);
 
 
 
SELECT b.*,
 
(@currank := @currank + 1) AS peringkat
 
FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b
 
ORDER BY id1 desc,id2 desc,id3 desc;

with 10.3, 10.4 and 10.5 the order if the variable (peringkat) follow the final "order by" clause

+------+------+------+-----------+
 
| id1  | id2  | id3  | peringkat |
 
+------+------+------+-----------+
 
|    3 |    1 |  5.9 |         1 |
 
|    3 |    1 |  2.9 |         2 |
 
|    2 |    2 |  8.6 |         3 |
 
|    2 |    2 |  6.8 |         4 |
 
|    2 |    1 |  7.6 |         5 |
 
|    1 |    2 |  7.2 |         6 |
 
|    1 |    2 |  3.5 |         7 |
 
|    1 |    2 |  0.2 |         8 |
 
|    1 |    1 |  7.7 |         9 |
 
|    1 |    1 |  2.2 |        10 |
 
+------+------+------+-----------+
 
10 rows in set (0.001 sec)

So not in 10.6

+------+------+------+-----------+
 
| id1  | id2  | id3  | peringkat |
 
+------+------+------+-----------+
 
|    3 |    2 |  5.6 |         1 |
 
|    3 |    2 |  5.6 |         4 |
 
|    3 |    2 |  4.2 |         6 |
 
|    3 |    2 |  3.5 |         5 |
 
|    3 |    1 |  9.3 |         8 |
 
|    2 |    2 |  7.5 |         9 |
 
|    2 |    1 |  2.3 |         7 |
 
|    1 |    1 |  8.7 |         2 |
 
|    1 |    1 |  7.4 |        10 |
 
|    1 |    1 |  2.7 |         3 |
 
+------+------+------+-----------+
 
10 rows in set (0.001 sec)

workarround with row_number() works

 SELECT b.*,row_number() over (order by id1 desc,id2 desc,id3 desc) as peringkat FROM ( select id1,id2,id3 from t1 a ) b;



 Comments   
Comment by Alice Sherepa [ 2022-02-04 ]

changed after be093c81a7955aa8309e650 commit (MDEV-24089 https://github.com/MariaDB/server/commit/be093c81a7)

create table t1 (id1 int, id2 int, id3 float) engine=innodb;
 
INSERT INTO `t1` VALUES (1,1,4),(2,1,2.7),(1,1,1.6),(2,2,0.3),(3,2,2.3),(1,1,8.6),(1,1,2.5),(3,1,9.5),(1,1,6.9),(2,2,9.7);
 
SELECT b.*, (@currank := @currank + 1) AS peringkat FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b ORDER BY id1 desc,id2 desc,id3 desc;
 
SELECT b.*, (@var:=@var+1) FROM  (SELECT a.* , (@var := 0) r from t1 a) b order by id1;
 
explain extended SELECT b.*, (@var:=@var+1) FROM  (SELECT a.* , (@var := 0) r from t1 a) b order by id1;

on 10.6 -Using temporary :

10.6

 
MariaDB [test]> explain extended SELECT b.*, (@var:=@var+1) FROM  (SELECT a.* , (@var := 0) r from t1 a) b order by id1;
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 10   |   100.00 | Using temporary; Using filesort |
 
|    2 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL | 10   |   100.00 |                                 |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
2 rows in set, 1 warning (0.001 sec)

10.5

 
MariaDB [test]> explain extended SELECT b.*, (@var:=@var+1) FROM  (SELECT a.* , (@var := 0) r from t1 a) b order by id1;
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
 
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 10   |   100.00 | Using filesort |
 
|    2 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL | 10   |   100.00 |                |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
 
2 rows in set, 1 warning (0.002 sec)

Comment by Michael Widenius [ 2022-10-06 ]

Hi!

This is not a bug, the issue is that the query is assuming too much about how
setting a variable in a query works.

The columns in the select clause are evaluated when they are accessed.
The cases are:

  • When the 'final row' is calculated and sent to the end user.
  • When the columns are stored in a temporary table that are later sorted.

In 10.6 the optimization for the query has changed from using the results directly
to first store the rows into a temporary table, sorting this and then sending the result to the end user.

The following queries shows the working in 10.6 in more detail:

MariaDB [test]> explain SELECT b.*,  (@currank := @currank + 1) AS peringkat  FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b  ORDER BY id1 desc,id2 desc,id3 desc;
 
+------+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
 
| id   | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra                           |
 
+------+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
 
|    1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | NULL    | NULL | 10   | Using temporary; Using filesort |
 
|    2 | DERIVED     | <derived3> | system | NULL          | NULL | NULL    | NULL | 1    |                                 |
 
|    2 | DERIVED     | a          | ALL    | NULL          | NULL | NULL    | NULL | 10   |                                 |
 
|    3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used                  |
 
+------+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
 
 
 
MariaDB [test]> SELECT b.*,  (@currank := @currank + 1) AS peringkat  FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b;+------+------+------+-----------+
 
| id1  | id2  | id3  | peringkat |
 
+------+------+------+-----------+
 
|    3 |    1 |  5.8 |         1 |
 
|    2 |    2 |    1 |         2 |
 
|    2 |    2 |  5.5 |         3 |
 
|    1 |    2 |  1.1 |         4 |
 
|    2 |    1 |    3 |         5 |
 
|    1 |    2 |  6.2 |         6 |
 
|    3 |    2 |  7.2 |         7 |
 
|    3 |    1 |  6.6 |         8 |
 
|    1 |    2 |  2.1 |         9 |
 
|    1 |    1 |  6.8 |        10 |
 
+------+------+------+-----------+
 
 
 
MariaDB [test]> SELECT b.*,  (@currank := @currank + 1) AS peringkat  FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b  ORDER BY id1 desc,id2 desc,id3 desc;
 
+------+------+------+-----------+
 
| id1  | id2  | id3  | peringkat |
 
+------+------+------+-----------+
 
|    3 |    2 |  7.2 |         7 |
 
|    3 |    1 |  6.6 |         8 |
 
|    3 |    1 |  5.8 |         1 |
 
|    2 |    2 |  5.5 |         3 |
 
|    2 |    2 |    1 |         2 |
 
|    2 |    1 |    3 |         5 |
 
|    1 |    2 |  6.2 |         6 |
 
|    1 |    2 |  2.1 |         9 |
 
|    1 |    2 |  1.1 |         4 |
 
|    1 |    1 |  6.8 |        10 |
 
+------+------+------+-----------+

The last result is a sorted version of the second to last version, which is expected when temporary tables are used to hold the result.

Using := to increment values should only be done when on is sure that the column is only accessed once. It is not usable for complex queries when one wants to get intermediate results.
In this case it's better to use row_number() which is well defined.

Generated at Thu Feb 08 09:55:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.