Re: [eclipselink-users] @Column / @EmbeddedId generates invalid SQL for

[Tom Ware <tom.ware@xxxxxxxxxx>]

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