On "select 'other' as \"x [b1b1]\"", the "other" is not answered as base64

[alanjds]

After working on a types extension branch on pyrqlite, I noticed that native types are answered as native string or number, but non-native and blob ones as base64. Ok.

But when I do provide a literal and a column name[type] with non-native type then I got 2 issues, as shown on this pyrqlite ticket:

1. the "other" is not answered as base64, breaking behaviour homogeneity.
2. the "other" value cannot contain non-ascii values, returning error if so.

Is this a misbehaving of the server or of the client? (in other words, where should patches land?)

[otoolep]

Could be on the server @alanjds -- some of this conversion logic needs more work, to ensure it's doing the right thing.

[otoolep]

Basically, it might have something to do with this area: #244, but I am not sure yet. I'm still researching what is the right thing to do here.

[alanjds]

Nice. For me the most important is behaviour consistency. Right now I cannot have a way to detect if I should or should not decode the answer based on the type.

[zmedico]

In order to handle binary data, there will have to be a way for the client to specify typed arguments for the SQLiteConn.Exec method. Since json can only contain text data, the client will have to encode binary data as base64, and rqlited will have to decode the base64 to []byte before it is passed as args to the SQLiteConn.Exec method.

When returning binary data from select statement, rqlited will have to encode the binary data as base64, and the client will have to use the types of the columns as a hint that the values need to be decoded from base64 to binary data.

[otoolep]

@zmedico -- I'm not fully following this. Can you give some examples of SQL statements that are failing on rqlite? Is this something that can be demonstrated purely with SQL?

[otoolep]

In other words, is there is a sequence of SQL statements one could execute directly at the sqlite3 prompt that fail to produce the right results when sent to rqlite?

[zmedico]

Actually, it might be possible to use BLOB literals containing hexadecimal data to insert binary data, which is how it can be done at the sqlite3 prompt. I'll test that, and see how rqlite returns the binary data in query results.

[zmedico]

BLOB literals containing hexadecimal data do work with rqlite, and query results return the binary data with base64 encoding.

[alanjds]

You mean... query = "select 'other' as \"" + "x [b1b1]".encode("hex") + '\"' should work?

[zmedico]

Unfortunately, I doubt that BLOB literals are helpful for this select 'other' as "x [bar]" thing. This is the result from rqlite if we encode 'other' as hex:

127.0.0.1:4001>  create table test(x foo);
0 row affected (0.000000 sec)
127.0.0.1:4001>  insert into test(x) values ('xxx');
1 row affected (0.000000 sec)
127.0.0.1:4001>  select x as "x [bar]" from test;
+---------+
| x [bar] |
+---------+
| eHh4    |
+---------+
127.0.0.1:4001>  select X'6f74686572' as "x [bar]" from test;
+---------+
| x [bar] |
+---------+
| other   |
+---------+