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

Incorrect crud.select result with filter conditions an unicode_ci index #301

Open
oleg-jukovec opened this issue Jun 9, 2022 · 0 comments
Labels
bug Something isn't working

Comments

@oleg-jukovec
Copy link
Contributor

Consider a space developers with an unicode_ci index:

box.schema.create_space('developers', {
    format = {
        {name = 'id', type = 'unsigned'},
        {name = 'bucket_id', type = 'unsigned'},
        {name = 'name', type = 'string'},
    }
})
box.space.developers:create_index('primary_index', {
    parts = {
        {field = 1, type = 'unsigned'},
    },
})
box.space.developers:create_index('bucket_id', {
    parts = {
        {field = 2, type = 'unsigned'},
    },
    unique = false,
})
box.space.developers:create_index('full_name', {
    parts = {
        {field = 3, type = 'string', collation = 'unicode_ci'},
    },
    unique = false,
})

Without filter conditions crud.select looks fine:

tarantool> crud.select('developers').rows
---
- - [1, 477, 'Alexey']
  - [2, 401, 'Sergey']
  - [3, 1064, 'alexey']
...

tarantool> crud.select('developers', {{'==', 'name', 'alexey'}}).rows
---
- - [1, 477, 'Alexey']
  - [3, 1064, 'alexey']
...

tarantool> crud.select('developers', {{'>=', 'name', 'alexey'}}).rows
---
- - [1, 477, 'Alexey']
  - [3, 1064, 'alexey']
  - [2, 401, 'Sergey']
...

But with additional filter conditions it's broken:

tarantool> crud.select('developers', {{'>=', 'name', 'alexey'}, {'=', 'name', 'alexey'}}).rows
---
- - [3, 1064, 'alexey']
...

tarantool> crud.select('developers', {{'>=', 'name', 'alexey'}, {'<=', 'name', 'alexey'}}).rows
---
- - [1, 477, 'Alexey']
  - [3, 1064, 'alexey']
  - [2, 401, 'Sergey']
...
@oleg-jukovec oleg-jukovec added bug Something isn't working teamE labels Jun 9, 2022
oleg-jukovec added a commit that referenced this issue Jun 10, 2022
Before the patch we copy an original EQ/REQ condition to post-filter
and change the original to GE/LE. The problem is that EQ/REQ can't be
a stop condition. As a result EQ/REQ + after unexpectedly became a
full scan.

The patch removes this behavior, the query becomes more expected and
in base executes as regular EQ/REQ.

We had an idea to replace EQ with GE+LE. This would help to skip less
tuples in the executor inside scroll_to_after_tuple. But unfortunately,
due to a bug[1], this will break the current behavior. So we need to
fix the bug first.

In additional, the 'potentially long select'[2] warning is confused by
the GE/LE in the query plan.

This patch only changes the internal behavior and does not affect a
client code.

1. #301
2. #277
DifferentialOrange pushed a commit that referenced this issue Jun 15, 2022
Before the patch we copy an original EQ/REQ condition to post-filter
and change the original to GE/LE. The problem is that EQ/REQ can't be
a stop condition. As a result EQ/REQ + after unexpectedly became a
full scan.

The patch removes this behavior, the query becomes more expected and
in base executes as regular EQ/REQ.

We had an idea to replace EQ with GE+LE. This would help to skip less
tuples in the executor inside scroll_to_after_tuple. But unfortunately,
due to a bug[1], this will break the current behavior. So we need to
fix the bug first.

In additional, the 'potentially long select'[2] warning is confused by
the GE/LE in the query plan.

This patch only changes the internal behavior and does not affect a
client code.

1. #301
2. #277
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

3 participants