Clarification on Data Type Conversion in Text Protocol for v1.8.0

[mimaken3]

I've been working with go-sql-driver/mysql and recently upgraded to version 1.8.1. I noticed in the release notes that as of this version, integer/float values are now converted into int64 or double even when using the text protocol:
https://github.com/go-sql-driver/mysql/releases/tag/v1.8.0

• Parse numbers on text protocol too by @methane in Parse numbers on text protocol too #1452
  • When text protocol is used, go-mysql-driver passed bare []byte to database/sql for avoid unnecessary allocation and conversion.
  • If user specified *any to Scan(), database/sql passed the []byte into the target variabe.
  • This confused users because most user doesn't know when text/binary protocol used.
  • go-mysql-driver 1.8 converts integer/float values into int64/double even in text protocol. This doesn't increase allocation compared to []byte and conversion cost is negilible.

I would appreciate some clarification on the following points:

What criteria determine whether a value is sent as int64 or double.
Specifically, for values like 100,000 and 1,000,000 which data type (int64 or double) would be used?

Context:
I'm using MySQL v5.7.42 and Go v1.21.0 with the go-sql-driver/mysql v1.8.1. I have a table with a JSON column, and I'm trying to sum values extracted from this JSON using JSON_EXTRACT().

Here's a simplified version of my setup:

CREATE TABLE users {
 name json NOT NULL 
}

mysql> select name from users;
+--------------------------------------------------------+
| name                                                   |
+--------------------------------------------------------+
| {"hoge1": {"fuga1": 100000}}                           |
| {"hoge2": {"fuga2": 1000000}}                          |
+--------------------------------------------------------+

In Go, I'm executing the following SQL queries and scanning the results into int64 variables:

import (
  ...
  "github.com/volatiletech/sqlboiler/queries"
  "database/sql"
)
...
var ( 
  v1 v2 int64
  db *sql.DB
)
...
row1 := queries.Raw(`
  select sum(JSON_EXTRACT(name, '$.hoge1.fuga1')) as piyo from users;
`).QueryRow(db)

err := row1.Scan(&v1)
fmt.Printf("%v, err\n", v1, err) // output: 100000, nil

row2 := queries.Raw(`
  select sum(JSON_EXTRACT(name, '$.hoge2.fuga2')) as piyo from users;
`).QueryRow(db)

err = row2.Scan(&v2)
fmt.Printf("%v, err\n", v2, err) // output: 0, Scan error on column index 0, name "piyo": converting driver.Value type float64 ("1e+06") to a int64: invalid syntax

The first query works as expected, but the second query returns the following error:

Scan error on column index 0, name "piyo": converting driver.Value type float64 ("1e+06") to a int64: invalid syntax

I thought that 1,000,000 is expected to be of type int64, but for some reason, it was retrieved as float64.

So what criteria determine whether a value is sent as int64 or double.
Specifically, for values like 100,000 and 1,000,000 which data type (int64 or double) would be used?

[methane]

It is question about behavior of the MySQL, not a driver.

[methane]

And your sample code is incomplete. I can not even compile your code.

[methane]

I managed to run your code and I can reproduce it.
https://gist.github.com/methane/8f7bf5c84705246ab7c8da3eb5889820

I confirm that MySQL returns DOUBLE (float64) for both queries.

$ mysql -h 127.0.0.1 -u root test --column-type-info

mysql> select sum(JSON_EXTRACT(name, '$.hoge1.fuga1')) as piyo from users;
Field   1:  `piyo`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 6
Decimals:   31
Flags:      BINARY NUM


+--------+
| piyo   |
+--------+
| 100000 |
+--------+
1 row in set (0.00 sec)

mysql> select sum(JSON_EXTRACT(name, '$.hoge2.fuga2')) as piyo from users;
Field   1:  `piyo`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 7
Decimals:   31
Flags:      BINARY NUM


+---------+
| piyo    |
+---------+
| 1000000 |
+---------+
1 row in set (0.01 sec)

The difference is came from here.

https://github.com/golang/go/blob/96d8ff00c2d6a88384863a656fb5e53716b614d3/src/database/sql/convert.go#L440-L451

When dest type (scanning into) is int and source type (the driver returns) is not int, database/sql stringify source value and use ParseInt() to get integer.

See and run this sample code. https://go.dev/play/p/-raUl94MbZW

fmt.Println(100000.0) becomes 100000 so ParseInt succeeds.
fmt.Println(1000000.0) becomes 1e+06 so ParseInt fails.

That's why two queries have different results.

[mimaken3]

I exactly confirmed that MySQL returns DOUBLE (float64) for both queries.
That causes "failed to converting to int64 error".

Thank you for the clear explanation!