query rqlite big number, the value is in float64 format

[Bruce-li-92]

converting driver.Value type float64 ("2.00000231e+08") to a int64

[axllent]

I've just hit this issue too while trying to return an db INTEGER value containing a time.UnixMilli() timestamps with values like 1749667779000.

Scan error on column index 0, name "Created": converting driver.Value type float64 ("1.749634828e+12") to a uint64: invalid syntax

Any suggestions @otoolep ? It works perfectly fine with "regular" SQLite - just fails with rqlite (using the gorqlite driver).

[otoolep]

AFAIK it's because the gorqlite driver doesn't ever call UseNumber. See https://pkg.go.dev/encoding/json#Decoder.UseNumber

See #51

I should be able to add it, though you are as a driver user may have to explicitly choose to put the driver into this mode.

[axllent]

Do you mean that currently any integer db value defaults to a float64 value on the client end after unmarshalling, but that you could probably add a configuration to change this behaviour to be int64 instead? Is it really a per JSON unmarshal setting all integer fields, and not a per value setting as one would expect based on the defined SQL query variables?

I have a workaround, that is to define these values as float64 in my SQL query and then convert them to what I actually need, in my case uint64, but this behaviour really caught me out. Fortunately I don't have many sqlite fields with integers. But I have to add comments everywhere in my code to remind myself why I'm defining these values as float64 when they are clearly not 😂

I'm not actually sure what the right approach is. Ideally that's inherited from the SQL query variables per field, but I don't think that would be possible. If this became a global setting, then that means floats are then not possible at all (not that I need them, but speaking now broader).

[otoolep]

The issue is due to the way Go handles marshaling of numbers into any a.ka. {}interface.

Because the goqlite library doesn't assume anything about the types of the values it's getting back from rqlite, it supplies variables of types any. When confronted with a number in the response from rqlite, any doesn't tell it what numeric type to use, so Go picks float64. If you knew ahead of time exactly what type it would be, then you would just supply Go with, say, a int64.

What UseNumber does is tell Go to defer that conversion when working with any. It just keeps the bytes as is. Then you, as the client library user, must assert its type later. Which is probably OK since you know the schema you're expecting.

So today you get float64 without doing any work, but float64 is not what you want. Of course, you could cast the float back to an int, but that can be lossy if the number is very large. But if we add UseNumber you get complete control, but you'll have slightly more work to do.

[otoolep]

It would be a call you would make on the client to enable this mode. I wouldn't switch the library to it by default as it would probably break people.

[otoolep]

You would do something this with the values you get back from gorqlite:

n := val.(json.Number)   // type-assert it because UseNumber() means all numbers are stored as json.Number, not float64
i, _ := n.Int64()        // integer
f, _ := n.Float64()      // floating point
s   := n.String()        // original literal

[otoolep]

Do you mean that currently any integer db value defaults to a float64 value on the client end after unmarshalling, but that you could probably add a configuration to change this behaviour to be int64 instead?

Hopefully the text above answers the question. I would add a config so no conversion takes place at all for numbers, and you would be handed a json.Number which you convert to what type you want (or expect since you know your schema).

[axllent]

Thanks for those clear explanations @otoolep, it explains a lot.

I'm not sure however that your proposed change would help in Mailpit's case, given the interchangeability between native SQLite and rqlite as the driver. It looks to me like (in my case) I'm better off just casting the variable as a float64 and converting to XYZ, rather than requiring these upstream changes from you and adding in even more complexity as to how I arrive at the type I expect. This explains why things are the way they are, and more importantly, that it's not a bug but rather a trait introduced by the JSON marshal / unmarshal process.

If you knew ahead of time exactly what type it would be, then you would just supply Go with, say, a int64.

With the native SQLite connection the return value type is correctly casted via the variable mapping to SQL query, but I guess this must work differently within gorqlite? I use a module called leporo/sqlf to make my life easier. It is just a wrapper for SQL which does the heavy lifting, however if I define the mapped value as an int64 then I get an int64 etc. Anyway, I'm not trying to create work for you here, I was just trying to get clarification (which you have provided) as to why I was getting this error, so thanks.

[otoolep]

That's fine @axllent -- makes sense. That said I think this flags something about this client library (and https://github.com/rqlite/rqlite-go-http which has the same issue) I should fix. At least make it clear and allow users to choose what behaviour they want.

For power users one almost wants to support accepting a schema ahead of time, but that starts to miss the point of using something easy-to-use like JSON.

[otoolep]

With the native SQLite connection the return value type is correctly casted via the variable mapping to SQL query, but I guess this must work differently within gorqlite?

I might be able to replicate this behavior then. Is this with mattn's driver? If he is doing it, I bet I can do it.

[axllent]

It does use module that under the hood, however I believe it indirectly uses the stdlib sql QueryContext() & QueryRowContext() functionality, so nothing special or unique. I assumed that gorqlite would have done the same, but maybe that's not the case. In an ideal world, if I was mapping an int32 value to a query values, then I would expect it to be set as either an int32 value or get an error (just like above). If there's a chance the value could be be null, then I would define the variable as a sql.NullInt32 which allows for that. I'm totally out of my comfort zone here as it's pretty low level, but maybe this helps you?

[otoolep]

OK, looked into it, I see how it works. This is something that needs improvement in gorqlite and my newer client library. I believe rqlite has all the required info, and communicate it across the wire in JSON to clients -- which will allow rqlite client libraries to work just as well as libraries talking direct to SQLite.

This will take a little time. @axllent -- sounds like you're unblocked for now?

[axllent]

No need to worry about me, @otoolep. I'm back to using float64 and converting to uint64 in a new release today. I know it's not the prettiest solution, but it works fine for now, so I'm not stuck. Of course I'd prefer not to and will switch back if you find a working (transparent) solution that is compatible with the stdlib, but there's no rush for me.

Ironically, I couldn't remember why I initially chose float64 for my mapped values, so I switched everything to uint64 a few releases ago without a second thought, especially since all my tests passed. Clearly, I must have known at some point about this limitation at some point in the past, but I can't recall the details (I've documented it this time though)! The main issue for me was that I wasn't testing against a rqlite database, just a local SQLite database. Today, I've addressed that by adding a separate set of tests in GitHub Actions specifically against rqlite, so I won't be caught off guard if I have another brain fart. Thankfully, someone reported the bug in Mailpit (they use rqlite) which brought me here.

Thanks mate, keep up the great work!