Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
ComaVN opened this issue Sep 24, 2018 · 10 comments
Closed

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

ComaVN opened this issue Sep 24, 2018 · 10 comments
Labels
FrozenDueToAge NeedsInvestigation Someone must examine and confirm this is a valid issue and not a duplicate of an existing one.
Milestone

Comments

@ComaVN
Copy link

ComaVN commented Sep 24, 2018

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
Copy link
Contributor

CC @kardianos

@ianlancetaylor ianlancetaylor added the NeedsInvestigation Someone must examine and confirm this is a valid issue and not a duplicate of an existing one. label Sep 24, 2018
@ianlancetaylor ianlancetaylor added this to the Go1.12 milestone Sep 24, 2018
@kardianos
Copy link
Contributor

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
Copy link
Author

ComaVN commented Sep 24, 2018

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
Copy link

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
Copy link
Contributor

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

@ComaVN
Copy link
Author

ComaVN commented Sep 28, 2018

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
Copy link
Contributor

kardianos commented Sep 28, 2018 via email

@renthraysk
Copy link

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
Copy link
Contributor

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

@renthraysk
Copy link

renthraysk commented Sep 28, 2018

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.

@golang golang locked and limited conversation to collaborators Sep 28, 2019
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
FrozenDueToAge NeedsInvestigation Someone must examine and confirm this is a valid issue and not a duplicate of an existing one.
Projects
None yet
Development

No branches or pull requests

5 participants