Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

make optimizer understand SELECT .... ORDER BY ? * EXPRESSION special case [CORE6131] #6380

Open
firebird-automations opened this issue Aug 26, 2019 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Arioch (arioch)

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)

@firebird-automations
Copy link
Collaborator Author

Modified by: Arioch (arioch)

summary: make optimizer understant SELECT .... ORDER BY ? * expression => make optimizer understand SELECT .... ORDER BY ? * EXPRESSION special case

@firebird-automations
Copy link
Collaborator Author

Modified by: Arioch (arioch)

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

=>

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Arioch (arioch)

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

=>

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)

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Can you tell me what difference with:
Select order by Exp asc
Select order by Exp desc
Select wihout order by?

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant