MySQL Bugs: #78561: Problem when using function in order by clause

Bug #78561	Problem when using function in order by clause
Submitted:	25 Sep 2015 15:11	Modified:	17 Oct 2015 22:54
Reporter:	Michel DOMINIQUE	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.34	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION

Description:
If I use a function in an order by clause, the sort is not done properly.

The following example reproduces the problem : the order by usinf ftest() should sort desc the table

How to repeat:
DROP TABLE test;

CREATE TABLE test (
  idtest INT NOT NULL ,
  test_name VARCHAR(45) NULL ,
  PRIMARY KEY (idtest) );
  
INSERT INTO test(idtest, test_name) VALUES (1, '1.0'), (2, '1.1'), (3, '2.0');

DROP FUNCTION IF EXISTS ftest;

DELIMITER $$

CREATE FUNCTION ftest(ver CHAR(50), order_sort CHAR(50)) 
    RETURNS TEXT
    DETERMINISTIC
BEGIN
 RETURN CONCAT(ver, ' ',order_sort); 
END;
$$

DELIMITER ;

select ftest("test_name", "desc") from test;
 displays 
     test_name desc
select test_name from test order by ftest("test_name", "desc");
 displays
    1.0
    1.1
    2.0
   ==> NOOK
select test_name from test order by test_name desc;
 displays
    2.0
    1.1
    1.0
   ==> OK

Please print here the queries result from mysql client. Thanks.

C:\Users\Michel>mysql -utracker_md -ppwdbtmd
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.8-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use tracker;
Database changed
mysql> DROP TABLE test;
ERROR 1051 (42S02): Unknown table 'test'
mysql>
mysql> CREATE TABLE test (
    ->   idtest INT NOT NULL ,
    ->   test_name VARCHAR(45) NULL ,
    ->   PRIMARY KEY (idtest) );
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> INSERT INTO test(idtest, test_name) VALUES (1, '1.0'), (2, '1.1'), (3, '2
.0');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> DROP FUNCTION IF EXISTS ftest;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE FUNCTION ftest(ver CHAR(50), order_sort CHAR(50))
    ->     RETURNS TEXT
    ->     DETERMINISTIC
    -> BEGIN
    ->  RETURN CONCAT(ver, ' ',order_sort);
    -> END;
    -> $$
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER ;
mysql> select ftest(test_name, "desc") from test;
+--------------------------+
| ftest(test_name, "desc") |
+--------------------------+
| 1.0 desc                 |
| 1.1 desc                 |
| 2.0 desc                 |
+--------------------------+
3 rows in set (0.00 sec)

mysql> select test_name from test order by test_name desc;
+-----------+
| test_name |
+-----------+
| 2.0       |
| 1.1       |
| 1.0       |
+-----------+
3 rows in set (0.00 sec)

Sorry my last submission is not right. Please remove it and replace with this one.
==============================================================================

C:\Users\Michel>mysql -utracker_md -ppwdbtmd
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.8-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use tracker;
Database changed
mysql> DROP TABLE test;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE test (
    ->   idtest INT NOT NULL ,
    ->   test_name VARCHAR(45) NULL ,
    ->   PRIMARY KEY (idtest) );
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> INSERT INTO test(idtest, test_name) VALUES (1, '1.0'), (2, '1.1'), (3, '2
.0');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> DROP FUNCTION IF EXISTS ftest;
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE FUNCTION ftest(ver CHAR(50), order_sort CHAR(50))
    ->     RETURNS TEXT
    ->     DETERMINISTIC
    -> BEGIN
    ->  RETURN CONCAT(ver, ' ',order_sort);
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> select ftest("test_name", "desc") from test;
+----------------------------+
| ftest("test_name", "desc") |
+----------------------------+
| test_name desc             |
| test_name desc             |
| test_name desc             |
+----------------------------+
3 rows in set (0.00 sec)

mysql> select test_name from test order by ftest("test_name", "desc");
+-----------+
| test_name |
+-----------+
| 1.0       |
| 1.1       |
| 2.0       |
+-----------+
3 rows in set (0.00 sec)

mysql> select test_name from test order by test_name desc;
+-----------+
| test_name |
+-----------+
| 2.0       |
| 1.1       |
| 1.0       |
+-----------+
3 rows in set (0.00 sec)

mysql>

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.