[BUG] Query Workbench: Semicolon in SQL strings terminates query

[smacrakis]

Describe the bug

In the Query Workbench, the SQL query << select "foo;bar" >> gives an error because it is segmenting the query at the semicolon, even though the semicolon is within a quoted string.

The problem is with Query Workbench since submitting the query using POST /_plugins/_sql works fine.

Tested on https://playground.opensearch.org/app/home

Related component

Other

To Reproduce

Go to https://playground.opensearch.org/app/opensearch-query-workbench#/ .
Enter the query
select "foo;bar"
Run.
See the errors:

Select "foo: Bad Request, this query is not runnable. 

Bar": Bad Request, this query is not runnable. 

It is strange that it capitalizes "Bar" in the error message....

Expected behavior

There should be one result row
foo;bar
with no error.

Additional Details

Plugins
Please list all plugins currently enabled.

Screenshots
If applicable, add screenshots to help explain your problem.

Host/Environment (please complete the following information):

• OS: [e.g. iOS]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

[macohen]

@opensearch-project/admin can this issue be moved to https://github.com/opensearch-project/dashboards-query-workbench, please? Thanks!

[ps48]

@smacrakis This may be the expected behaviour, where workbench tries to run multiple queries in UI and these queries are separated with semi-colon.

1. To reproduce the error on our side: Can you please provide us with sample data that you're using and the related queries which use semi-colon.
2. Did you see the same behavior when using backticks `foo;bar`

[smacrakis]

Sample data is not needed. The SQL query << select "foo" >> correctly returns the value << foo >>.

OpenSearch SQL does not support backticks for quoting strings. Entering the query << select foo >> gives << Bad Request, this query is not runnable. >>

OpenSearch SQL allows doublequote (") and singlequote (') for quoting strings. But Query Workbench doesn't understand that semicolons within quoted strings do not separate SQL queries:

<< select 'foo;bar' >> => error
<< select "foo;bar" >> => error

How can I enter a string including a semicolon in Query Workbench?

[ps48]

Was able to reproduce the issue:

select 'foo;bar' -> works in dashboards dev tools/API but doesn't work as expected in workbench.

This is due to the way workbench is built to run multiple queries at a time. It supports multiple queries to be run which are separated by ;. In the above query workbench is trying to split foo and bar as it sees a semicolon.

Action Item: Update split queries by checking if the ; resides within quotes