[BUG] "Unsupported conversion for Relational Data type: BINARY" error when accessing non-existent nested fields in struct types (Usability Issue)

[alexey-temnikov]

What is the bug?

When querying a non-existent nested field within a struct/object type, users receive a cryptic error message:

"Unsupported conversion for Relational Data type: BINARY"

This error message does not indicate the actual problem: that the referenced field does not exist in the index mapping.

How can one reproduce the bug?

Steps to reproduce:

1. Create an index with a nested structure:

curl -X PUT "localhost:9200/test-binary-bug" -H 'Content-Type: application/json' -d'
{
  "mappings": {
    "properties": {
      "timestamp": { "type": "date" },
      "service": {
        "properties": {
          "name": { "type": "keyword" },
          "version": { "type": "keyword" }
        }
      }
    }
  }
}'

2. Insert sample data:

curl -X POST "localhost:9200/test-binary-bug/_doc" -H 'Content-Type: application/json' -d'
{
  "timestamp": "2024-01-15T10:00:00Z",
  "service": {
    "name": "api-gateway",
    "version": "1.0.0"
  }
}'

3. Query a non-existent nested field (typo in field name):

curl -X POST "localhost:9200/_plugins/_sql" -H 'Content-Type: application/json' -d'
{
  "query": "SELECT service.port FROM \"test-binary-bug\" LIMIT 1"
}'

4. Observe the confusing error:

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Unsupported conversion for Relational Data type: BINARY",
    "type": "IllegalArgumentException"
  },
  "status": 400
}

Expected behaviour: A clear error message indicating the field does not exist:

"Unable to determine type for field access. This typically occurs when referencing a non-existent nested field within a struct/object type. Please verify that the field path 'service.port' exists in your index mapping."

Tentative Root Cause Analysis

This is a preliminary analysis and requires further investigation:

The issue occurs due to how STRUCT types are represented in the Calcite query planner:

1. STRUCT to MAP Conversion

In OpenSearchTypeFactory.java lines 179-184:

case STRUCT:
  // TODO: should use RelRecordType instead of MapSqlType here
  // https://github.com/opensearch-project/sql/issues/3459
  final RelDataType relKey = TYPE_FACTORY.createSqlType(SqlTypeName.VARCHAR);
  return TYPE_FACTORY.createMapType(
      relKey, TYPE_FACTORY.createSqlType(SqlTypeName.BINARY), nullable);

STRUCT types are converted to MAP<VARCHAR, BINARY>. This is a known limitation with a TODO to use RelRecordType instead.

2. Field Access Returns BINARY Type

When accessing a nested field like service.port, the INTERNAL_ITEM operator is used (similar to map access). Since the map's value type is BINARY, accessing any key—whether it exists or not—returns type BINARY.

3. Type Conversion Failure

In OpenSearchTypeFactory.java lines 267-269:

ExprType exprType = convertSqlTypeNameToExprType(type.getSqlTypeName());
if (exprType == UNKNOWN) {
  throw new IllegalArgumentException(
      "Unsupported conversion for Relational Data type: " + type.getSqlTypeName());

The convertSqlTypeNameToExprType() method doesn't handle SqlTypeName.BINARY (see lines 223-249), so it returns UNKNOWN, triggering the cryptic error message.

Impact

This affects all users working with:

• OCSF (Open Cybersecurity Schema Framework) schemas
• OpenTelemetry data
• Simple Schema for Observability (SS4O)
• Any custom schemas with nested object structures

When users make typos in field names or reference non-existent nested fields, they receive an unhelpful error message that doesn't guide them toward the actual problem.

INTERIM Proposed Solution

This is a preliminary analysis and requires further investigation:

Add a specific error message for BINARY type in the convertRelDataTypeToExprType() method:

public static ExprType convertRelDataTypeToExprType(RelDataType type) {
  if (isUserDefinedType(type)) {
    AbstractExprRelDataType<?> udt = (AbstractExprRelDataType<?>) type;
    return udt.getExprType();
  }
  ExprType exprType = convertSqlTypeNameToExprType(type.getSqlTypeName());
  if (exprType == UNKNOWN) {
    // Special case for BINARY type which typically indicates accessing non-existent nested field
    if (type.getSqlTypeName() == SqlTypeName.BINARY
        || type.getSqlTypeName() == SqlTypeName.VARBINARY) {
      throw new IllegalArgumentException(
          "Unable to determine type for field access. This typically occurs when referencing "
              + "a non-existent nested field within a struct/object type. Please verify that "
              + "the field path exists in your index mapping.");
    }
    throw new IllegalArgumentException(
        "Unsupported conversion for Relational Data type: " + type.getSqlTypeName());
  }
  return exprType;
}

Additional Context

This is a usability improvement focused on error messaging. The proper architectural fix (using RelRecordType instead of MapSqlType for STRUCT) is tracked in #3459, but this interim fix provides immediate value to users by making errors understandable.

[yuancu]

It seems the code is out of date: The current code gives the type of ANY instead of BINARY:

case STRUCT:
  final RelDataType relKey = TYPE_FACTORY.createSqlType(SqlTypeName.VARCHAR);
  // TODO: should we provide more precise type here?
  return TYPE_FACTORY.createMapType(
      relKey, TYPE_FACTORY.createSqlType(SqlTypeName.ANY), nullable);

• The result for SQL:

  POST /_plugins/_sql
  {
    "query": "SELECT service.port FROM test-binary-bug LIMIT 1"
  }
  

  "{\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"can't resolve Symbol(namespace=FIELD_NAME, name=service.port) in type env\",\n    \"type\": \"SemanticCheckException\"\n  },\n  \"status\": 400\n}"
  

• The result for PPL:

  POST /_plugins/_ppl
  {
    "query": "source=test-binary-bug | fields service.port"
  }
  

  {
    "schema": [
      {
        "name": "service.port",
        "type": "undefined"
      }
    ],
    "datarows": [
      [
        null
      ]
    ],
    "total": 1,
    "size": 1
  }
  

Besides, running SQL queries will not involve calcite during execution. Can you double check whether the problem still exists?

[LantaoJin]

Cannot reproduce.