Unexpected inline view behavior

From: Tornblad, John <JTornblad_at_emdeon.com>
Date: Tue, 12 Jun 2012 13:03:17 -0500
Message-ID: <0EC8AF65BC8E9A4E9C4EB86F1882C96E03E6D9EC_at_ZBNAAEEX052.na.webmd.net>

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 havoc. I am assuming there is something I didn't know about what this inline 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 number(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) quux from foo);

      COUNT(*)

---

             1

Try to look at the rows (?) we're counting:

    SQL> select * from (select bar, min(quux) quux from 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) quux 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) quux from foo);

      COUNT(*)

---

             1

This is 11.2.0.2. I'm sure I'm missing something completely obvious and simple but I think I've been staring at it for too long. 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 only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.

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

Received on Tue Jun 12 2012 - 13:03:17 CDT