Parameter Items Wrong Order - SQL Server

Description

Relating to bug HHH-10991. This solved an issue where the order was not correct. However, because it uses the limit function in the internal test case and also works fine on MySQL it does not work if you limit it on SQL Server as it puts the TOP() function on the query, and this throws out the parameters as it misses this.

A simple test case is:

This WILL work under your test environment and also on MySQL. Only when you use SQL Server does this fail because of the additional TOP() function at the start instead of at the end.

The issue is caused by adding the filter and the filter changes the parameters order. But you can't have a filter on an inner query only. If you could then it would probably be fine.

Environment

None

Activity

Show:
Gail Badner
October 12, 2016, 1:05 AM

Is this related to, or a duplicate of, HHH-11168?

Mathew Johnson
October 12, 2016, 9:18 AM

I'd say related to however they both seem to be issues with the dialect and how it formats the SQL.

Incidently, this issues arrises using it on paged data. If you do the old trick of

Then it does appear to work. It's like the first time it runs it doesn't get the paged items

Andrea Boriero
January 16, 2017, 10:39 PM

Hi ,

I tried your test with SQL Server and it looks fine, the log is

select
TOP this_.id1 as id1_1_0_,
this_.id3 as id2_1_0_,
this_.id4 as id3_1_0_,
this_.city as city4_1_0_,
this_.street as street5_1_0_,
this_.age as age6_1_0_,
this_.name as name7_1_0_,
this_.organisationUnit_id as organis10_1_0_,
this_.STATUS as STATUS8_1_0_,
this_.theId as theId9_1_0_
from
STUDENT this_
where
this_.STATUS = ?
and (
this_.organisationUnit_id IN (
?, ?
)
OR this_.organisationUnit_id IS NULL
)
and this_.theId in (
select
distinct s_.theId as y0_
from
STUDENT s_
where
s_.STATUS = ?
and (
s_.organisationUnit_id IN (
?, ?
)
OR s_.organisationUnit_id IS NULL
)
)
22:16:24,326 TRACE BasicBinder:65 - binding parameter [2] as [VARCHAR] - [active]
22:16:24,327 TRACE BasicBinder:65 - binding parameter [3] as [INTEGER] - [1]
22:16:24,327 TRACE BasicBinder:65 - binding parameter [4] as [INTEGER] - [2]
22:16:24,327 TRACE BasicBinder:65 - binding parameter [5] as [VARCHAR] - [active]
22:16:24,328 TRACE BasicBinder:65 - binding parameter [6] as [INTEGER] - [1]
22:16:24,328 TRACE BasicBinder:65 - binding parameter [7] as [INTEGER] - [2]

and as you can see the first binding parameter has index 2, the binding of the TOP is done by SQLServer2005LimitHandler#bindLimitParametersAtStartOfQuery() and simply is not logged.

Assignee

Andrea Boriero

Reporter

Mathew Johnson

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure