values of UInt8 data types get converted to 125

[girng]

Ran into this little quirk today. i checked with gitter, and pretty sure it's a bug, not entirely sure though

query:

        items2 = db.query_all "select i_hp from rpg_user_items where rpg_character_id = ? and user_id = ? and in_stash = 0", client.selected_characterid, client.user_id, as: {UInt8}
          puts items2[0]

storage type in mysql, set as tinyint, 255, unsigned

if you don't set the length to 255, but keep it unsigned, it will give you a read error:

MySql::ResultSet#read returned a Int8. A UInt8 was expected.

if you set the length to 255, that error is gone. however, now your value gets converted to 125

thanks for reading! special thanks to @dscottboggs_gitlab in gitter for the help

[dscottboggs]

It doesn't appear that unsigned is a standard SQL feature. It is not supported by my version of MySQL (MariaDB 10.1.34). For example, the query

create table tbl (
  col tinyint,
  col2 unsigned tinyint
);

results in an error like:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'unsigned tinyint)' at line 1

Could you verify that the above query works in your version of MySQL? If so, which version of MySQL are you running?

[girng]

nah it doesn't, wtf
edit: version 5.6.24

[girng]

@dscottboggs
try this;

CREATE TABLE `example_table` (
  `example_col` tinyint(3) unsigned NOT NULL
);

this works for me! in adminer.php, i set it to 255

[dscottboggs]

Oh, I see, my bad. SQL is not my best language... thanks for your help

[dscottboggs]

Ok so I played around with implementing this a bit, and I don't see any way to store an unsigned integer via the interface provided to Crystal. See the documentation for basic types and for response value types in the binary interface. Neither offer any means of differentiating between Int and UInt.

I recommend we add some error checking which simply says "use the next size bigger signed values, unsigned values don't work" but otherwise leave things as they are. If that's ok I'll submit a PR as soon as I get the chance.

[ysbaddaden]

There must be a flag in the protocol that reports the integer value as unsigned, and it's probably not supported/overlooked by the current implementation.

One should dig the MySQL protocol documentation, implement support and declare the UIntX encoder/decoder types.

[girng]

thanks for your time, @ysbaddaden. i did some digging, and found:
https://dev.mysql.com/doc/dev/mysql-server/8.0.4/page_protocol_basic_dt_integers.html

would this help?
edit: i switched to just a signed tinyint and worked around this issue so no rush at all. ya'll prob got better things to do. but i guess some other dev might run into this.

[bcardiff]

The fixed and and length encoded integers are implemented in read_packet.cr. I've just noticed that read_fixed_int is returning Int, but read_lenenc_int is returning UInt. Maybe the former is should be UInt also.

Nevertheless the mapping from the types of the database to the types in crystal is in types.cr and there, the read_fixed_int/read_lenenc_int are used as helper for returning dates. For integer numbers the bytes are read from the stream in LittleEndian order as described in https://dev.mysql.com/doc/internals/en/binary-protocol-value.html .

Maybe the sign information comes in the column definition and that should be used somewhere. Ref: read_column_definitions/ColumnSpec.

When reading values there needs to be two implementation one for binary protocol (prepared statements) and one for text protocol (non prepared statements).

The lack of UInt support is probably a consequence of trying to favor Int for general purpose and leaving UInt for protocol things. That is why UInt8 does not appear in DB::TYPES.

A PR is welcome as long as a sample_value entry is added in db_spec.cr ;-)

The great reference for protocol implementation can be found in https://dev.mysql.com/doc/internals/en/