MapScan values are garbled #225

volkanunsal · 2016-05-29T00:00:16Z

I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:

{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}

I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?

Here is my code:

type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}

The text was updated successfully, but these errors were encountered:

volkanunsal · 2016-06-04T20:28:43Z

Can someone help me with this? I found a related issue here, but it looks like this should have been solved by now. Maybe I'm just not getting it. Any help would be appreciated.

jmoiron · 2016-06-15T05:21:51Z

Hi @volkanunsal, can you replicate on an sqlite3 database?

I received a similar report about base64 encoded values in mapScan in the past (#191), but I wasn't really sure how to replicate. If you can give me some more detail about your database, schema, query, I can look into it, especially if the database is one of the 3 in the sqlx test suite (postgres, sqlite3 or mysql).

volkanunsal · 2016-06-16T17:33:46Z

@jmoiron It happens with postgres. I'm not exactly sure how to create a test case for it in sqlite, but I'll give it a shot this weekend.

volkanunsal · 2016-06-16T17:41:07Z

The schema of the table is very simple.

      Table "public.users"
│ id     │ numeric │           │
│ name   │ text    │           │

The query is SELECT * from users;

This is the part where I'm encoding it into JSON. Could this be where the encoding is getting messed up?

type rowMap map[string]interface{}

type fieldMap map[string]string

type fieldsMap map[string]fieldMap

type successPayload struct {
    Rows      []rowMap  `json:"rows"`
    Fields    fieldsMap `json:"fields"`
    Elapsed   float64   `json:"time"`
    TotalRows int64     `json:"total_rows"`
}

res := successPayload{scannedRows, fields, elapsed, count}

// Write it back to the client.
w.Header().Set("Content-Type", "application/json; charset=utf-8")
json.NewEncoder(w).Encode(res)

volkanunsal · 2016-06-18T22:12:58Z

I just tried it with sqlite3 using the following schema.

CREATE TABLE `users` (
    `id` INTEGER,
    `name` VARCHAR(64) NULL
  );

In sqlite3, the id field works, but the name field is still encoded in base64. Here is my test case:

https://gist.github.com/volkanunsal/e5b84aef87317fb4dff75c97f4c875a8

volkanunsal · 2016-07-03T00:57:46Z

@jmoiron Have you had a chance to look at the example above? Do you see any clues as to what might be happening? Is there anything I can do to fix this on my own end? Thanks.

JonathanFraser · 2017-02-20T20:06:13Z

definately seeing this with a mysql backend

ChristophPech · 2017-03-15T13:06:51Z

I have the same problem with SliceScan(), it will return []byte for the string values which the JSON encoder then will convert to base64. My workaround is manually convert them to string before JSON.

PumpkinSeed · 2017-05-09T21:13:01Z

I got the same error with mysql for MapScan() and for SliceScan() as well.

JonathanFraser · 2017-05-09T21:44:23Z

This may be related to prepared queries vs non-prepared queries. Try preparing the queries first and see if it fixes the problem.

brandonros · 2017-06-09T10:49:43Z

When performing a SELECT combined with Rows.mapScan, it base64 encodes the Postgres JSON data type instead of either a) parsing it or b) returning it as a string unaltered.

Any advice?

elvuel · 2017-11-24T03:57:08Z

The std encode/json encode byte slice with base64.

reproduce sample

SHOW FULL COLUMNS FROM _tablename_

go: 1.9
mysql driver: github.com/go-sql-driver/mysql (8fefef06)

// rows.Scan => []uint8

type dummy struct {
		Field      *string `db:"Field"`
		Type       *string `db:"Type"`
		Collation  *string `db:"Collation"`
		Null       *string `db:"Null"`
		Key        *string `db:"Key"`
		Default    *string `db:"Default"`
		Extra      *string `db:"Extra"`
		Privileges *string `db:"Privileges"`
		Comment    *string `db:"Comment"`
}

Fields with *string json marshal as expected, with interface{} not.

Doc for row#Scan database/sql/sql.go

wupeaking · 2017-12-27T09:35:12Z

I also encountered this problem, so I try transform the field like this。 It works :

		tmp := make(map[string]interface{})
		rows.MapScan(tmp)
		for k, encoded := range tmp {
			switch encoded.(type) {
			case []byte:
				tmp[k] = string(encoded.([]byte))
			}
		}

LuckyChen666 · 2020-03-19T10:19:44Z

I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:
{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}
I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?

Here is my code:
type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}

how to solve this problem?

elvuel · 2020-04-21T00:02:40Z

columns, _ := rows.Columns()
columnTypes, _ := rows.ColumnTypes()
columnPointers := make([]interface{}, len(columns))

for i := 0; i < len(columns); i++ {
	t := columnTypes[i].ScanType()
	...
	if t.Name() == "RawBytes" {
		columnPointers[i] = new(sql.RawBytes)
	}
        ...
}
...
for col, result := range results {
    switch result.(type) {
        case *sql.RawBytes:
           v, _ := result.(*sql.RawBytes)
           results[col] = string(*v)
        ....
    }
}

I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:
{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}
I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?
Here is my code:
type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}
I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:
{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}
I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?
Here is my code:
type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}
how to solve this problem?

