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: cannot select query when args number is huge #41163

Closed
GlebZabl opened this issue Sep 1, 2020 · 10 comments
Closed

database/sql: cannot select query when args number is huge #41163

GlebZabl opened this issue Sep 1, 2020 · 10 comments

Comments

@GlebZabl
Copy link

@GlebZabl GlebZabl commented Sep 1, 2020

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

$ go version
go version go1.14 linux/amd64

Does this issue reproduce with the latest release?

yes

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

go env Output
$ go env
GO111MODULE="on"
GOARCH="amd64"
GOBIN="/home/zabludovsky/go/bin"
GOCACHE="/home/zabludovsky/.cache/go-build"
GOENV="/home/zabludovsky/.config/go/env"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GOINSECURE=""
GONOPROXY="gl.rosst.ru/backend/*"
GONOSUMDB="gl.rosst.ru/backend/*"
GOOS="linux"
GOPATH="/home/zabludovsky/go"
GOPRIVATE="gl.rosst.ru/backend/*"
GOPROXY="https://proxy.golang.org,direct"
GOROOT="/usr/local/go"
GOSUMDB="sum.golang.org"
GOTMPDIR=""
GOTOOLDIR="/usr/local/go/pkg/tool/linux_amd64"
GCCGO="gccgo"
AR="ar"
CC="gcc"
CXX="g++"
CGO_ENABLED="1"
GOMOD="/home/zabludovsky/go/src/awesomeProject1/go.mod"
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-build174435943=/tmp/go-build -gno-record-gcc-switches"

What did you do?

package main

import (
	"database/sql"
	"fmt"
	"strconv"

	_ "github.com/lib/pq"
)

func main()  {
	err := selectFromDb(65536)
	if err != nil{
		panic(err)
	}

	println("where was no errors!")
}

func selectFromDb(argsNumber int) (err error){
	//open connect to postgres database, put your database connection string below, instead of second argument
	db, err := sql.Open("postgres", "postgres://mayber:password@localhost:5432/test?sslmode=disable")
	if err != nil {
		return
	}

	//preparing query, you can call this func with argsNumber = 10(or some not huge number)
	//to check that it is working correct
	var args []interface{}
	query := "SELECT id FROM test_tbl WHERE id IN ("
	for i := 0; i < argsNumber; i++ {
		args = append(args, strconv.Itoa(i))
		query = fmt.Sprintf("%s $%d, ", query, i+1)
		fmt.Printf("preparing query, %d/%d \n", i, argsNumber)
	}
	query = fmt.Sprintf("%s);", query[:len(query)-2])
	fmt.Println("preparing finished:")

	//uncomment string bellow to see result query and check it
	//fmt.Println(query)


	//preparing query
	stmt, err := db.Prepare(query)
	if err != nil {
		return
	}

	//selecting
	rows, err := stmt.Query(args...)
	if err != nil{
		return
	}

	//closing db connection
	err = rows.Close()
	if err != nil{
		return
	}

	return
}

What did you expect to see?

preparing query, 0/65536
preparing query, 1/65536
...
preparing query, 60535/65536
preparing finished:
where was no errors!

What did you see instead?

preparing finished:
panic: sql: expected 0 arguments, got 65536

for more details https://github.com/GlebZabl/golang_postgres_bug

@ainar-g
Copy link
Contributor

@ainar-g ainar-g commented Sep 1, 2020

PostgreSQL doesn't support more than 65535 query parameters. See the source code. The error message is weird, but it's probably an error in github.com/lib/pq, and not database/sql, although I'm not sure.

@GlebZabl
Copy link
Author

@GlebZabl GlebZabl commented Sep 2, 2020

@ainar-g nope, that is not postgres limit. i've try query with even 65540 in usual jetbrains db console and it works fine. If you want to make sure, just copy my code, replace 33th string with query = fmt.Sprintf("%s %d, ", query, i+1), uncomment printing final query to console(40th string), copy that to your db console and execute. So it is exactly database/sql or github.com/lib/pq lib bug, connected with parsing query string.

@GlebZabl
Copy link
Author

@GlebZabl GlebZabl commented Sep 2, 2020

@ainar-g I've took a look on source, you have posted, now its really weird that it works from console, anyway, that is not correct error message and it have to be fixed.

@ainar-g
Copy link
Contributor

@ainar-g ainar-g commented Sep 2, 2020

If you remove the dollar signs, then they are not parameters, they are literal values. That's different. There either isn't a limit on those or it's higher.

The error message is confusing and should be fixed, but again, I'm not sure if that's database/sql's fault or the driver's. It's probably the drivers' responsibility to report such errors.

@GlebZabl
Copy link
Author

@GlebZabl GlebZabl commented Sep 2, 2020

yep, you are right about parameters, it was my stupid mistake.

@GlebZabl
Copy link
Author

@GlebZabl GlebZabl commented Sep 3, 2020

anyway, i`ve create pr to github.com/lib/pq with fix, but i think it will take a long time from their side until it will be merged, so it will be great from developers of database/sql to fix it on this library layer.

@dmitshur dmitshur changed the title database/sql cannot select query when args number is huge database/sql: cannot select query when args number is huge Sep 3, 2020
@dmitshur dmitshur added this to the Backlog milestone Sep 3, 2020
@dmitshur
Copy link
Member

@dmitshur dmitshur commented Sep 3, 2020

@kardianos
Copy link
Contributor

@kardianos kardianos commented Sep 3, 2020

What is the bug in database/sql? I didn't see it articulated above.

@GlebZabl
Copy link
Author

@GlebZabl GlebZabl commented Sep 4, 2020

@kardianos it will be great if call of sql.Tx.Prepare() from database/sql will throw some correct error(something like "number of args more then allowed") when it was created with sql.Db initialized with driver "postgres" and args number in query string($1,$2....) more then 65535, because https://github.com/lib/pq (standard postgreSQL driver) throws incorrect error(sql: expected 0 arguments, got 65536) and it goes right through database/sql layer to developers and really confused them.

@kardianos
Copy link
Contributor

@kardianos kardianos commented Sep 4, 2020

This limit is specific to postgresql. Yes, the driver should probably error out here. The sql package allows for it to return an error.

I cannot check for this number, as it is by far not the only driver out there. SQL Server also has a limit, but it is different. I cannot know the limit for each database, only the drivers will.

I don't think this is fixable at this layer.

@kardianos kardianos closed this Sep 4, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
4 participants
You can’t perform that action at this time.