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

    User Defined Aggregate Function




      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


        • 1. Re: User Defined Aggregate Function

          Shouldn't you say


          SELECT a, GROUP_CONCAT(b,',')

          FROM table

          GROUP BY a, b



          • 2. Re: User Defined Aggregate Function

            Ramesh, his grouping is correct.




            Can you post your vdb?



            1 of 1 people found this helpful
            • 3. Re: User Defined Aggregate Function

              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


              • 4. Re: User Defined Aggregate Function



                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.



                • 5. Re: User Defined Aggregate Function

                  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.