Bind ignores previous values when not using a pointer.

[sjhitchner]

I'm trying to use Bind to grab data from the following aggregation query.

SELECT transactions.id as id
    , transactions.date as date
    , sum(line_items.debit) as debit
    , sum(line_items.credit) as credit 
FROM "transactions" INNER JOIN line_items on transactions.id = line_items.transaction_id 
WHERE (line_items.account_id = 8)
GROUP BY 1, 2
ORDER BY 2 ASC;

If I query Postgres directly I get the following results

id |    date    | debit  | credit
----+------------+--------+--------
 18 | 2021-12-05 |   0.00 |  75.14
 19 | 2021-12-15 |   0.00 |  39.99
 33 | 2021-12-27 | 115.13 |   0.00
 41 | 2022-01-01 |   0.00 | 175.34

I have created the following struct to hold the results

type Table struct {
	ID     string        `boil:"id"`
	Date   time.Time     `boil:"date"`
	Debit  types.Decimal `boil:"debit"`
	Credit types.Decimal `boil:"credit"`
}

func (t Table) String() string {
	b, err := json.MarshalIndent(t, "", "  ")
	if err != nil {
		return err.Error()
	}
	return string(b)
}

If run the following

var table []Table
	err = models.NewQuery(
		qm.Select("transactions.id as id",
			"transactions.date as date",
			"sum(line_items.debit) as debit",
			"sum(line_items.credit) as credit"),
		qm.From("transactions"),
		qm.InnerJoin("line_items on transactions.id = line_items.transaction_id"),
		qm.Where("line_items.account_id = ?", 8),
		qm.GroupBy("1, 2"),
		qm.OrderBy("2 ASC"),
	).Bind(ctx, db, &table)
	fmt.Println(table)

I get the following output (Query debugging is turned on)

SELECT transactions.id as id, transactions.date as date, sum(line_items.debit) as debit, sum(line_items.credit) as credit FROM "transactions" INNER JOIN line_items on transactions.id = line_items.transaction_id WHERE (line_items.account_id = $1) GROUP BY 1, 2 ORDER BY 2 ASC;
[8]
[{
  "ID": "18",
  "Date": "2021-12-05T00:00:00Z",
  "Debit": "0.00",
  "Credit": "175.34"
} {
  "ID": "19",
  "Date": "2021-12-15T00:00:00Z",
  "Debit": "0.00",
  "Credit": "175.34"
} {
  "ID": "33",
  "Date": "2021-12-27T00:00:00Z",
  "Debit": "0.00",
  "Credit": "175.34"
} {
  "ID": "41",
  "Date": "2022-01-01T00:00:00Z",
  "Debit": "0.00",
  "Credit": "175.34"
}]

Notice the query generated is correct but the data does not match postgres. IDs and Dates are correct but values for both the debit and credit Decimals fields are the same for for all records (the last result).

If []Table is changed to []*Table the correct result is returned.

var table []*Table
	err = models.NewQuery(
		qm.Select("transactions.id as id",
			"transactions.date as date",
			"sum(line_items.debit) as debit",
			"sum(line_items.credit) as credit"),
		qm.From("transactions"),
		qm.InnerJoin("line_items on transactions.id = line_items.transaction_id"),
		qm.Where("line_items.account_id = ?", 8),
		qm.GroupBy("1, 2"),
		qm.OrderBy("2 ASC"),
	).Bind(ctx, db, &table)
	fmt.Println(table)

SELECT transactions.id as id, transactions.date as date, sum(line_items.debit) as debit, sum(line_items.credit) as credit FROM "transactions" INNER JOIN line_items on transactions.id = line_items.transaction_id WHERE (line_items.account_id = $1) GROUP BY 1, 2 ORDER BY 2 ASC;
[8]
[{
  "ID": "18",
  "Date": "2021-12-05T00:00:00Z",
  "Debit": "0.00",
  "Credit": "75.14"
} {
  "ID": "19",
  "Date": "2021-12-15T00:00:00Z",
  "Debit": "0.00",
  "Credit": "39.99"
} {
  "ID": "33",
  "Date": "2021-12-27T00:00:00Z",
  "Debit": "115.13",
  "Credit": "0.00"
} {
  "ID": "41",
  "Date": "2022-01-01T00:00:00Z",
  "Debit": "0.00",
  "Credit": "175.34"
}]

