Hello list,

I have a problem with Interbase 6 not showing NULL values correctly for
integer fields when the source of data is a view based on a left outer join.
I have defined the following view:

CREATE VIEW BUG_ASSIGNED AS
SELECT
PVB.PROJECT,
PVB.MAJOR,
PVB.MINOR,
PVB.RELEASE,
PVB.BUILD,
B.ID,
B.PRIORITY,
B.ENTERED_BY,
B.OPEN,
B.STATUS
FROM
BUG B
LEFT OUTER JOIN PROJECT_VERSION_BUG PVB ON (PVB.BUG = B.ID)
WHERE (ASSIGNED_TO = USER);

When I run the following select statement, I can see the correct <null>
values for PVB.* fields in the ISQL output (e.g. bug 11 has no link to any
project version):

SELECT PVB.PROJECT, PVB.MAJOR, PVB.MINOR, PVB.RELEASE, PVB.BUILD, B.ID FROM
BUG B LEFT OUTER JOIN PROJECT_VERSION_BUG PVB ON (PVB.BUG = B.ID);

PROJECT MAJOR MINOR RELEASE BUILD ID
=========== ====== ====== ======= ====== ===========
1 0 0 0 10 10
1 0 0 1 0 10
2 1 2 0 0 10
<null> <null> <null> <null> <null> 11

But when I run select from the BUG_ASSIGNED view, I can see the following
ISQL output:

SELECT PROJECT, MAJOR, MINOR, RELEASE, BUILD, ID FROM BUG_ASSIGNED;

PROJECT MAJOR MINOR RELEASE BUILD ID
=========== ====== ====== ======= ====== ===========
1 0 0 0 10 10
1 0 0 1 0 10
2 1 2 0 0 10
0 0 0 0 0 11

SELECT PROJECT, MAJOR, MINOR, RELEASE, BUILD, ID FROM BUG_ASSIGNED WHERE
(PROJECT IS NULL);

PROJECT MAJOR MINOR RELEASE BUILD ID
=========== ====== ====== ======= ====== ===========
0 0 0 0 0 11

It seems that NULL values are not returned correctly through the view in
ISQL.

The client versions:
- 5.5
SHOW VERSION;

ISQL Version: WI-V5.5.0.742
InterBase/x86/Windows NT (access method), version "WI-V6.0.0.627"
InterBase/x86/Windows NT (remote server), version "WI-V6.0.0.627/tcp
(development)/P8"
InterBase/x86/Windows NT (remote interface), version "WI-V5.5.0.742/tcp
(ondrej_nt)/P8"
on disk structure version 10.0

- 6.1 (after update, same results)
SHOW VERSION;

ISQL Version: WI-V6.0.0.627
InterBase/x86/Windows NT (access method), version "WI-V6.0.0.627"
InterBase/x86/Windows NT (remote server), version "WI-V6.0.0.627/tcp
(development)/P10"
InterBase/x86/Windows NT (remote interface), version "WI-V6.0.0.627/tcp
(ondrej_nt)/P10"
on disk structure version 10.0

I have the same problem when running the select statement from the view
using the following Interbase access components from Delphi:
IBObjects version 3.4.Cl TIB_Column.IsNull returns False using the view,
True using the table.
IBX version 4.2 (using TIBQuery) TField.IsNull returns False using the view,
True using the table.
Surprisingly, BDE 5.1 (using TQuery) TField.IsNull returns correctly True
both using the view and the table.
Any ideas, solutions or a work-around will be most appreciated.

In case you're interested in the table definitions:

CREATE TABLE PROJECT (
ID UNIQUE_ID, // integer not null
NAME NAME, // varchar(50)
DESCRIPTION DESCRIPTION, // varchar(255)
DELETED BOOLEAN, // char(1) not null

CONSTRAINT PK_PROJECT PRIMARY KEY (ID) // primary key with generator
);

CREATE TABLE PROJECT_VERSION (
PROJECT UNIQUE_ID, // integer not null
MAJOR VERSION_NUMBER, // integer not null
MINOR VERSION_NUMBER, // integer not null
RELEASE VERSION_NUMBER, // integer not null
BUILD VERSION_NUMBER, // integer not null
DESCRIPTION DESCRIPTION, // varchar(255)

CONSTRAINT PK_PROJECT_VERSION PRIMARY KEY (PROJECT, MAJOR, MINOR, RELEASE,
BUILD),
CONSTRAINT FK_PROJECT_VERSION FOREIGN KEY (PROJECT) REFERENCES PROJECT
(ID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

CREATE TABLE BUG (
ID UNIQUE_ID, // integer not null
PRIORITY PRIORITY, // char(1)
TITLE NAME, // varchar(50)
DESCRIPTION DESCRIPTION, // varchar(255)
REPRO_STEPS MEMO, // text blob
RESULT_E MEMO, // text blob
RESULT_A MEMO, // text blob
ENTERED_BY USER_NAME, // char(31)
ASSIGNED_TO USER_NAME, // char(31)
OPEN BOOLEAN DEFAULT 'Y', // char(1)
STATUS BUG_STATUS, // char(1)

CONSTRAINT PK_BUG PRIMARY KEY (ID), // primary key with generator
CONSTRAINT FK_BUGUSER FOREIGN KEY (ASSIGNED_TO) REFERENCES USER_INFO
(USERNAME)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

CREATE TABLE PROJECT_VERSION_BUG (
PROJECT UNIQUE_ID, // integer not null
MAJOR VERSION_NUMBER, // integer not null
MINOR VERSION_NUMBER, // integer not null
RELEASE VERSION_NUMBER, // integer not null
BUILD VERSION_NUMBER, // integer not null
BUG UNIQUE_ID, // integer not null

CONSTRAINT PK_PVB PRIMARY KEY (PROJECT, MAJOR, MINOR, RELEASE, BUILD,
BUG),
CONSTRAINT FK_PVB_VERSION FOREIGN KEY (PROJECT, MAJOR, MINOR, RELEASE,
BUILD) REFERENCES PROJECT_VERSION (PROJECT, MAJOR, MINOR, RELEASE, BUILD)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT FK_PVB_BUG FOREIGN KEY (BUG) REFERENCES BUG (ID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

Thanks a lot in advance for your comments.

TOndrej