Hi, can you extract full metadata with
isql -a
and send me it directly to cvalde (at) myrealbox.com, please? I do not want
to lose time changing your definitions by hand to use hardcoded field types
instead of your domains. I hope I can reproduce the issue by entering a few
data rows by hand; otherwise I would prefer a backup of your gdb if it's
small.

C.

> -----Original Message-----
> From: Ondrej Kelle [mailto:O.Kelle@...]
> Sent: Viernes 5 de Enero de 2001 6:07
> To: ib-support@egroups.com; IBObjects@egroups.com
> Subject: [IBO] NULL incorrectly returned as 0 through a left outer join
> view
>
>
> 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
>
>
>
>