MySQL Forums
Forum List  »  Stored Procedures

Function in order by clause problem
Posted by: Michel DOMINIQUE
Date: October 18, 2015 07:19AM

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>

Options: ReplyQuote


Subject
Views
Written By
Posted
Function in order by clause problem
2128
October 18, 2015 07:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.