Hi all! Recently I had a closer look at HHH-1123 issue. This bug affects both - Criteria API and HQL. I have introduced Dialect#maximumInExpressionElements() method which returns maximum number of allowed elements in a single SQL IN clause, or null treated as infinite. The change of InExpression was very easy. However, fixing this bug for HQL queries requires modification of ParameterMetadata (namedDescriptorMap cannot remain unmodifiable), as well as AbstractQueryImpl (queryString). As I don't see any other solution, I wanted to ask you guys for suggestions. Is it the only possible way of fixing this issue? Finally, shall we really fix this? This is a DB vendor limitation, but 40 user gave their vote for it. Regards, Lukasz Antoniak _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Ah good point, I haven't thought of that problem with query splitting On 30 nov. 2011, at 22:20, Steve Ebersole wrote: > Splitting is not always an option. Consider a predicate like: > > ... where a in (x1, ... x2000) and b in (y1, ... y2000) ... > > If you split this up, you will have misses. Yes, it works if you can keep it all in one query because you can structure it to maintain the original semantics. However, please read the comments on that JIRA issue. For some databases, this restriction is not just on the number of elements in a in-list, but on the number of parameters overall. Splitting these 2 in-lists about into 4 does not address that. > > I commented on the issue that I am actually inclined to simply reject this one. In fact, I thought we already did. Maybe that was another earlier one? > > > On Wed 30 Nov 2011 04:45:55 AM CST, Emmanuel Bernard wrote: >> Also note that there is a limit for the query size globally in some vendors and that people relieved from HHH-1123 cal fall into the second limit. >> A solution would be for Hibernate to split one query into several but I'm not sure I like the idea. >> >> Emmanuel >> >> On 29 nov. 2011, at 21:29, Łukasz Antoniak wrote: >> >>> Hi all! >>> >>> Recently I had a closer look at HHH-1123 issue. This bug affects both - >>> Criteria API and HQL. I have introduced >>> Dialect#maximumInExpressionElements() method which returns maximum >>> number of allowed elements in a single SQL IN clause, or null treated as >>> infinite. The change of InExpression was very easy. However, fixing this >>> bug for HQL queries requires modification of ParameterMetadata >>> (namedDescriptorMap cannot remain unmodifiable), as well as >>> AbstractQueryImpl (queryString). As I don't see any other solution, I >>> wanted to ask you guys for suggestions. Is it the only possible way of >>> fixing this issue? Finally, shall we really fix this? This is a DB >>> vendor limitation, but 40 user gave their vote for it. >>> >>> Regards, >>> Lukasz Antoniak >>> _______________________________________________ >>> hibernate-dev mailing list >>> hibernate-dev(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > -- > steve(a)hibernate.org > http://hibernate.org _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

> One technical (and probably way out of scope!) way to handle this would > be to use a temp table, do a batch insert of the values, then change the > " in (v1, v2, v3...)" to " in (select v from temp)". I think I would rather hear people complain about query exceptions happening when they are doing something fundamentally broken with a database than seeing them realize their test queries working as expected and then when they go to production with enough data to pass the 1000 elements limit their read only queries suddenly are trashing the temp table space and their user need to be able to have create table permissions. /max > > > On 12/01/2011 04:20 AM, Emmanuel Bernard wrote: >> Ah good point, I haven't thought of that problem with query splitting >> >> On 30 nov. 2011, at 22:20, Steve Ebersole wrote: >> >>> Splitting is not always an option. Consider a predicate like: >>> >>> ... where a in (x1, ... x2000) and b in (y1, ... y2000) ... >>> >>> If you split this up, you will have misses. Yes, it works if you can keep it all in one query because you can structure it to maintain the original semantics. However, please read the comments on that JIRA issue. For some databases, this restriction is not just on the number of elements in a in-list, but on the number of parameters overall. Splitting these 2 in-lists about into 4 does not address that. >>> >>> I commented on the issue that I am actually inclined to simply reject this one. In fact, I thought we already did. Maybe that was another earlier one? >>> >>> >>> On Wed 30 Nov 2011 04:45:55 AM CST, Emmanuel Bernard wrote: >>>> Also note that there is a limit for the query size globally in some vendors and that people relieved from HHH-1123 cal fall into the second limit. >>>> A solution would be for Hibernate to split one query into several but I'm not sure I like the idea. >>>> >>>> Emmanuel >>>> >>>> On 29 nov. 2011, at 21:29, Łukasz Antoniak wrote: >>>> >>>>> Hi all! >>>>> >>>>> Recently I had a closer look at HHH-1123 issue. This bug affects both - >>>>> Criteria API and HQL. I have introduced >>>>> Dialect#maximumInExpressionElements() method which returns maximum >>>>> number of allowed elements in a single SQL IN clause, or null treated as >>>>> infinite. The change of InExpression was very easy. However, fixing this >>>>> bug for HQL queries requires modification of ParameterMetadata >>>>> (namedDescriptorMap cannot remain unmodifiable), as well as >>>>> AbstractQueryImpl (queryString). As I don't see any other solution, I >>>>> wanted to ask you guys for suggestions. Is it the only possible way of >>>>> fixing this issue? Finally, shall we really fix this? This is a DB >>>>> vendor limitation, but 40 user gave their vote for it. >>>>> >>>>> Regards, >>>>> Lukasz Antoniak >>>>> _______________________________________________ >>>>> hibernate-dev mailing list >>>>> hibernate-dev(a)lists.jboss.org >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> _______________________________________________ >>>> hibernate-dev mailing list >>>> hibernate-dev(a)lists.jboss.org >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>> -- >>> steve(a)hibernate.org >>> http://hibernate.org >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev /max http://about.me/maxandersen

