[BUG] `CAST` does not support casting date/time string to timestamp

[yuancu]

What is the bug?
CAST operator does not cast date/time string into a proper timestamp.

I.e.

• Calcite disabled

  source=any | eval t = cast('2011-10-20' as timestamp) | fields t
  

  would result in

  {
    "error": {
      "reason": "Invalid Query",
      "details": "timestamp:2011-10-20 in unsupported format, please use 'yyyy-MM-dd HH:mm:ss[.SSSSSSSSS]'",
      "type": "SemanticCheckException"
    },
    "status": 400
  }
  

• Calcite enabled: this case is a little weird -- while cast('2011-10-20' as timestamp) result in a timestamp as follows

  {
      "schema": [
          {
              "name": "t",
              "type": "timestamp"
          }
      ],
      "datarows": [
          [
              "2011-10-20"
          ]
      ],
      "total": 1,
      "size": 1
  }
  

  Feeding the result to a function that expects timestamp would result in error: eval t1 = cast('2011-10-20' as timestamp) | eval t2 = unix_timestamp(t1) | fields t1, t2 cause the same SemanticCheckException as that with Calcite disabled.
  Besides, date string -> time or time string -> date results in wrong data:

  > source=any | eval t1 = cast('2024-12-10' as time), t2 = cast('13:00:00' as date) | fields t1, t2
  {
      "schema": [
          {
              "name": "t1",
              "type": "time"
          },
          {
              "name": "t2",
              "type": "date"
          }
      ],
      "datarows": [
          [
              "2024-12-10",
              "13:00:00"
          ]
      ],
      "total": 1,
      "size": 1
  }
  

In contrast:

• In MySql:

  > SELECT CAST('2011-10-20' as DATETIME), CAST('12:00:00' as DATETIME)
  +--------------------------------+------------------------------+
  | CAST('2011-10-20' as DATETIME) | CAST('12:00:00' as DATETIME) |
  +--------------------------------+------------------------------+
  | 2011-10-20 00:00:00            | NULL                         |
  +--------------------------------+------------------------------+
  

• In Spark:

  > SELECT CAST('2011-10-20' as TIMESTAMP), CAST('12:00:00' as TIMESTAMP);
  2011-10-20 00:00:00	2025-06-03 12:00:00
  

How can one reproduce the bug?

$ curl --location 'http://localhost:9200/_plugins/_ppl' \
--header 'Content-Type: application/json' \
--data '{
  "query" : "source=dates |  eval t = cast('\''13:00:00'\'' as timestamp) | fields t"
}
'

What is the expected behavior?

• A date string should be casted to a timestamp with 00:00:00
• A time string should be casted to a timestamp with the current date (this aligns with how the rest of the project handles time-to-timestamp cast).

What is your host/environment?

• OS: macos
• Version: 3.10

Do you have any additional context?
\

[LantaoJin]

@RyanL1997 I don't think it's a calcite related issue. Will move the label.