[BUG] timestamp data type handling is inconsistent

[sablanscott]

What is the bug?

When using SQL query, a timestamp field is handled differently if the SQL query has a join.

How can one reproduce the bug?
Steps to reproduce the behavior:

Data:
POST _bulk
{ "index": { "_index": "index_one"} }
{"stanza":4,"line_number":19,"line_text":"Presently my soul grew stronger; hesitating then no longer,"}
{ "index": { "_index": "index_one"} }
{"stanza":4,"line_number":20,"line_text":""Sir," said I, "or Madam, truly your forgiveness I implore;"}
{ "index": { "_index": "index_one"} }
{"stanza":11,"line_number":64,"line_text":"Followed fast and followed faster till his songs one burden bore—"}
{ "index": { "_index": "index_one"} }
{"stanza":11,"line_number":65,"line_text":"Till the dirges of his Hope that melancholy burden bore"}

POST _bulk
{ "index": { "_index": "index_two"} }
{ "log_timestamp": "2024-11-13T09:11:01.668-06:00","stanza": 11,"some_number": "some data 11"}
{ "index": { "_index": "index_two"} }
{ "log_timestamp": "2024-11-13T09:12:01.668-06:00","stanza": 4,"some_number": "some data 4 u"}

1. Query index_two
   POST _plugins/_sql/
   {
   "query": "SELECT * from index_two"
   }
   The field log_timestamp schema is 'timestamp' but the timezone information is not displayed. (Image 1)

2. run a join on the 2 indexes.
   POST _plugins/_sql/
   {
   "query": "SELECT two.log_timestamp, two.stanza, one.line_number, one.line_text FROM index_two two JOIN index_one one ON one.stanza = two.stanza"
   }
   The field log_timestamp schema is 'date' now and the returned value has the timezone information. (Image 2)

What is the expected behavior?
A TIMESTAMP data type should not change to date unless explicitly cast to one.
Also, the timezone information should always be included in the data.

What is your host/environment?

• OS: Ubuntu 24.0.1 LTS
• Version 2.1s
• Plugins: SQL

Do you have any screenshots?
If applicable, add screenshots to help explain your problem.

Image 1.

Image 2

Do you have any additional context?
Add any other context about the problem.

Issue was found using the JDBC driver (1.4.0.1). Issue was replicated using the Devtools on the dashboard.

[sablanscott]

Version is 2.15 not 2.1s

[dblock]

[Catch All Triage - 1, 2, 3, 4]

[Swiddis]

Duplicate of #1545, and also reported in #3204. #3160 holds the fix, it's set to be shipped in 2.19.