Icon View Incident Report

Serious Serious
Reported By: Eric Hargreaves [E.M.A. Computer Solutio
Reported On: 7/5/2012
For: Version 4.32 Build 1
# 3563 Using BETWEEN in Join Condition Can Cause AV

Access violation in dbserver when running a query with a join and between value.

Create two tables first called jobs with three columns, 
one string column called anything you like and two 
date columns JB_DueInDate and JB_DueOutDate.
The second table called WorkDays with two columns WK_Type string size 1 
and wk_day type date.  Put some data in and run the following query 
in standard database system utility program.

select *
from Jobs 
left outer join WorkDays on 
((WK_Type = 'D') and 
(WK_Day between JB_DueInDate and JB_DueOutDate))



Comments Comments
This AV issue is fixed, but this type of join is still completely un-optimized and will take a long time to execute. To optimize such a condition it should be converted into:

select *
from Jobs
left outer join WorkDays on
((WK_Type = 'D') and
(WK_Day >= JB_DueInDate) and
(WK_Day <= JB_DueOutDate))


Resolution Resolution
Fixed Problem on 7/5/2012 in version 4.33 build 2


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image