Rows.Scan fails with Scanner objects, but Row.Scan works ok with Scanner objects.

[Fung-Chai]

Issue description

If I use scanner objects with Rows.Scan, the src argument is a []uint8 for all types.

Example code

My table is

CREATE TABLE IF NOT EXISTS fruits (
        id BIGINT(20) NOT NULL AUTO_INCREMENT,
        name VARCHAR(45),
        year SMALLINT,
        amount INT,
        price FLOAT(23),
        PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8mb4;

Here is my code

type Scanner struct {
        FieldName string
}

func (s *Scanner) Scan(src any) error {
        fmt.Printf("FieldName: %s, src: type=%[2]T value=%[2]v", s.FieldName, src)
        return nil
}

// prepare to query
id_scanner := &Scanner{"Id"}
name_scanner := &Scanner{"Name"}
year_scanner := &Scanner{"Year"}
amount_scanner := &Scanner{"Amount"}
price_scanner := &Scanner{"Price"}

// query single row
row := db.QueryRow("select * from fruits where id=?", 1)
err := row.Scan(id_scanner, name_scanner, year_scanner, amount_scanner, price_scanner)

The err is nil and the output looks very nice, such as

FieldName: Id, src: type=int64 value=1
FieldName: Name, src: type=[]uint8 value=[97 112 112 108 101]
FieldName: Year, src: type=int64 value=1965
FieldName: Amount, src: type=int64 value=123
FieldName: Price, src: type=float32 value=7.55

This shows that Row.Scan works with Scanner objects. However, Rows.Scan fails:

rows, err := db.Query("select * from fruits;")
// err is nil
defer rows.Close()
for rows.Next() {
        err := rows.Scan(id_scanner, name_scanner, year_scanner, amount_scanner, price_scanner)
        // err is nil, but output is wrong
}

Error log

The output is different:

FieldName: Id, src: type=[]uint8 value=[49]
FieldName: Name, src: type=[]uint8 value=[97 112 112 108 101]
FieldName: Year, src: type=[]uint8 value=[49 57 54 53]
FieldName: Amount, src: type=[]uint8 value=[49 50 51]
FieldName: Price, src: type=[]uint8 value=[55 46 53 53]

For the Id field, the value 49 is the ASCII '1'.
For the Name field, the value [97 112 112 108 101] is ASCII apple.
For the Year field, the value [49 57 54 53] is ASCII 1965.
For the Amount field, the value [49 50 51] is ASCII 123.
For the Price field, the value [55 46 53 53] is ASCII 7.55.

It looks like the src argument is the result of fmt.Print or something similar if Scanner objects are involved.

Configuration

This Scanner code works with Microsoft SQL Server. But it doesn't work with MySQL and I am testing the Scanner code against the mysql:8..0.31 docker image with go-1.20 on archlinux. My go.mod shows I am using github.com/go-sql-driver/mysql v1.7.1.

[Fung-Chai]

Using the delve debugger, I dug into the go-sql-driver code and discovered that Row.Scan calls binaryRows.Next and Rows.Scan calls textRows.Next. This explains why QueryRow returns the correct types but Query returns []uint8 for all columns. The question now is why Rows.Scan doesn't call binaryRows.Next as well. I will dig on.

[Fung-Chai]

Another discovery.

My original code, which makes use of Query produces the wrong results.

// Query will call the go-sql-driver's textRows.Next function, which presents []uint8 (strings) to all the scanners.
rows, err := db.Query("select * from fruits;")
// err is nil
defer rows.Close()
for rows.Next() {
        err := rows.Scan(id_scanner, name_scanner, year_scanner, amount_scanner, price_scanner)
        // err is nil, but output is wrong
}

But I can get the right output with Prepare, calling Query via the statement object.

statement, err := db.Prepare("select * from fruits;")
// err is ni
rows, err := statement.Query()
// err is nil
defer rows.Close()
for rows.Next() {
        err := rows.Scan(id_scanner, name_scanner, year_scanner, amount_scanner, price_scanner)
        // err is nil, and output is the same as that produced by Row.Scan
}

The mysqlConn.queyy returns textRows while mysqlStmt.query returns *binaryRows.

[methane]

Using placeholder makes your query binary.
When implementing Scanner, you should expect many driver.Value type input, not single consistent one.

See #1452 for mitigations implemented in next release.