database/sql: Scan results are of wrong type when Query has no args

[ComaVN]

What version of Go are you using (go version)?

go version go1.11 linux/amd64

Does this issue reproduce with the latest release?

Yes

What operating system and processor architecture are you using (go env)?

$ docker run --rm golang:1.11.0 go env
GOARCH="amd64"
GOBIN=""
GOCACHE="/root/.cache/go-build"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GOOS="linux"
GOPATH="/go"
GOPROXY=""
GORACE=""
GOROOT="/usr/local/go"
GOTMPDIR=""
GOTOOLDIR="/usr/local/go/pkg/tool/linux_amd64"
GCCGO="gccgo"
CC="gcc"
CXX="g++"
CGO_ENABLED="1"
GOMOD=""
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fmessage-length=0 -fdebug-prefix-map=/tmp/go-build662937076=/tmp/go-build -gno-record-gcc-switches"

What did you do?

I tried to use Rows.Scan to read fields of an sql result row into variables of type interface{}

See https://github.com/ComaVN/gosqltypespoc

What did you expect to see?

I expect integer sql types to be read as go type int64, or at least, the go type should be the same whatever the query looks like.

What did you see instead?

SELECT 123 WHERE ? = 1 returns int64: 123
SELECT 123 WHERE 1 = 1 returns []uint8: []byte{0x31, 0x32, 0x33}

[ianlancetaylor]

CC @kardianos

[kardianos]

Please try to use https://godoc.org/bitbucket.org/kardianos/table and see if this persists.

Also what driver are you using, and version?

[ComaVN]

Also what driver are you using, and version?

See the PoC: github.com/go-sql-driver/mysql

No idea what version, I presume the latest, since I don't specify any version.

[renthraysk]

Believe this is mysql driver specific.

The two queries use different mysql protocols, first one requires preparing (as is parameterized and DSN setting interpolateParams is false) so uses mysql's binary protocol.
https://github.com/go-sql-driver/mysql/blob/99ff426eb706cffe92ff3d058e168b278cabf7c7/packets.go#L1122

Second is simpler (without parameters) so uses text protocol.
https://github.com/go-sql-driver/mysql/blob/99ff426eb706cffe92ff3d058e168b278cabf7c7/packets.go#L713

If add interpolateParams=true to the DSN, the output of the two queries becomes consistent, as only text protocol will be used. Both returning the []uint8: []byte{0x31, 0x32, 0x33} (aka "123").

[kardianos]

This sounds correct. Please file an issue in the driver.

[ComaVN]

I made an issue in the driver, and they claim it's expected behaviour: go-sql-driver/mysql#861

I understand the reasoning, but the end result is that as a user of this library I need to be aware of implementation details of the driver, which is less than ideal.

[kardianos]

Yes, I find that behavior troubling as well. But then, it is MySQL...

…

On Fri, Sep 28, 2018, 04:15 ComaVN ***@***.***> wrote: I made in issue in the driver, and they claim it's expected behaviour: go-sql-driver/mysql#861 <go-sql-driver/mysql#861> I understand the reasoning, but the end result is that as a user of this library I need to be aware of implementation details of the driver, which is less than ideal. — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#27828 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAuFsZCpGhUkK1OJUDl7UjY_pRRCLo-iks5ufgTFgaJpZM4W2rlq> .

[renthraysk]

Should be noted that the return type does depend on the value, irrespective of driver.

If replace 123 with 18446744073709551615 (MaxUint64) in the above code, database/sql Scan() is not going to return an uint64 type but a string using &interface{}s.

[kardianos]

This is again true for MySQL. This would not be true for other databases.

[renthraysk]

It's true for github.com/denisenkom/go-mssqldb. I just tested it :)

Meaning both queries on mssql return
[]uint8: []byte{0x31, 0x38, 0x34, 0x34, 0x36, 0x37, 0x34, 0x34, 0x30, 0x37, 0x33, 0x37, 0x30, 0x39, 0x35, 0x35, 0x31, 0x36, 0x31, 0x35}

and not an uint64 value.