Re: Unexpected inline view behavior
Date: Tue, 12 Jun 2012 15:15:09 -0400
Message-ID: <492qFLToJ1696S03.1339528509_at_web03.cms.usa.net>
Running in 10.2.0.5.0, I get expected behaviour:
select count(*) f= rom (select bar, min(quux) quux from foo )
*
Error at line 1
ORA-00937: not a single-group group function
Must be an 11g problem.
Stephen Miller - email: miller_stephen at usa.net
------ Original Message ------
Received: 02:04 PM EDT, 06/12/2012
From: "Tornblad, John" <JTornblad_at_emdeon.com>= ;
To: <oracle-l_at_freelists.org>
Subject: Unexpected inline view behavior
I had a very large query that was going off the rails in= terms of
performance and unexpected results. After dissection,= I zeroed in on a flawed inline view, but it was flawed in a way that (= a) looked like an error but did not raise an error when parsed and (b) = worse, yielded an incorrect or at least unexpected result and caused ha= voc. I am assuming there is something I didn't know about what this inli= ne view is doing, or how it is being parsed / reorganized (?)... but it= 's a mystery to me.
Boiling it down...
Setup:
SQL> create table foo (bar varchar2(10), quux numb= er(10)); Table created.
SQL> insert into foo (bar, quux) values ('one',1);= 1 row created.
SQL> insert into foo (bar, quux) values ('two',2);= 1 row created.
This query's inline view is missing an intended GROUP= BY, but it still does something:
SQL> select count(*) from (select bar, min(quux) q= uux from foo);
COUNT(*)
1
Try to look at the rows (?) we're counting:
SQL> select * from (select bar, min(quux) quux fro= m foo);
select * from (select bar, min(quux) quux from foo)
*
ERROR at line 1:
ORA-00937: not a single-group group function
How is this inline view working at all:
SQL> select bar, min(quux) quux from foo;
select bar, min(quux) quux from foo
*
ERROR at line 1:
ORA-00937: not a single-group group function
Adding in the GROUP BY, all is well:
SQL> select count(*) from (select bar, min(quux) q= uux from foo GROUP BY bar);
COUNT(*)
2
What is going on? What are we counting?
SQL> delete from foo;
2 rows deleted
SQL> select count(*) from (select bar, min(quux) q= uux from foo);
COUNT(*)
1
This is 11.2.0.2. I'm sure I'm missing something comp= letely obvious and simple but I think I've been staring at it for too lo= ng. This really made me stop and think how much I might be relying on= the parser forcatching certain kinds of "errors" I might make.
-john
</pre>This message is confidential, intended on= ly for the named recipient(s) and may contain information that is priv= ileged or exempt from disclosure under applicable law. If you a= re not the intended recipient(s), you are notified that the
dissemination, distribution, or copying of this messa= ge is strictly prohibited. If you receive this message in e= rror or are not the named recipient(s), please notify the sen= der byreturn email and delete this message. Thank you.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 12 2012 - 14:15:09 CDT