Gorm mysql scan rows to map[string]interface,type DECIMAL is string #4466

GORM Other Playground Link https://github.com/go-gorm/playground/pull/337

mysql: scan rows to map[string]interface,type DECIMAL is string and is wrong If you know the table structure and define the structure, the data will be printed correctly. But if the table structure is unknown,then the data will be printed incorrectly.

defer rows.Close() columns, err := rows.Columns() if err != nil { return nil, err } numColumns := len(columns) values := make([]interface{}, numColumns) for i := range values { values[i] = new(interface{}) }

var results []map[string]interface{}
for rows.Next() {
    if err := rows.Scan(values...); err != nil {
        return nil, err
    }

    dest := make(map[string]interface{}, numColumns)
    for i, column := range columns {
        fmt.Println(reflect.TypeOf(values[i]))
        fmt.Println(values[i].(*interface{}))
        fmt.Println(*values[i].(*interface{}))
        dest[column] = values[i]
    }
    //fmt.Println("=====")
    //fmt.Println(dest)    
                 //print: 1234.5677 right
                 //print: NzMxLjI= wrong
    results = append(results, dest) //ps: NzMxLjI=  
}

if err := rows.Err(); err != nil {
    return nil, err
}
return results, nil

gorm.io/gorm v1.24.0

Comment From: a631807682

We rely on the driver's ScanType to receive the type, while mysql drvier usessql.RawBytes as the decimal ScanType. Although we can still handle them through the ColumnType innovation, but we must also handle the differences between different drivers. I don't think there is enough benefit to support this feature.

refer to https://github.com/go-sql-driver/mysql/issues/1386#issuecomment-1460013343

Comment From: elgs

@a631807682 I am not sure if this helps. See the comments.

https://github.com/go-sql-driver/mysql/issues/1401

Comment From: a631807682

@a631807682 I am not sure if this helps. See the comments.

go-sql-driver/mysql#1401

When we specialize the scan method for one type of a driver, it means that we start to specify the scan method for all types of all drivers, because each databasetype is handled differently by different drivers, and they may even use Scanner to dynamically allocate the type (e.g. clickhouse decimal type). It's not that we can't do it, it's just that we pay enough for it and not enough of the benefit.

Comment From: elgs

AFAIK, only the mysql driver that we are using has this issue. I am the author of the following packages. I work with all kinds of drivers, and found no other drivers having such problem.

https://github.com/elgs/gosqljson
https://github.com/elgs/gosqlapi