T_User id username 1 stliu 2 gail for example a native query sql "select v1.username, v2.username from T_User v1, T_User v2 where v1.id = '1' and v2.id = '2'" but the query returns ["stliu", "stliu"] instead of the expected ["stliu", "gail"] this is because hibernate uses column alias (in this case, both are "username") to get the result from ResultSet, and since the two result in RS are all keyed by "username" should we generate a alias for each like hql does? ------------------------- Best Regards, Strong Liu <stliu at hibernate.org> http://about.me/stliu/bio _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

I agree we should not do any magic on a native query; but following this reasoning just anything should be possible, and I wouldn't expect Hibernate to apply such magic to the results either. Why is it even looking at names in the resultset? As a user I'd want it to just return the same ordered sequence of values.

I would consider it very important to allow a full "jdbc fallback" experience, otherwise instead of being a nice tool it becomes an impediment and users will rightfully hate you. Sanne On 29 November 2011 15:25, Max Rydahl Andersen <max.andersen(a)redhat.com&gt; wrote: > the original idea of the native sql approach is to avoid/reduce doing anything magical with the query since > there is no way to fix these generally without a full sql parser. > > So I would say it works as expected. > > /max > > On Nov 29, 2011, at 11:59, Strong Liu wrote: > >> T_User >> id username >> 1 stliu >> 2 gail >> >> for example a native query sql "select v1.username, v2.username from T_User v1, T_User v2 where v1.id = '1' and v2.id = '2'" >> >> but the query returns ["stliu", "stliu"] instead of the expected ["stliu", "gail"] >> >> this is because hibernate uses column alias (in this case, both are "username") to get the result from ResultSet, and since the two result in RS are all keyed by "username" >> >> >> should we generate a alias for each like hql does? >> >> >> ------------------------- >> Best Regards, >> >> Strong Liu <stliu at hibernate.org> >> http://about.me/stliu/bio >> >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > /max > http://about.me/maxandersen > > > > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev >

Not sure when you sent this out, but I already fixed this (my) yeasterday. The only real option, IMO, is to throw an exception. As long as we are tied to pulling resultset values based on column name this is going to be a situation that we simply cannot reasonably support. Modifying the sql query is extremely difficult.

On Tue 29 Nov 2011 11:37:59 AM CST, Max Rydahl Andersen wrote: >> I agree we should not do any magic on a native query; but following >> this reasoning just anything should be possible, and I wouldn't expect >> Hibernate to apply such magic to the results either. >> Why is it even looking at names in the resultset? As a user I'd want >> it to just return the same ordered sequence of values. > > At the time of original implementation there wasn't any other way to do the mapping as I recall. > > I guess in this very special case (no scalars, nor entity mappings) adding on the query it could be handled differently. > > Note though that we need to ensure if you apply a result transformer to this query it will actually return something sensible > (i.e. named aliases for a map vs just indexes) > > The rearranging of hibernate core might allow for handling this now but I'm not uptodate on that. > > /max > >> I would consider it very important to allow a full "jdbc fallback" >> experience, otherwise instead of being a nice tool it becomes an >> impediment and users will rightfully hate you. >> >> Sanne >> >> On 29 November 2011 15:25, Max Rydahl Andersen&lt;max.andersen(a)redhat.com&gt; wrote: >>> the original idea of the native sql approach is to avoid/reduce doing anything magical with the query since >>> there is no way to fix these generally without a full sql parser. >>> >>> So I would say it works as expected. >>> >>> /max >>> >>> On Nov 29, 2011, at 11:59, Strong Liu wrote: >>> >>>> T_User >>>> id username >>>> 1 stliu >>>> 2 gail >>>> >>>> for example a native query sql "select v1.username, v2.username from T_User v1, T_User v2 where v1.id = '1' and v2.id = '2'" >>>> >>>> but the query returns ["stliu", "stliu"] instead of the expected ["stliu", "gail"] >>>> >>>> this is because hibernate uses column alias (in this case, both are "username") to get the result from ResultSet, and since the two result in RS are all keyed by "username" >>>> >>>> >>>> should we generate a alias for each like hql does? >>>> >>>> >>>> ------------------------- >>>> Best Regards, >>>> >>>> Strong Liu<stliu at hibernate.org> >>>> http://about.me/stliu/bio >>>> >>>> _______________________________________________ >>>> hibernate-dev mailing list >>>> hibernate-dev(a)lists.jboss.org >>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>> >>> /max >>> http://about.me/maxandersen >>> >>> >>> >>> >>> _______________________________________________ >>> hibernate-dev mailing list >>> hibernate-dev(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>> > > /max > http://about.me/maxandersen > > > > > _______________________________________________ > 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

