Marshalling of JSON data silently fails when column includes an underscore
- Dominant language
- Go
- Stars
- 14.3k
- Forks
- 1.1k
- Avg merge
- 6d 9h
- Merged PRs (30d)
- 11
Description
**Describe the bug**
When the name of a database column includes and underscore character, `pgx.CollectRows` with `pgx.RowToStructByName` fails to to correctly marshall the data into a struct.
**To Reproduce**
The following code will produce the bug. The values stored in `column_one` and `column_three` will fail to be carried over into the struct, but the values stored in `columntwo` and `columnfour` will correctly carry over.
This example is also available in the github repository https://github.com/Aerek-Yasa/pgxtest .
The test requires the test runner to fill in the credentials and location for a test database at the top of the file. The test database is expected to already be created.
```go
package main
import (
"context"
"fmt"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgxpool"
)
var databaseUser string = ""
var databasePassword string = ""
var databaseHost string = ""
var databasePort int32 = 0
var databaseName string = ""
type PGXTestTable struct {
ColumnOne int32 `db:"column_one"`
ColumnTwo int32 `db:"columntwo"`
ColumnThree []int32 `db:"column_three"`
ColumnFour []int32 `db:"columnfour"`
}
type Wrapper struct {
Values []PGXTestTable `db:"values"`
}
func main() {
if databaseUser == "" {
fmt.Println("Please add the username for the test database into the main.go file")
return
}
if databasePassword == "" {
fmt.Println("Please add the password for the test database into the main.go file")
return
}
if databaseHost == "" {
fmt.Println("Please add the hostname for the test database into the main.go file")
return
}
if databasePort == 0 {
fmt.Println("Please add the port for the test database into the main.go file")
return
}
if databaseName == "" {
fmt.Println("Please add the database name for the test database into the main.go file")
return
}
configString := fmt.Sprintf("user=%s password=%s host=%s port=%d dbname=%s", databaseUser, databasePassword, databaseHost, databasePort, databaseName)
config, err := pgxpool.ParseConfig(configString)
if err != nil {
fmt.Println(err)
return
}
pool, err := pgxpool.NewWithConfig(context.Background(), config)
if err != nil {
fmt.Println(err)
return
}
_, err = pool.Exec(context.Background(), `
CREATE TABLE IF NOT EXISTS pgx_test_table (
column_one INT,
columntwo INT,
column_three INT[],
columnfour INT[]
);
`)
if err != nil {
fmt.Println(err)
return
}
_, err = pool.Exec(context.Background(), `
INSERT INTO pgx_test_table
(column_one, columntwo, column_three, columnfour)
VALUES
(1001, 1011, '{1110}', '{1100}'),
(2002, 2022, '{2220}', '{2200}');
`)
if err != nil {
fmt.Println(err)
return
}
rows, err := pool.Query(context.Background(), `
SELECT
COALESCE(
(SELECT
json_agg(row_to_json(pgx_test_table))
FROM pgx_test_table
),
'[]'::json
) values
`)
if err != nil {
fmt.Println(err)
return
}
defer rows.Close()
wrapper, err := pgx.CollectRows[Wrapper](rows, pgx.RowToStructByName[Wrapper])
if err != nil {
fmt.Println(err)
return
}
for _, thing := range wrapper[0].Values {
fmt.Println("Got values: ", thing.ColumnOne, thing.ColumnTwo, thing.ColumnThree, thing.ColumnFour)
}
}
```
**Expected behavior**
The program is expected to output the values :
* `1001`, `1011`, `[1110]` and `[1100]` for the first database row
* `2002`, `2022`, `[2220]` and `[2200]` for the second database row
**Actual behavior**
The program outputs the following values;
* `0`, `1011`, `[ ]` and `[1100]` for the first database row
* `0`, `2022`, `[ ]`, and `[2200]` for the second database row
**Version**
- Go: `go version go1.22.4 windows/amd64`
- PostgreSQL: PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit'`
- pgx: `v5.6.0` and `v5.7.2` tested
Contributor guide
Research direction
Start by running the linked pgxtest reproduction with a configured PostgreSQL database, focusing on pgx.CollectRows and pgx.RowToStructByName. Compare the handling of column_one and column_three with columntwo and columnfour; done means all four fields, including underscore-named columns, contain the expected values for both rows.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100