Simple query results differ from parameterized query results (strings vs. true data types)

[ffdwg]

A "fast track" query (no placeholders / arguments) delivers string values (textRows) while a "normal" query (with arguments) delivers typed results (obv. using Prepare+Query & binaryRows) when using Scan interface function with empty interfaces (untyped!) as the value receivers.

To reproduce simply execute these (quite similar) examples on a table config with an id column (int unsigned) and a val column (double):

SELECT id, val FROM config;

You will receive two strings per row holding representations of id and val, whereas

SELECT id, val FROM config WHERE id>?;

with argument 0
delivers an int64 and a float64 value (as expected, right?).

Complete:

// mc, _ := sql.Open(...)
// ...
holderId := new(interface{})
holderVal := new(interface{})
res, _ := mc.Query("SELECT id, val FROM config")
for res.Next() {
    res.Scan(holderId, holderVal)
    // => holderId = *string("1"), holderVal = *string("1.11")
}
res.Close()
// ....
res = mc.Query("SELECT id, val FROM config WHERE id > ?", 0)
for res.Next() {
    res.Scan(holderId, holderVal)
    // => holderId = *int64(1), holderVal = *float64(1.11)
}
res.Close()

[arnehormann]

This is expected.
MySQL provides two protocols to communicate with the server, one in binary form for prepared statements and the other in text form.
If you use Query with arguments, it's the same as preparing a statement and calling Query on the prepared statement and the binary protocol is used.
When the text protocol is used, everything is sent as a string and the driver passes it on as is.

To get rid of your problem, change your code to use prepared statements.

[ffdwg]

Arne,
I understand that MySQL has two different APIs for sending queries.
But I don't think that this is the expected behaviour from Go's point of view!
Go's database/sql interface does not distinguish between a parameterized and a plain Query. And I don't expect other implementations (i.e. other drivers / other databases) to make a difference in the according result sets there.
I have patched the code locally with a few strconv parse operations in *textRows.readRow function (packets.go). I can contribute here if you feel this issue worth re-opening...

[arnehormann]

Right now, you can use prepared statements; either explicit with Prepare or implicit by calling Query with additional arguments. Both ways will lead to you using prepared statements and the binary protocol. The MySQL documentation helpfully mentions that not all queries can be prepared but neglects to mention which. I had problems with SHOW queries, for example.

Anyway, that's one reason why we can't silently use prepared statements everywhere and have to implement support for both protocols.
And that's also a reason why both protocols have to be user facing, otherwise the user couldn't use those queries.
To keep the highest performance possible for the driver, we only convert or allocate when we have to - that's why this is the fastest mysql driver for Go, and we work hard to keep it that way.
In addition, we are bound by the functions database/sql provides and can't really introduce new ones.

All of this makes it surprising to get a string result, but we don't have an alternative without downgrading the driver performance or making certain classes of queries impossible.
Though we strive to avoid surprises for our users, in this case our hands are tied.

If I'm not seeing something, please drop me another comment. Nearly all of the protocol handling was written by Julien, I'm not treading on the most solid ground here.

[xaprb]

Is this something we should mention in the database/sql tutorial, or is
this too driver-specific?

[xaprb]

I opened an issue ; we can consider explaining this there. Add further
comments etc
VividCortex/go-database-sql-tutorial#38