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

[BUG] _id is not available in where condition #639

Closed
Shams-yang opened this issue Jun 12, 2022 · 6 comments
Closed

[BUG] _id is not available in where condition #639

Shams-yang opened this issue Jun 12, 2022 · 6 comments
Assignees
Labels
enhancement New feature or request v2.7.0

Comments

@Shams-yang
Copy link

Shams-yang commented Jun 12, 2022

What is the bug?

reproduce:

POST _plugins/_sql
{
"query": "SELECT * FROM my-index1 where _id = 1"
}

SemanticCheckException was encountered

@Shams-yang Shams-yang added bug Something isn't working untriaged labels Jun 12, 2022
@penghuo penghuo added the enhancement New feature or request label Jun 13, 2022
@penghuo
Copy link
Collaborator

penghuo commented Jun 13, 2022

similar one. #339.

@penghuo penghuo removed bug Something isn't working untriaged labels Jun 13, 2022
@acarbonetto
Copy link
Collaborator

related issue: #432

@dai-chen
Copy link
Collaborator

Since we're adding more Storage implementation for external data source, such as S3, Prometheus. Probably another way to look at this is: every storage may have some special/meta fields. For example, OpenSearch has _id, _score, S3 may have _partition, _file like these in Trino https://trino.io/docs/current/connector/hive.html#special-columns.

@acarbonetto
Copy link
Collaborator

We can include metadata fields in the parser language as part of this ticket and hardcode the field types in the expression analyzer. This will also work to fix the double underscore fields.

@dai-chen unfortunately, this doesn't work for other storage devices - which means all _id fields will be treated equally. We would need to think of a way around this when defining our user-defined-languages and maybe just check the table storage type is part of a hardcoded list (for that storage type) or is defined in the user-defined-language.

Works for these examples:

SELECT calcs.key, str0, _id, _score, _maxscore FROM calcs WHERE _id="5"
>> returns calcs.key, calcs.str0, calcs._id, calcs._score, calcs._maxscore

SELECT *, _id FROM bigint WHERE _id="2"
>> returns all of the fields defined in bigint plus _id

SELECT __myCoolField FROM myindex WHERE __myCoolField="cool";
>>returns myindex.__myCoolField

PR prototype: Bit-Quill#142

@dai-chen dai-chen added the v2.5.0 'Issues and PRs related to version v2.5.0' label Dec 15, 2022
@dai-chen dai-chen removed the v2.5.0 'Issues and PRs related to version v2.5.0' label Jan 6, 2023
@dai-chen
Copy link
Collaborator

dai-chen commented Feb 7, 2023

@acarbonetto Any progress on this? I'm thinking can we label all these related issues 2.6.0?

