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
Comments
Modified by: Arioch (arioch)summary: make optimizer understant SELECT .... ORDER BY ? * expression => make optimizer understand SELECT .... ORDER BY ? * EXPRESSION special case |
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. => 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. |
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. => 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. |
Commented by: @livius2 Can you tell me what difference with: |
Commented by: Arioch (arioch) the difference is the same as between select .... where ID = ? and select .... where ID = 1 |
Commented by: @livius2 No it is not. as use same query plan for every of above queries Select order by Exp*0 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. |
Commented by: Arioch (arioch) That is implementation detail what you describe. 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 |
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)
The text was updated successfully, but these errors were encountered: