Gorm nil error returned instead of record not found with Raw()

GORM Playground Link

https://github.com/go-gorm/playground/pull/334

Description

AFAICT, GORM doesn't return record not found with Raw() when the resulting query has no result to return.

The playground link above covers the test scenario of this behavior but a real-world example we run in prod is:

SELECT *
FROM users
WHERE name = ?
LIMIT 1;

The way this query is executed in code looks like:

    err := db.Raw(`SELECT * FROM users WHERE name = ? LIMIT 1;`, "foobar").Scan(myUser).Error

It is my belief that we should be able to run the above code and then check if it contains gorm.ErrRecordNotFound:

    if errors.Is(err, gorm.ErrRecordNotFound) {
        // handle the record not being found
    }

Currently, we're seeing err is set to nil even though there are no results returned from the query.

This expectation comes from our migration efforts from V1 to V2.

GORM previously returned record not found when a query was ran and returned with no result.

Thank you in advance!

Comment From: jbrockopp

After some trial and error, we've been able to find a workaround for the above issue.

This workaround involves inspecting the RowsAffected field in the returning query.

To accomplish this, we have to modify the original code:

    err := db.Raw(`SELECT * FROM users WHERE name = ? LIMIT 1;`, "foobar").Scan(myUser).Error
    if errors.Is(err, gorm.ErrRecordNotFound) {
        // handle the record not being found
    }

To now manually check this field like the below code shows:

    result := db.Raw(`SELECT * FROM users WHERE name = ? LIMIT 1;`, "foobar").Scan(myUser)
    if errors.Is(result.Error, gorm.ErrRecordNotFound) || result.RowsAffected == 0 {
        // handle the record not being found
    }

Comment From: jinzhu

only First, Last, Take methods returns ErrRecordNotFound.

Comment From: jbrockopp

@jinzhu I don't agree with that implementation, but I understand.

Is that in the the GORM documentation somewhere?

I see no mention of this change in behavior which I think is important for customers migrating from V1 -> V2.

Comment From: jinzhu

https://gorm.io/docs/v2_release_note.html#ErrRecordNotFound

Comment From: antonpodkur

only First, Last, Take methods returns ErrRecordNotFound.

Can you please explain why. For example, I want to find a user by email using Find method. But it does not say that user is not found or there are no users with such email and it does not even set object to nil but initialises it with default values. So even if there is no user with such email (for example) it will still be initialised and I will have no errors.