[BUG] MIN(str, str) and MAX(str, str) functions fail

[Yury-Fridlyand]

Describe the bug Queries with functions MIN and MAX called with 2 arguments of type string fail and produce incorrect response

To Reproduce

curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query":  select MAX (\"Apple\",\"Banana\") from calcs limit 1;"}''
JSON does not allow non-finite numbers.
curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query":  select MIN (\"Apple\",\"Banana\") from calcs limit 1;"}''
JSON does not allow non-finite numbers.

Expected behavior According to Tableau's definition:

With strings, MAX finds the value that is highest in the sort sequence defined by the database for that column. It returns Null if either argument is Null. Example MAX ("Apple","Banana") = "Banana"

The server log is not verbose:

[2021-11-11T05:00:09,303][WARN ][o.o.s.l.u.QueryDataAnonymizer] [6a3601a40d1d]  Caught an exception when anonymizing sensitive data
[2021-11-11T05:00:09,303][INFO ][o.o.s.l.p.RestSqlAction  ] [6a3601a40d1d]  [bab60cbb-d175-4c45-806f-b05fc08bd5fe] Incoming request /_plugins/_sql:  select MAX ("Apple","Banana") from calcs limit 1;
[2021-11-11T05:00:09,313][WARN ][stderr                   ] [6a3601a40d1d]  line 1:20 no viable alternative at input 'MAX ("Apple",'
[2021-11-11T05:00:09,316][WARN ][o.o.s.l.e.AsyncRestExecutor] [6a3601a40d1d]  [bab60cbb-d175-4c45-806f-b05fc08bd5fe] [MCB] async task got an unknown throwable: JSON does not allow non-finite numbers.
[2021-11-11T05:00:49,952][WARN ][o.o.s.l.u.QueryDataAnonymizer] [6a3601a40d1d]  Caught an exception when anonymizing sensitive data
[2021-11-11T05:00:49,952][INFO ][o.o.s.l.p.RestSqlAction  ] [6a3601a40d1d]  [116a891b-0786-4e32-b28b-18219b626782] Incoming request /_plugins/_sql:  select Min ("Apple","Banana") from calcs limit 1;
[2021-11-11T05:00:49,962][WARN ][stderr                   ] [6a3601a40d1d]  line 1:20 no viable alternative at input 'Min ("Apple",'
[2021-11-11T05:00:49,965][WARN ][o.o.s.l.e.AsyncRestExecutor] [6a3601a40d1d]  [116a891b-0786-4e32-b28b-18219b626782] [MCB] async task got an unknown throwable: JSON does not allow non-finite numbers

[chloe-zh]

@Yury-Fridlyand Thanks for reporting this issue, will take a look and make it fixed once we have resources.

[ghost]

This issue affects TDVT tests:

• exprtests/standard\setup.agg.max.txt
• exprtests/standard\setup.agg.min.txt

[acarbonetto]

Reference: https://opensearch.org/docs/latest/opensearch/aggregations/ By default, OpenSearch doesn't perform aggregations on string values. We would have to understand how to map a MAX([strings]) to a valid OpenSearch query (not necessarily an aggregation) before tackling this issue.