java.lang.IllegalArgumentException: Format 't' not supported

[ZhiXingHeYiApple]

opendistro version：1.6.0
When I do the sql jdbc search, I get the exception below:

// in kibana
POST _opendistro/_sql
{
  "query": "select * from my-index"
}

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Format 't' not supported",
    "type": "IllegalArgumentException"
  },
  "status": 500
}

The error log in elasticsearch node describe the cuase:

es7-node1     | 2020-04-07T08:04:28.897786856Z [2020-04-07T08:04:28,896][WARN ][c.a.o.s.e.f.PrettyFormatRestExecutor] [es7-node1] Error happened in pretty formatter
es7-node1     | 2020-04-07T08:04:28.897824448Z java.lang.IllegalArgumentException: Format 't' not supported
es7-node1     | 2020-04-07T08:04:28.897832213Z  at org.apache.commons.lang3.time.FastDateParser.getStrategy(FastDateParser.java:560) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897837272Z  at org.apache.commons.lang3.time.FastDateParser.access$200(FastDateParser.java:73) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897842119Z  at org.apache.commons.lang3.time.FastDateParser$StrategyParser.letterPattern(FastDateParser.java:230) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897862974Z  at org.apache.commons.lang3.time.FastDateParser$StrategyParser.getNextStrategy(FastDateParser.java:216) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897867601Z  at org.apache.commons.lang3.time.FastDateParser.init(FastDateParser.java:165) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897871719Z  at org.apache.commons.lang3.time.FastDateParser.<init>(FastDateParser.java:151) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897877248Z  at org.apache.commons.lang3.time.FastDateParser.<init>(FastDateParser.java:116) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897881287Z  at org.apache.commons.lang3.time.DateUtils.parseDateWithLeniency(DateUtils.java:377) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897892475Z  at org.apache.commons.lang3.time.DateUtils.parseDate(DateUtils.java:302) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897896975Z  at org.apache.commons.lang3.time.DateUtils.parseDate(DateUtils.java:279) ~[commons-lang3-3.9.jar:3.9]
es7-node1     | 2020-04-07T08:04:28.897901655Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.DateFieldFormatter.parseDateString(DateFieldFormatter.java:184) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897915001Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.DateFieldFormatter.applyJDBCDateFormat(DateFieldFormatter.java:90) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897919511Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.populateRows(SelectResultSet.java:558) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897923880Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.extractData(SelectResultSet.java:526) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897931007Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.<init>(SelectResultSet.java:108) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897935525Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.loadResultSet(Protocol.java:83) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897939785Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.<init>(Protocol.java:65) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897945146Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:71) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897949493Z  at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:47) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897953740Z  at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:161) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897959747Z  at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:121) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897963900Z  at com.amazon.opendistroforelasticsearch.sql.utils.LogUtils.lambda$withCurrentContext$0(LogUtils.java:72) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
es7-node1     | 2020-04-07T08:04:28.897974492Z  at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:633) [elasticsearch-7.6.1.jar:7.6.1]
es7-node1     | 2020-04-07T08:04:28.897978845Z  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
es7-node1     | 2020-04-07T08:04:28.897982988Z  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
es7-node1     | 2020-04-07T08:04:28.897988917Z  at java.lang.Thread.run(Thread.java:835) [?:?]

I download the opendistro sql plugin code and view it. Find that the code only consider the "date_optional_time" format of the date field, but I define the date field in my index like this.

"properties": {
    "time": {
        "format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss||epoch_millis",
        "type": "date"
    },
    ....
}

But the code in sql plugin only consider the "date_optional_time".
DateFieldFormatter class

private Date parseDateString(List<String> formats, String columnOriginalDate) {
        TimeZone originalDefaultTimeZone = TimeZone.getDefault();
        Date parsedDate = null;

        // Apache Commons DateUtils uses the default TimeZone for the JVM when parsing.
        // However, since all dates on Elasticsearch are stored as UTC, we need to
        // parse these values using the UTC timezone.
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
        for (String columnFormat : formats) {
            try {
                switch (columnFormat) {
                    case "date_optional_time":
                        parsedDate = DateUtils.parseDate(
                            columnOriginalDate,
                            FORMAT_DOT_KIBANA_SAMPLE_DATA_LOGS_EXCEPTION,
                            FORMAT_DOT_KIBANA_SAMPLE_DATA_FLIGHTS_EXCEPTION,
                            FORMAT_DOT_KIBANA_SAMPLE_DATA_FLIGHTS_EXCEPTION_NO_TIME,
                            FORMAT_DOT_KIBANA_SAMPLE_DATA_ECOMMERCE_EXCEPTION,
                            FORMAT_DOT_DATE_AND_TIME,
                            FORMAT_DOT_DATE);
                        break;
                    case "epoch_millis":
                        parsedDate = new Date(Long.parseLong(columnOriginalDate));
                        break;
                    case "epoch_second":
                        parsedDate = new Date(Long.parseLong(columnOriginalDate) * 1000);
                        break;
                    default:
                        String formatString = DateFormat.getFormatString(columnFormat);
                        if (formatString == null) {
                            // Custom format; take as-is
                            formatString = columnFormat;
                        }
                        parsedDate = DateUtils.parseDate(columnOriginalDate, formatString);
                }
            } catch (ParseException | NumberFormatException e) {
                LOG.warn(String.format("Could not parse date string %s as %s", columnOriginalDate, columnFormat));
            }
        }
        // Reset default timezone after parsing
        TimeZone.setDefault(originalDefaultTimeZone);

        return parsedDate;
    }

