scanning an sql NULL into an int or string

[Brad Fitzpatrick]

Imagine you're doing an SQL select statement from a table containing NULL values.

rows, err := db.Query("SELECT name, age FROM people")

...

for rows.Next {

    var name string

    var age int

    err := rows.Scan(&name, &age)

    ...

}

The sql package defines NullableString (which implements sql.ScannerInto), if you want to know whether or not a column is null or not.

But what about code like above, where a user is scanning into a plain int and plain string?

Should a NULL:

a) return an error from Scan

b) embrace Go's zero value (assigning "" and 0, respectively) and silently carry on.

Thoughts?

[Kyle Lemons]

I was using a database library (which shall go unnamed) that crashed when I tried to do this; I had to explicitly COALESCE my columns if they could be NULL.  I think I would prefer the "" string, but I would have no issues with returning an error too.  However, I would want everything to proceed, so that I could choose to ignore the error if I didn't mind having my zero valued strings.

[Brad Fitzpatrick]

I don't follow your last sentence.

[Ian Lance Taylor]

Brad Fitzpatrick <brad...@golang.org> writes:

I believe alernative (b) would be best. The SQL package needs a way to
distinguish NULL from a zero value, and it sounds like you have provided
that. If the program chooses not to use that mechanism, I think a NULL
should become the zero value. I think that is more likely to be useful
in practice. In most databases in my experience, both a NULL and an
empty string mean "information not available."

BTW, I haven't looked at your code (sorry), but one way to implement
NULL would be

var name *string
var arg *int
err := rows.Scan(&name, &arg)

in which NULL sets the pointers to nil and values set them to point to
values. That would make it dead easy for the program to indicate
whether or not it cares about NULL.

Ian

[Brad Fitzpatrick]

That's kinda where I'm leaning, but could see both sides.  I didn't want to be the one to decide, though.  I'll let others weigh in before I send out a CL changing anything.

 

BTW, I haven't looked at your code (sorry), but one way to implement
NULL would be

       var name *string
       var arg *int
       err := rows.Scan(&name, &arg)

in which NULL sets the pointers to nil and values set them to point to
values.  That would make it dead easy for the program to indicate
whether or not it cares about NULL.

That could work.  Feels a bit proto-esque, but maybe that's good to be consistent.  If we already need the ScannerInto interface, though, I feel like the standard library should include users of it (struct NullableString { String string; Valid bool }).  I would worry about proliferation about NullableType (whereas your pointer proposal catches everything), but I don't think there will be more than a handful.

Another thing I could do is define:

package sql

// NotNull returns a ScannerInto destination that writes to dest, but

// raising an error if the source value is NULL.

func NotNull(dest interface{}) ScannerInto {

  ...

}

So then users could write, if desired:

for rows.Next {

    var name string

    var age int

    err := rows.Scan(sql.NotNull(&name), &age)

    ...

}

[Kyle Lemons]

Another thing I could do is define:

package sql

// NotNull returns a ScannerInto destination that writes to dest, but

// raising an error if the source value is NULL.

func NotNull(dest interface{}) ScannerInto {

  ...

}

So then users could write, if desired:

for rows.Next {

    var name string

    var age int

    err := rows.Scan(sql.NotNull(&name), &age)

    ...

}

