Home » SQL & PL/SQL » SQL & PL/SQL » ANSI Join inline view with GROUP BY Bug? (11.1.0.6)
ANSI Join inline view with GROUP BY Bug? [message #341017] Fri, 15 August 2008 09:24 Go to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Has anyone encountered a problem with the following:

1) An inline view that contains a GROUP BY
2) The main query then performs a GROUP BY on the inline view

The result set does not group properly. Below are two queries. The first is the problematic query, and the second is the same query re-written using the "traditional" syntax, which works fine. The execution plan (not included) in both queries are identical.

I noticed the problem in 11.1.0.6.

Here's an example:
SQL> SELECT course_id, AVG(sum_paid) AS avg_paid
  2  FROM (
  3        SELECT a.offering_id
  4             , o.course_id
  5             , SUM(a.amount_paid) AS sum_paid
  6        FROM   attendance a
  7        JOIN   offering o
  8          ON   a.offering_id = o.offering_id
  9        GROUP BY o.course_id, a.offering_id
 10      )
 11  GROUP BY course_id
 12  ORDER BY course_id;

COURSE_ID AVG_PAID
--------- --------
      215     4000
      315     4000
      515     4000
      615     4000
      815     4000
      915     4000

6 rows selected.


Yet it works fine with the older syntax:

SQL> SELECT course_id, AVG(sum_paid) AS avg_paid
  2  FROM (
  3        SELECT a.offering_id
  4             , o.course_id
  5             , SUM(a.amount_paid) AS sum_paid
  6        FROM   attendance a, offering o
  7        WHERE  a.offering_id = o.offering_id
  8        GROUP BY o.course_id, a.offering_id
  9       )
 10  GROUP BY course_id
 11  ORDER BY course_id;

COURSE_ID AVG_PAID
--------- --------
      215     5985
      315  4661.67
      515     2995
      615     5700
      815     4990
      915     5200

6 rows selected.

Re: ANSI Join inline view with GROUP BY Bug? [message #341019 is a reply to message #341017] Fri, 15 August 2008 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we don't have your tables, indexes and statistics we can't confirm or not in this version or other ones.
Activate a trace and see if the actual (and not explain plan) plans are same.
I think optimizer (maybe runtime one) wrongly rewrites the query to merge the inline view and so the 2 "group by".

Regards
Michel
Re: ANSI Join inline view with GROUP BY Bug? [message #341024 is a reply to message #341017] Fri, 15 August 2008 09:56 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
So is that a "yes" or "no" you've seen this before? Regardless of the reason for the difference in values, I am most concerned that previous queries I have written using this technique may contain bad results.
Re: ANSI Join inline view with GROUP BY Bug? [message #341025 is a reply to message #341017] Fri, 15 August 2008 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: ANSI Join inline view with GROUP BY Bug? [message #341029 is a reply to message #341024] Fri, 15 August 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I said what I said, post the requested information.

Regards
Michel
Re: ANSI Join inline view with GROUP BY Bug? [message #341047 is a reply to message #341029] Fri, 15 August 2008 17:18 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Well it looks a lot like a bug. You should run a search on Metalink to see if anyone else ha found it. That is MUCH more likely to turn up a similar case than posting here.

When you get a bug, try to create a self-contained test case that creates a table, inserts data, then demonstrates the error. That way when you send it to Oracle, they can re-create the problem.

When you have this test case, you might find that you CANNOT repeat the problem - it only happens with your one table. This may mean it is LESS likely to happen on other queries.

Alternatively, the test case does demonstrate the error. You can try it on other Oracle versions. You can also post it here and we can try it.

Ross Leishman
Previous Topic: Trigger
Next Topic: How to write this query?
Goto Forum:
  


Current Time: Wed Dec 07 06:45:04 CST 2016

Total time taken to generate the page: 0.45096 seconds