Hibernate version: 3.26

Name and version of the database you are using: MySQL 5.0.22

This is my first Hibernate forum help request. I have done a search of the FAQ and the general documentation and a preliminary search on the forums, but if I missed something I'm happy to be told, "It's there -- go find it."

We have an object whose data are mapped to three tables, the two subsidiary tables linked to the primary key of the main table. We currently have:

<class name="MetricRecord"
table="MetricRecord"
node="MetricRecord" lazy="false">

<id name="RecordId" type="integer" column="dbid">
<generator class="native"/>
</id>
<!-- ... -->
<join table="MetricRecord_Meta">
<key column="dbid"/>
<!-- ... -->
</join>
<join table="MetricRecord_Xml" optional="true">
<key column="dbid"/>
<property name="ExtraXml" type="text"/>
<property name="RawXml" type="text" />
</join>
</class>

You may already see the problem coming up -- we have columns MetricRecord.dbid (primary Id field), MetricRecord_Meta.dbid (foreign key linking to MetricRecord.dbid) and MetricRecord_Xml.dbid (ditto). This doesn't cause a problem most of the time, but we recently tried to do an expired record cleanup as, (HQL):

select record.id from MetricRecord record where Timestamp.Value < :dateLimit and ServerDate < :dateLimit

followed by:

delete MetricRecord where RecordId in ( :ids )

As you might guess, we got:

org.hibernate.exception.ConstraintViolationException: could not insert/select ids for bulk delete
<snip>
Caused by:
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column 'dbid' in where clause is ambiguous
<snip>

during the delete phase.

Is there some way to change the <id> or <generator> clause to specify the table whenever raw SQL is generated that refers to the primary key field and not the identically named foreign key field in the subsidiary tables? Alternatively, is there something else I could do to achieve the same effect?

My initial searches turned up a promisingly similar request to specify the schema name using a <param name="sequence">...</param> within <generator> but this was for an ID where the generator was "sequence". Since we are using MySQL, the generator would be identity (or native), and the same clause does not appear to work.

We also don't have the option of renaming column, as much as that would bypass the problem.

Help, imprecations to re-read some doc or post I may have misunderstood or just plain missed, etc, welcome.

Thanks,
Chris Green.

Quick test: have you tried to add an alias to the table you are deleting from? Something like:

Hi,

Thanks for your prompt response. I'm afraid doing as you suggested did not change the outcome in any way, as the three tables automatically appear in the raw SQL delete statement since they all three make up the hibernate entity, "MetricRecord." Hence in order to distinguish the primary key the SQL needs to specify MetricRecord.dbid in the raw SQL of the delete statement. I hope to post the debug SQL from hibernate shortly but I wanted to get a preliminary reply to you before you left for the weekend and my home connection isn't optimal (thank you Comcast) for X communication.

Thanks again,
Chris.

After turning on debug SQL statements in the log, and inserting backticks into the identifier list, we get the same error. SQL debug follows (the first two log entries relate to the query to obtain the list of IDs, and the last to to the attempt to delete using them.

2008-08-08 09:47:57,525 org.hibernate.SQL(Thread-15) [DEBUG]:
/* select
record.id
from
MetricRecord record
where
Timestamp.Value < :dateLimit
and ServerDate < :dateLimit */ select
metricreco0_.`dbid` as col_0_0_
from
MetricRecord metricreco0_
inner join
MetricRecord_Meta metricreco0_1_
on metricreco0_.`dbid`=metricreco0_1_.dbid
left outer join
MetricRecord_Xml metricreco0_2_
on metricreco0_.`dbid`=metricreco0_2_.dbid
where
metricreco0_.Timestamp<?
and metricreco0_1_.ServerDate<? limit ?
2008-08-08 09:47:57,525 org.hibernate.SQL(Thread-15) [DEBUG]:
/* select
record.id
from
MetricRecord record
where
Timestamp.Value < :dateLimit
and ServerDate < :dateLimit */ select
metricreco0_.`dbid` as col_0_0_
from
MetricRecord metricreco0_
inner join
MetricRecord_Meta metricreco0_1_
on metricreco0_.`dbid`=metricreco0_1_.dbid
left outer join
MetricRecord_Xml metricreco0_2_
on metricreco0_.`dbid`=metricreco0_2_.dbid
where
metricreco0_.Timestamp<?
and metricreco0_1_.ServerDate<? limit ?
2008-08-08 09:47:57,831 org.hibernate.SQL(Thread-15) [DEBUG]:
/* insert-
select
for net.sf.gratia.storage.MetricRecord ids */ insert
into
HT_MetricRecord
select
metricreco0_.`dbid` as `dbid`
from
MetricRecord metricreco0_
inner join
MetricRecord_Meta metricreco0_1_
on metricreco0_.`dbid`=metricreco0_1_.dbid
left outer join
MetricRecord_Xml metricreco0_2_
on metricreco0_.`dbid`=metricreco0_2_.dbid
where
`dbid` in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)
2008-08-08 09:47:57,831 org.hibernate.SQL(Thread-15) [DEBUG]:
/* insert-
select
for net.sf.gratia.storage.MetricRecord ids */ insert
into
HT_MetricRecord
select
metricreco0_.`dbid` as `dbid`
from
MetricRecord metricreco0_
inner join
MetricRecord_Meta metricreco0_1_
on metricreco0_.`dbid`=metricreco0_1_.dbid
left outer join
MetricRecord_Xml metricreco0_2_
on metricreco0_.`dbid`=metricreco0_2_.dbid
where
`dbid` in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)