acarbonetto added a commit to Bit-Quill/opensearch-project-sql that referenced this issue Feb 14, 2023
acarbonetto added a commit to Bit-Quill/opensearch-project-sql that referenced this issue Mar 21, 2023
…nction (#228)

* Rebase from main

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update to define and include metadata when visiting the expr node

Signed-off-by: Andrew Carbonetto <[email protected]>

* Add specific metadata identifiers

Signed-off-by: Andrew Carbonetto <[email protected]>

* Add IT tests and add parser changes

Signed-off-by: Andrew Carbonetto <[email protected]>

* Rebase from main

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update score function expression analyzer to return boosted relevance function

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update builder to track scores

Signed-off-by: Andrew Carbonetto <[email protected]>

* Remove ScoreExpression.java and cleanup checkstyle

Signed-off-by: Andrew Carbonetto <[email protected]>

* cleanup checkstyle

Signed-off-by: Andrew Carbonetto <[email protected]>

* Cleanup and add alternative score function syntax

Signed-off-by: Andrew Carbonetto <[email protected]>

* Cleanup and add alternative score function syntax

Signed-off-by: Andrew Carbonetto <[email protected]>

* Fix some bugs and add Expression tests

Signed-off-by: Andrew Carbonetto <[email protected]>

* Add expresssion and analyzer tests

Signed-off-by: Andrew Carbonetto <[email protected]>

* Add score doctests

Signed-off-by: Andrew Carbonetto <[email protected]>

* Add score function doctests

Signed-off-by: Andrew Carbonetto <[email protected]>

* Add metafield tests

Signed-off-by: Andrew Carbonetto <[email protected]>

* Move legacy test and mark old as ignore

Signed-off-by: Andrew Carbonetto <[email protected]>

* fix checkstyle violations

Signed-off-by: Andrew Carbonetto <[email protected]>

* fix checkstyle violations

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update tests and identifier to accept metafields

Signed-off-by: Andrew Carbonetto <[email protected]>

* Checkstyle fixes

Signed-off-by: Andrew Carbonetto <[email protected]>

* Rebase from main

Signed-off-by: Andrew Carbonetto <[email protected]>

* Rebase from main

Signed-off-by: Andrew Carbonetto <[email protected]>

* Rebase from main

Signed-off-by: Andrew Carbonetto <[email protected]>

* fix checkstyle violations

Signed-off-by: Andrew Carbonetto <[email protected]>

* Revert bad conflict resolution

Signed-off-by: Andrew Carbonetto <[email protected]>

* Fix for review comments

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update IT tests and legacy tests for comments

Signed-off-by: Andrew Carbonetto <[email protected]>

* Minor comment

Signed-off-by: Andrew Carbonetto <[email protected]>

* Updates for whitespace

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update basics.rst to show OS result

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update basics.rst to show OS result

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update basics.rst description

Signed-off-by: Andrew Carbonetto <[email protected]>

* Change Score function to accept a double/integer not an unresolved

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update functions.rst

Signed-off-by: Andrew Carbonetto <[email protected]>

* Checkstyle update

Signed-off-by: Andrew Carbonetto <[email protected]>

* Move reserved world symbol table to OpenSearchTable

Signed-off-by: Andrew Carbonetto <[email protected]>

* Update functions.rst for review comments

Signed-off-by: Andrew Carbonetto <[email protected]>

* Removed parser meta tokens; Changes ImmutableMap to Map

Signed-off-by: Andrew Carbonetto <[email protected]>

* Removed parser meta tokens; Changes ImmutableMap to Map

Signed-off-by: Andrew Carbonetto <[email protected]>

---------

Signed-off-by: Andrew Carbonetto <[email protected]>
MaxKsyunz pushed a commit that referenced this issue Apr 10, 2023
…ction (#228) (#1456)

Allow metadata fields and score OpenSearch function.

Signed-off-by: Andrew Carbonetto <[email protected]>
opensearch-trigger-bot bot pushed a commit that referenced this issue Apr 10, 2023
…ction (#228) (#1456)

Allow metadata fields and score OpenSearch function.

Signed-off-by: Andrew Carbonetto <[email protected]>
(cherry picked from commit e805151)
opensearch-trigger-bot bot pushed a commit that referenced this issue Apr 10, 2023
…ction (#228) (#1456)

Allow metadata fields and score OpenSearch function.

Signed-off-by: Andrew Carbonetto <[email protected]>
(cherry picked from commit e805151)
penghuo pushed a commit that referenced this issue Apr 12, 2023
…ion (#228) (#1509)

* #639: Support OpenSearch metadata fields and the score OpenSearch function (#228) (#1456)

Signed-off-by: Andrew Carbonetto <[email protected]>
Co-authored-by: Andrew Carbonetto <[email protected]>
penghuo pushed a commit that referenced this issue Apr 12, 2023
…ion (#228) (#1508)

* #639: Support OpenSearch metadata fields and the score OpenSearch function (#228) (#1456)

Signed-off-by: Andrew Carbonetto <[email protected]>
Co-authored-by: Andrew Carbonetto <[email protected]>
@acarbonetto
Copy link
Collaborator

@dai-chen this issue can be closed as part of #1456

acarbonetto added a commit to Bit-Quill/opensearch-project-sql that referenced this issue Apr 18, 2023
…elds and the score OpenSearch function (#228) (opensearch-project#1456)

Allow metadata fields and score OpenSearch function.

Signed-off-by: Andrew Carbonetto <[email protected]>
andy-k-improving pushed a commit to Bit-Quill/opensearch-project-sql that referenced this issue Nov 16, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request v2.7.0
Projects
None yet
Development

No branches or pull requests

5 participants