select count with is operator

atorres · **Posted:** Tue Aug 09, 2005 12:19 pm

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.5
Mapping documents:

Code:

<hibernate-mapping package="com.methark.econair">
   <class
      name="Pessoa"
      table="Pessoa"
   >   

<meta attribute="class-code">
</meta>

      <id
         column="idPessoa"
         name="idPessoa"
         type="java.lang.Integer"
      >
         <generator class="increment">
         </generator>
      </id>   

   <joined-subclass
      name="PessoaJuridica"
      table="PessoaJuridica"
   >   
    <key column="idPessoaJuridica"/>   
...

</joined-subclass>
   <joined-subclass
      name="Filial"
      table="Filial"
   >   
    <key column="idFilial"/>   
...
</joined-subclass>
   <joined-subclass
      name="PessoaFisica"
      table="PessoaFisica"
   >   
    <key column="idPessoaFisica"/>   
...
</joined-subclass>

   </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
not important
Full stack trace of any exception that occurs:

Quote:

org.postgresql.util.PSQLException: ERROR: relation "pessoa0_1_" does not exist

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)

at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)

at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)

at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)

at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:515)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)

at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:231)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)

at org.hibernate.loader.Loader.doQuery(Loader.java:391)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)

at org.hibernate.loader.Loader.doList(Loader.java:1593)

at org.hibernate.loader.Loader.list(Loader.java:1577)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)

at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

at org.hibernate.console.HQLQueryPage.getList(HQLQueryPage.java:30)

at org.hibernate.eclipse.console.views.QueryPageViewer$ContentProviderImpl.getElements(QueryPageViewer.java:80)

at org.eclipse.jface.viewers.StructuredViewer.getRawChildren(StructuredViewer.java:848)

at org.eclipse.jface.viewers.TableViewer.getRawChildren(TableViewer.java:1086)

at org.eclipse.jface.viewers.StructuredViewer.getFilteredChildren(StructuredViewer.java:790)

at org.eclipse.jface.viewers.StructuredViewer.getSortedChildren(StructuredViewer.java:905)

at org.eclipse.jface.viewers.TableViewer.internalRefreshAll(TableViewer.java:798)

at org.eclipse.jface.viewers.TableViewer.internalRefresh(TableViewer.java:748)

at org.eclipse.jface.viewers.TableViewer.internalRefresh(TableViewer.java:737)

at org.eclipse.jface.viewers.StructuredViewer$7.run(StructuredViewer.java:1264)

at org.eclipse.jface.viewers.StructuredViewer.preservingSelection(StructuredViewer.java:1201)

at org.eclipse.jface.viewers.StructuredViewer.refresh(StructuredViewer.java:1262)

at org.eclipse.jface.viewers.StructuredViewer.refresh(StructuredViewer.java:1221)

at org.eclipse.jface.viewers.TableViewer.inputChanged(TableViewer.java:698)

at org.eclipse.jface.viewers.ContentViewer.setInput(ContentViewer.java:248)

at org.eclipse.jface.viewers.StructuredViewer.setInput(StructuredViewer.java:1417)

at org.hibernate.eclipse.console.views.QueryPageViewer.createTable(QueryPageViewer.java:174)

at org.hibernate.eclipse.console.views.QueryPageViewer.createControl(QueryPageViewer.java:153)

at org.hibernate.eclipse.console.views.QueryPageViewer.<init>(QueryPageViewer.java:111)

at org.hibernate.eclipse.console.views.QueryPageTabView.rebuild(QueryPageTabView.java:83)

at org.hibernate.eclipse.console.views.QueryPageTabView$1.contentsChanged(QueryPageTabView.java:52)

at org.hibernate.eclipse.console.views.QueryPageTabView$1.intervalAdded(QueryPageTabView.java:57)

at javax.swing.AbstractListModel.fireIntervalAdded(Unknown Source)

at org.hibernate.console.QueryPageModel.add(QueryPageModel.java:50)

at org.hibernate.console.KnownConfigurations$3.queryPageCreated(KnownConfigurations.java:167)

at org.hibernate.console.ConsoleConfiguration.fireQueryPageCreated(ConsoleConfiguration.java:266)

at org.hibernate.console.ConsoleConfiguration.executeHQLQuery(ConsoleConfiguration.java:247)

at org.hibernate.eclipse.console.actions.ExecuteHQLAction.execute(ExecuteHQLAction.java:99)

at org.hibernate.eclipse.console.actions.ExecuteHQLAction.run(ExecuteHQLAction.java:94)

at org.eclipse.jface.action.Action.runWithEvent(Action.java:996)

at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:538)

at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:488)

at org.eclipse.jface.action.ActionContributionItem$6.handleEvent(ActionContributionItem.java:441)

at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)

at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:843)

at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3080)

at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2713)

at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1699)

at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1663)

at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:367)

at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:143)

at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:103)

at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:226)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:376)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:163)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.eclipse.core.launcher.Main.invokeFramework(Main.java:334)

at org.eclipse.core.launcher.Main.basicRun(Main.java:278)

at org.eclipse.core.launcher.Main.run(Main.java:973)

at org.eclipse.core.launcher.Main.main(Main.java:948)

Name and version of the database you are using:
Postgres 8
----
Hi , I have a query over Pessoa class with where clause using class "is" operator:

Code:

