Error returning "null" on QueryRow (INSERT RETURNING)

[ghost]

After some testing I ended up with this situation: When I use "db.Exec" to insert boolean values in database, everything works just fine. But, if I use QueryRow (in this case, I am trying to return some data, so that's why I need QueryRow) it fails. The problem is that it only fails when I send "false". If I send "true", it works.

Ex: db.Exec("insert into table(my_bool) values (false)") -> OK db.Exec("insert into table(my_bool) values (true)") -> OK db.QueryRow("insert into table(my_bool) values (true) returning id").scan(&id) -> OK db.QueryRow("insert into table(my_bool) values (false) returning id").scan(&id) -> FAIL

I am using GO 1.9 and Firebird 3 btw. Thanks :)

[nakagami]

I want exact create table statement and exact insert statement (not pseudo code)

insert into table(my_bool) values(false) is not correct SQL statement.

And I don't realize local variable id 's type with that code.

So, show me working code.

[ghost]

Sorry for not posting a "real code" for you. Anyway, I tested again and the problem is not related to boolean type, but is related to some "null" returns in QueryRow.

Cuz u asked nicely, here goes an example:

sqlCreate := `
    CREATE TABLE person (
        id INTEGER generated by default as identity primary key,
        name VARCHAR(60) NOT NULL,
        is_polite BOOLEAN,
        created TIMESTAMP
    )
`
dbconfig.DBConn.Exec(sqlCreate)

type response struct {
    name string
    isPolite *bool
    created time.Time
}
r := response{}

// WORKS
sqlTest1 := `
    insert into person (name, created)
    values ('Giovanni', current_timestamp)
`
dbconfig.DBConn.Exec(sqlTest1)
sqlTest1 = `
    select name, is_polite, created from person
    where id = 1
`
dbconfig.DBConn.QueryRow(sqlTest1).Scan(&r.name, &r.isPolite, &r.created)
fmt.Println(r.name, r.isPolite, r.created)

sqlTest1 = `
    insert into person (name, is_polite, created)
    values ('Giovanni Gaspar', true, current_timestamp)
    returning name, is_polite, created
`
dbconfig.DBConn.QueryRow(sqlTest1).Scan(&r.name, &r.isPolite, &r.created)
fmt.Println(r.name, r.isPolite, r.created)

// FAILS
sqlTest2 := `
    insert into person (name, is_polite)
    values ('Nakagami', false)
    returning name, is_polite, created
`
if err := dbconfig.DBConn.QueryRow(sqlTest2).Scan(
    &r.name, &r.isPolite, &r.created);
err != nil {
    fmt.Println(err)
} else {
    fmt.Println(r.name, r.isPolite, r.created)
}

The two first examples work just fine, but the second one fails. The problem just occurs when I insert something returning null. BTW, I have the same SQL running on Python and it's working just fine...

Well, I did some research about it and found out that GO has some workarounds for it, but just for some kinds of vars like String, Int, Float and Boolean... But not for timestamps. Example of a working second test:

type response struct {
    name string
    isPolite sql.NullBool // LOOK HERE
    created time.Time
}
r := response{}
sqlTest2 := `
    insert into person (name, is_polite)
    values ('Nakagami', false)
    returning name, is_polite
`
if err := dbconfig.DBConn.QueryRow(sqlTest2).Scan(
    &r.name, &r.isPolite);
err != nil {
    fmt.Println(err)
} else {
    fmt.Println(r.name, r.isPolite.Value)
}

I removed the timestamp from the return, but the boolean is working cuz it was declared as a NullBool. Using a null pointer to the var for "SELECTS" works, but that's not the case for "INSERT RETURNING"

Usually people do something like this in the driver's code:

type NullTime struct {
    Time  time.Time
    Valid bool // Valid is true if Time is not NULL
}

// Scan implements the Scanner interface.
func (nt *NullTime) Scan(value interface{}) error {
    nt.Time, nt.Valid = value.(time.Time)
    return nil
}

// Value implements the driver Valuer interface.
func (nt NullTime) Value() (driver.Value, error) {
    if !nt.Valid {
        return nil, nil
    }
    return nt.Time, nil
}

Thank you for ur time and good work! :)