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.

Sign up for GitHub

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

Jump to bottom

[BUG] Inconsistent behaviour for date field in nested collection queries #1545

Open
parked-toes opened this issue Apr 18, 2023 · 3 comments · May be fixed by #3160
Open

[BUG] Inconsistent behaviour for date field in nested collection queries #1545

parked-toes opened this issue Apr 18, 2023 · 3 comments · May be fixed by #3160
Labels
bug Something isn't working SQL v2.19.0 Issues targeting release v2.19.0

Comments

@parked-toes
Copy link

What is the bug?
When a query contains nested collection (self join) it returns different data and datatype, compared to a "simple" query.

How can one reproduce the bug?
Steps to reproduce the behavior:

  1. Create mapping:
PUT datetype_test
{
  "mappings": {
    "properties": {
      "dateAsDate": {
        "type": "date"
      },
      "longAsDate": {
        "type": "date"
      },      
      "id": {
        "type": "long"
      },
      "projects": {
        "type": "nested", 
        "properties": {
          "name": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword"
              }
            }
          }
        }
      }
    }
  }
}

Note that both date fields are mapped as date, projects is nested

  1. Index data:
POST datetype_test/_bulk?refresh
{"index":{"_id":"1"}}
{"id":3,"dateAsDate":"2023-01-01T00:00:00", "longAsDate": 1672531200000, "projects":[{"name":"SQL Spectrum querying"},{"name":"SQL security"},{"name":"OpenSearch security"}]}
{"index":{"_id":"2"}}
{"id":4,"dateAsDate":"2024-01-01T00:00:00", "longAsDate": 1704067200000, "projects":[]}
{"index":{"_id":"3"}}
{"id":6,"dateAsDate":"2025-01-01T00:00:00","longAsDate": 1735689600000, "projects":[{"name":"SQL security"},{"name":"Hello security"}]}
  1. Query date fields using simple query:
SELECT t.dateAsDate,
       t.longAsDate
FROM datetype_test AS t

Result:

{
  "schema": [
    {
      "name": "dateAsDate",
      "type": "timestamp"
    },
    {
      "name": "longAsDate",
      "type": "timestamp"
    }
  ],
  "datarows": [
    [
      "2023-01-01 00:00:00",
      "2023-01-01 00:00:00"
    ],
    [
      "2024-01-01 00:00:00",
      "2024-01-01 00:00:00"
    ],
    [
      "2025-01-01 00:00:00",
      "2025-01-01 00:00:00"
    ]
  ],
  "total": 3,
  "size": 3,
  "status": 200
}

Query the same date fields using nested collection query:

SELECT t.dateAsDate,
       t.longAsDate
FROM datetype_test AS t,
       t.projects AS p
{
  "schema": [
    {
      "name": "dateAsDate",
      "type": "date"
    },
    {
      "name": "longAsDate",
      "type": "date"
    }
  ],
  "total": 3,
  "datarows": [
    [
      "2023-01-01 00:00:00.000",
      1672531200000
    ],
    [
      "2024-01-01 00:00:00.000",
      1704067200000
    ],
    [
      "2025-01-01 00:00:00.000",
      1735689600000
    ]
  ],
  "size": 3,
  "status": 200
}

Note that there is a type change: timestamp -> date in schema and inconsistent resultset field formats.

Moreover, both require inconsistent date format in WHERE clause:

WHERE t.dateAsDate > "2023-05-01T00:00:00Z"

vs

WHERE t.dateAsDate > "2023-05-01 00:00:00"

What is the expected behavior?
All queries return date type in schema and "2025-01-01 00:00:00.000" like format for both date fields as defined in mapping.

What is your host/environment?
2.6.0
@parked-toes parked-toes added bug Something isn't working untriaged labels Apr 18, 2023
@dai-chen
Copy link
Collaborator

Thanks for reporting the issue! I think this is caused by inconsistency between our legacy engine and V2 engine. PartiQL feature in second query is not migrated to V2 yet. We're actively working on this and will update once it's available. Thanks!

@dai-chen dai-chen added the SQL label Apr 19, 2023
@parked-toes parked-toes changed the title [BUG] Inconsistend behaviour for date field in nested collection queries [BUG] Inconsistent behaviour for date field in nested collection queries Apr 26, 2023
@anasalkouz anasalkouz added the v2.19.0 Issues targeting release v2.19.0 label Nov 15, 2024
@andy-k-improving
Copy link

Hi team,
I'm picking up this.

@andy-k-improving andy-k-improving linked a pull request Nov 20, 2024 that will close this issue
Open
7 tasks
@andy-k-improving
Copy link

Proposed fix submitted under: #3160

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working SQL v2.19.0 Issues targeting release v2.19.0
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix: Date field format parsing for legacy query engine Bit-Quill/opensearch-project-sql
4 participants
@anasalkouz @dai-chen @parked-toes @andy-k-improving