1

I have a "Duration" field of type Decimal(38, 3) in a ClickHouse table.

From my Golang service I'm sending a query to get the SUM() of it but I just can't scan the result back. I tried using int, uint, float, sql.NullFloat64, a struct type, scanning row by row, the whole struct, arrays, structs with arrays, using sqlx.Query, sqlx.Select and nothing worked.

rows, _ := s.db.Query("SELECT sum(Duration) AS duration FROM mytable")
for rows.Next() {
    var count sql.NullFloat64
    log.Println(rows.Scan(&count))
    log.Printf("count: %v\n", count)
}

I always get back this kind of errors: sql: Scan error on column index 0, name "duration": converting driver.Value type []uint8 ("\x04!\xdf\xdc\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00") to a float64: invalid syntax

While checking some of the rows info I get the following:

log.Printf("db type name: %v\n", t.DatabaseTypeName())
log.Printf("db scan type: %v\n", t.ScanType())
>>>
db type name: Decimal(38, 3)
db scan type: []uint8

For other SELECT statements of Decimal types without aggregation functions I used float32/64 but this one just refuses to work.

Running the query directly in the console I get a single value as expected: 3702.5

Any ideas?

--- UPDATE 1 ---

I manage to scan it into a byte array but I don't know how to transform it into the number shown in the client: 3708199.5

count := []byte{}
rows.Scan(&count)
log.Printf("count: %v\n", count)
>>>
count: [76 162 6 221 0 0 0 0 0 0 0 0 0 0 0 0]
Ismael Farfán
  • 71
  • 5
  • which version clickhouse-go driver do you use? did you try https://github.com/clickhouse/clickhouse-go/tree/v2 ? – Slach Mar 01 '22 at 07:56
  • I fixed it by casting the result with toUInt64(sum...) How silly of me, I'm using 1.5.1, I'll update to v2 right away. – Ismael Farfán Mar 02 '22 at 15:49

1 Answers1

1

After migration from v1 to v2 of the library, the sum() returns now an string, but it still fails because the destination is a float, so I cast the value to a known type to fix it.

rows, _ := s.db.Query("SELECT toDecimal64(sum(Duration), 2) AS duration FROM mytable")
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Ismael Farfán
  • 71
  • 5