search

opensearch-project / sql

Query your data using familiar SQL or intuitive Piped Processing Language (PPL)
https://opensearch.org/docs/latest/search-plugins/sql/index/
Apache License 2.0
119 stars 138 forks source link

[BUG] SQL-CLI alias bug - output of a column can depend if alias is used #642

Closed MitchellGale closed 1 year ago

MitchellGale commented 2 years ago

Description When alias used in select statement on integer column result can differ from without alias.

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

  1. Go to cli
  2. run select on a column of integers with and without an alias

What is the expected behavior? Aliased and non-aliased columns should show the same data.

What is your host/environment?


opensearchsql> SELECT int0 as "test" FROM calcs;                                                                                                                                                                                                            
fetched rows / total rows = 17/17
+----------+
| "test"   |
|----------|
| 1        |
|          |
|          |
|          |
| 7        |
| 3        |
| 8        |
|          |
|          |
| 8        |
| 4        |
| 10       |
|          |
| 4        |
| 11       |
| 4        |
| 8        |
+----------+
opensearchsql> SELECT int0 FROM calcs;                                                                                                                                                                                                                      
fetched rows / total rows = 17/17
+--------+
| int0   |
|--------|
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
| 0      |
+--------+

expected output is the same for both queries, with and without alias used.

mochi-zhang commented 2 years ago

I didn't see any error from local, can you give some dataset sample for them to repro

opensearchsql> select name as "nn", age as "aa" from accounts;
fetched rows / total rows = 4/4
+----------+--------+
| "nn"     | "aa"   |
|----------+--------|
| Bob      | 30     |
| Jade     | 32     |
| Calorine | 27     |
| Kelly    | null   |
+----------+--------+
opensearchsql> select name, age from accounts;
fetched rows / total rows = 4/4
+----------+-------+
| name     | age   |
|----------+-------|
| Bob      | 30    |
| Jade     | 32    |
| Calorine | 27    |
| Kelly    | null  |
+----------+-------+
MitchellGale commented 2 years ago

Hi @mochi-zhang,

testing file is below.

Should run with.

chmod +x Upload.sh
./Upload.sh

SampleAlias.zip

Test with

SELECT int0 as "test" FROM calcs;  
SELECT int0 FROM calcs;

in the OpenSearchSQL CLI.

MitchellGale commented 1 year ago

CLOSED: Unable to duplicate in 2.4.