Cannot read query_one result as Bool

[Blacksmoke16]

require "mysql"

DB.open "mysql://user:pass@localhost:3306/test" do |db|
  pp db.query_one "SELECT EXISTS(SELECT 1 FROM articles WHERE id = 1)", as: Bool
end

Unhandled exception: cast from Int64 to Int8 failed, at ~/dev/git/granite/lib/mysql/src/mysql/result_set.cr:92:28:92 (TypeCastError)
  from lib/mysql/src/mysql/result_set.cr:0:28 in 'read'
  from lib/db/src/db/query_methods.cr:116:9 in 'query_one:as'
  from src/granite.cr:14:6 in '__crystal_main'
  from /snap/crystal/18/share/crystal/src/crystal/main.cr:97:5 in 'main_user_code'
  from /snap/crystal/18/share/crystal/src/crystal/main.cr:86:7 in 'main'
  from /snap/crystal/18/share/crystal/src/crystal/main.cr:106:3 in 'main'
  from __libc_start_main
  from _start
  from ???

Where the table looks like:

id	articlebody
1	The Article Body

[bcardiff]

The query_one and others performs a cast without conversions. If the type is not the expected one this kind of errors appear.

You can check rs.read.class to know what type the EXIST is generating and use that type in query_one. From the error it should be an Int64.

I have some thoughts to include some conversions to handle type miss-matches more gracefully. But they are not settled yet.

[Blacksmoke16]

That makes sense as to why its happening, but it is slightly annoying since it works fine for the PG and Sqlite shards. IMO each DB shard should act in a similar fashion.

Was hoping to use #scalar here but it doesn't allow arguments to be supplied, without just interloping them in the string, like the other query methods do.

[bcardiff]

I think I got it. In result_set and text_result_set there is a conversion to mimic boolean types since mysql represent as tinyint. That code asumes Int8 (tinyint) is will be present. That seems to not hold on EXISTS result.

Making other ints available in https://github.com/crystal-lang/crystal-mysql/search?utf8=%E2%9C%93&q=from_mysql&type= should be enough.

Do you want to check and send a PR?

[Blacksmoke16]

Done @bcardiff.