RE: SQL question

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 24 Oct 2012 17:34:14 -0400
Message-ID: <035001cdb22f$516a9940$f43fcbc0$_at_rsiz.com>

If you add rownum to the inner query, does it repair the projection?

select count(*),max(inner_rownum)
from
(select rownum "inner_rownum", A.COL1,

                     A.COL2,
                     B.COL3
                FROM TABLE1 A,
                     TABLE2 B,
                     TABLE3 C
               WHERE A.ID = B.ID AND B.ID = C.ID)

Should clarify whether there is an easy work-around to the bug if a patch is not yet available.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark
Sent: Wednesday, October 24, 2012 3:48 PM To: oracle-l_at_freelists.org
Subject: RE: SQL question

Check out the following Oracle support bug report: Bug 14193629 - wrong results with correlated COUNT subquery inside an expression [ID 14193629.8]

-----Original Message-----

From: Ramadoss, Karthik [mailto:Karthik.Ramadoss_at_accidentfund.com] Sent: Wednesday, October 24, 2012 3:22 PM To: Powell, Mark; oracle-l_at_freelists.org Subject: RE: SQL question

I should have mentioned that the data is static in the tables involved.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark
Sent: Wednesday, October 24, 2012 3:19 PM To: oracle-l_at_freelists.org
Subject: RE: SQL question

Any change there the difference in the two queries is just due to DML activity on the target tables?

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ramadoss, Karthik
Sent: Wednesday, October 24, 2012 3:01 PM To: oracle-l_at_freelists.org
Subject: SQL question

This is probably a simple one but definitely something new for me. Database: 11.2.0.3
OS: Oracle Linux 5.6

A SQL like

              SELECT A.COL1,
                     A.COL2,
                     B.COL3
                FROM TABLE1 A,
                     TABLE2 B,
                     TABLE3 C
               WHERE A.ID = B.ID AND B.ID = C.ID

returns 1,192,940 rows.

And

        SELECT COUNT(*) from
      (SELECT A.COL1,
                     A.COL2,
                     B.COL3
                FROM TABLE1 A,
                     TABLE2 B,
                     TABLE3 C
               WHERE A.ID = B.ID AND B.ID = C.ID)

Returns 1,192,978 rows.

Anyone know what is going on here? I would expect both to return the same number of rows.

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 24 2012 - 23:34:14 CEST