Custom int type not working with interpolateParams=true

[nemith]

Issue description

My SQL enviroment does not support Prepared statements so I turned on interpolateParams=True and my custom type's Scan function gets a []uint8 instead of an int64.

buck run //experimental/bbennett/sqltest
Building: finished in 3.1 sec (100%) 112/112 jobs, 2 updated
  Total time: 3.2 sec
2019/01/22 12:06:37 sql: Scan error on column index 2, name "start_time": cannot scan type []uint8 into NanoTime: [49 52 57 50 55 49 56 53 56 57 52 55 54 54 57 55 53 51 57]

Example code

 describe my_table;
+---------------+----------------------+------+-----+---------+----------------+
| Field         | Type                 | Null | Key | Default | Extra          |
+---------------+----------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned  | NO   | PRI | NULL    | auto_increment |
| name          | varchar(255)         | NO   |     | NULL    |                |
| start_time    | bigint(20) unsigned  | YES  |     | NULL    |                |
+---------------+----------------------+------+-----+---------+----------------+
11 rows in set (0.06 sec)

type NanoTime struct {
        time.Time
}

func (t *NanoTime) Scan(v interface{}) error {
        switch x := v.(type) {
        case nil:
                t.Time = time.Time{}
                return nil
        case int64:  // works fine with interplateParams=false
                if x == 0 {
                        t.Time = time.Time{}
                } else {
                        t.Time = time.Unix(0, x)
                }
                return nil
        }
        return fmt.Errorf("cannot scan type %T into NanoTime: %v", v, v)
}


type MyRow struct {
        Id        int      `db:"id"`
        Name      string   `db:"name"`
        StartTime NanoTime `db:"start_time"`
}

func main() {
        // ....

        const query = `SELECT id, name, start_time FROM my_table`
        rows, err := conn.Query(query)
        if err != nil {
                fmt.Fprintf(os.Stderr, "error querying: %v\n", err)
                os.Exit(1)
        }

        for rows.Next() {
                var r MyRow
                if err := rows.Scan(&r.Id, &r.Name, &r.StartTime); err != nil {
                        log.Fatal(err)
                }
                fmt.Printf("Row: %#v\n", r)
        }
        fmt.Println()
       //...
}

Configuration

Driver version (or git SHA): lastest

Go version: go version go1.11.2 linux/amd64

Server version: MySQL 5.6.35

Server OS: E.g. Centos 7

[nemith]

As a work around this works since it uses the built in database/sql convertAssign function to convert []uint8 (i.e a byte slice) to an integer. However the Scanner interface documentation states that we can expect int64 values from the driver and I am wondering why it is different from interpolateParams set to true vs false.

func (t *NanoTime) Scan(v interface{}) error {
        if v == nil {
                t.Time = time.Time{}
                return nil
        }

        val := sql.NullInt64{}
        if err := val.Scan(v); err != nil {
                return err
        }

        if val.Int64 == 0 {
                t.Time = time.Time{}
                return nil
        }

        t.Time = time.Unix(0, val.Int64)
        return nil
}

[nemith]

Digging in a bit further this seems to be the result of an interpolated query using textRows and a prepared statement uses binaryRows.

It seems that the column definitions are given in the resultSet, but then aren't actually used to type the results so everything being sent from an interpolated query is always a byte slice. :(

[methane]

Duplicate of #861.

However the Scanner interface documentation states that we can expect int64 values from the driver

https://golang.org/pkg/database/sql/#Scanner says:

        // The src value will be of one of the following types:
        //
        //    int64
        //    float64
        //    bool
        //    []byte
        //    string
        //    time.Time
        //    nil - for NULL values

So you should expect one of them, not only int64.

why it is different from

Performance reason. We choose most efficient type.

As a work around this works since it uses the built in database/sql convertAssign function to convert []uint8 (i.e a byte slice) to an integer.

It's not work around. It's by design. convertAssign knows destination type. But driver doesn't know destination type. So driver should avoid conversion as possible. convertAssign do it.

[nemith]

I guess the problem is that I had to spend a lot of time digging in deep to figuring out that the two protocols are fundamentally different. Maybe some documentation would be in order since this is the second time it has come up?

[methane]

Maybe some documentation

There is:

https://golang.org/pkg/database/sql/#Scanner says:

        // The src value will be of one of the following types:
        //
        //    int64
        //    float64
        //    bool
        //    []byte
        //    string
        //    time.Time
        //    nil - for NULL values

No need to understand underlaying protocol. You should expect these types, as document says.
If you're really interested in protocol, there is MySQL internal doc.