Query or QueryRows does not return ErrNoRows error when no record is found

[cdong8812]

Describe the bug Query or QueryRow method does not return ErrNoRows error when using SQL left joins another table with where filter

To Reproduce Sample SQL to reproduce the bug:

query := SELECT f.id, f.name FROM foo f LEFT JOIN bar b ON f.id = b.foo_id WHERE f.id = 123

Expected behavior Query or QueryRow returns ErrNoRows error when no record is found

Actual behavior Query or QueryRow returns no error when no record is found

Version

• Go: 1.22.3
• PostgreSQL: 12.2
• pgx: v5.7.2

[jackc]

First, Query will never return ErrNoRows. A query returning no rows is a perfectly valid result. Only QueryRow has the expectation of a non-empty result.

Second, the fact that your query has a left join is irrelevant to pgx. It sends the query and reads the result. The actual type of query doesn't matter. I suggest trying your query in psql. It probably is not returning what you expect.

[silentstranger5]

=>  56:         if err == pgx.ErrNoRows {
    57:                 ...
    58:                 ...
    59:         }
    60:         ...

(dlv) p err == pgx.ErrNoRows
true

Okay, (typing n)

    56:         if err == pgx.ErrNoRows {
    57:                 ...
    58:                 ...
    59:         }
=>  60:         ...

Wait, WHAT?

UPD: This seem to work for now:

if err.Error() == pgx.ErrNoRows.Error() { ... }

But its slower, obviously.

[jackc]

@silentstranger5 Not sure what's going on from that little snippet -- but you should test errors with https://pkg.go.dev/errors#Is.

[cdong8812]

@jackc sorry for the the late response, I checked the implementation of Row and Rows interface, it looks like for both calling Scan() on Row vs Rows, and calling CollectRows() vs CollectOneRow() as the comment in the code recommends to use, pgx.ErrNoRows error is only returned for single row query, I'm not sure if this is by design or not, but from user perspective it'd be great to make it more consistent. In our use case, we rely on the error returned from the library to decide what error response to be returned in our API service so that we don't need to handle it differently say querying by single id or a list of ids in the code.

[jackc]

QueryRow and CollectOneRow are designed for when exactly one row is expected and it is an error if it is not found. Typically, this is find a single row by ID. But Query and CollectRows are designed for result sets with any number of rows, including zero. This pattern was taken from database/sql and is extremely unlikely to ever be changed, even if there as a new major release of pgx.

However, if you really need something like this then I suggest creating your own version of CollectRows. It should be trivial to adapt it to your own needs.

[cdong8812]

@jackc thanks for the detailed response, I'll close this issue for now as we don't expect there will be any major update in the near future.