Hi,

I'm using jpa and hibernate.
I have the following error message :

Column 'id' in where clause is ambiguous

It's caused by a bad sql generated.

I've got two tables like this :
Table Entity (id, field1, field2)
Table Chapter (id, name)

My mapping :

@MappedSuperclass
public class TemporalBehavior implements Serializable{

// mapping field1
// mapping field2 ...
//
}


@Entity
@Table(name = "entity")
@Inheritance(strategy=InheritanceType.JOINED)
public abstract class EntityAff extends TemporalBehavior implements Nameable, Serializable {

/**
*
*/
private static final long serialVersionUID = 6883572560586578684L;

protected final Log logger = LogFactory.getLog(getClass());

protected Integer id;


public EntityAffiliation() {
}


@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id", unique = true, nullable = false)
public Integer getId() {
return this.id;
}

public void setId(Integer id) {
this.id = id;
}

}

@Entity
@Indexed
@Table(name="chapter")
@PrimaryKeyJoinColumn(name="id")
public class Chapter extends EntityAff {

/**
*
*/
private static final long serialVersionUID = -6912952785079360312L;

@Field(index=Index.TOKENIZED, store=Store.NO)
@NotNull
@Size(min=1 , max=100)
protected String name;


public Chapter() {
}

public Chapter(String name) {
...
}

@Column(name="name", nullable=false, length=100)
public String getName() {
return this.name;
}

public void setName(String name) {
this.name = name;
}
}


When I'm executing the following query :

String query = "DELETE Chapter o WHERE o.id IN (:ids)";
return em.createQuery(query).setParameter("ids",ids).executeUpdate();

I obtain sql generated :
Hibernate:
insert
into
HT_chapter
select
chapter0_.id as id_
from
chapter chapter0_
inner join
entity chapter0_1_
on chapter0_.id=chapter0_1_.id
where
id in (
? , ?
)

And obviously this error message : "Column 'id' in where clause is ambiguous"
Because 'id' in the where clause can reference table entity or table chapter

The good generated sql should be :
...
where
chapter0_.id in (
? , ?
)
Do you see an error in my mapping ? Or do see any solution to this problem ?

Thanks much

Nicolas

hi, thanks for reporting that.
Please add, which version of Hibernate? Could you try the latest one and check for duplicate issues on JIRA ?

Hi and thanks for your message.

I use the last version of Hibernate : 3.6.0.Final

And you're right it's a bug which has already been reported : http://opensource.atlassian.com/project ... e/HHH-1657
It has not yet been resolved but I found this solution to my problem :
In the table Chapter I rename 'id' to 'entity_id' in order to have different primary key names in tables EntityAff and Chapter.
Then I changed the @PrimaryKeyJoinColumn annotation over the class Chapter :

@Entity
@Indexed
@Table(name="chapter")
@PrimaryKeyJoinColumn(name="entity_id")
public class Chapter extends EntityAff {
...
}

These changes resolve the problem I quoted.