SQL: TIME and DATE types metadata attributes corrections

[bpintea]

The values returned by SYS TYPES for the time data types needs adjusting for the following attributes: MINIMUM_SCALE, MAXIMUM_SCALE and SQL_DATETIME_SUB as follows:

• the DATE and TIME must have the values swapped for MINIMUM_SCALE and MAXIMUM_SCALE attributes (since DATE has no seconds component);
• SQL_DATETIME_SUB must have these subcode values:
  • for DATE: 1;
  • for TIME: 2.

Current values (for convenience):

        TYPE_NAME        |   DATA_TYPE   |   PRECISION   |LITERAL_PREFIX |LITERAL_SUFFIX | CREATE_PARAMS |   NULLABLE    |CASE_SENSITIVE |  SEARCHABLE   |UNSIGNED_ATTRIBUTE|FIXED_PREC_SCALE|AUTO_INCREMENT |LOCAL_TYPE_NAME| MINIMUM_SCALE | MAXIMUM_SCALE | SQL_DATA_TYPE |SQL_DATETIME_SUB|NUM_PREC_RADIX |INTERVAL_PRECISION
-------------------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+------------------+----------------+---------------+---------------+---------------+---------------+---------------+----------------+---------------+------------------
DATE                     |91             |29             |'              |'              |null           |2              |false          |3              |true              |false           |false          |null           |3              |3              |91             |0               |null           |null              
TIME                     |92             |18             |'              |'              |null           |2              |false          |3              |true              |false           |false          |null           |null           |null           |92             |0               |null           |null              
DATETIME                 |93             |29             |'              |'              |null           |2              |false          |3              |true              |false           |false          |null           |3              |3              |9              |3               |null           |null              

[elasticmachine]

Pinging @elastic/es-search

[ithompson4]

Could I work on this? Thanks.

[matriv]

@ithompson4 Feel free to work on it. The class to start with is: https://github.com/elastic/elasticsearch/blob/master/x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/type/DataTypes.java

[matriv]

Fixed by #55316

[matriv]

master : c23677c
7.x : 8abdf7c
7.7 : 736b8a8