Bulk insert with select (f1) und subquery (f2)

Description

It is not possible to have a bulk insert and select with subselect with a field reference:

INSERT INTO table1 (column1,column2,...)
SELECT (column1,column2,...) FROM table2
WHERE table2.column1 = (SELECT column1 from table3
WHERE table2.column1 = table3.column1)

The last where clause leads to wrong SQL that leads to SQL-Exception, because it references not the table alias!

Usually alle columns will be referenced with a table alias, but in the last where clause it references the table directly without alias which leads to unrecognized column.

INSERT INTO SecondPerson (id, name, vorname, date)
SELECT p.id, p.name, p.vorname, p.date FROM Person p
WHERE p.date = (select max(p2.date) FROM Person p2
WHERE p.id = p2.id)

generated SQL:
Hibernate: insert into SecondPerson ( id, name, vorname, date ) select person0_.id as col_0_0_, person0_.name as col_1_0_, person0_.vorname as col_2_0_, person0_.date as col_3_0_ from Person person0_ where person0_.date=(select max(person1_.date) from Person person1_ where Person.id=person1_.id)

SQL-Exception: ERROR: Unknown column 'Person.id' in 'where clause'

It should be: ... where person0_.id=person1_.id)

Hibernate: insert into SecondPerson ( id, name, vorname, date ) select person0_.id as col_0_0_, person0_.name as col_1_0_, person0_.vorname as col_2_0_, person0_.date as col_3_0_ from Person person0_ where person0_.date=(select max(person1_.date) from Person person1_ where person0_.id=person1_.id)

Environment

Windows Professional
Java7 SE
Oracle 10G and MySQL5.1

Assignee

Unassigned

Reporter

Evgeny

Fix versions

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure