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

GlebZabl · 2020-09-01T15:28:27Z

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

The text was updated successfully, but these errors were encountered:

ainar-g · 2020-09-01T22:23:48Z

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 · 2020-09-02T09:31:43Z

@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 · 2020-09-02T09:44:06Z

@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 · 2020-09-02T09:54:32Z

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 · 2020-09-02T10:28:23Z

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

GlebZabl · 2020-09-03T08:49:51Z

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 · 2020-09-03T17:37:40Z

/cc @bradfitz @kardianos @kevinburke per owners.

kardianos · 2020-09-03T19:47:13Z

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

GlebZabl · 2020-09-04T07:26:48Z

@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 · 2020-09-04T10:51:48Z

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.

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 added the NeedsInvestigation Someone must examine and confirm this is a valid issue and not a duplicate of an existing one. label Sep 3, 2020

dmitshur added this to the Backlog milestone Sep 3, 2020

dmitshur added the WaitingForInfo Issue is not actionable because of missing required information, which needs to be provided. label Sep 3, 2020

kardianos closed this as completed Sep 4, 2020

golang locked and limited conversation to collaborators Sep 4, 2021

gopherbot added the FrozenDueToAge label Sep 4, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

GlebZabl commented Sep 1, 2020

ainar-g commented Sep 1, 2020

GlebZabl commented Sep 2, 2020 •

edited

GlebZabl commented Sep 2, 2020

ainar-g commented Sep 2, 2020

GlebZabl commented Sep 2, 2020

GlebZabl commented Sep 3, 2020 •

edited

dmitshur commented Sep 3, 2020

kardianos commented Sep 3, 2020

GlebZabl commented Sep 4, 2020

kardianos commented Sep 4, 2020

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

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

Comments

GlebZabl commented Sep 1, 2020

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

Does this issue reproduce with the latest release?

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

What did you do?

What did you expect to see?

What did you see instead?

ainar-g commented Sep 1, 2020

GlebZabl commented Sep 2, 2020 • edited

GlebZabl commented Sep 2, 2020

ainar-g commented Sep 2, 2020

GlebZabl commented Sep 2, 2020

GlebZabl commented Sep 3, 2020 • edited

dmitshur commented Sep 3, 2020

kardianos commented Sep 3, 2020

GlebZabl commented Sep 4, 2020

kardianos commented Sep 4, 2020

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

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

GlebZabl commented Sep 2, 2020 •

edited

GlebZabl commented Sep 3, 2020 •

edited