[hibernate-dev] Difference between Comment and Hint in Hibernate query

[Vlad Mihalcea]

I created a new Pull Request so that comments can be handled for named
queries (even for UPDATE/DELETE queries):

https://github.com/hibernate/hibernate-orm/pull/1970

I think we should add two new issues;

1. So that we could pass Query Hints for Named (Native) Queries as well.
Right now we can only pass comments which are appended at the beginning of
the SQL statement.
2. I see we support Query Hints for Oracle and SQL Server. We should
support MySQL as well:

https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

If you have time to take a look on the PR, let me know what you think.

Thanks

On Tue, Aug 1, 2017 at 4:28 PM, Steve Ebersole <steve at hibernate.org> wrote:

> They have different intentions and are potentially applied in different
> ways for different databases - hence separate.
>
> E.g. DB2 supports a query comment, but does not support optimizer hints...
> so these need to be handled differently
>
> On Tue, Aug 1, 2017, 8:19 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
> wrote:
>
>> I'm asking because the
>>
>> org.hibernate.annotations.NamedNativeQuery or
>> org.hibernate.annotations.NamedQuery
>>
>> define the comment attribute with the following Javadoc:
>>
>> /**
>>  * A comment added to the generated SQL query.  Useful when engaging with DBA.
>>  */
>> String comment() default "";
>>
>> So, Hibernate clients could use the comment attribute in order to supply
>> an Oracle query hint, right?
>>
>> In this case, should we treat this attribute as a query hint so that the
>> Oracle/SQL Server hint logic applies to this one as well?
>>
>> Vlad
>>
>> On Tue, Aug 1, 2017 at 4:07 PM, Steve Ebersole <steve at hibernate.org>
>> wrote:
>>
>>> Query hints are hints for the database' s query parser/optimizer.
>>>
>>> A comment is a... well a comment :)
>>>
>>> On Tue, Aug 1, 2017, 6:37 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> While working on integrating a Pull Request, I realized that the
>>>> org.hibernate.engine.spi.QueryParameters provides these two attributes:
>>>>
>>>> private String comment;
>>>> private List<String> queryHints;
>>>>
>>>> Both these two are to be sent to the database, so why do we have both?
>>>>
>>>> I also noticed that only for Query Hints we do take into consideration
>>>> DB
>>>> specific query hint syntax:
>>>>
>>>> // Keep this here, rather than moving to Select.  Some Dialects may
>>>> need the hint to be appended to the very
>>>> // end or beginning of the finalized SQL statement, so wait until
>>>> everything is processed.
>>>> if ( parameters.getQueryHints() != null &&
>>>> parameters.getQueryHints().size() > 0 ) {
>>>>    sql = dialect.getQueryHintString( sql, parameters.getQueryHints() );
>>>> }
>>>>
>>>> Shouldn't we only have either comment or queryHints? Or what is the
>>>> difference between these two?
>>>>
>>>> Vlad
>>>> _______________________________________________
>>>> hibernate-dev mailing list
>>>> hibernate-dev at lists.jboss.org
>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>
>>>
>>