fetch big int value error

[linlexing]

package main

import (
    "database/sql"

    _ "github.com/mattn/go-oci8"
)

func main() {
    db, err := sql.Open("oci8", "***@localhost:1521/orcl")
    if err != nil {
        panic(err)
    }
    rows, err := db.Query("select 123456789 from dual")
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    // tys, err := rows.ColumnTypes()
    // if err != nil {
    //  panic(err)
    // }
    // spew.Dump(tys)
    var r int64 // float64 is ok
    if rows.Next() {
        if err = rows.Scan(&r); err != nil {
            panic(err)
        }
    }
}

output:

panic: sql: Scan error on column index 0, name "123456789": converting driver.Value type float64 ("1.23456789e+08") to a int64: invalid syntax

Checking for 0116b03c4cd4db06f2a2719868b7ba07224bdda2 may cause this errors. *the `select count() from ...` same error**

[MichaelS11]

Unless doing a cast Oracle returns numbers selected from dual as SQLT_BDOUBLE which oci8 converts to a float64. I believe that the Golang SQL driver does not have convert from float64 to int64, which we can confirm if needed. One simple fix for this is to cast any number selected from dual. For example: select cast (12345678 as INTEGER) from dual

I tired select count(1) from a table as well as from dual and scan into int64 worked fine.

[linlexing]

In the code, we use a lot of "select count" and then scan int64 directly. When the table records are larger than 10 million, an exception will occur and we hope to improve it.please！

[MichaelS11]

The select count from table should be returned as an int64. Did you update to the newest version? It should be working.

[cjbj]

@linlexing does the same thing happen in https://github.com/go-goracle/goracle ?

[linlexing]

I built a ten-million-level test table, and count gets an exception：

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 8月 29 10:26:51 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from test;

  COUNT(*)
----------
  11340032

package main

import (
    "database/sql"

    _ "github.com/mattn/go-oci8"
)

func main() {
    db, err := sql.Open("oci8", "scott/123456@*****:1521/orcl")
    if err != nil {
        panic(err)
    }
    rows, err := db.Query("select count(*) from test")
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    // tys, err := rows.ColumnTypes()
    // if err != nil {
    //  panic(err)
    // }
    // spew.Dump(tys)
    var r int64 // float64 is ok
    if rows.Next() {
        if err = rows.Scan(&r); err != nil {
            panic(err)
        }
    }
    println(r)
}

panic: sql: Scan error on column index 0, name "COUNT(*)": converting driver.Value type float64 ("1.1340032e+07") to a int64: invalid syntax

goroutine 1 [running]:
main.main()
        D:/skydrive/go/src/test/main.go:27 +0x1fb

[linlexing]

@linlexing does the same thing happen in https://github.com/go-goracle/goracle ?

I haven't used gooracle before. I tried go get today, but it didn't work. I use Windows and may need to upgrade GCC version.

[MichaelS11]

Should work fine in Windows or Linux.

The same test you did above, but with only a few rows, does it work or fail?

[cjbj]

@linlexing stick with go-oci8 and let @MichaelS11 help you.

[linlexing]

Should work fine in Windows or Linux.

The same test you did above, but with only a few rows, does it work or fail?

If the number of digits is more than 6, an error will occur. If the number of digits is less than or equal to 6, it works well You can quickly expand the record of the test table with insert into test select * from test.

[MichaelS11]

Able to reproduce the issue. Will look more into exact cause.

[MichaelS11]

The short version is this is a issue/bug with either/both Oracle OCI driver and Go convertAssignRows in database/sql. Details later.

Here are some suggestions to get around the issue:

1) Change your SQL: select cast (count(1) as INTEGER) from foo; 2) Change your scan to a float64 which will handle numbers up to 2^52. 3) (Untested) Use a named out variable, something like: sql.Named("my_count", sql.Out{Dest: &myInt}

.

The details on the issue/bug:

Oracle OCI C++ driver returns a precision and scale for what kind of number it is returning. https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci06des.htm#LNOCI16458 For both the select sum function and the count function it returns a 0 for precision and 0 for scale. For sum the result could be a float but for count it is only an integer. Seems like a wiser choice to make it that the sum and count function always return a float instead of only be able to return integers for both. So the go-oci8 driver returns a float64 for both.

For Go, the database/sql package makes a call to rows.Next() which calls the go-oci8 driver rows function Next which returns the rows data from select count as a float64 because of the previous details mentioned. Then when Scan is called the Go database/sql package calls convertAssignRows to convert the go-oci8 driver rows returned to the destination variables that were provided to Scan. https://golang.org/src/database/sql/convert.go There is no way to bypass or change this call that I can find. This means that a conversion from a float64 to a int64 is up to that convertAssignRows function which currently seems to be returning an error if the number of digits is more than six.

Sorry I do not have better news. Please let me know if you have any additional questions.

[linlexing]

So bad,but why was it right before version 0116b03

[MichaelS11]

It was not exactly right before either. It did return int64 for count but it also returned int64 for sum and for other select from dual statements. Returning a float64 instead of int64 for all cases gives better compatibility then int64.

[MichaelS11]

Can we close this issue now?

[linlexing]

The select sum(int) maybe overflow the float64,For some projects, this is very, very important.

[MichaelS11]

Yes, then it can be cast or an out variable can be used.

[linlexing]

Conclusion: the sum statement USES the cast function and the count statement USES the float64 data type.

[MichaelS11]

The sum and count functions default to a Go float64 data type. This can be changed by either SQL cast or by using an Go out variable.