MySQL NULL value issue on SelectInt gorp

Question

I'm trying to run the following query using gorp library with mysql

query = "SELECT SUM(outputoctets) FROM monthlyacct where date >= ? AND date < ? AND location = ?"
count, err := dbMap.SelectInt(query , constrains.From, constrains.To, constrains.LocationId)

This query throws the following error,

Scan error on column index 0: converting string \"\u003cnil\u003e\" to a int64: strconv.ParseInt: parsing \"\u003cnil\u003e\": invalid syntax

Here column outputoctets is type BIGINT. I also tried changing SelectInt to SelectStr, then it threw the following error;

Scan error on column index 0: unsupported driver -\u003e Scan pair: \u003cnil\u003e -\u003e *string

When I ran the query without location filter, it worked. Seems like this is due to some NULL values present in the location column. However I need to have the location filter on the query. How can I solve this?

What does MySQL output for the query?

0x434D53
– 0x434D53

2015-10-12 01:52:04 +00:00
Commented Oct 12, 2015 at 1:52 — 0x434D53
– 0x434D53, Commented Oct 12, 2015 at 1:52
It's always a number SUM(outputoctets)

Anuruddha
– Anuruddha

2015-10-12 04:38:54 +00:00
Commented Oct 12, 2015 at 4:38 — Anuruddha
– Anuruddha, Commented Oct 12, 2015 at 4:38
SUM() returns NULL if there were no matching rows

Mark
– Mark

2015-10-12 05:28:20 +00:00
Commented Oct 12, 2015 at 5:28 — Mark
– Mark, Commented Oct 12, 2015 at 5:28

Mark · Accepted Answer · 2015-10-12 02:21:13Z

1

Looks like the additional location criteria is filtering out all results, so the sum(outputoctets) is null. \u003cnil\u003e is the string "<nil>", which go rejects as an invalid integer.

Use a 'nullable' type to hold the sum, eg, NullInt64

Alternatively, use the mysql IFNULL function to avoid returning a null from the query, eg, IFNULL(sum(outputoctets),0)

See the Go database tutorial for more information

edited Oct 12, 2015 at 2:21

answered Oct 12, 2015 at 1:42

Mark

7,1511 gold badge43 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL NULL value issue on SelectInt gorp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related