> -----Original Message-----
> From: Claudio Valderrama C. [mailto:cvalde@...]
> Sent: Domingo 7 de Enero de 2001 0:28
> To: IBObjects@egroups.com
>
> 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.

Hi, Ondrej. I tested the scripts you sent me directly and this is just
another flaw in IB6's views. The most ridiculous part is:
select project
from bug_assigned
where project is null
=> you get ZERO in the "project" column! That seems like an internal
conversion problem. The engine is doing the left join okay and honoring the
filtering clauses, so it knows the field is NULL. However, the field is
reported as value zero instead of non-value NULL to the client. I found,
only for curiosity, that if you do:

select pvb.project, pvb.mjr, pvb.mnr, pvb.rls, pvb.bld, b.id, b.priority,
b.entered_by, b.closed, b.status
from bug b left outer join project_version_bug pvb
on (pvb.bug = b.id) where b.assigned_to = user
union
select * from bug_assigned

You get the right results, being "bug_assigned" a view that contains exactly
the same SQL that it's being put in the first part of the UNION. Of course,
this defeats the purpose of the view and executes two times the same
statement, first directly and second inside the view... but it shows correct
results. Try to use a selectable stored procedure if you can; it will give
you the right answer:

set term ^;
create procedure defeat_bug
returns(project int, mjr smallint, mnr smallint,
rls smallint, bld smallint, id int, priority smallint, entered_by char(31),
closed char, status char)
as begin
for
select pvb.project, pvb.mjr, pvb.mnr, pvb.rls, pvb.bld,
b.id, b.priority, b.entered_by, b.closed, b.status
from bug b left outer join project_version_bug pvb
on (pvb.bug = b.id) where b.assigned_to = user
into
:project, :mjr, :mnr, :rls, :bld,
:id, :priority, :entered_by, :closed, :status
do suspend;
end ^
set term ;^

I made a public answer to make aware others of the flaw. Please go to
SourceForge.net at
http://firebird.sourceforge.net
and devote time to read the list of bugs that have been logged against
Firebird. This link takes you straight to the bug list:
http://sourceforge.net/bugs/?group_id=9028
if you do not want to read more about Firebird. There're at least 3 bugs
more that are directly related to views.

C.