View Incident Report

Serious

Reported By: Terry Swiers
Reported On: 3/3/2010
For: Version 2.03 Build 8

# 3157 Query Optimizer Re-Ordering Joins Improperly When Joins Are Already in the Correct Order

I've got what appears to be a bug on inner joins between three tables when the third table is joined by a field from each of the first two tables.

The following query shows the problem. These are all inner joins, so any records in the result set should have the
same value in both poi.stockcode and vc.stockcode since that is one of the joins. But running the query gives the following results

2713,"SYNNEX", "AC-AD-BAR-NUS2#0-USB", "MB-AS-775-P5BVM-MTX", "4220130"

As you can see, the 3rd and 4th columns are returning different values but they should be the same.

If I use a outer join between the vendcd and poitem table, the correct results are returned:

2713,"SYNNEX", "AC-AD-BAR-NUS2#0-USB", "AC-AD-BAR-NUS2#0-USB", "NUSCAN 2000U"

select po.number, po.vendor, poi.stockcode, vc.stockcode, vc.vendorcode 
from po
  join poitem poi on po.number = poi.number
  join vendcd vc on poi.stockcode = vc.stockcode and po.vendor = vc.vendor
where number = 2713

Resolution

Fixed Problem on 3/4/2010 in version 2.03 build 9

Products Affected

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap