opensearch-project / opensearch-project/sql

[BUG] specifying fetch_size in REST API causes issue with aliases

Open
#2,579 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug SQL
Dominant language
Java
Stars
176
Forks
229
Avg merge
2d 21h
Merged PRs (30d)
43

Description

What is the bug?
If I select the same column twice, with 2 different aliases, and specify fetch_size, then first alias is ignored. It works fine if I don't include fetch_size.

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

POST /_plugins/_sql
{
    "query": "SELECT `process.executable` AS `colA`, `process.executable` AS `colB` FROM my_index LIMIT 3",
    "fetch_size": 1000
}

What is the expected behavior?
fetch_size should not effect SQL interpretation.

What is your host/environment?

  • OS: Unsure
  • Version: 2.12.0
  • Plugins: sql

Do you have any screenshots?
With fetch_size:

curl -u "password:password" https://example.com:9200/_plugins/_sql/ -X POST -d '{"query": "SELECT `process.executable` AS `colA`, `process.executable` AS `colB` FROM my_index LIMIT 1", "fetch_size": 10}' -H "Content-Type: application/json"
{
  "schema": [
    {
      "name": "process.executable",
      "alias": "colB",
      "type": "keyword"
    },
    {
      "name": "process.executable",
      "alias": "colB",
      "type": "keyword"
    }
  ],
  "total": 10000,
    "datarows": [[
    "C:\\Windows\\explorer.exe",
    "C:\\Windows\\explorer.exe"
  ]],
  "size": 1,
  "status": 200
}

Notice in the schema of the response, both columns have alias "colB"

Without fetch_size:

curl -u "username:password" https://example.com:9200/_plugins/_sql/ -X POST -d '{"query": "SELECT `process.executable` AS `colA`, `process.executable` AS `colB` FROM my_index LIMIT 1"}' -H "Content-Type: application/json"
{
  "schema": [
    {
      "name": "process.executable",
      "alias": "colA",
      "type": "keyword"
    },
    {
      "name": "process.executable",
      "alias": "colB",
      "type": "keyword"
    }
  ],
  "datarows": [
    [
      "C:\\Windows\\explorer.exe",
      "C:\\Windows\\explorer.exe"
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

Here the alias fields in the result schema match the query.

Do you have any additional context?
The value of fetch_size doesn't seem to matter; its presence triggers the problem.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the POST /_plugins/_sql request with duplicate column aliases and fetch_size, then trace the response schema handling for paginated results. Done means the schema preserves colA and colB exactly as written in the query when fetch_size is present, with a regression test covering the request.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
api
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.