ryanlath · 2020-07-23T18:43:39Z

I'm also having this problem. 10.2.32-MariaDB =-(

syedalisait · 2021-03-11T08:48:09Z

Facing the same problem with mySql, data type after doing a mapScan []byte, whereas the actual type in DB is int and varchar

jeffdupont · 2021-04-22T01:06:05Z

also experiencing this with AWS Aurora (mysql) any good solutions?

electrofocus · 2021-07-09T10:12:44Z

Have the same issue with MySQL

xyanyue · 2021-08-31T01:36:08Z

My solution:

for rows.Next() {
    var data map[string]interface{} = make(map[string]interface{})
    err = rows.MapScan(data)
    if err != nil {
	    fmt.Println(err, sqlStr, params.params)
    }
    for k, v := range data {
	    if value, ok := v.([]byte); ok {
		    data[k] = string(value)
	    }
    }
    res = append(res, data)
}

methane · 2021-11-10T05:20:23Z

There are two problems.

1. `Scan(*[]byte)` may return internal buffer directly.

The contents of []byte will be overwritten by Next(). So user need to copy the buffer before calling Next(). This is design of database/sql. This is not specific to sqlx and MapScan(). I think there is nothing sqlx can do here. It is user's responsibility.

2. `Rows.Scan(interface{})` returns `[]byte` even for string and other types.

Drivers don't know the scan target type. So drivers return raw values in []byte to avoid allocations.

database/sql converts values returned by driver to scan targe type. But when the target type is interface{}, database/sql returns the value from driver without any conversion. This is why []byte is returned for string columns.

I think sqlx should use ColumnType.ScanType instead of itnerface{} in here.

sqlx/sqlx.go

Line 841 in 92bfa36

values := make([]interface{}, len(columns))

Maybe, database/sql can do the conversion if small backward incompatibility is acceptable.

jmoiron added bug cannot reproduce labels Jun 15, 2016

methane mentioned this issue Nov 10, 2021

Convert VARCHAR, VARBINARY type to string go-sql-driver/mysql#1281

Closed

5 tasks

long2ice mentioned this issue Jul 1, 2022

rows.MapScan 方法是不是有BUG? #191

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MapScan values are garbled #225

MapScan values are garbled #225

volkanunsal commented May 29, 2016 •

edited

Loading

volkanunsal commented Jun 4, 2016

jmoiron commented Jun 15, 2016

volkanunsal commented Jun 16, 2016

volkanunsal commented Jun 16, 2016 •

edited

Loading

volkanunsal commented Jun 18, 2016 •

edited

Loading

volkanunsal commented Jul 3, 2016

JonathanFraser commented Feb 20, 2017

ChristophPech commented Mar 15, 2017

PumpkinSeed commented May 9, 2017

JonathanFraser commented May 9, 2017

brandonros commented Jun 9, 2017

elvuel commented Nov 24, 2017

wupeaking commented Dec 27, 2017

LuckyChen666 commented Mar 19, 2020

elvuel commented Apr 21, 2020 •

edited

Loading

ryanlath commented Jul 23, 2020

syedalisait commented Mar 11, 2021

jeffdupont commented Apr 22, 2021

electrofocus commented Jul 9, 2021

xyanyue commented Aug 31, 2021 •

edited

Loading

methane commented Nov 10, 2021 •

edited

Loading

MapScan values are garbled #225

MapScan values are garbled #225

Comments

volkanunsal commented May 29, 2016 • edited Loading

volkanunsal commented Jun 4, 2016

jmoiron commented Jun 15, 2016

volkanunsal commented Jun 16, 2016

volkanunsal commented Jun 16, 2016 • edited Loading

volkanunsal commented Jun 18, 2016 • edited Loading

volkanunsal commented Jul 3, 2016

JonathanFraser commented Feb 20, 2017

ChristophPech commented Mar 15, 2017

PumpkinSeed commented May 9, 2017

JonathanFraser commented May 9, 2017

brandonros commented Jun 9, 2017

elvuel commented Nov 24, 2017

wupeaking commented Dec 27, 2017

LuckyChen666 commented Mar 19, 2020

elvuel commented Apr 21, 2020 • edited Loading

ryanlath commented Jul 23, 2020

syedalisait commented Mar 11, 2021

jeffdupont commented Apr 22, 2021

electrofocus commented Jul 9, 2021

xyanyue commented Aug 31, 2021 • edited Loading

methane commented Nov 10, 2021 • edited Loading

1. Scan(*[]byte) may return internal buffer directly.

2. Rows.Scan(interface{}) returns []byte even for string and other types.

volkanunsal commented May 29, 2016 •

edited

Loading

volkanunsal commented Jun 16, 2016 •

edited

Loading

volkanunsal commented Jun 18, 2016 •

edited

Loading

elvuel commented Apr 21, 2020 •

edited

Loading

xyanyue commented Aug 31, 2021 •

edited

Loading

methane commented Nov 10, 2021 •

edited

Loading

1. `Scan(*[]byte)` may return internal buffer directly.

2. `Rows.Scan(interface{})` returns `[]byte` even for string and other types.