Skip to main content
[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 
Thanks for entering the bug.

There are two places to look for the handling of that distinct
1. Uur JPQL processing: look at org.eclipse.persistence.internal.jpa.parsing.SelectNode private variable "distinct"
2. Our query expression processing code: Look at org.eclipse.persistence.expressions.ExpressionOperator static variable "Distinct"
To tell which area is causing the problem is a matter of writing a query based on EclipseLink Expressions and seeing if the problem shows up when JPQL parsing is not used. 

-Tom

John Manko wrote:

Filed: https://bugs.eclipse.org/bugs/show_bug.cgi?id=346729
Tom, can you point me to the implementation class that handles DISTINCT and/or where the bug is? I'd like to write a short tech article (for my blog) analyzing the problem. I'm a geek like that. :) 

Thank you for helping me with this.

-John
On Fri, May 20, 2011 at 11:59 AM, Tom Ware <tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>> wrote: 

    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> <mailto: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>
        <mailto: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>
        <mailto: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 <mailto:eclipselink-users@xxxxxxxxxxx>
    https://dev.eclipse.org/mailman/listinfo/eclipselink-users




--
“If the American people ever allow private banks to control the issue of their currency, first by inflation, then by deflation, the banks...will deprive the people of all property until their children wake-up homeless on the continent their fathers conquered... The issuing power should be taken from the banks and restored to the people, to whom it properly belongs." -- Thomas Jefferson 


------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top