Icon View Incident Report

Serious Serious
Reported By: Oliver Bock
Reported On: 3/12/2006
For: Version 4.22 Build 6
# 2168 IF() Function That Refereces Multiple Tables in Join Expressions Causes Incorrect Results

The SELECT below should return no rows, but does.

drop table if exists t1;
create table t1 (f1 INTEGER NOT NULL, f2 BOOLEAN NOT NULL, primary 
key (f1));
insert into t1 (f1, f2) values (1, true);
insert into t1 (f1, f2) values (2, false);

drop table if exists t2;
create table t2 (f1 INTEGER NOT NULL, f3 INTEGER, primary key (f1));
insert into t2 (f1) values (3);
insert into t2 (f1, f3) values (4, 0);

select t1.f1, t2.f1, t2.f3, if(t1.f2, t2.f3 is not null, false)
 from t1
 join t2 on t2.f1 = 3 and if(t1.f2, t2.f3 is not null, false);

Comments Comments and Workarounds
The workaround is to put the IF() condition directly in the WHERE clause instead of in the JOIN clause.

Resolution Resolution
Fixed Problem on 3/14/2006 in version 4.23 build 1

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