Issue Details (XML | Word | Printable)

Key: CORE-6131
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Arioch
Votes: 0
Watchers: 8
Operations

If you were logged in you would be able to see more operations.
Firebird Core

make optimizer understand SELECT .... ORDER BY ? * EXPRESSION special case

Created: 26/Aug/19 04:02 PM   Updated: 27/Aug/19 02:21 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None

QA Status: No test


 Description  « Hide
Sometimes clients want to have sorting of variable (user-selectable) directions.

Example: https://www.sql.ru/forum/1316301/napravlenie-sortirovki

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 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)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski added a comment - 26/Aug/19 04:51 PM
Can you tell me what difference with:
Select order by Exp asc
Select order by Exp desc
Select wihout order by?

Arioch added a comment - 27/Aug/19 08:43 AM
the difference is the same as between

select .... where ID = ?

and

select .... where ID = 1
select .... where ID = 2
select .... where ID = 3
select .... where ID = 4
select .... where ID = 5
....etc

Karol Bieniaszewski added a comment - 27/Aug/19 01:28 PM
No it is not.

as
select .... where ID = 1
select .... where ID = 2 ..

use same query plan for every of above queries
but your proposition use different query plan for different value in the parmeter.
e.g.

Select order by Exp*0
plan natural
select order by exp*1
plan (order idx_asc)
select order by exp*-1
plan (order idx_desc)

I do not know if there is a ticket in the tracker with new feature like addaptable query plans during query execution. Now Firebird use fixed plan from query prepare time.

Arioch added a comment - 27/Aug/19 02:21 PM
That is implementation detail what you describe.
I was talking about the goal, the intention.

Yes, implementing this would probably demand Firebird to implement "plans with variants". Which would be a major feature and probably with other possible uses too.

However for the SQL developers it would be extending the scope of using SQL parameters, offering largely the same cons and pros as using parameters in prepared queries vs inlining constants and conditions in WHERE section.

I would mention even more "variative" use of parameters: ....WHERE ID = 1 OR 1 IS NULL
This can also be switched on and off by mere change of parameter value, instead of changing the query source text and then plan.