Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13662

MariaDB 10.1 user-define variables and order by question ?

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.1.22, 10.1.26
    • N/A
    • Optimizer
    • None
    • 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.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          sysdljr sysdljr added a comment -

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

          sysdljr sysdljr added a comment - ok, many thanks for your kind reply. Wish you happy work.

          People

            Unassigned Unassigned
            sysdljr sysdljr
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.