[BUG] SQL confusing type conversion for TIMESTAMP when using join

[dhvcc]

Describe the bug

So given 2 indexes, with a matching field (to be used in a JOIN) and a timestamp field. When querying without a join, a WHERE clause like WHERE t.timestamp > timestamp('2001-05-07 00:00:00') will work and WHERE t.timestamp > 0 won't work since it's a TIMESTAMP field, that's what opensearch tell you

{\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"> function expected {[BYTE,BYTE],[SHORT,SHORT],[INTEGER,INTEGER],[LONG,LONG],[FLOAT,FLOAT],[DOUBLE,DOUBLE],[STRING,STRING],[BOOLEAN,BOOLEAN],[DATE,DATE],[TIME,TIME],[DATETIME,DATETIME],[TIMESTAMP,TIMESTAMP],[INTERVAL,INTERVAL],[STRUCT,STRUCT],[ARRAY,ARRAY]}, but get [TIMESTAMP,INTEGER]\",\n    \"type\": \"ExpressionEvaluationException\"\n  },\n  \"status\": 400\n}

But, if you're using a JOIN, the behavior reverses, while the error becomes even more confusing than just "can't match type"
WHERE t.timestamp > timestamp('2001-05-07 00:00:00') now will not work and WHERE t.timestamp > 0 will with error

{\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"Cannot invoke \\\"String.startsWith(String)\\\" because \\\"fieldName\\\" is null\",\n    \"type\": \"NullPointerException\"\n  },\n  \"status\": 400\n}

Related component

Search

To Reproduce

1. Go to 'Dashboard -> Management -> Dev Tools'
2. Paste the example below
3. Run those queries one by one and observe the behavior

PUT /a
{
  "mappings": {
    "properties": {
      "some_id": {
        "type": "keyword"
      },
      "timestamp": {
        "type": "date"
      }
    }
  }
}

PUT /b
{
  "mappings": {
    "properties": {
      "some_id": {
        "type": "keyword"
      },
      "timestamp": {
        "type": "date"
      }
    }
  }
}

POST /a/_doc
{
  "some_id": "a1",
  "timestamp": "2024-12-15T10:00:00Z"
}

POST /b/_doc
{
  "some_id": "a1",
  "timestamp": "2024-12-15T11:00:00Z"
}

-- NO JOIN - TIMESTAMP works
POST /_plugins/_sql 
{
"query": "SELECT b.timestamp from b                                                WHERE b.timestamp > timestamp('2001-05-07 00:00:00');"
}
-- NO JOIN - INTEGER doesn't work
-- {\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"> function expected {[BYTE,BYTE],[SHORT,SHORT],[INTEGER,INTEGER],[LONG,LONG],[FLOAT,FLOAT],[DOUBLE,DOUBLE],[STRING,STRING],[BOOLEAN,BOOLEAN],[DATE,DATE],[TIME,TIME],[DATETIME,DATETIME],[TIMESTAMP,TIMESTAMP],[INTERVAL,INTERVAL],[STRUCT,STRUCT],[ARRAY,ARRAY]}, but get [TIMESTAMP,INTEGER]\",\n    \"type\": \"ExpressionEvaluationException\"\n  },\n  \"status\": 400\n}
POST /_plugins/_sql 
{
"query": "SELECT b.timestamp from b                                                WHERE b.timestamp > 0;"
}

-- JOIN - INTEGER works
POST /_plugins/_sql 
{
"query": "SELECT b.timestamp from b JOIN a ON a.some_id = b.some_id WHERE b.timestamp > 0;"
}
-- JOIN - TIMESTAMP doesn't work
-- {\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"Cannot invoke \\\"String.startsWith(String)\\\" because \\\"fieldName\\\" is null\",\n    \"type\": \"NullPointerException\"\n  },\n  \"status\": 400\n}
POST /_plugins/_sql 
{
"query": "SELECT b.timestamp from b JOIN a ON a.some_id = b.some_id WHERE b.timestamp > timestamp('2001-05-07 00:00:00');"
}

Expected behavior

Expected to see timestamp filtering work when using a join. Also, since it's a timestamp, i'd also expect INTEGER to work both ways

Additional Details

Plugins
AFAIK, sql, it's a default OpenSearch AWS installation

Screenshots
I've put the whole repro above with error results, so don't really need it

Host/Environment (please complete the following information):

• Environment: AWS
• Version: OpenSearch 2.17 (latest)
• Service software version: OpenSearch_2_17_R20241112-P3 (latest)

[dhvcc]

Re-raised the same bug as here (opensearch-project/OpenSearch#16847), since it seems to be a more suitable place

[Swiddis]

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

[Swiddis]

Turns out the other two issues aren't quite the same, the issue here is more to do with a difference in validation/supported function logic between the V1 and V2 engines. Out of scope for the current system, but this should be solved by the Calcite initiative.

@ Calcite team: I left a TODO for this test 4a83cd7 in #3613, remove the Ignore to resolve.