On Mon 05 Dec 2011 12:59:19 PM CST, David Mansfield wrote: > While I do agree wholeheartedly with most of what you say, I do think > it's unfair to say that the requirement is "fundamentally broken". That > label should be reserved for SQL itself ;-) > > If there is some opaque business rule R that operates on a large set of > input data, and that rule is implemented in Java, using Hibernate to get > the input data, it's quite possible that the rule generates a large list > of entities to retrieve. Is there a better way to batch fetch a > s***load of specific entities, given a List or Set of identifiers? for ( Long identifier : setOrListOfIdentifiers ) { session.get( YourEntity.class, identifier ); }

While I do agree wholeheartedly with most of what you say, I do think it's unfair to say that the requirement is "fundamentally broken". That label should be reserved for SQL itself ;-)

If there is some opaque business rule R that operates on a large set of input data, and that rule is implemented in Java, using Hibernate to get the input data, it's quite possible that the rule generates a large list of entities to retrieve. Is there a better way to batch fetch a s***load of specific entities, given a List or Set of identifiers?

But I DO think it's actually easier on the temp segment than to not use it, in most cases.

David On 12/03/2011 05:23 AM, Max Rydahl Andersen wrote: >> One technical (and probably way out of scope!) way to handle this would >> be to use a temp table, do a batch insert of the values, then change the >> " in (v1, v2, v3...)" to " in (select v from temp)". > I think I would rather hear people complain about query exceptions happening when > they are doing something fundamentally broken with a database than seeing them realize their test > queries working as expected and then when they go to production with enough data to pass the 1000 elements limit > their read only queries suddenly are trashing the temp table space and their user need to be able to have create table permissions. > > /max > > > >> >> >> On 12/01/2011 04:20 AM, Emmanuel Bernard wrote: >>> Ah good point, I haven't thought of that problem with query splitting >>> >>> On 30 nov. 2011, at 22:20, Steve Ebersole wrote: >>> >>>> Splitting is not always an option. Consider a predicate like: >>>> >>>> ... where a in (x1, ... x2000) and b in (y1, ... y2000) ... >>>> >>>> If you split this up, you will have misses. Yes, it works if you can keep it all in one query because you can structure it to maintain the original semantics. However, please read the comments on that JIRA issue. For some databases, this restriction is not just on the number of elements in a in-list, but on the number of parameters overall. Splitting these 2 in-lists about into 4 does not address that. >>>> >>>> I commented on the issue that I am actually inclined to simply reject this one. In fact, I thought we already did. Maybe that was another earlier one? >>>> >>>> >>>> On Wed 30 Nov 2011 04:45:55 AM CST, Emmanuel Bernard wrote: >>>>> Also note that there is a limit for the query size globally in some vendors and that people relieved from HHH-1123 cal fall into the second limit. >>>>> A solution would be for Hibernate to split one query into several but I'm not sure I like the idea. >>>>> >>>>> Emmanuel >>>>> >>>>> On 29 nov. 2011, at 21:29, Łukasz Antoniak wrote: >>>>> >>>>>> Hi all! >>>>>> >>>>>> Recently I had a closer look at HHH-1123 issue. This bug affects both - >>>>>> Criteria API and HQL. I have introduced >>>>>> Dialect#maximumInExpressionElements() method which returns maximum >>>>>> number of allowed elements in a single SQL IN clause, or null treated as >>>>>> infinite. The change of InExpression was very easy. However, fixing this >>>>>> bug for HQL queries requires modification of ParameterMetadata >>>>>> (namedDescriptorMap cannot remain unmodifiable), as well as >>>>>> AbstractQueryImpl (queryString). As I don't see any other solution, I >>>>>> wanted to ask you guys for suggestions. Is it the only possible way of >>>>>> fixing this issue? Finally, shall we really fix this? This is a DB >>>>>> vendor limitation, but 40 user gave their vote for it. >>>>>> >>>>>> Regards, >>>>>> Lukasz Antoniak >>>>>> _______________________________________________ >>>>>> hibernate-dev mailing list >>>>>> hibernate-dev(a)lists.jboss.org >>>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>>> _______________________________________________ >>>>> hibernate-dev mailing list >>>>> hibernate-dev(a)lists.jboss.org >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> -- >>>> steve(a)hibernate.org >>>> http://hibernate.org >>> _______________________________________________ >>> hibernate-dev mailing list >>> hibernate-dev(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > /max > http://about.me/maxandersen > > >