Well a long term solution is to move to resultset value extraction based on position rather than name. Which I believe we should do, but it wont happen any time soon. That would actually fix this problem.

On Wed 30 Nov 2011 10:57:31 AM CST, Max Rydahl Andersen wrote: > > On Nov 30, 2011, at 15:22, Steve Ebersole wrote: > >> Not sure when you sent this out, but I already fixed this (my) yeasterday. The only real option, IMO, is to throw an exception. As long as we are tied to pulling resultset values based on column name this is going to be a situation that we simply cannot reasonably support. Modifying the sql query is extremely difficult. > > Agreed - seems you throw only when duplicated aliases and with auto discovery - that seems like the best option yes. > > Might break existing queries though - but thats better than the silent bug problem. > > /max > > >> >> >> >> On Tue 29 Nov 2011 11:37:59 AM CST, Max Rydahl Andersen wrote: >>>> I agree we should not do any magic on a native query; but following >>>> this reasoning just anything should be possible, and I wouldn't expect >>>> Hibernate to apply such magic to the results either. >>>> Why is it even looking at names in the resultset? As a user I'd want >>>> it to just return the same ordered sequence of values. >>> >>> At the time of original implementation there wasn't any other way to do the mapping as I recall. >>> >>> I guess in this very special case (no scalars, nor entity mappings) adding on the query it could be handled differently. >>> >>> Note though that we need to ensure if you apply a result transformer to this query it will actually return something sensible >>> (i.e. named aliases for a map vs just indexes) >>> >>> The rearranging of hibernate core might allow for handling this now but I'm not uptodate on that. >>> >>> /max >>> >>>> I would consider it very important to allow a full "jdbc fallback" >>>> experience, otherwise instead of being a nice tool it becomes an >>>> impediment and users will rightfully hate you. >>>> >>>> Sanne >>>> >>>> On 29 November 2011 15:25, Max Rydahl Andersen&lt;max.andersen(a)redhat.com&gt; wrote: >>>>> the original idea of the native sql approach is to avoid/reduce doing anything magical with the query since >>>>> there is no way to fix these generally without a full sql parser. >>>>> >>>>> So I would say it works as expected. >>>>> >>>>> /max >>>>> >>>>> On Nov 29, 2011, at 11:59, Strong Liu wrote: >>>>> >>>>>> T_User >>>>>> id username >>>>>> 1 stliu >>>>>> 2 gail >>>>>> >>>>>> for example a native query sql "select v1.username, v2.username from T_User v1, T_User v2 where v1.id = '1' and v2.id = '2'" >>>>>> >>>>>> but the query returns ["stliu", "stliu"] instead of the expected ["stliu", "gail"] >>>>>> >>>>>> this is because hibernate uses column alias (in this case, both are "username") to get the result from ResultSet, and since the two result in RS are all keyed by "username" >>>>>> >>>>>> >>>>>> should we generate a alias for each like hql does? >>>>>> >>>>>> >>>>>> ------------------------- >>>>>> Best Regards, >>>>>> >>>>>> Strong Liu<stliu at hibernate.org> >>>>>> http://about.me/stliu/bio >>>>>> >>>>>> _______________________________________________ >>>>>> hibernate-dev mailing list >>>>>> hibernate-dev(a)lists.jboss.org >>>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>>> >>>>> /max >>>>> http://about.me/maxandersen >>>>> >>>>> >>>>> >>>>> >>>>> _______________________________________________ >>>>> hibernate-dev mailing list >>>>> hibernate-dev(a)lists.jboss.org >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>>> >>> >>> /max >>> http://about.me/maxandersen >>> >>> >>> >>> >>> _______________________________________________ >>> 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 > > /max > http://about.me/maxandersen > > > -- steve(a)hibernate.org http://hibernate.org