8 Replies Latest reply on Sep 28, 2008 1:13 PM by Bernhard Mäser

    SQLState: 42883 error when using EntityQuery with restrictions

    Bernhard Mäser Newbie

      Hello everybody!


      I spotted out a problem when i try to use restrictions in EntityQuery.


      My query-class looks like this:


      @Name("kundeList")
      public class KundeList extends EntityQuery {
           
           @Override
           public String getEjbql() {
                return "select kunde from Kunde kunde";
           }
      
           private static final String[] RESTRICTIONS = {
                "lower(kunde.name) like concat(lower(#{kundeList.kunde.name}),'%')",
                "lower(kunde.tel1) like concat(lower(#{kundeList.kunde.tel1}),'%')",
                "lower(kunde.tel2) like concat(lower(#{kundeList.kunde.tel2}),'%')",
                "lower(kunde.fax) like concat(lower(#{kundeList.kunde.fax}),'%')",
                "lower(kunde.email) like concat(lower(#{kundeList.kunde.email}),'%')",
                "lower(kunde.www) like concat(lower(#{kundeList.kunde.www}),'%')", };
      
           private Kunde kunde = new Kunde();
           
      
           public Kunde getKunde() {
                return kunde;
           }
      
           public void setKunde(Kunde kunde) {
                this.kunde = kunde;
           }
      
      
           @Override
           public List<String> getRestrictions() {
                return Arrays.asList(RESTRICTIONS);
           }
           
      }



      if I try to execute this one i get the following error:



      18:49:15,774 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 42883
      18:49:15,774 ERROR [JDBCExceptionReporter] FEHLER: Funktion concat(text, unknown) existiert nicht
      18:49:15,775 WARN  [lifecycle] javax.el.ELException: /kundeList.xhtml @62,66 rendered="#{empty kundeList.resultList}": Error reading 'resultList' on type scaleit.session.KundeList_$$_javassist_1
      18:49:15,822 INFO  [lifecycle] WARNING: FacesMessage(s) have been enqueued, but may not have been displayed.
      sourceId=null[severity=(WARN 1), summary=(Transaction failed), detail=(Transaction failed)]



      Code is EXACTLY what seam-gen generated (by the way: the seam-gen generated query throws the same error)
      Working with jboss-seam-2.0.2.GA, jboss-4.2.2.GA, PostgreSQL-8.3


      Any ideas?
      Thanks in advance


      Berni


        • 1. Re: SQLState: 42883 error when using EntityQuery with restrictions
          Stuart Douglas Master

          Try this:



          "lower(kunde.name) like lower(#{kundeList.kunde.name}) || '%'"




          Re-write all your restriction like the one above. The problem is that concat is not a postgresql function. Instead it is done with the || operator.

          • 2. Re: SQLState: 42883 error when using EntityQuery with restrictions
            Bernhard Mäser Newbie

            thanks for your answer, but it didn't help me out of this problem


            i get exactly the same error. i think seam's EntityQuery uses concat itself

            • 3. Re: SQLState: 42883 error when using EntityQuery with restrictions
              Stuart Douglas Master

              can you post your new code and the error messages you got?

              • 4. Re: SQLState: 42883 error when using EntityQuery with restrictions
                Bernhard Mäser Newbie

                this is the query-class:


                package scaleit.session;
                
                import java.util.Arrays;
                import java.util.List;
                
                import org.jboss.seam.annotations.Name;
                import org.jboss.seam.framework.EntityQuery;
                
                import scaleit.entity.Kunde;
                
                @Name("kundeList")
                public class KundeList extends EntityQuery {
                
                     private static final String[] RESTRICTIONS = {
                               "lower(kunde.name) like lower(#{kundeList.kunde.name}) || '%'",
                               "lower(kunde.tel1) like lower(#{kundeList.kunde.tel1}) || '%'",
                               "lower(kunde.tel2) like lower(#{kundeList.kunde.tel2}) || '%'",
                               "lower(kunde.fax) like lower(#{kundeList.kunde.fax}) || '%'",
                               "lower(kunde.email) like lower(#{kundeList.kunde.email}) || '%'",
                               "lower(kunde.www) like lower(#{kundeList.kunde.www}) || '%'", };
                
                     private Kunde kunde = new Kunde();
                
                     @Override
                     public String getEjbql() {
                          return "select kunde from Kunde kunde";
                     }
                
                     @Override
                     public Integer getMaxResults() {
                          return 25;
                     }
                
                     public Kunde getKunde() {
                          return kunde;
                     }
                
                     @Override
                     public List<String> getRestrictions() {
                          return Arrays.asList(RESTRICTIONS);
                     }
                
                }



                13:36:41,045 INFO  [STDOUT] Hibernate: 
                    select
                        kunde0_.ID as ID4_,
                        kunde0_.OPTLOCK as OPTLOCK4_,
                        kunde0_.NAME as NAME4_,
                        kunde0_.TEL1 as TEL4_4_,
                        kunde0_.TEL2 as TEL5_4_,
                        kunde0_.FAX as FAX4_,
                        kunde0_.EMAIL as EMAIL4_,
                        kunde0_.WWW as WWW4_,
                        kunde0_.kundenart_ID as kundenart14_4_,
                        kunde0_.kundentyp_ID as kundentyp15_4_,
                        kunde0_.hauptadresse_ID as hauptad12_4_,
                        kunde0_.rechnungsadrUsesDefault as rechnung9_4_,
                        kunde0_.rechnungsadresse_ID as rechnun13_4_,
                        kunde0_.lieferadrUsesDefault as liefera10_4_,
                        kunde0_.lieferadresse_ID as liefera11_4_ 
                    from
                        Kunde kunde0_ 
                    where
                        lower(kunde0_.NAME) like concat(lower(?), '%')
                13:36:41,094 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 42883
                13:36:41,094 ERROR [JDBCExceptionReporter] FEHLER: Funktion concat(text, unknown) existiert nicht
                13:36:41,098 WARN  [lifecycle] javax.el.ELException: /kundeList.xhtml @62,66 rendered="#{empty kundeList.resultList}": Error reading 'resultList' on type scaleit.session.KundeList_$$_javassist_1
                13:36:41,214 INFO  [lifecycle] WARNING: FacesMessage(s) have been enqueued, but may not have been displayed.
                sourceId=null[severity=(WARN 1), summary=(Transaction failed), detail=(Transaction failed)]



                • 5. Re: SQLState: 42883 error when using EntityQuery with restrictions
                  Stuart Douglas Master

                  there is something wrong here, try doing a clean and make absolutley sure that this is the entity query becing called, because the sql that is gernated in the debug logs does not match up to what is in the code.

                  • 6. Re: SQLState: 42883 error when using EntityQuery with restrictions
                    Bernhard Mäser Newbie

                    i tried to clean, nothing has changed....
                    the Entity-Query should be called by this:



                    <!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
                                                 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
                    <ui:composition xmlns="http://www.w3.org/1999/xhtml"
                                    xmlns:s="http://jboss.com/products/seam/taglib"
                                    xmlns:ui="http://java.sun.com/jsf/facelets"
                                    xmlns:f="http://java.sun.com/jsf/core"
                                    xmlns:h="http://java.sun.com/jsf/html"
                                    xmlns:rich="http://richfaces.org/rich"
                                    template="layout/template.xhtml">
                                           
                    <ui:define name="body">
                        
                        <h:messages globalOnly="true" styleClass="message"/>
                        
                        <rich:simpleTogglePanel label="Filter" switchType="ajax" opened="false">
                        <h:form id="kundeSearch" styleClass="edit">
                        
                                <s:decorate template="layout/display.xhtml">
                                    <ui:define name="label">name</ui:define>
                                    <h:inputText id="name" value="#{kundeList.kunde.name}"/>
                                </s:decorate>
                    
                                <s:decorate template="layout/display.xhtml">
                                    <ui:define name="label">tel1</ui:define>
                                    <h:inputText id="tel1" value="#{kundeList.kunde.tel1}"/>
                                </s:decorate>
                    
                                <s:decorate template="layout/display.xhtml">
                                    <ui:define name="label">tel2</ui:define>
                                    <h:inputText id="tel2" value="#{kundeList.kunde.tel2}"/>
                                </s:decorate>
                    
                                <s:decorate template="layout/display.xhtml">
                                    <ui:define name="label">fax</ui:define>
                                    <h:inputText id="fax" value="#{kundeList.kunde.fax}"/>
                                </s:decorate>
                    
                                <s:decorate template="layout/display.xhtml">
                                    <ui:define name="label">email</ui:define>
                                    <h:inputText id="email" value="#{kundeList.kunde.email}"/>
                                </s:decorate>
                    
                                <s:decorate template="layout/display.xhtml">
                                    <ui:define name="label">www</ui:define>
                                    <h:inputText id="www" value="#{kundeList.kunde.www}"/>
                                </s:decorate>
                                
                                <div class="actionButtons">
                                <h:commandButton id="search" value="Search" action="/KundeList.xhtml"/>
                            </div>
                            </h:form>
                        </rich:simpleTogglePanel>
                        
                        <rich:spacer height="15" title="Here is a spacer..."/>
                        <rich:panel>
                        
                            <f:facet name="header">kundeList</f:facet>
                            
                            <div class="results">
                            
                                <h:outputText value="No kunde exists" 
                                           rendered="#{empty kundeList.resultList}"/>
                                       
                                <h:dataTable id="kundeList" var="kunde"
                                          value="#{kundeList.resultList}" 
                                       rendered="#{not empty kundeList.resultList}">
                                    <h:column>
                                        <f:facet name="header">Id</f:facet>
                                        #{kunde.id}
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Name</f:facet>
                                        <s:link id="kunde" 
                                             value="#{kunde.name}" 
                                             view="/kunde.xhtml">
                                            <f:param name="kundeId" 
                                                    value="#{kunde.id}"/>
                                        </s:link>
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Telefon</f:facet>
                                        #{kunde.tel1}
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Telefon2</f:facet>
                                        #{kunde.tel2}
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Fax</f:facet>
                                        #{kunde.fax}
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Email</f:facet>
                                        #{kunde.email}
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Homepage</f:facet>
                                        #{kunde.www}
                                    </h:column>
                                    
                                    <h:column>
                                        <f:facet name="header">Hauptadresse etik1</f:facet>
                                        #{kunde.hauptadresse.etik1}
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Rechungsadresse etik1</f:facet>
                                        #{kunde.rechnungsadresse.etik1}
                                    </h:column>
                                    <h:column>
                                        <f:facet name="header">Lieferadresse etik1</f:facet>
                                        #{kunde.lieferadresse.etik1}
                                    </h:column>
                                    
                                </h:dataTable>
                            
                            </div>
                            
                        </rich:panel>
                        
                        <div class="actionButtons">
                            <s:button id="done" 
                                   value="Create kunde"
                                    view="/kunde.xhtml"/>                          
                        </div>
                        <rich:spacer height="15" title="Here is a spacer..."/>
                        <rich:simpleTogglePanel label="Kundendetails" switchType="ajax">
                         <rich:tabPanel switchType="client">
                            <rich:tab label="First">
                                Here is tab #1
                            </rich:tab>
                            <rich:tab label="Second">
                                Here is tab #2
                            </rich:tab>
                            <rich:tab label="Third">
                                Here is tab #3
                            </rich:tab>
                        </rich:tabPanel>
                        <div class="actionButtons">
                            <s:button id="kunde" 
                                   value="Bearbeiten" 
                                             view="/kunde.xhtml">
                                            <f:param name="kundeId" 
                                                    value="#{kunde.id}"/>
                                                    </s:button>
                        </div>
                        </rich:simpleTogglePanel>
                        
                    </ui:define>
                    
                    </ui:composition>
                    
                    



                    the stacktrace i posted, was not the full one, because it is really very long and this wiki didnt let me paste it :-). i thought the error on the end of it would be enought. here is the console-log (i cutted some lines in the middle):



                    15:25:09,757 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_,
                            kunde0_.OPTLOCK as OPTLOCK20_,
                            kunde0_.NAME as NAME20_,
                            kunde0_.TEL1 as TEL4_20_,
                            kunde0_.TEL2 as TEL5_20_,
                            kunde0_.FAX as FAX20_,
                            kunde0_.EMAIL as EMAIL20_,
                            kunde0_.WWW as WWW20_,
                            kunde0_.kundenart_ID as kundenart14_20_,
                            kunde0_.kundentyp_ID as kundentyp11_20_,
                            kunde0_.hauptadresse_ID as hauptad12_20_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_,
                            kunde0_.lieferadresse_ID as liefera13_20_ 
                        from
                            Kunde kunde0_
                    15:25:09,770 INFO  [STDOUT] Hibernate: 
                        select
                            hauptadres0_.ID as ID18_5_,
                            hauptadres0_.OPTLOCK as OPTLOCK18_5_,
                            hauptadres0_.etik1 as etik3_18_5_,
                            hauptadres0_.etik2 as etik4_18_5_,
                            hauptadres0_.strasse as strasse18_5_,
                            hauptadres0_.plz as plz18_5_,
                            hauptadres0_.ort as ort18_5_,
                            hauptadres0_.land as land18_5_,
                            kunde1_.ID as ID20_0_,
                            kunde1_.OPTLOCK as OPTLOCK20_0_,
                            kunde1_.NAME as NAME20_0_,
                            kunde1_.TEL1 as TEL4_20_0_,
                            kunde1_.TEL2 as TEL5_20_0_,
                            kunde1_.FAX as FAX20_0_,
                            kunde1_.EMAIL as EMAIL20_0_,
                            kunde1_.WWW as WWW20_0_,
                            kunde1_.kundenart_ID as kundenart14_20_0_,
                            kunde1_.kundentyp_ID as kundentyp11_20_0_,
                            kunde1_.hauptadresse_ID as hauptad12_20_0_,
                            kunde1_.rechnungsadrUsesDefault as rechnung9_20_0_,
                            kunde1_.rechnungsadresse_ID as rechnun15_20_0_,
                            kunde1_.lieferadrUsesDefault as liefera10_20_0_,
                            kunde1_.lieferadresse_ID as liefera13_20_0_,
                            kundenart2_.ID as ID23_1_,
                            kundenart2_.OPTLOCK as OPTLOCK23_1_,
                            kundenart2_.NAME as NAME23_1_,
                            kundentyp3_.ID as ID21_2_,
                            kundentyp3_.OPTLOCK as OPTLOCK21_2_,
                            kundentyp3_.NAME as NAME21_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            HAUPTADRESSEN hauptadres0_ 
                        left outer join
                            Kunde kunde1_ 
                                on hauptadres0_.ID=kunde1_.hauptadresse_ID 
                        left outer join
                            KUNDENARTEN kundenart2_ 
                                on kunde1_.kundenart_ID=kundenart2_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp3_ 
                                on kunde1_.kundentyp_ID=kundentyp3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde1_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde1_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            hauptadres0_.ID=?
                    15:25:09,773 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_5_,
                            kunde0_.OPTLOCK as OPTLOCK20_5_,
                            kunde0_.NAME as NAME20_5_,
                            kunde0_.TEL1 as TEL4_20_5_,
                            kunde0_.TEL2 as TEL5_20_5_,
                            kunde0_.FAX as FAX20_5_,
                            kunde0_.EMAIL as EMAIL20_5_,
                            kunde0_.WWW as WWW20_5_,
                            kunde0_.kundenart_ID as kundenart14_20_5_,
                            kunde0_.kundentyp_ID as kundentyp11_20_5_,
                            kunde0_.hauptadresse_ID as hauptad12_20_5_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_5_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_5_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_5_,
                            kunde0_.lieferadresse_ID as liefera13_20_5_,
                            kundenart1_.ID as ID23_0_,
                            kundenart1_.OPTLOCK as OPTLOCK23_0_,
                            kundenart1_.NAME as NAME23_0_,
                            kundentyp2_.ID as ID21_1_,
                            kundentyp2_.OPTLOCK as OPTLOCK21_1_,
                            kundentyp2_.NAME as NAME21_1_,
                            hauptadres3_.ID as ID18_2_,
                            hauptadres3_.OPTLOCK as OPTLOCK18_2_,
                            hauptadres3_.etik1 as etik3_18_2_,
                            hauptadres3_.etik2 as etik4_18_2_,
                            hauptadres3_.strasse as strasse18_2_,
                            hauptadres3_.plz as plz18_2_,
                            hauptadres3_.ort as ort18_2_,
                            hauptadres3_.land as land18_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            Kunde kunde0_ 
                        left outer join
                            KUNDENARTEN kundenart1_ 
                                on kunde0_.kundenart_ID=kundenart1_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp2_ 
                                on kunde0_.kundentyp_ID=kundentyp2_.ID 
                        left outer join
                            HAUPTADRESSEN hauptadres3_ 
                                on kunde0_.hauptadresse_ID=hauptadres3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde0_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde0_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            kunde0_.rechnungsadresse_ID=?
                    15:25:09,776 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_5_,
                            kunde0_.OPTLOCK as OPTLOCK20_5_,
                            kunde0_.NAME as NAME20_5_,
                            kunde0_.TEL1 as TEL4_20_5_,
                            kunde0_.TEL2 as TEL5_20_5_,
                            kunde0_.FAX as FAX20_5_,
                            kunde0_.EMAIL as EMAIL20_5_,
                            kunde0_.WWW as WWW20_5_,
                            kunde0_.kundenart_ID as kundenart14_20_5_,
                            kunde0_.kundentyp_ID as kundentyp11_20_5_,
                            kunde0_.hauptadresse_ID as hauptad12_20_5_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_5_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_5_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_5_,
                            kunde0_.lieferadresse_ID as liefera13_20_5_,
                            kundenart1_.ID as ID23_0_,
                            kundenart1_.OPTLOCK as OPTLOCK23_0_,
                            kundenart1_.NAME as NAME23_0_,
                            kundentyp2_.ID as ID21_1_,
                            kundentyp2_.OPTLOCK as OPTLOCK21_1_,
                            kundentyp2_.NAME as NAME21_1_,
                            hauptadres3_.ID as ID18_2_,
                            hauptadres3_.OPTLOCK as OPTLOCK18_2_,
                            hauptadres3_.etik1 as etik3_18_2_,
                            hauptadres3_.etik2 as etik4_18_2_,
                            hauptadres3_.strasse as strasse18_2_,
                            hauptadres3_.plz as plz18_2_,
                            hauptadres3_.ort as ort18_2_,
                            hauptadres3_.land as land18_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            Kunde kunde0_ 
                        left outer join
                            KUNDENARTEN kundenart1_ 
                                on kunde0_.kundenart_ID=kundenart1_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp2_ 
                                on kunde0_.kundentyp_ID=kundentyp2_.ID 
                        left outer join
                            HAUPTADRESSEN hauptadres3_ 
                                on kunde0_.hauptadresse_ID=hauptadres3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde0_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde0_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            kunde0_.lieferadresse_ID=?
                    15:25:09,778 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_5_,
                            kunde0_.OPTLOCK as OPTLOCK20_5_,
                            kunde0_.NAME as NAME20_5_,
                            kunde0_.TEL1 as TEL4_20_5_,
                            kunde0_.TEL2 as TEL5_20_5_,
                            kunde0_.FAX as FAX20_5_,
                            kunde0_.EMAIL as EMAIL20_5_,
                            kunde0_.WWW as WWW20_5_,
                            kunde0_.kundenart_ID as kundenart14_20_5_,
                            kunde0_.kundentyp_ID as kundentyp11_20_5_,
                            kunde0_.hauptadresse_ID as hauptad12_20_5_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_5_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_5_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_5_,
                            kunde0_.lieferadresse_ID as liefera13_20_5_,
                            kundenart1_.ID as ID23_0_,
                            kundenart1_.OPTLOCK as OPTLOCK23_0_,
                            kundenart1_.NAME as NAME23_0_,
                            kundentyp2_.ID as ID21_1_,
                            kundentyp2_.OPTLOCK as OPTLOCK21_1_,
                            kundentyp2_.NAME as NAME21_1_,
                            hauptadres3_.ID as ID18_2_,
                            hauptadres3_.OPTLOCK as OPTLOCK18_2_,
                            hauptadres3_.etik1 as etik3_18_2_,
                            hauptadres3_.etik2 as etik4_18_2_,
                            hauptadres3_.strasse as strasse18_2_,
                            hauptadres3_.plz as plz18_2_,
                            hauptadres3_.ort as ort18_2_,
                            hauptadres3_.land as land18_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            Kunde kunde0_ 
                        left outer join
                            KUNDENARTEN kundenart1_ 
                                on kunde0_.kundenart_ID=kundenart1_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp2_ 
                                on kunde0_.kundentyp_ID=kundentyp2_.ID 
                        left outer join
                            HAUPTADRESSEN hauptadres3_ 
                                on kunde0_.hauptadresse_ID=hauptadres3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde0_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde0_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            kunde0_.hauptadresse_ID=?
                    15:25:09,781 INFO  [STDOUT] Hibernate: 
                        select
                            kundenart0_.ID as ID23_0_,
                            kundenart0_.OPTLOCK as OPTLOCK23_0_,
                            kundenart0_.NAME as NAME23_0_ 
                        from
                            KUNDENARTEN kundenart0_ 
                        where
                            kundenart0_.ID=?
                    15:25:09,782 INFO  [STDOUT] Hibernate: 
                        select
                            kundentyp0_.ID as ID21_0_,
                            kundentyp0_.OPTLOCK as OPTLOCK21_0_,
                            kundentyp0_.NAME as NAME21_0_ 
                        from
                            KUNDENTYPEN kundentyp0_ 
                        where
                            kundentyp0_.ID=?
                    
                    ==========CUT ===========================
                    15:25:15,083 INFO  [STDOUT] Hibernate: 
                        select
                            hauptadres0_.ID as ID18_5_,
                            hauptadres0_.OPTLOCK as OPTLOCK18_5_,
                            hauptadres0_.etik1 as etik3_18_5_,
                            hauptadres0_.etik2 as etik4_18_5_,
                            hauptadres0_.strasse as strasse18_5_,
                            hauptadres0_.plz as plz18_5_,
                            hauptadres0_.ort as ort18_5_,
                            hauptadres0_.land as land18_5_,
                            kunde1_.ID as ID20_0_,
                            kunde1_.OPTLOCK as OPTLOCK20_0_,
                            kunde1_.NAME as NAME20_0_,
                            kunde1_.TEL1 as TEL4_20_0_,
                            kunde1_.TEL2 as TEL5_20_0_,
                            kunde1_.FAX as FAX20_0_,
                            kunde1_.EMAIL as EMAIL20_0_,
                            kunde1_.WWW as WWW20_0_,
                            kunde1_.kundenart_ID as kundenart14_20_0_,
                            kunde1_.kundentyp_ID as kundentyp11_20_0_,
                            kunde1_.hauptadresse_ID as hauptad12_20_0_,
                            kunde1_.rechnungsadrUsesDefault as rechnung9_20_0_,
                            kunde1_.rechnungsadresse_ID as rechnun15_20_0_,
                            kunde1_.lieferadrUsesDefault as liefera10_20_0_,
                            kunde1_.lieferadresse_ID as liefera13_20_0_,
                            kundenart2_.ID as ID23_1_,
                            kundenart2_.OPTLOCK as OPTLOCK23_1_,
                            kundenart2_.NAME as NAME23_1_,
                            kundentyp3_.ID as ID21_2_,
                            kundentyp3_.OPTLOCK as OPTLOCK21_2_,
                            kundentyp3_.NAME as NAME21_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            HAUPTADRESSEN hauptadres0_ 
                        left outer join
                            Kunde kunde1_ 
                                on hauptadres0_.ID=kunde1_.hauptadresse_ID 
                        left outer join
                            KUNDENARTEN kundenart2_ 
                                on kunde1_.kundenart_ID=kundenart2_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp3_ 
                                on kunde1_.kundentyp_ID=kundentyp3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde1_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde1_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            hauptadres0_.ID=?
                    15:25:15,087 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_5_,
                            kunde0_.OPTLOCK as OPTLOCK20_5_,
                            kunde0_.NAME as NAME20_5_,
                            kunde0_.TEL1 as TEL4_20_5_,
                            kunde0_.TEL2 as TEL5_20_5_,
                            kunde0_.FAX as FAX20_5_,
                            kunde0_.EMAIL as EMAIL20_5_,
                            kunde0_.WWW as WWW20_5_,
                            kunde0_.kundenart_ID as kundenart14_20_5_,
                            kunde0_.kundentyp_ID as kundentyp11_20_5_,
                            kunde0_.hauptadresse_ID as hauptad12_20_5_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_5_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_5_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_5_,
                            kunde0_.lieferadresse_ID as liefera13_20_5_,
                            kundenart1_.ID as ID23_0_,
                            kundenart1_.OPTLOCK as OPTLOCK23_0_,
                            kundenart1_.NAME as NAME23_0_,
                            kundentyp2_.ID as ID21_1_,
                            kundentyp2_.OPTLOCK as OPTLOCK21_1_,
                            kundentyp2_.NAME as NAME21_1_,
                            hauptadres3_.ID as ID18_2_,
                            hauptadres3_.OPTLOCK as OPTLOCK18_2_,
                            hauptadres3_.etik1 as etik3_18_2_,
                            hauptadres3_.etik2 as etik4_18_2_,
                            hauptadres3_.strasse as strasse18_2_,
                            hauptadres3_.plz as plz18_2_,
                            hauptadres3_.ort as ort18_2_,
                            hauptadres3_.land as land18_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            Kunde kunde0_ 
                        left outer join
                            KUNDENARTEN kundenart1_ 
                                on kunde0_.kundenart_ID=kundenart1_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp2_ 
                                on kunde0_.kundentyp_ID=kundentyp2_.ID 
                        left outer join
                            HAUPTADRESSEN hauptadres3_ 
                                on kunde0_.hauptadresse_ID=hauptadres3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde0_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde0_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            kunde0_.rechnungsadresse_ID=?
                    15:25:15,091 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_5_,
                            kunde0_.OPTLOCK as OPTLOCK20_5_,
                            kunde0_.NAME as NAME20_5_,
                            kunde0_.TEL1 as TEL4_20_5_,
                            kunde0_.TEL2 as TEL5_20_5_,
                            kunde0_.FAX as FAX20_5_,
                            kunde0_.EMAIL as EMAIL20_5_,
                            kunde0_.WWW as WWW20_5_,
                            kunde0_.kundenart_ID as kundenart14_20_5_,
                            kunde0_.kundentyp_ID as kundentyp11_20_5_,
                            kunde0_.hauptadresse_ID as hauptad12_20_5_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_5_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_5_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_5_,
                            kunde0_.lieferadresse_ID as liefera13_20_5_,
                            kundenart1_.ID as ID23_0_,
                            kundenart1_.OPTLOCK as OPTLOCK23_0_,
                            kundenart1_.NAME as NAME23_0_,
                            kundentyp2_.ID as ID21_1_,
                            kundentyp2_.OPTLOCK as OPTLOCK21_1_,
                            kundentyp2_.NAME as NAME21_1_,
                            hauptadres3_.ID as ID18_2_,
                            hauptadres3_.OPTLOCK as OPTLOCK18_2_,
                            hauptadres3_.etik1 as etik3_18_2_,
                            hauptadres3_.etik2 as etik4_18_2_,
                            hauptadres3_.strasse as strasse18_2_,
                            hauptadres3_.plz as plz18_2_,
                            hauptadres3_.ort as ort18_2_,
                            hauptadres3_.land as land18_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            Kunde kunde0_ 
                        left outer join
                            KUNDENARTEN kundenart1_ 
                                on kunde0_.kundenart_ID=kundenart1_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp2_ 
                                on kunde0_.kundentyp_ID=kundentyp2_.ID 
                        left outer join
                            HAUPTADRESSEN hauptadres3_ 
                                on kunde0_.hauptadresse_ID=hauptadres3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde0_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde0_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            kunde0_.lieferadresse_ID=?
                    15:25:15,097 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_5_,
                            kunde0_.OPTLOCK as OPTLOCK20_5_,
                            kunde0_.NAME as NAME20_5_,
                            kunde0_.TEL1 as TEL4_20_5_,
                            kunde0_.TEL2 as TEL5_20_5_,
                            kunde0_.FAX as FAX20_5_,
                            kunde0_.EMAIL as EMAIL20_5_,
                            kunde0_.WWW as WWW20_5_,
                            kunde0_.kundenart_ID as kundenart14_20_5_,
                            kunde0_.kundentyp_ID as kundentyp11_20_5_,
                            kunde0_.hauptadresse_ID as hauptad12_20_5_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_5_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_5_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_5_,
                            kunde0_.lieferadresse_ID as liefera13_20_5_,
                            kundenart1_.ID as ID23_0_,
                            kundenart1_.OPTLOCK as OPTLOCK23_0_,
                            kundenart1_.NAME as NAME23_0_,
                            kundentyp2_.ID as ID21_1_,
                            kundentyp2_.OPTLOCK as OPTLOCK21_1_,
                            kundentyp2_.NAME as NAME21_1_,
                            hauptadres3_.ID as ID18_2_,
                            hauptadres3_.OPTLOCK as OPTLOCK18_2_,
                            hauptadres3_.etik1 as etik3_18_2_,
                            hauptadres3_.etik2 as etik4_18_2_,
                            hauptadres3_.strasse as strasse18_2_,
                            hauptadres3_.plz as plz18_2_,
                            hauptadres3_.ort as ort18_2_,
                            hauptadres3_.land as land18_2_,
                            rechnungsa4_.ID as ID18_3_,
                            rechnungsa4_.OPTLOCK as OPTLOCK18_3_,
                            rechnungsa4_.etik1 as etik3_18_3_,
                            rechnungsa4_.etik2 as etik4_18_3_,
                            rechnungsa4_.strasse as strasse18_3_,
                            rechnungsa4_.plz as plz18_3_,
                            rechnungsa4_.ort as ort18_3_,
                            rechnungsa4_.land as land18_3_,
                            lieferadre5_.ID as ID18_4_,
                            lieferadre5_.OPTLOCK as OPTLOCK18_4_,
                            lieferadre5_.etik1 as etik3_18_4_,
                            lieferadre5_.etik2 as etik4_18_4_,
                            lieferadre5_.strasse as strasse18_4_,
                            lieferadre5_.plz as plz18_4_,
                            lieferadre5_.ort as ort18_4_,
                            lieferadre5_.land as land18_4_ 
                        from
                            Kunde kunde0_ 
                        left outer join
                            KUNDENARTEN kundenart1_ 
                                on kunde0_.kundenart_ID=kundenart1_.ID 
                        left outer join
                            KUNDENTYPEN kundentyp2_ 
                                on kunde0_.kundentyp_ID=kundentyp2_.ID 
                        left outer join
                            HAUPTADRESSEN hauptadres3_ 
                                on kunde0_.hauptadresse_ID=hauptadres3_.ID 
                        left outer join
                            RECHNUNGSADRESSEN rechnungsa4_ 
                                on kunde0_.rechnungsadresse_ID=rechnungsa4_.ID 
                        left outer join
                            LIEFERADRESSEN lieferadre5_ 
                                on kunde0_.lieferadresse_ID=lieferadre5_.ID 
                        where
                            kunde0_.hauptadresse_ID=?
                    15:25:15,196 INFO  [STDOUT] Hibernate: 
                        select
                            kunde0_.ID as ID20_,
                            kunde0_.OPTLOCK as OPTLOCK20_,
                            kunde0_.NAME as NAME20_,
                            kunde0_.TEL1 as TEL4_20_,
                            kunde0_.TEL2 as TEL5_20_,
                            kunde0_.FAX as FAX20_,
                            kunde0_.EMAIL as EMAIL20_,
                            kunde0_.WWW as WWW20_,
                            kunde0_.kundenart_ID as kundenart14_20_,
                            kunde0_.kundentyp_ID as kundentyp11_20_,
                            kunde0_.hauptadresse_ID as hauptad12_20_,
                            kunde0_.rechnungsadrUsesDefault as rechnung9_20_,
                            kunde0_.rechnungsadresse_ID as rechnun15_20_,
                            kunde0_.lieferadrUsesDefault as liefera10_20_,
                            kunde0_.lieferadresse_ID as liefera13_20_ 
                        from
                            Kunde kunde0_ 
                        where
                            lower(kunde0_.NAME) like concat(lower(?), '%')
                    15:25:15,280 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 42883
                    15:25:15,280 ERROR [JDBCExceptionReporter] FEHLER: Funktion concat(text, unknown) existiert nicht
                    15:25:15,288 WARN  [lifecycle] javax.el.ELException: /kundeList.xhtml @62,66 rendered="#{empty kundeList.resultList}": Error reading 'resultList' on type scaleit.session.KundeList_$$_javassist_1
                    15:25:15,360 INFO  [lifecycle] WARNING: FacesMessage(s) have been enqueued, but may not have been displayed.
                    sourceId=null[severity=(WARN 1), summary=(Transaction failed), detail=(Transaction failed)]



                    • 7. Re: SQLState: 42883 error when using EntityQuery with restrictions
                      Stuart Douglas Master

                      Can you grep through your code and make sure that you are not using concat anywhere else? That entityQuery should not be generating that SQL, for one thing there is only one restriction (the one on the name field).

                      • 8. Re: SQLState: 42883 error when using EntityQuery with restrictions
                        Bernhard Mäser Newbie

                        ok... i am blind or stupid....


                        i found out what the problem was: my eclipse-generated project took the pointbase and not the postgres dialect, and used it in persistence.xml...


                        i read this file about 20 times and didn't see it....


                        but anyway: thanks a lot for your help! anything is working ok now :-)