[BUG] Dashboard filters issue with Quicksight

[rupal-bq]

What is the bug?
While adding filters (as controls or otherwise), it gives an SQL exception/fails to populate any option in the dropdown, for both single-option and multi-option dropdown.

While selecting the filter as a raw text field, it accepts single as well multiple values, and works as expected.

[Yury-Fridlyand]

@rupal-bq, is it a JDBC driver issue or Dashboards issue?

[rupal-bq]

@rupal-bq, is it a JDBC driver issue or Dashboards issue?

Seems to be a JDBC driver issue. While applying filters, getting below error.

[Yury-Fridlyand]

I don't have access to Quicksight now. Can you reproduce that issue? Can you send me anonymized query please (SQL plugin prints it into logs)? What is the data mapping?

[rupal-bq]

SQL query:

SELECT item_type AS abc123
FROM tmp_index
GROUP BY item_type
ORDER BY abc123 IS NOT NULL, abc123
LIMIT 10001

mapping:

{
  “tmp_index": {
    "mappings": {
      "properties": {
        “item”: {
          "type": "keyword"
        },
        "time": {
          "type": "date"
        },
        “item_type": {
          "type": "keyword"
        }
      }
    }
  }
}

[MaxKsyunz]

Thank you @rupal-bq!

What versions of OpenSearch and JDBC driver is this using?

[Yury-Fridlyand]

Probably it is V2 parser issue:

opensearchsql> select city as abc from bank group by city order by abc is not null, abc limit 100;
{'reason': 'Invalid SQL query', 'details': "can't resolve Symbol(namespace=FIELD_NAME, name=abc) in type env", 'type': 'SemanticCheckException'}
opensearchsql> select city as abc from bank group by city order by city is not null, abc limit 100;
fetched rows / total rows = 7/7
...

I see alias is not supported in expression in ORDER BY clause; meanwhile it is supported in ORDER BY without expression. UPD GROUP BY is also affected.

@rupal-bq, please, confirm that the query is correct. I want to ensure that we found the right bug.

[rupal-bq]

Probably it is V2 parser issue:

opensearchsql> select city as abc from bank group by city order by abc is not null, abc limit 100;
{'reason': 'Invalid SQL query', 'details': "can't resolve Symbol(namespace=FIELD_NAME, name=abc) in type env", 'type': 'SemanticCheckException'}
opensearchsql> select city as abc from bank group by city order by city is not null, abc limit 100;
fetched rows / total rows = 7/7
...

I see alias is not supported in expression in ORDER BY clause; meanwhile it is supported in ORDER BY without expression. UPD GROUP BY is also affected.

@rupal-bq, please, confirm that the query is correct. I want to ensure that we found the right bug.

Hi @Yury-Fridlyand, the query is correct. Didn't find much information in quicksight logs, it only had generic sql exception.

[acarbonetto]

Seems like this is related to:

• [Bug] Alias in ORDER BY expression throws semantic exception #798
• [BUG] IS NOT NULL with alias throws SemanticCheckException #292

[rupal-bq]

Thank you @rupal-bq!

What versions of OpenSearch and JDBC driver is this using?

OpenSearch version is 2.5 but not sure what JDBC version (using Quicksight connector)

[gamebusterz]

Hi @rupal-bq , is there any progress on this?