4 Replies Latest reply on Nov 28, 2012 10:45 AM by sroberts

    Teiid Aggregate Count Star Behavior

    sroberts

      I am having strange behavior when executing Select Count(*) queries through by custom Teiid translator. My Teiid translator connects to a database API with a very complex data model. I have supportsAggregatesCountStar in my ExecutionFactory set to the default false behavior.

       

      When I execute this query

      > select count(*) from table_a

      I expect it to arrive at my translator as

      > select column_a from table_a

      But my translator is getting

      > select 1 from table_a

       

      I can’t figure out where the 1 is coming from. How should I be interpreting this query? Is it a metadata issue?

       

      I am using Teiid 8.1.0 final.

       

      Thank you

        • 1. Re: Teiid Aggregate Count Star Behavior
          shawkins

          Since your translator indicates support for select expressions (but not count(*)), Teiid will choose to use a dummy value rather than retrieving all of a single column which could be significantly more data.

           

          Steve

          1 of 1 people found this helpful
          • 2. Re: Teiid Aggregate Count Star Behavior
            sroberts

            I received a private message asking for more information about my translator, so I will try and provide more detail. The translator is not mysterious. It bridges the gap between an ancient in house database API and Teiid. Our API is an object model that has template style select, delete, update behavior. The Teiid translator is actually a thin and minimal layer bridging the Teiid query language to our template based system. We have Oracle as our database backend. We cannot go to Oracle directly because there is a lot of logic inside our system including unit conversion and data transformation. Our data model is huge including about 900 tables


             

            Steve,


            Thank you for your response.


            I agree that retrieving all of a single column could be a lot of data and should be avoided if possible. My translator is only expecting to retrieve columns that have been defined in the metadata. How are these dummy columns defined and in what other scenarios can I expect to see them?


            In my case, adding support for count(*) directly will be easier than adding a special case for dummy values. Getting the dummy column was a surprise for me, and I thought I had stumbled onto a bug.  In house, we have other teams working on different Teiid translators. When I asked them to demonstrate this, their translators received commands with a single column selected. It is possible they are using an older version of Teiid though. Is this a recent change in Teiid?

             

            Thank you,

            Stanley

            • 3. Re: Teiid Aggregate Count Star Behavior
              shawkins

              My translator is only expecting to retrieve columns that have been defined in the metadata. How are these dummy columns defined and in what other scenarios can I expect to see them?

               

              Then you may not want to support select expressions, since that indicates select literals, and any non-column reference expression (that is otherwise supported) can be used.

               

              This count(*) scenario or if the user query is effectively select literal from tbl can result in using a dummy value instead.

               

              > In my case, adding support for count(*) directly will be easier than adding a special case for dummy values.

               

              Yes, that will be better for performance as well.

               

              > It is possible they are using an older version of Teiid though. Is this a recent change in Teiid?

               

              This was introduced somewhat early on in 7.x if I recall.  It's also possible that their translators don't have support for select expressions, which would force Teiid to choose a column instead.

               

              Steve


              • 4. Re: Teiid Aggregate Count Star Behavior
                sroberts

                Thank you Steve. That explains it.