Support SELECT <literal>

[jordanw-bq]

Using Docker image amazon/opendistro-for-elasticsearch:1.1.0
Dataset: Flights sample data (loaded through Kibana)
SQL Plugin version: 1.1.0.2
Tableau version: 2019.3

Query: SELECT 1 as `Number of Records`, AvgTicketPrice FROM kibana_sample_data_flights

Result:

[2019-10-25T18:59:17,978][ERROR][c.a.o.s.e.f.PrettyFormatRestExecutor] [odfe-node1] Error happened in pretty formatter
java.lang.UnsupportedOperationException: The following method is not supported in Schema: assign
	at com.amazon.opendistroforelasticsearch.sql.utils.SQLFunctions.getScriptFunctionReturnType(SQLFunctions.java:615) ~[opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.fetchMethodReturnType(SelectResultSet.java:319) ~[opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.populateColumns(SelectResultSet.java:374) ~[opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.loadFromEsState(SelectResultSet.java:145) ~[opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.<init>(SelectResultSet.java:85) ~[opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.loadResultSet(Protocol.java:61) ~[opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.<init>(Protocol.java:48) ~[opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:70) [opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:46) [opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:160) [opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:120) [opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at com.amazon.opendistroforelasticsearch.sql.utils.LogUtils.lambda$withCurrentContext$0(LogUtils.java:72) [opendistro_sql-1.1.1.0.jar:1.1.1.0]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:681) [elasticsearch-7.1.1.jar:7.1.1]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:834) [?:?]

Note: From simple testing, this only prevents Tableau users from previewing table data when selecting them in the window below. They can still use the data when creating visualizations.

Expected Behaviour: SELECT <literal> should return the literal value for each row in the query result.

[chloe-zh]

Thanks for reporting. This exception is thrown from the JDBC formatter and the query has been executed before going into the formatter. We will fix it.

[dai-chen]

Need to investigate if we need/can support other literal values. For example, 1.0 (double), 'abc' (text), '2019-10-25' (date) and true (boolean) etc.

Literals in MySQL for reference: https://dev.mysql.com/doc/refman/8.0/en/literals.html

[chloe-zh]

@jordanw-bq Which literal types are needed to support for now?
We can fix it byt supporting number literal types (including interger and double) in very short time if only these two types are requited right now, and the other types (bool, string, etc.) will take longer. So if the other types are not equally urgent, we will fix the number literals first.

[jordanw-bq]

Number literals (like the example query) are more important; other types can wait for now.