Function in order by clause problem
Hello.
I'm struggling with function. I created a function which return TEXT string. I tried to use it in order by clause by it doesn't work.
The function is deterministic and called version_sort.
After creating the function, if I do
'select version_name from version order by version_sort("version_name", "desc")'
the result is not what expected. However if I get the TEXT returned by the function using 'select version_sort("version_name", "desc") from version limit 1'
and then replace the result in the first query, it's OK:
select version_name from version order by
CONVERT(SUBSTRING_INDEX(version_name,".",1), signed) desc,CONVERT(REPLACE(SUBSTRING_INDEX(version_name,".",2),CONCAT(SUBSTRING_INDEX(version_name,".",1),"."),""), signed) desc,CONVERT(REPLACE(SUBSTRING_INDEX(version_name,".",3),CONCAT(SUBSTRING_INDEX(version_name,".",2),"."),""), signed) desc,LOCATE(" ",version_name) asc,LENGTH(version_name) desc,version_name desc,CONVERT(REPLACE(SUBSTRING_INDEX(version_name,".",4),CONCAT(SUBSTRING_INDEX(version_name,".",3),"."),""), signed) desc
Is it possible to use function in 'order by' clause ?
Thanks for help
Michel.
================
HOW TO REPRODUCE
================
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>