+1 for sql.NotNull -- assuming my memory is correct in that it would return an error in err and not a panic (I think that's the way the ScannerInto interface works)

And my last sentence, rewritten:

If you were to choose an API which returns an error when it stores NULL into a non-pointer/non-interface type, I would expect that all other possible assignments would still be made.  This way, if I ignored the error (after inspecting it, of course), it would be as if the driver had simply stored the empty string in the NULL values and not returned the error at all.  I think there's some other instance of this (returning an error that is ignorable) in the standard library, though I can't for the life of me find it.

[Brad Fitzpatrick]

On Tue, Nov 15, 2011 at 5:00 PM, Kyle Lemons <kev...@google.com> wrote:

Another thing I could do is define:

package sql

// NotNull returns a ScannerInto destination that writes to dest, but

// raising an error if the source value is NULL.

func NotNull(dest interface{}) ScannerInto {

  ...

}

So then users could write, if desired:

for rows.Next {

    var name string

    var age int

    err := rows.Scan(sql.NotNull(&name), &age)

    ...

}

+1 for sql.NotNull -- assuming my memory is correct in that it would return an error in err and not a panic (I think that's the way the ScannerInto interface works)

And my last sentence, rewritten:

If you were to choose an API which returns an error when it stores NULL into a non-pointer/non-interface type, I would expect that all other possible assignments would still be made.  This way, if I ignored the error (after inspecting it, of course), it would be as if the driver had simply stored the empty string in the NULL values and not returned the error at all.

Ah.  Gotcha.  Scan could return sql.ErrNull, but you wouldn't necessarily know which column was null.  Seems a bit useless.  It could return a custom error type with a column index or name, but that seems like a pain to check for.

[Ian Lance Taylor]

Brad Fitzpatrick <brad...@golang.org> writes:

>> BTW, I haven't looked at your code (sorry), but one way to implement
>> NULL would be
>>
>> var name *string
>> var arg *int
>> err := rows.Scan(&name, &arg)
>>
>> in which NULL sets the pointers to nil and values set them to point to
>> values. That would make it dead easy for the program to indicate
>> whether or not it cares about NULL.
>>
>
> That could work. Feels a bit proto-esque, but maybe that's good to be
> consistent.

To be clear, I'm suggesting that you support both the above and also

var name string
var arg int
err := rows.Scan(&name, &arg)

Ian

[Gustavo Niemeyer]

>>> BTW, I haven't looked at your code (sorry), but one way to implement
>>> NULL would be
>>>
>>>        var name *string
>>>        var arg *int
>>>        err := rows.Scan(&name, &arg)

(...)

> To be clear, I'm suggesting that you support both the above and also
>
>       var name string
>       var arg int
>       err := rows.Scan(&name, &arg)

FWIW, that's how mgo works. People seem happy with it.

--
Gustavo Niemeyer
http://niemeyer.net
http://niemeyer.net/plus
http://niemeyer.net/twitter
http://niemeyer.net/blog

-- I'm not absolutely sure of anything.

[Brad Fitzpatrick]

I'm happy with that, if that doesn't trigger people's complexity thresholds.

So we'd have scan into T, *T, and ScannerInto.

[Brad Fitzpatrick]

On Tue, Nov 15, 2011 at 5:11 PM, Gustavo Niemeyer <gus...@niemeyer.net> wrote:

>>> BTW, I haven't looked at your code (sorry), but one way to implement
>>> NULL would be
>>>
>>>        var name *string
>>>        var arg *int
>>>        err := rows.Scan(&name, &arg)

(...)

> To be clear, I'm suggesting that you support both the above and also
>
>       var name string
>       var arg int
>       err := rows.Scan(&name, &arg)

FWIW, that's how mgo works. People seem happy with it.

 The *T stuff too? Where in the mgo or gobson docs is this described?

[Gustavo Niemeyer]

>  The *T stuff too? Where in the mgo or gobson docs is this described?

I don't have in depth documentation about that aspect, but the logic
is actually very similar to the standard json package. If a field has
a pointer type and it has a value in the unmarshalled document, the
pointer will be initialized:

http://play.golang.org/p/2uvaZ2p97S

There's one aspect that is different from json, though, which touches
the point you're bringing up. If the field type is not a pointer or an
interface and the document has a null value for such a field, json
errors out, while gobson does not. IMO, failing in these cases is not
very useful in practice. If the user intended to manage the null case
specially, there are means to do so.

[Daniel Theophanes]

I strongly dislike checking for nulls, so must of my table columns in the DB I arch are "not null default X".  However, there are times I'd like to easily check for one or more columns being null, independent of the rest of the data read.  The row level NotNull doesn't seam that useful.

I think returning default values in rows.Scan would be great (that's what I'd like to do 95% of the time).

For more granularity, you could also introduce a function called rows.Get(columnName string) (value interface{}, isNull bool). 

-Daniel

[Kyle Lemons]

>> BTW, I haven't looked at your code (sorry), but one way to implement
>> NULL would be
>>
>>        var name *string
>>        var arg *int
>>        err := rows.Scan(&name, &arg)
>>
>> in which NULL sets the pointers to nil and values set them to point to
>> values.  That would make it dead easy for the program to indicate
>> whether or not it cares about NULL.
>>
>
> That could work.  Feels a bit proto-esque, but maybe that's good to be
> consistent.

To be clear, I'm suggesting that you support both the above and also

      var name string
      var arg int
      err := rows.Scan(&name, &arg)

I'm happy with that, if that doesn't trigger people's complexity thresholds.

So we'd have scan into T, *T, and ScannerInto.

SGTM.  It's not much more complex than json.

[mattn]

If it is possible to pass interface{} to Scan(), I thinkg that it don't need to add code for *T.