pq and left join scan problems

[will]

Hi Gophers,

Here is a query with the pq postgres library that has odd behaviour ...

My querry,

select A.ID, A.name, A.surname, B.phone, B.fax

from employee A

LEFT JOIN addresses B

ON A.ID = B.ID

when i run this query and scan 

err = rows.Scan(&ID, &name, &surname, &phone, &fax)

It gives me the results for  A.ID, A.name, A.surname

but not for B.phone, B.fax

Please help

Will

[Kiki Sugiaman]

That is the property of left join for rows where the equality condition is not satisfied. Does it happen to all rows?

[will]

Hi Kiki,

When i run the SQL in the postgres terminal, the results all come out fine, but when using the golang pq library only the values from table A are seen for ALL rows.

[Gyu-Ho Lee]

If the query returns fine, you don't store the results in variables phone and fax correctly, I guess.

What are your lines before rows.Scan?

[will]

var rows *sql.Rows

    rows, err = stmt.Query()

    if err != nil {

        myResult = "db.Prepare error2: " + err.Error()

        return myResult, "error"

    }

    defer stmt.Close()

[Kiki Sugiaman]

What Gyu-Ho Lee said.

Additionally, you may want to use NullString, NullInt64, etc as your capture vars if you have not already. Since left join will return a lot of null columns on B side.

[Gyu-Ho Lee]

This still doesn't show how you defined Go variables in your code. If phone and fax were local variables inside for rows.Next() {
loop, let's suppose, they won't be referable outside for loop.

[will]

There are no null strings

[will]

var selectStatement string = `

select A.ID, A.name, A.surname, B.phone, B.fax

from employee A

LEFT JOIN addresses B

ON A.ID = B.ID

`

var stmt *sql.Stmt

var err error

stmt, err = db.Prepare(selectStatement)

if err != nil {

myResult = "db.Prepare error1: " + err.Error()

return myResult, "error"

}

var rows *sql.Rows

    rows, err = stmt.Query()

    if err != nil {

        myResult = "db.Prepare error2: " + err.Error()

        return myResult, "error"

    }

    defer stmt.Close()

var ID int

var name, surname, phone, fax string

for rows.Next() {

err = rows.Scan(&ID, &name, &surname, &phone, &fax)

if err != nil { 

myResult = "db.Prepare error 3: " + err.Error()

[Gyu-Ho Lee]

Try with []byte. They might be just empty strings, different than null.

[Kiki Sugiaman]

I understand that you may have 100% A.ID=B.ID match right now, but it won't always be the case (if you always do, you should merge the 2 tables). Left join will insert the null's by definition (from pg docs):

LEFT OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.

So using Null___ types as capture vars for your scan will prevent future bugs.

[will]

Lets assume i try and use NullString, How would this work, i tried the following and still got no values....

var phone sql.NullString

var Phone string

......

if phone.Valid {

// use s.String

Phone = phone.String

} else {

// NULL value

Phone = ""

[Kiki Sugiaman]

Yes, that's correct use of NullString.

The problem may lie somewhere else, but do keep the use of Null__ vars since it's improvement to the previous code.

Well, in that case good luck. I can't reproduce the problem on my side.

[will]

My only conclusion is the pq driver cant handle left joins

[Kiki Sugiaman]

The driver is query blind.

http://play.golang.org/p/-82Ovzcn3E

[will]

I figured out what was causing the odd behaviour,.

Apparently if there is any column you are calling that has a null value, you will have this strange behaviour if you do not use sql.NullString.

Thanks ksug and Gyu-Ho for your patience