[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
Re: [eclipselink-users] @Column / @EmbeddedId generates invalid SQL for Ms SQL Server 2005
|
Hi John,
It looks like this is a bug. Please enter a bug.
The issue is related to our processing of the "distinct" keyword. You can
workaround by not using "distinct".
-Tom
John Manko wrote:
Thanks for the response, Tom. Here is the version (from server.log):
[EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913)
-john
On Fri, May 20, 2011 at 10:28 AM, Tom Ware <tom.ware@xxxxxxxxxx
<mailto:tom.ware@xxxxxxxxxx>> wrote:
Hi John,
What version of EclipseLink are you running? I tried to reproduce
this on the latest and do not see the issue.
-Tom
John Manko wrote:
I'm trying to understand the intention of the JPA spec and
EclipseLink's implementation. The following arose from the
following Entity.
Let's say I have the following entity class and embedded-id class:
@Entity
public class CityZip implements Serializable {
@EmbeddedId
protected CityZipPK cityZipPK;
@Column
private String county;
}
@Embeddable
public class CityZipPK implements Serializable {
@Basic(optional = false)
@Column(name = "zip")
private String zip;
@Basic(optional = false)
@Column(name = "city")
private String city;
@Basic(optional = false)
@Column(name = "state")
private String state;
}
My JPA query is:
*SELECT DISTINCT o FROM CityZip o WHERE o.cityZipPK.city = :city
ORDER BY o.cityZipPK.state, o.cityZipPK.city, o.cityZipPK.zip*
Everything works fine in SQL Server 2000, but SQL Server 2005
throws the following error:
*Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'state'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'city'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'zip'.*
Tracing the query in SQL Manager reveals the following SQL
statement:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'SELECT
DISTINCT city, County, state, zip, state, city, zip FROM cityzip
WHERE (city = @P0) ORDER BY state ASC, city ASC, zip ASC
',N'New York'
select @p1*/
/*So, the problem is the repeat of city, state and zip in the
SELECT clause, causing the ORDER BY to get confused. I read
something about Compatibility Mode 80 versus 90 for MS SQL, but
I want to learn more about what should happen with this query.
Why does EclipseLink include the extra columns?
Side note: Is there an EclipseLink configuration available to
prevent this?
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users