make optimizer understant SELECT .... ORDER BY ? * expression
-------------------------------------------------------------
Key: CORE-6131
URL: http://tracker.firebirdsql.org/browse/CORE-6131
Project: Firebird Core
Issue Type: Improvement
Reporter: Arioch
Sometimes clients want to have sorting of variable (user-selectable) directions.
Example:
https://www.sql.ru/forum/1316247-2/firebird-3-0-embedded-podklucheniya-s-raznyh-prilozheniy
One way to achieve it would be to transcode some field (or fields-based
expression) to a number (maybe that is already a number, or maybe it is for
example timestamp, so can be reduced to number using something like DATEDIFF),
and then to multiply it by SQL parameter, with parameter then being either +1
or -1 or 0 ( direct sort, reverse sort, no sort )
The database can have ascending and descending indexes by the said expression
(or a bi-directional index if Firebird would have one implemented).
The thing is to recognize that the SQL parameter is de-facto a constant value
and then to build fetching plan using either ascending or descending or non
index, or none at all, based upon the value of the parameter.
This is a special case, so no "general" problems should emerge.
- multiplication of expression and numeric parameter in ORDER BY clause
- existing index by that expression
- the numeric parameter value being +1 or -1 or 0 (actually, any non-zero
number there can be substituted with +1 or -1 for the sake of sorting)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
