Automatic binary casting

Help
1

Hey — I have a question about automatic binary casting.

I was just doing some random tests and stumbled on the following issue. This query prints just fine from MemSQL Studio, but using the MariaDB JDBC driver it displays as all undifferentiated byte arrays:

(select v from eavt_bytes limit 10) -- table whose values are byte arrays / `longblob`
union
(select v from eavt_long limit 10); -- table whose values are `bigint`

(For reference’s sake, the number 5 displays via JDBC as the byte array [53], and the number 10 displays as [49, 48].)

One workaround is this — it prints correctly via JDBC — but it’s not ideal:

(select v, null from eavt_bytes limit 10)
union
(select null, v from eavt_long limit 10);

Another better option is this (having a column marking the datatype) but has the disadvantage of still printing wrong via JDBC:

(select 0, v from eavt_bytes limit 10)
union
(select 1, v from eavt_long limit 10);

I saw at SingleStoreDB Cloud · SingleStore Documentation that There is usually no visible effect on the printed value; what changes is the rules for comparison and sorting. What logic does the MemSQL console have that displays these bigint values correctly where JDBC does not? I’m sure I’m missing something here. I could probably reverse engineer it but would be nice to not have to take the time :slightly_smiling_face:

2

All columns in the union result (like any relation) have a specific datatype - it’s not possible for different rows to treat the field with different datatypes. So it’s best to avoid UNIONing together different datatypes unless it makes sense to convert them into the same datatype. The union column’s datatype is generally a datatype that can hold all the input datatypes - in your case, it’s longblob, so the bigint values have to be converted into longblob. You can explicitly control the conversions by adding typecasts (like the :> operator SingleStoreDB Cloud · SingleStore Documentation) in the select statements.

MemSQL Studio and many other clients (like the MySQL CLI client) try to display longblob as a string, not as an array of raw bytes. The conversion from bigint to longblob prints it as a string, e.g. 123 becomes ‘123’, so it ends up displaying as desired.

3

Got it — thanks @jack! Yeah when I map over the values and convert the byte arrays to strings, they print as expected (stringified numbers), which explains both JDBC and console behavior.