Binary fields answer their base64 text, not their bytes #421

An Elasticsearch binary field stores bytes, and every Elasticsearch API carries them as a base64 string (_source included). Core, the Arrow layer (softclient4es-arrow) and the JDBC driver (softclient4es-jdbc) answer that base64 text for the field, as a string or as the UTF-8 bytes of the string, never the bytes the field stores. So a VARBINARY column answers other bytes than a SQL engine holding the same data, and the bytes written through JDBC's setBytes are not the bytes getBytes reads back.

Measured

Core, through the gateway, on Elasticsearch 8.18.3. Table t (id KEYWORD, s KEYWORD, bin VARBINARY), three documents:

• bin = YWI= (the bytes 0x6162, "ab"), s = a;
• bin = eA== (the byte 0x78, "x"), s = b;
• no bin, s = c.

DuckDB 1.5.5 was given the same data as a BLOB holding the stored bytes.

Given the bytes of the base64 text instead (what the Arrow layer hands it today, below), DuckDB answers 0x5957493d, 0x65413d3d: the base64 text, as core does.

What each layer hands out

Read at the current main of each repository.

• Core (SoftClient4ES main, 7fe1d66):
  • core/src/main/scala/app/softnetwork/elastic/client/ElasticConversion.scala:1443-1446 (jsonNodeToAny) answers a text node as its text, whatever the column's type: a binary field is its base64 String.
  • Core's own contract for a VARBINARY cell is byte[]:
    • sql/src/main/scala/app/softnetwork/elastic/sql/type/ValueCoercion.scala:55 infers VARBINARY from Array[Byte];
    • core/src/main/scala/app/softnetwork/elastic/client/metadata/JdbcTypeCatalog.scala:65 reports Types.VARBINARY;
    • core/src/main/scala/app/softnetwork/elastic/client/metadata/JavaValueConversion.scala:66 documents the byte[] cell.
  • With fix(sql): set operations take DuckDB's types and answer one class per column; aggregates without GROUP BY answer one row #420, where a set operation types a VARBINARY beside text as VARBINARY, the base64 String becomes its UTF-8 bytes (a column, an object's field, a list's element), to agree with the two layers below.
• Arrow (softclient4es-arrow main, 264eac3), arrow-core/src/main/scala/app/softnetwork/elastic/arrow/ArrowTypeMapping.scala:
  • :234-235 maps VARBINARY to Arrow Binary;
  • :288-293 writes a value that is not a byte[] as other.toString.getBytes(StandardCharsets.UTF_8): the bytes of the base64 text, which DuckDB then holds as its BLOB;
  • :716-717 reads them back unchanged.
• JDBC (softclient4es-jdbc main, 39c1434), under driver/src/main/scala/app/softnetwork/elastic/jdbc/:
  • getBytes answers a String cell as s.getBytes("UTF-8"), in ElasticResultSet.scala:250-259 and in ElasticStreamingResultSet.scala:233-242;
  • setBytes (ElasticPreparedStatement.scala:437-438) binds a byte[], rendered as its base64 literal (:202).
  • So setBytes with 0x6162 stores YWI=, and getBytes reads back 0x5957493d.

Expected

A binary field answers the bytes it stores, as SQL engines do, in core, over Arrow and through JDBC:

• SELECT bin FROM t answers byte[] 0x6162, 0x78, NULL, inferred and reported VARBINARY;
• the same for bin UNION ALL bin and bin UNION ALL NULL;
• in bin UNION ALL s the binary rows are the stored bytes, and a text row is cast to bytes as DuckDB casts a VARCHAR to a BLOB.

Fix direction

Decode the base64 into the stored bytes in one place, and move the three layers together:

1. Core: answer a VARBINARY cell (the schema says which columns are binary) as byte[] decoded from its base64, in a single statement and on every route a set operation takes. The UNION ALL conversion then uses that decoding instead of the UTF-8 bytes of the text.
2. Arrow: VarBinaryVector then receives byte[] from core, which its byte[] arm already writes unchanged.
3. JDBC: getBytes then receives byte[]. setBytes keeps writing base64, so the value written is the value read back.

Changing one layer alone only moves the mismatch between layers, so the three should ship in the same release.