Can't scan a null into a *string

[xaprb]

I used to use the code from Google Code. After updating to the latest code in master, I'm getting the following error I didn't get previously:

sql: Scan error on column index 7: unsupported driver -> Scan pair: <nil> -> *string

I will investigate more, but perhaps in the meantime you know something about this already?

[julienschmidt]

The old code returned an empty []byte for NULL values which made it impossible to distinguish a NULL value from an empty string.
See Issue #20 for details.

The error is returned by the database/sql package because of this goal:

* Be flexible with type conversions, but be paranoid about silent
  truncation or other loss of precision.

You must use http://golang.org/pkg/database/sql/#NullString if the column may contain NULL values

[xaprb]

Thanks, that makes sense.

I almost want a way to configure the library to give me the old behavior. In some ways, Go's "zero value as default" makes a lot of sense and eliminates a ton of boilerplate code. I'd kind of like to put that into one place, configurably, instead of scattering it everywhere in my code that I might encounter a NULL. Using the NullString, etc is pretty ugly code-wise.

NULL is just a nightmare and I want to get rid of NULLs. I explicitly define every column in my tables as NOT NULL, but occasionally a command like SHOW PROCESSLIST is going to have a NULL in it, and my code just doesn't care; I want an empty string instead. I'm just going to use a NullString, and ignore its .Valid property and just get its .String, which will be empty if the column was NULL.

Being able to tell the driver to convert NULL to zero-value for the type (or actually, I guess what it'd really do is skip scanning the column into the variable) would reduce a lot of hard and error prone (and not-future-proof) work for me. Or, if I don't want all the boilerplate code, I can take my chances that the column a) is really non-nullable b) will stay that way forever.

[julienschmidt]

I'd also prefer the database/sql package to follow Go's "default zero value" policy. You could still differentiate with the Null* types if you need to, but unfortunately the design decision was made this way. Maybe they change it in Go2 (+1 from me for that).
For now I have no plan to add a driver option for that. Compared to PostgreSQL the protocol already is a mess (tough i assume more efficient). I don't want to jumble the driver even more.
One str = nullStr.Value more per scanned string is the lesser evil for now.

[julienschmidt]

I tried to add an option to zero NULL values: https://github.com/Go-SQL-Driver/MySQL/tree/zeroNULL
But it's not possible on a driver level. If you set the zero value to []byte{} you can scan it to string and []byte but not to numerical types. If you set it to 0, you can scan it to numerical types but you get "0" as a string / []byte.

[xaprb]

I don't quite understand how the driver actually scans, but instead of
setting dest[i] to something, what if the driver just skips setting
dest[i]?

[julienschmidt]

dest is basically an []interface{} slice. The default value of interface{} is nil. So skip setting dest[i] has the same result as setting dest[i]=nil

I updated the branch. You can try it out for yourself if you want.

[xaprb]

That looks perfect for my needs, and I'll try it out next time I update our
copy of the driver.

[julienschmidt]

Another workaround came just to my mind:
Just use []byte instead of string. Converting a nil-[]byte results in an empty string:

string([]byte(""))  // => ""
string([]byte(nil)) // => ""

http://play.golang.org/p/nivY1yBK3x

[dryaf]

Maybe keep this open, maybe good for the Examples

Solution: https://github.com/guregu/null

[Dynom]

Since this page still shows up fairly high in search results, my two cents:

You can also solve it in the part where, imo, the problem actually lies: The database abstraction level. You can work around this problem by doing the following:

  SELECT
    id,
    COALESCE(name, '') as name
  FROM users

This way, if name contains NULL, it's substituted for an empty string when scanning. Coalesce is widely supported.

[mageddo]

I don't understand why did throws a exception when the string is null

[mageddo]

This example works for me

[kimiazhu]

You can pass a string pointer to deal with it like this:

var txt *string
checkErr(result.Scan(&txt))
// do something with type *string

it works fine for me.

[gonzalo-bulnes]

Besides @Dynom great comment, I think can still be worth mentioning that the sql.NullString type comes handy if you want to address the problem in the destination of the Scan (which I see as the Go side of the database abstraction level).

Additionally, it preserves the ability to distinguish between a nil value and an empty string.

[mageddo]

With nullstring, the dev is required to create a map vo if want to show the information like a json for example. No problem here, is a good and correct approach but it's not make sense for all cases then can be a additional work by a language limitation.

[haithamelmengad]

the issue with sql.NullString is all the additional boilerplate to unMarshal when interfacing with many different micro-services