"NULL" literal in TEXT[] #1494
Description
Describe the bug
We managed to get literal strings with the value "NULL" in TEXT[] data types, like this: '{"NULL"," NULL "}'::TEXT[]. However, when we want to decode that into a []string, the decoder doesn't like that and fails with:
2023/02/09 09:04:00 can't scan into dest[0]: cannot scan NULL into *string
To Reproduce
Latest v5:
package main
import (
"context"
"log"
"os"
"github.com/jackc/pgx/v5"
)
func main() {
ctx := context.Background()
conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer conn.Close(context.Background())
sql(conn, `DROP TABLE IF EXISTS pgxnull`)
sql(conn, `CREATE TABLE pgxnull (id INT NOT NULL, columns TEXT[] NOT NULL)`)
sql(conn, `INSERT INTO pgxnull (id, columns) VALUES (41, '{"happy","case"}')`)
sql(conn, `INSERT INTO pgxnull (id, columns) VALUES (42, '{"NULL"," NULL "}')`)
// sql(conn, `INSERT INTO pgxnull (id, columns) VALUES (43, '{NULL," NULL "}')`) // will and should never work
rows, err := conn.Query(ctx, `
SELECT columns
FROM pgxnull
ORDER BY id
`)
if err != nil {
log.Fatal(err)
}
res, err := pgx.CollectRows(rows, pgx.RowTo[[]string])
if err != nil {
log.Fatal(err)
}
log.Printf("rows: %#v", res)
}
func sql(c *pgx.Conn, query string) {
_, err := c.Exec(context.Background(), query)
if err != nil {
log.Fatal(err)
}
}The values in the database look fine:
harmen=> select * from pgxnull ;
id │ columns
────┼───────────────────
41 │ {happy,case}
42 │ {"NULL"," NULL "}
(2 rows)
Expected behavior
I expect that a PG value '{"NULL"," NULL "}'::TEXT[] can be scanned into a Go []string.
For the code above that would be:
2023/02/09 09:08:19 rows: [][]string{[]string{"happy", "case"}, []string{"NULL", " NULL "}}
Actual behavior
It returns this error:
2023/02/09 09:04:00 can't scan into dest[0]: cannot scan NULL into *string
Version
- Go:
go version go1.20 linux/amd64 - PostgreSQL:
psql (13.8 (Debian 13.8-0+deb11u1)) - pgx:
github.com/jackc/pgx/v5 v5.2.0
Additional context
If I comment the if s != "NULL" in pgtype/array_codec.go like this:
for i, s := range uta.Elements {
elem := array.ScanIndex(i)
var elemSrc []byte
// if s != "NULL" { // <---
elemSrc = []byte(s)
// }
err = elementScanPlan.Scan(elemSrc, elem)
if err != nil {
return err
}
}
then I have no problem.