NULL value causes #2000 Unknown MySQL error

[dtjm]

I'm having a problem wherein I have an integer field defined but set to NULL, I get this error when calling Statement.Fetch() on the prepared statement:

    #2000 Unknown MySQL error%!(EXTRA string=
    )

If I go back and replace NULL with a value, I get no error.

This code reproduces it: https://gist.github.com/1007214

[Philio]

Thanks for the report, I'll have a look at this over the weekend.

[jimrobinson]

I've found myself having to wrap any columns that may potentially contain NULLs with a COALESCE(), to work around this problem.

[edwardrf]

Hi dtjm, Philio,

After debugging into the code dtjm provided, I realize it is a 2 part problem, the first part is very easy to solve, but the 2nd part is really difficult, which may touch some fundamental stuff of the project to solve.

First problem is actuallly in handler.go line 231, after the binary handler determined the next value is nil, though assigned nil to field, but field is never added to the row slice. So you need to add an append to the row: "row = append(row, field)"

        if nbm[posByte]&(1<<uint8(posBit)) != 0 {
            field = nil
            row = append(row, field)
            continue
        }

However, this brings up the 2nd issue. The statement auto converter is unable to convert nil to any value, in fact, if i am not wrong, there is no way you can assign nil to a primitive variable in go. So basically we are left with 3 choices which are all not very correct:

1. Convert nil to 0. But NULL is totally different from 0 in the DB, there is a lost of information with this method
2. For primitive types, we need to pass in a pointer to the primitive type, which can be assigned nil, this would results in a really hard to use API
3. Create Types for the mysql types which nil has been taken into consideration.