Does anybody know which version this issue HHH-9576 was addressed in?
I’m using 5.3.7.Final and it’s still present where it doesn’t use a binding variable
for the numeric and the ticket says it was fixed in 5.2.13
Like everybody else this ends up being a hard parse in Oracle and a less efficient way to running the query together will filling up the statement cache because each and every query with a distinct value is seen as a different query.
The fix for this issue is part of the 5.3.7.Final release. Please post the entity model, query usage and generated SQL query. If you think this is a bug, please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-5/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) that reproduces the issue.
In my case I can see by looking at gv$sql that Oracle is seeing a literal value and not a binding variable whenever a numeric is used. If I have a query and I’m filtering on strings and numbers only the numbers show as literals in the eventual SQL query.
for where I use a string the sql query has :1 :2 :3 etc
where I have a number it has
field = 63.54000000
if I have time I’ll try to debug into hibernate to see why it’s doing this but do you have any pointers as to when to start looking?
FWIW in java the return value of the getter of the numeric attribute is a BigDecimal
I can also say that when constructing the query it’s using
builder.equals(key, val) where val is the BigDecimal
ValueHandlerFactory.render seems to just return a string with a “BD” suffix.
It’s pretty straight forward isn’t it since the RenderingContext is AUTO
and so therefore if it’s numeric it’s never going to get to bindLiteral since it returns earlier.
So it’s looking like I need to learn how to configure my session factory to use BIND LiteralHandingMode.
Puzzling as to why you have to opt into this but in any case it seems that if I use
spring.jpa.properties.hibernate.criteria.literal_handling_mode=BIND
my issue is addressed.
Defaulting to AUTO seems strange.
The way Hibernate 5 does things makes it hard to improve the situation. In Hibernate 6 this was properly addressed. The only way you will get literals rendered there is by using criteriaBuilder.literal(...) or configuring a property for the value handling mode.
Still, what is the rationale for opting into using binding variables for numerics?
I don’t know exactly why this was done this way in Hibernate 5 and before, but like I wrote, in Hibernate 6 values will by default be rendered as parameters. I can imagine that rendering as literals is useful for databases that do not support bind variable peeking like Oracle.
Can you elaborate on what you mean “do not support bind variable peeking?”
It seems to me that the whole thing was designed only around the concern of SQL injection and only if you know the two arguments are numeric do you have less fear but this completely ignores the overhead of any hard parse due to not using binding variables with Oracle.
If you use a query like select * from tbl where fk_col = 1 vs select * from tbl where fk_col = ? the optimizer might choose completely different plans because the selectivity estimate for a specific value is better than for a parameter, because an optimizer usually determines selectivity based on value distribution in case of parameters, whereas for specific values, it can rely on a histogram. This has severe effects on bigger queries.
If we always render parameters, you might not be happy with some DB because the optimizer keeps using a generic plan that has a wrong selectivity estimate. My guess is, that in Hibernate 5 numeric values were rendered as literals to circumvent this, which I think is reasonable because criteria queries are usually dynamic.
Can you elaborate on what you mean “do not support bind variable peeking?”
Oracle can generate optimized plans for a specific parameter/variable binding, so the selectivity mis-estimates due to the use of parameters aren’t that bad in the Oracle world.
Anyway, I agree that by default parameters should be rendered to get better statement cache hits and like I wrote, in Hibernate 6 this is fixed.