Comment on database/sql type limitations

[Boris]

I have been using some uint64 types in my database (MySQL supports BIGINT UNSIGNED) and having trouble using database/sql with it:

_, err = db.Exec("INSERT INTO ... ?", myUint64Value)

sql: converting argument #2's type: uint64 values with high bit set are not supported

I looked at the code quickly and it seems that database/sql's idea of a Value is something that might be NULL. The high bit is used to determine whether the value is NULL or not. Is that correct?

If so, this is not a great design IMO. I'm not sure what the right design is, but this is a limitation I will have to think hard to design around... I think the best way so far is for me to fmt.Sprintf("%d", myUint64Value). But that is not what I want to litter through my code :-( The other option I can see is to use an array of bytes instead of a uint64, but that's pretty clumsy to convert back and forth too.

It might be better to use boxing and unboxing to represent a Value, rather than saying "we reserve one bit of your data for our own uses, sorry."

Thoughts? Suggestions?

- Boris

[Andy Balholm]

On Saturday, December 22, 2012 7:00:02 AM UTC-8, Boris wrote:

I looked at the code quickly and it seems that database/sql's idea of a Value is something that might be NULL. The high bit is used to determine whether the value is NULL or not. Is that correct?

No.

The problem is that the database/sql package converts all integer types to int64. Since this is a signed type, values with the high bit set are negative. Rather than converting your unsigned number to a negative one, it gives an error. 

Are you using your uint64's to store actual numbers, or to store bit patterns?

If they are actual numbers, converting them to strings is probably the best way to go. (Maybe the sql package could even be changed to do that for you.)

If they are bit patterns, maybe you could just store them as signed numbers. 

[Boris Solovyov]

I am storing actual numbers. (Sometimes I am also using them to store hashes, but that is not a problem for me to do in other ways than uint64). Maybe the ultimate fix is for a future version of database/sql to support both signed and unsigned integers, unless there is a reason this can't be done. I know many DBMS's don't support unsigned integers, but it is a feature I love about MySQL and I know a lot of other people who use it too.

[Andy Balholm]

On Dec 22, 2012, at 9:01 AM, Boris Solovyov wrote:
> I am storing actual numbers. (Sometimes I am also using them to store hashes, but that is not a problem for me to do in other ways than uint64). Maybe the ultimate fix is for a future version of database/sql to support both signed and unsigned integers, unless there is a reason this can't be done. I know many DBMS's don't support unsigned integers, but it is a feature I love about MySQL and I know a lot of other people who use it too.

Brad,

Would it be appropriate for the DefaultValueConverter to convert these large uint64's to string (or rather to []byte) instead of giving an error? I think it would work fine with most databases, but I don't know if it would work with all.

Andy

[Brad Fitzpatrick]

Perhaps? If it doesn't break or surprise anybody else. I haven't thought about it.

I'd suggest filing a bug with background & examples and we can figure it out there.

[Andy Balholm]

On Dec 22, 2012, at 9:17 AM, Brad Fitzpatrick wrote:

> Perhaps? If it doesn't break or surprise anybody else. I haven't thought about it.
>
> I'd suggest filing a bug with background & examples and we can figure it out there.

Boris, do you want to file the bug, since you're the one having the problem?

[Boris Solovyov]

OK, I will try to make a minimal testcase and file a bug.

[MC.Spring]

Has this issue fixed yet?

I run into this issue today:

$go version

go version go1.4.2 darwin/amd64

$uname -a

Darwin Springs-MacBook-Pro.local 14.3.0 Darwin Kernel Version 14.3.0: Mon Mar 16 23:12:31 PDT 2015; root:xnu-2782.20.48~4/RELEASE_X86_64 x86_64

[Dave Cheney]

To my knowledge no but was filed so the issue remains open. Please consider creating a stand alone example that shows the problem and then raise a bug.