Icon View Incident Report

Serious Serious
Reported By: Donat Hebert
Reported On: 1/13/2003
For: Version 3.21 Build 1
# 1302 SQL DELETE Statement with Join Condition Not Working Correctly When No Indexes Exist for Joins

Delete from .. join failing. The following generates two tables to demonstrate the issue. Only works if I use the first value in the table ie '08'. Appears to be a bug as this used to work fine.

 DROP TABLE IF EXISTS "BgSaempb";

CREATE TABLE IF NOT EXISTS "BgSaempb"
(
   "RecIdent" INTEGER,
   "Identifier" INTEGER,
   "EmployerBen" CHARACTER(10)
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 1
);

DROP TABLE IF EXISTS "m1";

CREATE TABLE IF NOT EXISTS "m1"
(
   "Identifier" INTEGER
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 1
);

 insert into m1
 Values ( 3);

 Insert into BgSaempb
 values( 1, 3, '08' );
 Insert into BgSaempb
 values( 2, 3, '20' );
 Insert into BgSaempb
 values( 3, 3, '2890' );
 Insert into BgSaempb
 values( 4, 3, '300' );

 Delete from m1 join BgSaEmpB on BgSaEmpB.Identifier = m1.Identifier
  WHERE BgSaEmpB.EmployerBen in ('2890','300');



Resolution Resolution
Fixed Problem on 1/26/2003 in version 3.22 build 1
Image