DateFormat class
There is no "strict_date_optional_time" entry for formatMap.

public class DateFormat {

    private static Map<String, String> formatMap = new HashMap<>();

    static {
        // Special cases that are parsed separately
        formatMap.put("date_optional_time", "");
        formatMap.put("epoch_millis", "");
        formatMap.put("epoch_second", "");

        formatMap.put("basic_date", Date.BASIC_DATE);
        formatMap.put("basic_date_time", Date.BASIC_DATE + Time.T + Time.BASIC_TIME + Time.MILLIS + Time.TZ);
        formatMap.put("basic_date_time_no_millis", Date.BASIC_DATE + Time.T + Time.BASIC_TIME + Time.TZ);

        formatMap.put("basic_ordinal_date", Date.BASIC_ORDINAL_DATE);
        formatMap.put("basic_ordinal_date_time",
                      Date.BASIC_ORDINAL_DATE + Time.T + Time.BASIC_TIME + Time.MILLIS + Time.TZ);
        formatMap.put("basic_ordinal_date_time_no_millis", Date.BASIC_ORDINAL_DATE+ Time.T + Time.BASIC_TIME + Time.TZ);

        formatMap.put("basic_time", Time.BASIC_TIME + Time.MILLIS + Time.TZ);
        formatMap.put("basic_time_no_millis", Time.BASIC_TIME + Time.TZ);

        formatMap.put("basic_t_time", Time.T + Time.BASIC_TIME + Time.MILLIS + Time.TZ);
        formatMap.put("basic_t_time_no_millis", Time.T + Time.BASIC_TIME + Time.TZ);

        formatMap.put("basic_week_date", Date.BASIC_WEEK_DATE);
        formatMap.put("basic_week_date_time", Date.BASIC_WEEK_DATE + Time.T + Time.BASIC_TIME + Time.MILLIS + Time.TZ);
        formatMap.put("basic_week_date_time_no_millis", Date.BASIC_WEEK_DATE + Time.T + Time.BASIC_TIME + Time.TZ);

        formatMap.put("date", Date.DATE);
        formatMap.put("date_hour", Date.DATE + Time.T + Time.HOUR);
        formatMap.put("date_hour_minute", Date.DATE + Time.T + Time.HOUR_MINUTE);
        formatMap.put("date_hour_minute_second", Date.DATE + Time.T + Time.TIME);
        formatMap.put("date_hour_minute_second_fraction", Date.DATE + Time.T + Time.TIME + Time.MILLIS);
        formatMap.put("date_hour_minute_second_millis", Date.DATE + Time.T + Time.TIME + Time.MILLIS);
        formatMap.put("date_time", Date.DATE + Time.T + Time.TIME + Time.MILLIS + Time.TZZ);
        formatMap.put("date_time_no_millis", Date.DATE + Time.T + Time.TIME + Time.TZZ);

        formatMap.put("hour", Time.HOUR);
        formatMap.put("hour_minute", Time.HOUR_MINUTE);
        formatMap.put("hour_minute_second", Time.TIME);
        formatMap.put("hour_minute_second_fraction", Time.TIME + Time.MILLIS);
        formatMap.put("hour_minute_second_millis", Time.TIME + Time.MILLIS);

        formatMap.put("ordinal_date", Date.ORDINAL_DATE);
        formatMap.put("ordinal_date_time", Date.ORDINAL_DATE + Time.T + Time.TIME + Time.MILLIS + Time.TZZ);
        formatMap.put("ordinal_date_time_no_millis", Date.ORDINAL_DATE + Time.T + Time.TIME + Time.TZZ);

        formatMap.put("time", Time.TIME + Time.MILLIS + Time.TZZ);
        formatMap.put("time_no_millis", Time.TIME + Time.TZZ);

        formatMap.put("t_time", Time.T + Time.TIME + Time.MILLIS + Time.TZZ);
        formatMap.put("t_time_no_millis", Time.T + Time.TIME + Time.TZZ);

        formatMap.put("week_date", Date.WEEK_DATE);
        formatMap.put("week_date_time", Date.WEEK_DATE + Time.T + Time.TIME + Time.MILLIS + Time.TZZ);
        formatMap.put("week_date_time_no_millis", Date.WEEK_DATE + Time.T + Time.TIME + Time.TZZ);

        // Note: input mapping is "weekyear", but output value is "week_year"
        formatMap.put("week_year", Date.WEEKYEAR);
        formatMap.put("weekyear_week", Date.WEEKYEAR_WEEK);
        formatMap.put("weekyear_week_day", Date.WEEK_DATE);

        formatMap.put("year", Date.YEAR);
        formatMap.put("year_month", Date.YEAR_MONTH);
        formatMap.put("year_month_day", Date.DATE);
    }
    ......
}

[penghuo]

Thanks for reporting the issue and detail debug information.