[BUG] timestamp data type handling is inconsistent #3159
Description
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"}
-
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) -
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.