Description John Manko 2011-05-20 12:21:57 EDT

Build Identifier: 2.2.0.v20110202-r8913

Let's say I have the following entity class and embedded-id classes:

@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.  

This appears to be a problem with using DISTINCT.  MS SQL Server 2000 doesn't complain with the resulting SQL statement, but MS SQL Seerver 2005 does.


Reproducible: Always

Steps to Reproduce:
Test with the following:

@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;
}

SELECT DISTINCT o FROM CityZip o WHERE o.cityZipPK.city = :city ORDER BY o.cityZipPK.state, o.cityZipPK.city, o.cityZipPK.zip