Possible SQL Bug? What do you think?

From: <buckeye714_at_my-deja.com>
Date: 2000/03/30
Message-ID: <8c0e40$m59$1@nnrp1.deja.com>#1/1

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.

Does anybody have any clues or concerns? What are your thoughts?

Thanks for any help.
Patrick ...

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