[MDEV-13662] MariaDB 10.1 user-define variables and order by question ? Created: 2017-08-28  Updated: 2017-08-31  Resolved: 2017-08-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.22, 10.1.26
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sysdljr Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Cent OS 6.7



 Description   

Hi,
recently, we encountered a order by question on product server.
example:

CREATE TABLE tmp (id INT AUTO_INCREMENT PRIMARY KEY , c1 VARCHAR(10) , c2 INT);
 
 
 
INSERT INTO tmp VALUES (1, 'a', 90),(2,'a', 36), (3, 'b',103) ,(4, 'a',320),(5,'b', 691);
 
 
 
SELECT t1.* , if(@t = c1 , @r := @r + 1, @r := 1) AS rownm , @t := c1 AS t
 
 FROM 
(SELECT * FROM tmp ORDER BY c1 ,c2 DESC  ) t1 , (SELECT @r := 0 , @t := NULL) t2

result on produce server (MariaDB 10.1.22)

id	c1	c2	rownm	t
 
2	a	36	1	a
 
1	a	90	2	a
 
4	a	320	3	a
 
3	b	103	1	b
 
5	b	691	2	b

after upgrade to 10.1.26, it is same result.

on MySQL 5.7.17, this is we want to get result.

id	c1	c2	rownm	t
 
4	a	320	1	a
 
1	a	90	2	a
 
2	a	36	3	a
 
5	b	691	1	b
 
3	b	103	2	b

We could not confirm , it is MairaDB optimizer and MySQL's different or bug, or others cause ?
please help confirm, thanks.

 Comments   
Comment by Elena Stepanova [ 2017-08-30 ]

Your query is non-deterministic by its nature, neither in MySQL nor in MariaDB. You shouldn't be using queries like that in production.

First, ORDER BY clause in a subquery is meaningless, it does not guarantee anything. See more details here: https://mariadb.com/kb/en/the-mariadb-library/why-is-order-by-in-a-from-subquery-ignored

Second, assigning and using variables in the same query causes undefined behavior, it is documented both in MySQL manual and in MariaDB KB.
https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.
...

https://mariadb.com/kb/en/the-mariadb-library/user-defined-variables/

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

Comment by sysdljr [ 2017-08-31 ]

ok, many thanks for your kind reply.
Wish you happy work.

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