select Pessoa from com.methark.econair.Pessoa as Pessoa 
Where  Pessoa.class is com.methark.econair.PessoaFisica

It works fine. But the count query gives me the above error:

Code:

select count(*) from com.methark.econair.Pessoa as Pessoa 
Where  Pessoa.class is com.methark.econair.PessoaFisica

well, is it an error or a limitation ? it´s possible to count based in a class where?
Give me a feedback and I may open jira.

ExpeditorsSusanA · **Joined:** Thu Mar 31, 2005 5:59 pm **Posts:** 34

It might be helpful if you put show_sql to true and show us all your output... I am curious what exactly is the SQL Hibernate is generating in this case...

Daniel · **Posted:** Tue Aug 09, 2005 12:44 pm

Do you try:

select count(*) from com.methark.econair.PessoaFisica

?

Good luck,

atorres · **Posted:** Tue Aug 09, 2005 1:09 pm

all those work fine:
select count(*) from com.methark.econair.Pessoa
select count(*) from com.methark.econair.PessoaFisica
select count(*) from com.methark.econair.PessoaJuridica
select count(*) from com.methark.econair.Filial

sorry about the show_sql, I was using the eclipse hibernate console.
here it go:

SQL

Quote:

select count(*) as col_0_0_ from Pessoa pessoa0_ where case when pessoa0_1_.idPessoaJuridica is not null then 1 when pessoa0_2_.idFilial is not null then 2 when pessoa0_3_.idPessoaFisica is not null then 3 when pessoa0_.idPessoa is not null then 0 end=3

it ignores the need of from clause... notice that if I remove the count and select Pessoa it works:

Quote:

select pessoa0_.idPessoa as idPessoa, pessoa0_.nome as nome99_, pessoa0_1_.CNPJ as CNPJ100_, pessoa0_1_.website as website100_, pessoa0_1_.inscrEstadual as inscrEst4_100_, pessoa0_1_.inscrMunicipal as inscrMun5_100_, pessoa0_2_.numero as numero101_, pessoa0_2_.idPessoaJuridica as idPessoa3_101_, pessoa0_3_.CPF as CPF102_, pessoa0_3_.nascimento as nascimento102_, pessoa0_3_.foto as foto102_, pessoa0_3_.sexo as sexo102_, pessoa0_3_.nomeMae as nomeMae102_, pessoa0_3_.nomePai as nomePai102_, pessoa0_3_.email as email102_, pessoa0_3_.estadoCivil as estadoCi9_102_, pessoa0_3_.Naturalidade as Natural10_102_, pessoa0_3_.Nacionalidade as Naciona11_102_, pessoa0_3_.RG_numero as RG12_102_, pessoa0_3_.RG_orgao as RG13_102_, pessoa0_3_.RG_estado as RG14_102_, pessoa0_3_.RG_emissao as RG15_102_, case when pessoa0_1_.idPessoaJuridica is not null then 1 when pessoa0_2_.idFilial is not null then 2 when pessoa0_3_.idPessoaFisica is not null then 3 when pessoa0_.idPessoa is not null then 0 end as clazz_ from Pessoa pessoa0_ left outer join PessoaJuridica pessoa0_1_ on pessoa0_.idPessoa=pessoa0_1_.idPessoaJuridica left outer join Filial pessoa0_2_ on pessoa0_.idPessoa=pessoa0_2_.idFilial left outer join PessoaFisica pessoa0_3_ on pessoa0_.idPessoa=pessoa0_3_.idPessoaFisica where case when pessoa0_1_.idPessoaJuridica is not null then 1 when pessoa0_2_.idFilial is not null then 2 when pessoa0_3_.idPessoaFisica is not null then 3 when pessoa0_.idPessoa is not null then 0 end=3

but If I select only fields from the master class, it gets wrong again!

Code:

select Pessoa.idPessoa from com.methark.econair.Pessoa as Pessoa Where  Pessoa.class is com.methark.econair.PessoaFisica

Quote:

select pessoa0_.idPessoa as col_0_0_ from Pessoa pessoa0_ where case when pessoa0_1_.idPessoaJuridica is not null then 1 when pessoa0_2_.idFilial is not null then 2 when pessoa0_3_.idPessoaFisica is not null then 3 when pessoa0_.idPessoa is not null then 0 end=3

well, looks like a bug...[/b]

ExpeditorsSusanA · **Joined:** Thu Mar 31, 2005 5:59 pm **Posts:** 34

Huh, that is strange... Seems like a bug to me, but I probly don't fully understand how this stuff works... Maybe somebody more knowledgeable will come along... From your output tho, something weird is going on! Good luck with it! Wish I could help!

gavin · **Posted:** Tue Aug 09, 2005 2:39 pm

This is a known bug.

btw, I think we recommend = rather than is, it is kinda luck that is works here.

atorres · **Posted:** Tue Aug 09, 2005 2:57 pm

Hi Gavin!
known bug or known limitation ? it will be solved ? :-)

"=" didn´t solve, got same problem.
My objective is do something like

select count(*)
from Pessoa
where Pessoa.class = ClassX or Pessoa.class=ClassY

this "or" I can´t emulate with from in a hierarchy.
I'm stuck now.
Thanks.

sven · **Posted:** Tue Aug 09, 2005 3:16 pm

You could do a count on each class.

The sum of the results is what you need.

Code:

select count(*) from PessoaJuridica
select count(*) from PessoaFisica

Best regards
Sven