This seems like odd behaviour, I would expect both []Table and []*Table to return the correct data.

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v4.4.0

What is your database and version (eg. Postgresql 10)

Postgresql 10.5

If this happened at runtime what code produced the issue? (if not applicable leave blank)

See above

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE IF NOT EXISTS transactions (
  id SERIAL NOT NULL PRIMARY KEY,
  user_id INT NOT NULL,
  date DATE NOT NULL DEFAULT CURRENT_DATE,
  description TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users (id),
  UNIQUE (date, description)
);

CREATE TABLE IF NOT EXISTS line_items (
  id SERIAL NOT NULL PRIMARY KEY,
  transaction_id INT NOT NULL,
  user_id INT NOT NULL,
  account_id INT NOT NULL,
  description TEXT NOT NULL,
  debit NUMERIC(12,2) NOT NULL,
  credit NUMERIC(12,2) NOT NULL,
  annotation TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (account_id) REFERENCES accounts (id),
  FOREIGN KEY (user_id) REFERENCES users (id),
  FOREIGN KEY (transaction_id) REFERENCES transactions (id),
  UNIQUE (transaction_id, account_id)
);

Further information. What did you do, what did you expect?

[stephenafamo]

My first guess is that this is because internally, types.Decimal contains a pointer to decimal.Big.
It is possible that it is copying that pointer around which causes the values to end up being the same.

This is just a guess, someone would have to properly investigate this to pinpoint if this is the cause or not.

[ghost]

I ran into the same issue with types.JSON. I think the problem is the use of oneStruct when target is of type []Struct. The function repeatedly scans into the same oneStruct and appends a shallow copy to the target slice, but that only works for types that can be safely shallow-copied.

I think an easy fix would be to add oneStruct.Set(reflect.Zero(structType)) somewhere in the loop.

[stephenafamo]

If you're certain this is the cause, can you send in a PR to fix it? @anders-cognite

Even a PR with tests that replicate this will be greatly appreciated 🙏🏾 .

[LelouBil]

I was about to write a detailed issue about this, but I just found this one. I also encountered this bug, I'll do a pull request soon.

Edit: Actually, it seems more complicated than I thought. Adding oneStruct.Set(reflect.Zero(structType)) at the end of the loop to reset the struct does not seem to be working. I'll let someone else figure this out, in the meantime I just turned my array into a pointer array.

[zibi94]

I found similar bug: sqlboiler v4.14.2

Lets keep:

type ItemCore struct {
	ProductKey    string    `boil:"product_key" json:"-"`
	ProductName   string    `boil:"name" json:"name"`
}

type Item struct {
	*ItemCore               `boil:",bind"`
	ID                     string `boil:"id" json:"uuid"`
	Created                struct {
		UserID string    `boil:"created_by" json:"-"`
		By     string    `boil:"created_by_name" json:"by"`
		At     time.Time `boil:"created_at" json:"at"`
	} `boil:",bind" json:"created"`
	Modified struct {
		UserID string    `boil:"modified_by" json:"-"`
		By     string    `boil:"modified_by_name" json:"by"`
		At     time.Time `boil:"updated_at" json:"at"`
	} `boil:",bind" json:"modified"`
}

And try:

items := []Item{}
if err := queries.Raw("SELECT * FROM table").Bind(ctx, db, &items); err != nil {
	return nil, fmt.Errorf("exec query: %w", err)
}

There is an error:

"errorMessage": "reflect: call of reflect.Value.Field on zero Value",
"errorType": "ValueError"

When we use items := []*Item{} this work correctly. Also if we use in Item:

type Item struct {
	ItemCore               `boil:",bind"`

both cases ( items := []*Item{} and items := []Item{}) works correctly.