Why result type is different on sql.Query()

[Harry]

I mention about the below func of ```database/sql``` package and I'm using golang 1.7.

func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {...}

I'm using MySQL and handling query result like that code below, (there are some omissions.)

sql := "SELECT field1, field2 FROM t_xxx WHERE flg=?"
//1-1) added args parameter
rows, err := ms.DB.Query(sql, 1)

values := make([]interface{}, 2)
scanArgs := make([]interface{}, 2)
for i := range values {
 scanArgs[i] = &values[i]
}

err = rows.Scan(scanArgs...)

//1-2) I expect that type of field1 is int64
val := reflect.ValueOf(values[0])
//val.Kind()==reflect.Int64


//------------------
//Next check
//------------------

//2-1) no args parameter
rows1, err := ms.DB.Query(sql)

values2 := make([]interface{}, 2)
scanArgs2 := make([]interface{}, 2)
for i := range values2 {
 scanArgs2[i] = &values2[i]
}

err = rows2.Scan(scanArgs2...)

//2-2) I expect that type of field1 is int64
val := reflect.ValueOf(values2[0])
//But, result was not int, it was byte. and it can be asseted as string.
//val.Kind()==reflect.Slice ([]uint8)

When adding args to Query func, result type is OK, but without it, result type change into []byte.

Why, result type changed???



Thank you.

Harry

[Kiki Sugiaman]

Did you check your error after each call to Query()?
Without an arg, what do you expect the "?" in "flg=?" to be formatted into?

[Harry]

I made a mistake.

Second query don't include placeholder.

sql := "SELECT field1, field2 FROM t_xxx"

rows1, err := ms.DB.Query(sql)

There is no error.
I'm asking why result type was changed/


2016年9月28日水曜日 19時30分23秒 UTC+9 ksug:

[Kiki Sugiaman]

In a nutshell, Scan() internally performs several nested type switches
based on the type of the Scan destination and what is received from the
database (in other words, the driver).

As to why the two scans returned values of different types, more
information is needed. But since the queries were similar and the dest
were the same, you could start by looking at what is stored in the
database and what's the int64/[]byte returned.

[Harry]

Thanks ksug.

These two SQL expect same result.

Field1's type on MySQL is int.
When returning value as string type, that value is same to int value after asserted to int.
But it's troublesome to handle two different result by placeholder added/not added.


2016年9月28日水曜日 21時23分52秒 UTC+9 ksug:

[Kiki Sugiaman]

I don't think placeholder vs no placeholder makes a difference. Here is
an example where Scan() into the same table could return values of
different types (using sqlite instead of mysql).

https://play.golang.org/p/rAA04pv_I_

I'm not saying that this is what's definitely happening on your end,
just that it's possible.
Look at:
- implicit type conversions
- database, driver behavior
- driver bug
- etc

As I said, more information is needed.

[Konstantin Khomoutov]

On Wed, 28 Sep 2016 06:30:05 -0700 (PDT)
Harry <hiro...@gmail.com> wrote:

> > In a nutshell, Scan() internally performs several nested type
> > switches based on the type of the Scan destination and what is
> > received from the database (in other words, the driver).
> >
> > As to why the two scans returned values of different types, more
> > information is needed. But since the queries were similar and the
> > dest were the same, you could start by looking at what is stored in
> > the database and what's the int64/[]byte returned.
> >

> These two SQL expect same result.
>
> Field1's type on MySQL is int.
> When returning value as string type, that value is same to int value
> after asserted to int.
> But it's troublesome to handle two different result by placeholder
> added/not added.

I would take tcpdump / Wireshark, capture the exchange with the server
in both cases and look at what Wireshark's dissectors decode from that
exchange in both cases. If there's some information delivered by the
server along with its response (it should, I'd think), you'll be able
to see how it differs. Alternatively, you should be able to tell any
difference between how the MySQL driver on the Go side handles the
query in both the cases. This one is interesting. Say, the driver
might actually prepare the query behind your back. Or it may do
something weird when escaping the argument when constructing the full
query string if it's not using prepared statements.

To recap, there are at least two points of interest to look at.

[Konstantin Khomoutov]

On Wed, 28 Sep 2016 16:59:53 +0300
Konstantin Khomoutov <flat...@users.sourceforge.net> wrote:

[...]

> > Field1's type on MySQL is int.
> > When returning value as string type, that value is same to int value
> > after asserted to int.
> > But it's troublesome to handle two different result by placeholder
> > added/not added.
>
> I would take tcpdump / Wireshark, capture the exchange with the server
> in both cases and look at what Wireshark's dissectors decode from that
> exchange in both cases.
> If there's some information delivered by the server along with its
> response (it should, I'd think),

Sorry, I meant "type information returned by the server" here.

[...]

[Harry]

Thank you ksug,

Your indication makes sense.

I try to check more detail.
And I'll feedback.

2016年9月28日水曜日 22時58分24秒 UTC+9 ksug:

[Harry]

Thanks, Konstantin.

It would be nice idea,
I'll try to check it!

2016年9月28日水曜日 23時00分14秒 UTC+9 Konstantin Khomoutov: