hibernate-issues

[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-1397) HQL insert into ... select ... statement problem due to lacking table aliases in select clause

[Lukas B. (JIRA) <no...@at...>]

HQL insert into ... select ... statement problem due to lacking table alias=
es in select clause
---------------------------------------------------------------------------=
-------------------

         Key: HHH-1397
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH=
-1397
     Project: Hibernate3
        Type: Bug

  Components: hql =20
    Versions: 3.1.1   =20
 Environment: 3.1.1., PostgresSQL
    Reporter: Lukas Barton


I have got problem with INSERT INTO ... SELECT ...
As I have very complex table stucture and join many tables easy workaround =
is not possible :-(

I have SQL like this:

insert into IdfKlient (dotazID,tabulkaVSabloneID,obec,status,metaTypKlienta=
,ulice,ic,psc,rc,jmeno,idKlient) select dotaz.id,sablonaMain.id,dtaklient.o=
bec,dtaklient.status,dtaklient.metaTypKlienta,dtaklient.ulice,dtaklient.ic,=
dtaklient.psc,dtaklient.rc,dtaklient.jmeno,dtaklient.idKlient from DtaKlien=
t as dtaklient,Dotaz as dotaz,TabulkaVSablone as sablonaMain, HodnotaParame=
truDotazuString parametr_0 where sablonaMain.id=3D:sablona_id and exists(fr=
om Sablona as sablona where dotaz.sablona=3Dsablona and sablona.hlavniTabul=
ka=3DsablonaMain and exists(from IdfDavka as davka where dotaz.davka=3Ddavk=
a and davka.status=3D:status)) and parametr_0.dotaz=3Ddotaz and dtaklient.r=
c=3Dparametr_0.hodnota and exists (from Parametr as param where param=3Dpar=
ametr_0.parametr)


But hibernate translates it into this (see where at the and of statement - =
where idsablona=3Did and hlavnitabulka=3Didtabulkavsablone......):

insert into idfklient ( dotaz_id, sablona_id, obec, status, metatypklienta_=
id, ulice, ic, psc, rc, jmeno, idklient ) select dotaz1_.id as col_0_0_, ta=
bulkavsa2_.idtabulkavsablone as col_1_0_, dtaklient0_.obec as col_2_0_, dta=
klient0_.status as col_3_0_, dtaklient0_.metatypklienta_id as col_4_0_, dta=
klient0_.ulice as col_5_0_, dtaklient0_.ic as col_6_0_, dtaklient0_.psc as =
col_7_0_, dtaklient0_.rc as col_8_0_, dtaklient0_.jmeno as col_9_0_, dtakli=
ent0_.idklient as col_10_0_ from dtaklient dtaklient0_, dotaz dotaz1_, tabu=
lkavsablone tabulkavsa2_, hodnotaparametrudotazustring hodnotapar3_ inner j=
oin hodnotaparametrudotazu hodnotapar3_1_ on hodnotapar3_.idhodnoty=3Dhodno=
tapar3_1_.id where idtabulkavsablone=3D? and (exists (select sablona4_.id f=
rom sablona sablona4_ where idsablona=3Did and hlavnitabulka=3Didtabulkavsa=
blone and (exists (select idfdavka5_.ID from IDFDAVKA idfdavka5_ where idda=
vky=3DID and STATUS=3D?)))) and iddotazu=3Did and rc=3Dhodnota and (exists =
(select parametr6_.id from parametr parametr6_ where id=3Didparametru))

I found similar error here: http://opensource2.atlassian.com/projects/hiber=
nate/browse/HHH-1349

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators=
.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1397) HQL insert into ... select ... statement problem due to lacking table aliases in select clause

[Matan (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1397?pa=
ge=3Dcomments#action_23049 ]=20

Matan commented on HHH-1397:
----------------------------

I have this problem as well with version 3.1.2.

There are no aliases created in the select portion of the insert statement =
and therefore i'm getting "xxx" column is ambigous errors:

This HQL:

        insert into
            NotifyRec (company, project, dateEntered, status)
        select
            distinct c, proj, now(), 0
        from
            Company c
            inner join c.companyPrefss p
            inner join p.companyPrefsRecCats cats
            inner join p.companyPrefsRecBudgets budget,
            Project proj
        where
            p.emailRecommendations =3D :email
            and cats.id in (:categories)
            and budget.budget in (:budgets)
            and c.provider =3D :provider
            and c.systemStatus in (:system_status)
            and proj.id =3D :project_id


Turns into:

    insert  into
        notify_rec
        ( company_id, project_id, date_entered, status ) select
            distinct company0_.company_id as col_0_0_,
            project5_.project_id as col_1_0_,
            now() as col_2_0_,
            0 as col_3_0_=20
        from
            company company0_=20
        inner join
            company_prefs companypre1_=20
                on company_id=3Dcompanypre1_.company_id=20
        inner join
            company_prefs_rec_cat companypre2_=20
                on company_prefs_id=3Dcompanypre2_.company_prefs_id=20
        inner join
            cat cat3_=20
                on companypre2_.cat_id=3Dcat3_.cat_id=20
        inner join
            company_prefs_rec_budget companypre4_=20
                on company_prefs_id=3Dcompanypre4_.company_prefs_id,
            project project5_=20
        where
            email_recommendations=3D?=20
            and (
                cat_id in (
                    ? , ? , ? , ? , ?
                )
            )=20
            and (
                budget in (
                    ?
                )
            )=20
            and provider=3D?=20
            and (
                system_status in (
                    ?
                )
            )=20
            and project_id=3D?

Notice that the select statement where clause has no table aliases....  get=
 an SQL exception because of it :(

Any news on when this will be fixed?

> HQL insert into ... select ... statement problem due to lacking table ali=
ases in select clause
> -------------------------------------------------------------------------=
---------------------
>
>          Key: HHH-1397
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/H=
HH-1397
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.1.1
>  Environment: 3.1.1., PostgresSQL
>     Reporter: Lukas Barton

>
>
> I have got problem with INSERT INTO ... SELECT ...
> As I have very complex table stucture and join many tables easy workaroun=
d is not possible :-(
> I have SQL like this:
> insert into IdfKlient (dotazID,tabulkaVSabloneID,obec,status,metaTypKlien=
ta,ulice,ic,psc,rc,jmeno,idKlient) select dotaz.id,sablonaMain.id,dtaklient=
.obec,dtaklient.status,dtaklient.metaTypKlienta,dtaklient.ulice,dtaklient.i=
c,dtaklient.psc,dtaklient.rc,dtaklient.jmeno,dtaklient.idKlient from DtaKli=
ent as dtaklient,Dotaz as dotaz,TabulkaVSablone as sablonaMain, HodnotaPara=
metruDotazuString parametr_0 where sablonaMain.id=3D:sablona_id and exists(=
from Sablona as sablona where dotaz.sablona=3Dsablona and sablona.hlavniTab=
ulka=3DsablonaMain and exists(from IdfDavka as davka where dotaz.davka=3Dda=
vka and davka.status=3D:status)) and parametr_0.dotaz=3Ddotaz and dtaklient=
.rc=3Dparametr_0.hodnota and exists (from Parametr as param where param=3Dp=
arametr_0.parametr)
> But hibernate translates it into this (see where at the and of statement =
- where idsablona=3Did and hlavnitabulka=3Didtabulkavsablone......):
> insert into idfklient ( dotaz_id, sablona_id, obec, status, metatypklient=
a_id, ulice, ic, psc, rc, jmeno, idklient ) select dotaz1_.id as col_0_0_, =
tabulkavsa2_.idtabulkavsablone as col_1_0_, dtaklient0_.obec as col_2_0_, d=
taklient0_.status as col_3_0_, dtaklient0_.metatypklienta_id as col_4_0_, d=
taklient0_.ulice as col_5_0_, dtaklient0_.ic as col_6_0_, dtaklient0_.psc a=
s col_7_0_, dtaklient0_.rc as col_8_0_, dtaklient0_.jmeno as col_9_0_, dtak=
lient0_.idklient as col_10_0_ from dtaklient dtaklient0_, dotaz dotaz1_, ta=
bulkavsablone tabulkavsa2_, hodnotaparametrudotazustring hodnotapar3_ inner=
 join hodnotaparametrudotazu hodnotapar3_1_ on hodnotapar3_.idhodnoty=3Dhod=
notapar3_1_.id where idtabulkavsablone=3D? and (exists (select sablona4_.id=
 from sablona sablona4_ where idsablona=3Did and hlavnitabulka=3Didtabulkav=
sablone and (exists (select idfdavka5_.ID from IDFDAVKA idfdavka5_ where id=
davky=3DID and STATUS=3D?)))) and iddotazu=3Did and rc=3Dhodnota and (exist=
s (select parametr6_.id from parametr parametr6_ where id=3Didparametru))
> I found similar error here: http://opensource2.atlassian.com/projects/hib=
ernate/browse/HHH-1349

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators=
.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1397) HQL insert into ... select ... statement problem due to lacking table aliases in select clause

[howard b. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1397?pa=
ge=3Dcomments#action_23354 ]=20

howard branch commented on HHH-1397:
------------------------------------

I also have this problem and I have no way round it except iterating throug=
h potentially thousands of queries on a
production system. Please could we have an ETA for a fix?

http://forum.hibernate.org/viewtopic.php?t=3D960800

> HQL insert into ... select ... statement problem due to lacking table ali=
ases in select clause
> -------------------------------------------------------------------------=
---------------------
>
>          Key: HHH-1397
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/H=
HH-1397
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.1.1
>  Environment: 3.1.1., PostgresSQL
>     Reporter: Lukas Barton

>
>
> I have got problem with INSERT INTO ... SELECT ...
> As I have very complex table stucture and join many tables easy workaroun=
d is not possible :-(
> I have SQL like this:
> insert into IdfKlient (dotazID,tabulkaVSabloneID,obec,status,metaTypKlien=
ta,ulice,ic,psc,rc,jmeno,idKlient) select dotaz.id,sablonaMain.id,dtaklient=
.obec,dtaklient.status,dtaklient.metaTypKlienta,dtaklient.ulice,dtaklient.i=
c,dtaklient.psc,dtaklient.rc,dtaklient.jmeno,dtaklient.idKlient from DtaKli=
ent as dtaklient,Dotaz as dotaz,TabulkaVSablone as sablonaMain, HodnotaPara=
metruDotazuString parametr_0 where sablonaMain.id=3D:sablona_id and exists(=
from Sablona as sablona where dotaz.sablona=3Dsablona and sablona.hlavniTab=
ulka=3DsablonaMain and exists(from IdfDavka as davka where dotaz.davka=3Dda=
vka and davka.status=3D:status)) and parametr_0.dotaz=3Ddotaz and dtaklient=
.rc=3Dparametr_0.hodnota and exists (from Parametr as param where param=3Dp=
arametr_0.parametr)
> But hibernate translates it into this (see where at the and of statement =
- where idsablona=3Did and hlavnitabulka=3Didtabulkavsablone......):
> insert into idfklient ( dotaz_id, sablona_id, obec, status, metatypklient=
a_id, ulice, ic, psc, rc, jmeno, idklient ) select dotaz1_.id as col_0_0_, =
tabulkavsa2_.idtabulkavsablone as col_1_0_, dtaklient0_.obec as col_2_0_, d=
taklient0_.status as col_3_0_, dtaklient0_.metatypklienta_id as col_4_0_, d=
taklient0_.ulice as col_5_0_, dtaklient0_.ic as col_6_0_, dtaklient0_.psc a=
s col_7_0_, dtaklient0_.rc as col_8_0_, dtaklient0_.jmeno as col_9_0_, dtak=
lient0_.idklient as col_10_0_ from dtaklient dtaklient0_, dotaz dotaz1_, ta=
bulkavsablone tabulkavsa2_, hodnotaparametrudotazustring hodnotapar3_ inner=
 join hodnotaparametrudotazu hodnotapar3_1_ on hodnotapar3_.idhodnoty=3Dhod=
notapar3_1_.id where idtabulkavsablone=3D? and (exists (select sablona4_.id=
 from sablona sablona4_ where idsablona=3Did and hlavnitabulka=3Didtabulkav=
sablone and (exists (select idfdavka5_.ID from IDFDAVKA idfdavka5_ where id=
davky=3DID and STATUS=3D?)))) and iddotazu=3Did and rc=3Dhodnota and (exist=
s (select parametr6_.id from parametr parametr6_ where id=3Didparametru))
> I found similar error here: http://opensource2.atlassian.com/projects/hib=
ernate/browse/HHH-1349

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators=
.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1397) HQL insert into ... select ... statement problem due to lacking table aliases in select clause

[Matan (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1397?pa=
ge=3Dcomments#action_23356 ]=20

Matan commented on HHH-1397:
----------------------------

My workaround is direct SQL (yuck!).... only thing that avoids the performa=
nce bottleneck.

Surprised we aren't getting any feedback from the team though...

> HQL insert into ... select ... statement problem due to lacking table ali=
ases in select clause
> -------------------------------------------------------------------------=
---------------------
>
>          Key: HHH-1397
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/H=
HH-1397
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.1.1
>  Environment: 3.1.1., PostgresSQL
>     Reporter: Lukas Barton

>
>
> I have got problem with INSERT INTO ... SELECT ...
> As I have very complex table stucture and join many tables easy workaroun=
d is not possible :-(
> I have SQL like this:
> insert into IdfKlient (dotazID,tabulkaVSabloneID,obec,status,metaTypKlien=
ta,ulice,ic,psc,rc,jmeno,idKlient) select dotaz.id,sablonaMain.id,dtaklient=
.obec,dtaklient.status,dtaklient.metaTypKlienta,dtaklient.ulice,dtaklient.i=
c,dtaklient.psc,dtaklient.rc,dtaklient.jmeno,dtaklient.idKlient from DtaKli=
ent as dtaklient,Dotaz as dotaz,TabulkaVSablone as sablonaMain, HodnotaPara=
metruDotazuString parametr_0 where sablonaMain.id=3D:sablona_id and exists(=
from Sablona as sablona where dotaz.sablona=3Dsablona and sablona.hlavniTab=
ulka=3DsablonaMain and exists(from IdfDavka as davka where dotaz.davka=3Dda=
vka and davka.status=3D:status)) and parametr_0.dotaz=3Ddotaz and dtaklient=
.rc=3Dparametr_0.hodnota and exists (from Parametr as param where param=3Dp=
arametr_0.parametr)
> But hibernate translates it into this (see where at the and of statement =
- where idsablona=3Did and hlavnitabulka=3Didtabulkavsablone......):
> insert into idfklient ( dotaz_id, sablona_id, obec, status, metatypklient=
a_id, ulice, ic, psc, rc, jmeno, idklient ) select dotaz1_.id as col_0_0_, =
tabulkavsa2_.idtabulkavsablone as col_1_0_, dtaklient0_.obec as col_2_0_, d=
taklient0_.status as col_3_0_, dtaklient0_.metatypklienta_id as col_4_0_, d=
taklient0_.ulice as col_5_0_, dtaklient0_.ic as col_6_0_, dtaklient0_.psc a=
s col_7_0_, dtaklient0_.rc as col_8_0_, dtaklient0_.jmeno as col_9_0_, dtak=
lient0_.idklient as col_10_0_ from dtaklient dtaklient0_, dotaz dotaz1_, ta=
bulkavsablone tabulkavsa2_, hodnotaparametrudotazustring hodnotapar3_ inner=
 join hodnotaparametrudotazu hodnotapar3_1_ on hodnotapar3_.idhodnoty=3Dhod=
notapar3_1_.id where idtabulkavsablone=3D? and (exists (select sablona4_.id=
 from sablona sablona4_ where idsablona=3Did and hlavnitabulka=3Didtabulkav=
sablone and (exists (select idfdavka5_.ID from IDFDAVKA idfdavka5_ where id=
davky=3DID and STATUS=3D?)))) and iddotazu=3Did and rc=3Dhodnota and (exist=
s (select parametr6_.id from parametr parametr6_ where id=3Didparametru))
> I found similar error here: http://opensource2.atlassian.com/projects/hib=
ernate/browse/HHH-1349

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators=
.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1397) HQL insert into ... select ... statement problem due to lacking table aliases in select clause

[Christian B. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1397?pa=
ge=3Dcomments#action_23357 ]=20

Christian Bauer commented on HHH-1397:
--------------------------------------

This is not primary functionality. The regular Hibernate developers are cur=
rently busy with other things. If you want to see this fixed quicker, submi=
t a patch.


> HQL insert into ... select ... statement problem due to lacking table ali=
ases in select clause
> -------------------------------------------------------------------------=
---------------------
>
>          Key: HHH-1397
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/H=
HH-1397
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.1.1
>  Environment: 3.1.1., PostgresSQL
>     Reporter: Lukas Barton

>
>
> I have got problem with INSERT INTO ... SELECT ...
> As I have very complex table stucture and join many tables easy workaroun=
d is not possible :-(
> I have SQL like this:
> insert into IdfKlient (dotazID,tabulkaVSabloneID,obec,status,metaTypKlien=
ta,ulice,ic,psc,rc,jmeno,idKlient) select dotaz.id,sablonaMain.id,dtaklient=
.obec,dtaklient.status,dtaklient.metaTypKlienta,dtaklient.ulice,dtaklient.i=
c,dtaklient.psc,dtaklient.rc,dtaklient.jmeno,dtaklient.idKlient from DtaKli=
ent as dtaklient,Dotaz as dotaz,TabulkaVSablone as sablonaMain, HodnotaPara=
metruDotazuString parametr_0 where sablonaMain.id=3D:sablona_id and exists(=
from Sablona as sablona where dotaz.sablona=3Dsablona and sablona.hlavniTab=
ulka=3DsablonaMain and exists(from IdfDavka as davka where dotaz.davka=3Dda=
vka and davka.status=3D:status)) and parametr_0.dotaz=3Ddotaz and dtaklient=
.rc=3Dparametr_0.hodnota and exists (from Parametr as param where param=3Dp=
arametr_0.parametr)
> But hibernate translates it into this (see where at the and of statement =
- where idsablona=3Did and hlavnitabulka=3Didtabulkavsablone......):
> insert into idfklient ( dotaz_id, sablona_id, obec, status, metatypklient=
a_id, ulice, ic, psc, rc, jmeno, idklient ) select dotaz1_.id as col_0_0_, =
tabulkavsa2_.idtabulkavsablone as col_1_0_, dtaklient0_.obec as col_2_0_, d=
taklient0_.status as col_3_0_, dtaklient0_.metatypklienta_id as col_4_0_, d=
taklient0_.ulice as col_5_0_, dtaklient0_.ic as col_6_0_, dtaklient0_.psc a=
s col_7_0_, dtaklient0_.rc as col_8_0_, dtaklient0_.jmeno as col_9_0_, dtak=
lient0_.idklient as col_10_0_ from dtaklient dtaklient0_, dotaz dotaz1_, ta=
bulkavsablone tabulkavsa2_, hodnotaparametrudotazustring hodnotapar3_ inner=
 join hodnotaparametrudotazu hodnotapar3_1_ on hodnotapar3_.idhodnoty=3Dhod=
notapar3_1_.id where idtabulkavsablone=3D? and (exists (select sablona4_.id=
 from sablona sablona4_ where idsablona=3Did and hlavnitabulka=3Didtabulkav=
sablone and (exists (select idfdavka5_.ID from IDFDAVKA idfdavka5_ where id=
davky=3DID and STATUS=3D?)))) and iddotazu=3Did and rc=3Dhodnota and (exist=
s (select parametr6_.id from parametr parametr6_ where id=3Didparametru))
> I found similar error here: http://opensource2.atlassian.com/projects/hib=
ernate/browse/HHH-1349

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators=
.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira