Re: Unexpected inline view behavior

From: Stephen Miller <miller_stephen_at_usa.net>
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&gt= ; 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 for 
catching 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 by 
return email and delete this message. Thank you.
-- 
http://www.freelists.org/webpage/oracle-l 







--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 12 2012 - 14:15:09 CDT

Original text of this message