Re: Possible SQL Bug? What do you think?

You are absolutely correct. I included the original query, but the output was from a test case we sent to Oracle support. We removed all personal data before we sent it to Oracle. I am positive that there are no bitmap indexes. We did do explain plans on all cases and nothing seemed out of the ordinary. My concern is that with all else being equal, (No statistics on the tables and no primary key defined on emp_class_code) if I list emp_class_code before emp_job_code in the from clause, I get the correct output. If I list emp_job_code before emp_class_code in the from clause, I get the wrong output. I couldn't think of a reason that this could happen.

I will definitely try your suggestions below and see what I come up with. Thanks for the input.

FYI .... I checked the tar on MetaLink this evening and Oracle has opened a bug based on this.

Thanks again.

Patrick

In article <954451720.5428.0.nnrp-01.9e984b29_at_news.demon.co.uk>, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> You output is not consistent with
> the sample query in either case,
> a request for employee '452871824'
> has returned employee 000000000,
> but I assume this is just a copy error.
>
> Have you checked the different execution
> paths ? Cost based and rule based are
> probably using different table orders and
> indexes to acquire the data.
>
> It may be that one of the sundry index
> inconsistency bugs exists in an index
> being used by the rule based optimiser.
>
> (Are there any bitmap indexes involved ?
> this used to be a relatively common
> cause of errors).
>
> You could try
> analyze table XXX validate structure cascade;
> on each table in turn to see if Oracle thinks
> that the indexes are consistent with the tables;
> or you could simply rebuild all the indexes and
> see if anything changes.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> buckeye714_at_my-deja.com wrote in message <8c0e40$m59
 $1_at_nnrp1.deja.com>...
> >One of our developers came across the following problem, if you
 change
> >the order of the second and third table in the following query, then
> >different data is returned.
> >
> >select e.employee_id,
> > e.job_code,
> > ecc.class_desc,
> > e.class_code,
> > ecc.class_code
> > from employee_dim e,
> > emp_job_code ejc,
> > emp_class_code ecc
> > where e.employee_id = '452871824'
> > and e.class_code = ecc.class_code
> > and e.month_number = ecc.month_number
> > and e.year_number = ecc.year_number
> > and e.job_code = ejc.job_code
> > and e.month_number = ejc.month_number
> > and e.year_number = ejc.year_number
> > and e.month_number = 3
> > and e.year_number = 2000;
> >
> >The following incorrect data is returned from the above query:
> >EMPLOYEE_ JOB_ CLASS_DESC CL CL
> >000000000 3145 Temporary F F
> >
> >Change the order of the tables in the where clause:
> >select e.employee_id,
> > e.job_code,
> > ecc.class_desc,
> > e.class_code,
> > ecc.class_code
> > from employee_dim e,
> > emp_class_code ecc,
> > emp_job_code ejc
> > where e.employee_id = '452871824'
> > and e.class_code = ecc.class_code
> > and e.month_number = ecc.month_number
> > and e.year_number = ecc.year_number
> > and e.job_code = ejc.job_code
> > and e.month_number = ejc.month_number
> > and e.year_number = ejc.year_number
> > and e.month_number = 3
> > and e.year_number = 2000;
> >
> >and the correct data is returned:
> >EMPLOYEE_ JOB_ CLASS_DESC CL CL
> >000000000 3145 Full Time F F
> >There is only one record in ecc with class_code = 'F' and the
> >corresponding class_desc is 'Full Time'
> >
> >Additional research found that the error goes away if we have a
 primary
> >key on emp_class_code, or if we run statistics on each of the three
> >tables and use the cost based optimizer. If we have statistics, but
> >force the use of the rule based optimizer with a hint, then the bad
 data
> >is returned. For example:
> >"select /*+ RULE */ e.employee_id" returns the incorrect data.
> >
> >We were running Oracle 8.1.5.0 on Solaris 2.6. We called Oracle
 support
> >and they suggested we install the 8.1.5.1 patch. We did, but the
 problem
> >still exists.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 31 2000 - 00:00:00 CST