Mysql: add support for type conversion for tinyint (boolean) in mysql to bool type in golang

[mglaske]

Issue description

currently, when there is a tinyint, or bit (boolean) field in mysql, you cannot store this type into a golang 'bool' type without error. Currently these are pulled in as string, or possibly int64.

Example code

var isEnabled bool
conn.Query('SELECT isEnabled FROM table')
rows.Scan(&isEnabled)

results in:
panic: sql: Scan error on column index 9: sql/driver: couldn't convert "\x01" into type bool

Error log

Configuration

*Driver version (or git SHA): 66312f7fe2678aa0f5ec770f96702f4c4ec5aa8e

_Go version:_ go version go1.2.1 linux/amd64

_Server version:_ MySQL 5.0.45

_Server OS:_ Ubuntu 14

[choieunsuk]

var strEnabled string
conn.Query('SELECT isEnabled FROM table')
rows.Scan(&isEnabled)

var isEnabled bool
isEnabled = false
if len(strEnabled ) == 1 && strEnabled [0] == 1 {
isEnabled = true
}

[FongWan]

How about this way:

var isEnabled bool
sql.QueryRow("SELECT (isEnabled = b'1') FROM table").Scan(&isEnabled)

[arnehormann]

This is a known problem which is impossible to fix in the driver (ugh 😞 ).

database/sql doesn't tell what the destination type to scan into is and MySQL doesn't tell us the value is a boolean as it doesn't exist on the protocol level (https://dev.mysql.com/doc/internals/en/binary-protocol-value.html).

You could store e.g. 2 in that tinyint. And that's impossible to read if we try to cast all tinyints to bool - even if it's only for those with length 1. "Booleanizing" the value yourself is tedious, but it's the best way we have, here. I don't know from memory how BIT behaves - I bet it's sent as a number, too - but it's plausible you want it as a number and it would be very surprising if a field with a length of 1 had a different return type than one with length 2.

That's why we are stuck with the status quo...

[akamensky]

@arnehormann

While this is already closed I still think it would be nice to allow scan into boolean directly... Why not to use simple logic such as for tinyint field - if 0 means false, then anything non-zero means true, for any NULL field (varchar for example) null is false, non-null is then by logic true? Otherwise I have to deal with situations like:

type User struct {
    Username string
    Email string
    Acitve bool
}

u := new(User)
var active int

stmt.QeryRow(email).Scan(&u.Username, &u.Email, &active)

if active == 0 {
    u.Active = false
} else {
    u.Active = true
}

Which in case of more than one such field can lead to pretty unreadable code...

[arnehormann]

@akamensky but that's impossible - or we would have done it a long time ago.
MySQL doesn't tell us we are dealing with a boolean, we have to infer it from unsigned tinyint(1) - which could also be [0..9]. And database/sql doesn't tell us which type it wants. We have no way to know if you're scanning into an int or a boolean, we have to set the type provided by MySQL in a slice (see https://golang.org/pkg/database/sql/driver/#Stmt Query and Exec).
So there are only two options for the driver:

1. for unsigned tinyint, always assume it's a boolean. Will break if someone scans into an int.
2. for unsigned tinyint, always assume it's an int. Very inconvenient if it's a boolean, but it works in every scenario - for booleans with an ugly workaround.

We picked 2. If you still think there's a way to solve this based on the MySQL protocol and on database/sql/driver, please tell us how to do it.

[akamensky]

@arnehormann I think this can be done based on type of interface we received, no? I presume you do type checking (without it many bad things may happen)? didn't look inside the code, but this easily can be done with reflect.TypeOf() afaik, though of course that will slow down the performance.

Such as that if passed interface is *bool type then the value of field can be interpreted as boolean.

[akamensky]

Also since reflection can slow down performance significantly it can be done with simple type assertions such as this example, type assertions are much faster than reflection according to this

[methane]

@akamensky Read this function.
https://github.com/golang/go/blob/master/src/database/sql/convert.go#L125

If you think it's possible after reading this function, please try it by yourself.

[akamensky]

@methane So the type conversion/assignment is implemented in go itself, that's a ridiculous decision IMO... Still possible, but then patch should be sent not here, but to golang itself...

[RayfenWindspear]

I made this valuer/scanner type that I use. Because it just aliases a bool, you can use it normally in code.

https://github.com/jmoiron/sqlx/commit/581097f1be765de629ca6a6e0110d64e98b13267

[namax]

it's also possible just compare with "\x00"
for example

type SysParams struct {
    IsActive            sql.NullString   `db:"is_active"`
}

err := db.QueryRow(`SELECT is_active FROM sys_params`).Scan(&sysParams.IsActive)
result := sysParams.IsActive.String == "\x01" 

+ error checking

[gus-canya]

I tried with:

type Transaction struct {
      IsSent           sql.NullBool   `gorm:"column:is_sent" json:"isSent,omitempty"`
}

CREATE TABLE `transaction` (
  `is_sent` tinyint(1) unsigned DEFAULT '0',
...

And it worked, getting JSON response like:

"isSent": {
        "Bool": true,
        "Valid": true
    },