RE: SQL question
Date: Wed, 24 Oct 2012 19:48:02 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1121594E5_at_G5W2734.americas.hpqcorp.net>
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
Received on Wed Oct 24 2012 - 21:48:02 CEST