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

NULLS FIRST/LAST should not cause index loss [CORE4266] #4590

Open
firebird-automations opened this issue Nov 13, 2013 · 15 comments
Open

NULLS FIRST/LAST should not cause index loss [CORE4266] #4590

firebird-automations opened this issue Nov 13, 2013 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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

=>

Hi!

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

@firebird-automations
Copy link
Collaborator Author

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
union all
select ... where <fieldname> is null

instead?

@firebird-automations
Copy link
Collaborator Author

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Edited the Subject to better reflect the request.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: Hi!

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

=>

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

summary: CREATE INDEX with NULLS FIRST/LAST options => Add NULLS FIRST/LAST options to CREATE INDEX Syntax

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Karol, NULLs are currently stored before other nodes in ASC indices and after other nodes in DESC indices.

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Dmitry
The union all is not a solution, it also do not use index.
And ICO many order by with nulls last the union all expression is become horribly complex.

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.

@firebird-automations
Copy link
Collaborator Author

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
and
ORDER BY DESC == ORDER BY DESC NULLS LAST

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
which require changing logic in data retrieve for nulls

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Personally i prefer second - speed will be good and client response time very fast
with first proposition there will be slow response time with many nulls

@firebird-automations
Copy link
Collaborator Author

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!

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE5914 [ CORE5914 ]

@EPluribusUnum
Copy link

This is still an issue (Tested with FB30 and FB40)

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

2 participants