5 Replies Latest reply on Sep 26, 2013 1:01 PM by shawkins

    User Defined Aggregate Function

    gadeyne.bram
    gadeyne.bram Sep 26, 2013 7:44 AM

      Hi,

       

      I'm using Teiid 8.3 and I'm trying to add the UDAF as described in this post: http://teiid.blogspot.be/2012_04_01_archive.html

       

      I noticed that the teiid designer issued a warning that normal user defined function model als depricated. So I added a new function in a relational model using new -> child -> procedure -> UDF.

       

      Then I entered the necessary fields and selected Aggregate, returns non on null and allows order by and added a new jar counting the necessary code implementing UserDefinedAggregate<String>.

       

      I then synchronized my VDB and deployed it to the teiid server.

       

      All seems fine except when I execute a funtion like

       

      select a, GROUP_CONCAT(b, ',')

      from table

      group by a

       

      i get an error: org.teiid.api.exception.query.QueryValidatorException: TEIID30492 [b] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.

       

      However I'm sure I've indicated that GROUP_CONCAT should be a aggregate function.

       

      How can I resolve this?

       

      With kind regards

      Bram

        • 1. Re: User Defined Aggregate Function
          rareddy
          rareddy Sep 26, 2013 8:44 AM (in response to gadeyne.bram)

          Shouldn't you say

           

          SELECT a, GROUP_CONCAT(b,',')

          FROM table

          GROUP BY a, b

           

          Ramesh..

            • Actions
          • 2. Re: User Defined Aggregate Function
            shawkins
            shawkins Sep 26, 2013 9:13 AM (in response to rareddy)

            Ramesh, his grouping is correct.

             

            Bram,

             

            Can you post your vdb?

             

            Steve

            1 of 1 people found this helpful
              • Actions
            • 3. Re: User Defined Aggregate Function
              gadeyne.bram
              gadeyne.bram Sep 26, 2013 10:22 AM (in response to rareddy)

              Hi Ramesh,

               

              Maybe I'm completely wrong here but i thought an aggregate function should work with the values of the rows within the group. So if I would add column b to the group by statement it would only have 1 row in the group.

               

              I have some rows like this

                   a      |        b         |          c

              1           | value1         |  xxxx

              1           | value2         | xxxxx

              2           | value3         | xxxx

               

              when i do select a, GROUP_CONCAT(b, ',') from table group by a

               

              I expected a result like

               

              a          |    groupconcat

              1          | value1,value2

              2          | value3

               

               

               

              I can't post the vdb since it connects to some proprietary medical databases.

               

              I've added an extraction from the xmi file concerning the function. I hope this will suffice.

               

              I've also added the .java file with my class.

               

              Kind regards

              Bram

                • Actions
              • 4. Re: User Defined Aggregate Function
                shawkins
                shawkins Sep 26, 2013 11:27 AM (in response to gadeyne.bram)

                Bram,

                 

                It would be best to post a complete vdb.  You can strip everything else out except for the UDAF if you want.  That just gives us the best starting point for seeing the exact same thing that you are.

                 

                Steve

                  • Actions
                • 5. Re: User Defined Aggregate Function
                  shawkins
                  shawkins Sep 26, 2013 1:01 PM (in response to shawkins)

                  Actually I had forgotten that there is a legacy issue with our parser that requires us to parse user defined aggregate functions using aggregate specific syntax (rather than something that just looks like a function).  This includes all/distinct, an ordering, etc.

                   

                  This is touched on in Expressions - Teiid 8.6 (draft) - Project Documentation Editor and the original issue [TEIID-1560] Add support for user defined / pushdown aggregate functions - JBoss Issue Tracker

                   

                  So try adding an ALL to the aggregate:

                   

                  select a, GROUP_CONCAT(ALL b, ',')

                  from table

                  group by a

                   

                  Since your metadata looks good, that should work.  If not go ahead and post the vdb.  I'll log an issue to refine the user defined aggregate handling.

                   

                  Steve

                    • Actions