7 Replies Latest reply: Mar 8, 2014 1:57 AM by Jonathan Lewis RSS

    Aggregate function in a correlated subquery

    Prathap S

      Hi All,

       

      We have this query in our application which works fine in 9i but fails in 11gR2 (on Exadata) giving an "ORA-00937: not a single-group group function" error.

       

      SELECT   A.CNTRL_ID,

               B.FILEID,

               C.VNDR_ID,

               D.STRT_DT,

               D.END_DT,

               COUNT ( * ) TOT_REC,

               COUNT (DECODE (PROCESSED_FILE, 'Y', 1, NULL)) PROCESSED,

               COUNT (DECODE (PROCESSED_FILE, 'N', 1, NULL)) NOT_PROCESSED

          FROM   EMPLOYEE B,

                 EXTRACT_CONTROL D,

                 FILE_CONFIG C

         WHERE   C.FILEID = B.FILEID

                 AND C.CONFIG_ID = D.CONFIG_ID

                 AND STATUS NOT IN ('INCOMP')

                 and (NVL(D.END_DT,TRUNC(SYSDATE)),D.CONFIG_ID)

                  in

                  (SELECT MAX(NVL(END_DT,TRUNC(SYSDATE))),C.CONFIG_ID

                  from EXTRACT_CONTROL C WHERE C.CONFIG_ID = D.CONFIG_ID  

                    AND C.STATUS not in ('INCOMP','FAILED')

                  )

      GROUP BY   CNTRL_ID,

                 B.FILEID,

                 C.VNDR_ID,

                 D.STRT_DT,

                 D.END_DT


      The subquery is selecting a column and it doesn't have a group by clause at all. I am not sure how is this even working in 9i. I always thought that on a simple query using an aggregate function (without any analytic functions / clause), we cannot select a column without having that column in the group by clause. So, how 11g behaves was not a surprise but surprised to see how 9i behaves. Can someone explain this behaviour?

       

      Thanks,

      Prathap

        • 1. Re: Aggregate function in a correlated subquery
          Jonathan Lewis

          This may be due to a change in some of the transformation code.

          It's legal to have things like:  "select {constant}, max(colX) from t1 where cola = {constant}" and it's possible that after transformation Oracle 9i left your subquery as a filter subquery (check the predicate section of the plan) in the form:

           

          (SELECT MAX(NVL(END_DT,TRUNC(SYSDATE))),:B1

                      from EXTRACT_CONTROL C

               WHERE C.CONFIG_ID = :B1 

                        AND C.STATUS not in ('INCOMP','FAILED')


          Perhaps in 11g a "clever" transformation unnested the subquery but, as a side effect , failed to note that the C.config_id in the select list was effectively a constant by virtue of the correlation. (You might find this if you checked the 10053 trace file looking for the "UNPARSED QUERY".



          It looks as if the two-column comparison isn't needed - but maybe I'm missing something.

          Bad use of aliases, by the way, but I'm guessing that's you obfuscating the example.


          Regards

          Jonathan Lewis


          • 2. Re: Aggregate function in a correlated subquery
            Frank Kulash

            Hi, Prathap,

             

             

            Prathap S wrote:

             

            ...
                        and (NVL(D.END_DT,TRUNC(SYSDATE)),D.CONFIG_ID)

                        in

                        (SELECT MAX(NVL(END_DT,TRUNC(SYSDATE))),C.CONFIG_ID

                        from EXTRACT_CONTROL C WHERE C.CONFIG_ID = D.CONFIG_ID  

                          AND C.STATUS not in ('INCOMP','FAILED')

                        )

            ...
            The subquery is selecting a column and it doesn't have a group by clause at all. I am not sure how is this even working in 9i. I always thought that on a simple query using an aggregate function (without any analytic functions / clause), we cannot select a column without having that column in the group by clause. So, how 11g behaves was not a surprise but surprised to see how 9i behaves. Can someone explain this behaviour?

             

            Thanks,

            Prathap

            I agree; that sounds like a bug in version 9.

            Perhaps Oracle 9 realized that C.CONFIG_ID was correlated to something in the main query, and therefore it was almost like being a constant, and constants are allowed in the SELECT cglause of an aggregate query.

             

            That hints at a work around: since it it correlated, there's no reason to include config_id anywhere else in the condition.  That is, replace the condition above with

            ...     and NVL ( D.END_DT

                            , TRUNC (SYSDATE)

                            )  = (   SELECT  MAX ( NVL ( C1.END_DT

                                                       ,TRUNC (SYSDATE)

                                                       )

                                                 )

                                     ,       C1.CONFIG_ID

                                     from    EXTRACT_CONTROL C1      -- C is used in main query, so use a different alias, like C1, here

                                     WHERE   C1.CONFIG_ID = D.CONFIG_ID 

                                     AND     C1.STATUS    not in ('INCOMP', 'FAILED')

                                 )

             

            You could also do an un-corrleated IN sub-query, with a GROUP BY clause.  I suspect that was done in this query sometime in the past; otherwise, I don't see why anyone would use IN rather than =.

            • 3. Re: Aggregate function in a correlated subquery
              Jonathan Lewis

              I've just created a quick test based on my hypothesis - my idea was right, but the guess about which transformation was wrong.

              I have a simple query which has a subquery like yours in it (aggregate with select column that does appear as a group by column).

               

              If the subquery unnested (into a distinct inline view) the query runs, if I block unnesting (/*+ no_unnest */ hint) it fails with the expected Oracle error.  I can get it to work in versions up to 11.1.0.7, but not in versions from 11.2.0.4.  It looks as if 11gR2 may change the order of transformation and optimisation in a way that means the subquery block is optimised independently before it can be transformed out of danger.

               

              I'll publish on my blog in a few minutes (I hope).

               

              Regards

              Jonathan Lewis

              • 4. Re: Aggregate function in a correlated subquery
                Jonathan Lewis

                 

                It looks as if 11gR2 may change the order of transformation and optimisation in a way that means the subquery block is optimised independently before it can be transformed out of danger.

                 

                I'll publish on my blog in a few minutes (I hope).

                 

                 

                Done: Subquery Anomaly | Oracle Scratchpad

                 

                11.2 seems to have a check on subquery validity that earlier versions don't have, and the check blocks an unnest that manages to hide the problem.

                 

                Regards

                Jonathan Lewis

                • 5. Re: Aggregate function in a correlated subquery
                  Prathap S

                  Thank you Jonathan and Frank. I still need to read through your replies a couple times to fully understand what you've said. But I guess I'm convinced that its the order of transformation between 9i and 11g is causing this. Thanks!!

                  • 6. Re: Aggregate function in a correlated subquery
                    Prathap S

                    Yes, these are not the aliases used in the actual query.

                    • 7. Re: Aggregate function in a correlated subquery
                      Jonathan Lewis

                      There's a comment on my blog note which identifies this as the effect of a fix to a bug (9477688) in earlier versions that could cause wrong results.

                       

                      Regards

                      Jonathan Lewis