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.
sablanscott
commented
2 hours ago
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?
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.