View Incident Report

Serious

Reported By: Robert Kaplan
Reported On: 11/6/2000
For: Version 2.04 Build 1

# 654 SQL SELECT Inner Joins Not Working Properly in Multi-Way Join Scenario

The problem was that, quering the same identical tables, I would get a different result on a MAX query for a date. The curious thing is that the query works in every condition except when 2 tables, Make and Model, unrelated to the table from which we extract the MAX date are used in the SELECT.

The queries are (can all be run with DBSYS, I'm enclosing the required SQL files and tables):

1) Bothfields - reads both make and model, produce a bad date field
2) Nomake - same query except that I drop the Make table, works fine
3) Nomodel - ditto for Model table
4) Noproduct - dropped another table, to see if the problem was in any way related to the number of tables used. This query, since it uses both the Make and Model tables, also produces a bad result in the date field.

{ BothFields.SQL - causes problem }

select u_id id,
max(dl_date) "Last Tran.",
u_vin "VIN",
cast(0 as integer) Tiebreak,
u_origamt "Amount Floored",
u_currbal "Current Balance",
pl_cust "Cust No",
pr_description "Product",
pl_desc "Plan",
u_make,
u_model,
rc_code RC,
md_desc "Model",
mk_desc "Make" 
from units, dolls, plans, product, rc, model md, make mk
where (u_id between 280 and 290) and (u_id = dl_unit) and
(u_plan=pl_id) and (pl_prod = pr_id) and (pl_rc = rc_id) and
(u_model = md.md_id) and (u_make = mk.mk_id)
group by u_id
order by u_id

Comments and Workarounds

The problem was that the query was not including all of the records it should have been. The workaround in this case was to reverse the position of the first two tables in the FROM clause.

Resolution

Fixed Problem on 11/8/2000 in version 2.05 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap