Go SQL query inconsistency

问题:

I am experiencing some really weird inconsistencies when executing queries, and was wondering if anyone knew why.

Imagine I have a struct defined as follows:

type Result struct {
    Afield string      `db:"A"`
    Bfield interface{} `db:"B"`
    Cfield string      `db:"C"`
    Dfield string      `db:"D"`
}

And a MySQL Table with the following cols:

A : VARCHAR(50)
B : INT
C : VARCHAR(50)
D : VARCHAR(50)

The query I would like to execute:

SELECT A, B, C, D FROM table WHERE A="a"

first way it can be executed:

db.Get(&result, `SELECT A, B, C, D FROM table WHERE A="a"`)

second way it can be executed:

db.Get(&result, `SELECT A, B, C, D FROM table WHERE A=?`, "a")

The inconsistencies I am experiencing are as follows: When executing the query the first way, the type of Bfield is int. However, when executing the query the second time, it is []uint8.

This outcome is occurring for example when B is 1.

Why is the type of Bfield different depending on how the query is executed?

connection declaration:

// Connection is an interface for making queries.
type Connection interface {
    Exec(query string, args ...interface{}) (sql.Result, error)
    Get(dest interface{}, query string, args ...interface{}) error
    Select(dest interface{}, query string, args ...interface{}) error
}

EDIT

This is also happening using the Go database/sql package + driver. The queries below are assigning Bfield to []uint8 and int64 respectively.

db is of type *sql.DB

query 1:

db.QueryRow(SELECT A, B, C, D FROM table WHERE A="a").Scan(&result.Afield, &result.Bfield, &result.Cfield, &result.Dfield)

-- > type of Bfield is []uint8

query 2:

db.QueryRow(SELECT A, B, C, D FROM table WHERE A=?, "a").Scan(&result.Afield, &result.Bfield, &result.Cfield, &result.Dfield)

--> type of Bfield is int64

EDIT

Something else to note, when chaining multiple WHERE clauses, as long as at least 1 is populated using ?, the query will return int. Otherwise if they are all populated in the string, it will return []uint8

回答1:

Short answer: because the MySQL driver uses a different protocol for queries with and without parameters. Use a prepared statement to get consistent results.

The following explanation refers to the standard MySQL driver github.com/go-sql-driver/mysql, version 1.4

In the first case, the driver sends the query directly to MySQL, and interprets the result as a *textRows struct. This struct (almost) always decodes results into a byte slice, and leaves the conversion to a better type to the Go sql package. This works fine if the destination is an int, string, sql.Scanner etc, but not for interface{}.

In the second case, the driver detects that there are arguments and returns driver.ErrSkip. This causes the Go SQL package to use a PreparedStatement. And in that case, the MySQL driver uses a *binaryRows struct to interpret the results. This struct uses the declared column type (INT in this case) to decode the value, in this case to decode the value into an int64.

Fun fact: if you provide the interpolateParams=true parameter to the database DSN (e.g. "root:testing@/mysql?interpolateParams=true"), the MySQL driver will prepare the query on the client side, and not use a PreparedStatement. At this point both types of query behave the same.

A small proof of concept:

package main

import (
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

type Result struct {
    Afield string
    Bfield interface{}
}

func main() {
    db, err := sql.Open("mysql", "root:testing@/mysql")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if _, err = db.Exec(`CREATE TABLE IF NOT EXISTS mytable(A VARCHAR(50), B INT);`); err != nil {
        log.Fatal(err)
    }
    if _, err = db.Exec(`DELETE FROM mytable`); err != nil {
        log.Fatal(err)
    }
    if _, err = db.Exec(`INSERT INTO mytable(A, B) VALUES ('a', 3)`); err != nil {
        log.Fatal(err)
    }

    var (
        usingLiteral         Result
        usingParam           Result
        usingLiteralPrepared Result
    )
    row := db.QueryRow(`SELECT B FROM mytable WHERE A='a'`)
    if err := row.Scan(&usingLiteral.Bfield); err != nil {
        log.Fatal(err)
    }
    row = db.QueryRow(`SELECT B FROM mytable WHERE A=?`, "a")
    if err := row.Scan(&usingParam.Bfield); err != nil {
        log.Fatal(err)
    }
    stmt, err := db.Prepare(`SELECT B FROM mytable WHERE A='a'`)
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()
    row = stmt.QueryRow()
    if err := row.Scan(&usingLiteralPrepared.Bfield); err != nil {
        log.Fatal(err)
    }

    log.Printf("Type when using literal:  %T", usingLiteral.Bfield)         // []uint8
    log.Printf("Type when using param:    %T", usingParam.Bfield)           // int64
    log.Printf("Type when using prepared: %T", usingLiteralPrepared.Bfield) // int64
}

回答2:

Your first SQL string, in MySql is ambigous and can have too meaning as explained on StackOverflow in following address

When to use single quotes, double quotes, and back ticks in MySQL

Depending on SQL-MODE, your SQL command can be interpreted as

SELECT A, B, C, D FROM table WHERE A='a'

that is what I think you are expecting.

or as

SELECT A, B, C, D FROM table WHERE A=`a`

To avoid this ambiguity, can you make a new FIRST test in replacing double quotes by single quote ?

If the same behavior continue to be there, my answer is not a good response.

If BOTH SQL select return same value, your question has been solved.

Using ` character, you pass a variable name and not a string value !