Because of this setting, not refreshed SQL views results were used.
For example: When I opened add items modal on the invoice workflow and I add one item with its maximum available quantity and I open this modal a second time, the createCriteria is looking for items that are currently added to the invoice and were deleted from invoice_item_candidate view.
It was causing this error: No row with the given identifier exists

@alannadolny Interesting. I was seeing those "No row with the given identifier exists" errors at some point as well but had no idea what was causing them.

During your research did you find any articles or documentation that would help explain this? If so, please add what you've discovered to the ticket.

https://gorm.grails.org/6.0.x/hibernate/manual/#_caching_queries

I'm fine with disabling the query cache if that makes the most sense and is considered best practice.

However, I'm skeptical that the problem we're facing is solely due to the use_query_cache setting itself. My skepticism comes from the fact that the behavior should be considered a critical bug. So if this is what the query cache was intended to do, then no one would use it and it would be burned with fire. I haven't done much research on this yet, but I'm guessing this might have more to do with our lack of equals and hashCode for most domain classes. See https://pihemr.atlassian.net/browse/OBGM-341.

Some resources to review
https://blog.nareshak.com/effective-java-with-groovy-are-you-implementing-equals-and-hashcode-correctly/
https://stackoverflow.com/questions/29683592/should-i-implement-equals-and-hashcode-in-a-domain-class

So while we can turn this feature off for now, we should create a new spike ticket to research the actual problem in more depth, as well as gain a better understanding of the Hibernate Query Cache.

As part of that ticket please also provide some details on the different Hibernate cache settings. For example, what's the difference between these two settings

hibernate.cache.queries
hibernate.cache.use_query_cache

On top of the links provided above, my cursory search provided some very old results. I'm not sure if these are still relevant but they gave me a better understanding of what might be happening.

• https://schneide.blog/2012/08/29/grails-and-the-query-cache/
• https://www.ceva24.dev/posts/hibernate-grails-caching/
• https://stackoverflow.com/questions/26463189/how-to-cache-entire-table-or-results-of-domain-list-method-in-grails
• https://puredanger.github.io/tech.puredanger.com/2009/07/10/hibernate-query-cache/

@jmiranda as Alan is out today, I'll answer, because I advised Alan to set this property to false globally.
Alan discovered, that the view data is not refreshed and adding this:

cache(false)

to the createCriteria caused the query to be working fine.
What came immediately to my mind was this hibernate setting, as not a long time ago I was digging into that and read a bit about it. Setting this to false fixed the issue and we were scared that this could be a big bug going forward, we disabled this for now globally.

I know we'd expect that the caching should be enabled explicitly (it shouldn't be enabled by default in this case for createCriteria), but I think the reason for that might be how mapping in InvoiceItemCandidate is defined (3rd line):

static mapping = {
    id generator: 'uuid'
    version false
    cache usage: "read-only"
}

so maybe Hibernate/Grails automatically for domains defined like that, enables the caching (to be figured out).

I agree we should have a spike ticket regarding this caching (and I believe you made a comment in my hibernate document about that, to create the ticket).
For now this LGTM.

cc @alannadolny @awalkowiak @drodzewicz

@alannadolny Interesting. I was seeing those "No row with the given identifier exists" errors at some point as well but had no idea what was causing them.

During your research did you find any articles or documentation that would help explain this? If so, please add what you've discovered to the ticket.

https://gorm.grails.org/6.0.x/hibernate/manual/#_caching_queries

I'm fine with disabling the query cache if that makes the most sense and is considered best practice.

However, I'm skeptical that the problem we're facing is solely due to the use_query_cache setting itself. My skepticism comes from the fact that the behavior should be considered a critical bug. So if this is what the query cache was intended to do, then no one would use it and it would be burned with fire. I haven't done much research on this yet, but I'm guessing this might have more to do with our lack of equals and hashCode for most domain classes. See https://pihemr.atlassian.net/browse/OBGM-341.

Some resources to review
https://blog.nareshak.com/effective-java-with-groovy-are-you-implementing-equals-and-hashcode-correctly/
https://stackoverflow.com/questions/29683592/should-i-implement-equals-and-hashcode-in-a-domain-class

So while we can turn this feature off for now, we should create a new spike ticket to research the actual problem in more depth, as well as gain a better understanding of the Hibernate Query Cache.

As part of that ticket please also provide some details on the different Hibernate cache settings. For example, what's the difference between these two settings

hibernate.cache.queries
hibernate.cache.use_query_cache

On top of the links provided above, my cursory search provided some very old results. I'm not sure if these are still relevant but they gave me a better understanding of what might be happening.

• https://schneide.blog/2012/08/29/grails-and-the-query-cache/
• https://www.ceva24.dev/posts/hibernate-grails-caching/
• https://stackoverflow.com/questions/26463189/how-to-cache-entire-table-or-results-of-domain-list-method-in-grails
• https://puredanger.github.io/tech.puredanger.com/2009/07/10/hibernate-query-cache/

@jmiranda as Alan is out today, I'll answer, because I advised Alan to set this property to false globally.
Alan discovered, that the view data is not refreshed and adding this:

cache(false)

to the createCriteria caused the query to be working fine.
What came immediately to my mind was this hibernate setting, as not a long time ago I was digging into that and read a bit about it. Setting this to false fixed the issue and we were scared that this could be a big bug going forward, we disabled this for now globally.

I know we'd expect that the caching should be enabled explicitly (it shouldn't be enabled by default in this case for createCriteria), but I think the reason for that might be how mapping in InvoiceItemCandidate is defined (3rd line):

static mapping = {
    id generator: 'uuid'
    version false
    cache usage: "read-only"
}

so maybe Hibernate/Grails automatically for domains defined like that, enables the caching (to be figured out).

I agree we should have a spike ticket regarding this caching (and I believe you made a comment in my hibernate document about that, to create the ticket).
For now this LGTM.

cc @alannadolny @awalkowiak @drodzewicz