Re: Possible SQL Bug? What do you think?

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.