Re: Unexpected inline view behavior
- From: "Stephen Miller" <miller_stephen@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Jun 2012 15:15:09 -0400
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@xxxxxxxxxx>= ;
To: <oracle-l@xxxxxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
