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
NULLS FIRST/LAST should not cause index loss [CORE4266] #4590
Comments
Modified by: Attila Molnár (e_pluribus_unum)description: Hi! Currently ORDER BY with ASC <fieldname> NULLS LAST cannot use either ASC or DESC index. CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname => Hi! Currently ORDER BY with ASC <fieldname> NULLS LAST cannot use either ASC or DESC index. CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname |
Commented by: @dyemanov I'm afraid this would over-complicate the indexing code, as NULLs have to be encoded and handled differently depending not only by the ASC/DESC option by also by the NULLS FIRST/LAST option. Especially this is going to become a nightmare if the NULLs option is specified per index segment, as you suggest. What's the problem with using: select ... where <fieldname> is not null order by <fieldname> asc instead? |
Commented by: @livius2 I also disagree with specify option for index creation but Dimitry what is the problem with using normal index when nulls first/last is used? is speciall null value stored in specified place in index e.g at first node in tree? |
Commented by: Sean Leyne (seanleyne) Edited the Subject to better reflect the request. |
Modified by: Sean Leyne (seanleyne)description: Hi! Currently ORDER BY with ASC <fieldname> NULLS LAST cannot use either ASC or DESC index. CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname => Currently ORDER BY with ASC <fieldname> NULLS LAST cannot use either ASC or DESC index. CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname summary: CREATE INDEX with NULLS FIRST/LAST options => Add NULLS FIRST/LAST options to CREATE INDEX Syntax |
Commented by: @dyemanov Karol, NULLs are currently stored before other nodes in ASC indices and after other nodes in DESC indices. |
Commented by: Attila Molnár (e_pluribus_unum) Dmitry For an alternative request : do not change the create, but use index whenever there is NULLS FIRST/LAST in order by. Somehow must be a way to use an index. |
Commented by: @livius2 >>Dmitry Yemanov , NULLs are currently stored before other nodes in ASC indices and after other nodes in DESC indices. Then i suppose that ORDER BY ASC == ORDER BY ASC NULLS FIRST only parser not choose index if "NULLS FIRST or LAST" is present? This i suppose is simple to fix Problem are only ORDER BY ASC NULLS LAST and ORDER BY DESC NULLS FIRST |
Commented by: @dyemanov There's nothing to fix in the parser / optimizer, it works equally good with both ORDER BY ASC and ORDER BY ASC NULLS FIRST. The ticket is about ASC indices and getting ORDER BY ASC NULLS LAST. |
Commented by: @dyemanov I can see two ways to satisfy this feature request without major pain. Example for ORDER BY ASC NULLS LAST: 1) walk through the keys in their native order but cache rows with NULLs without returning them to client. After all non-NULL keys are processed, return the cached rows. 2) subsequently perform two range index scans, the first starts after NULLs and go until EOF and the second starts with BOF and stops before first non-NULL. But this is not for v2.x versions anyway. |
Commented by: @livius2 Personally i prefer second - speed will be good and client response time very fast |
Commented by: Attila Molnár (e_pluribus_unum) Please rename the ticket : "NULLS FIRST/LAST should not cause index loss". (see prev. comments) Thank you! |
Modified by: Attila Molnár (e_pluribus_unum)issuetype: New Feature [ 2 ] => Improvement [ 4 ] summary: Add NULLS FIRST/LAST options to CREATE INDEX Syntax => NULLS FIRST/LAST should not cause index loss |
This is still an issue (Tested with FB30 and FB40) |
Submitted by: Attila Molnár (e_pluribus_unum)
Is duplicated by CORE5914
Votes: 1
Currently ORDER BY with ASC <fieldname> NULLS LAST cannot use either ASC or DESC index.
It would be great to specify NULLS FIRST/LAST for fields when create an index, and in this case index usage would be possible.
CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
ON tablename
{ (<col> [NULLS FIRST/LAST] [, <col> [NULLS FIRST/LAST]...]) | COMPUTED BY (expression) }
The text was updated successfully, but these errors were encountered: