Re: possible mis-handling of nulls in views in 7.0.2

Tom,

I tried to build a simple test case and I couldn't reproduce either.
I'm still actively working with that database, though-- If I run into it
again, and can reproduce a condensed case, I'll definitely submit it. It
sounds like views are being improved a good deal in 7.1, so perhaps my
oddity would be repaired anyway. Thanks for checking it out.

-mark

http://mark.stosberg.com/

Tom Lane wrote:
>
> Mark Stosberg <mark(at)summersault(dot)com> writes:
> > I'm running Postgres 7.0.2 and have run into a curious situation. I
> > got a back a null value in a select on VIEW that is defined as not
> > allowing that column to be null.
>
> I think this is an artifact of the curious (not to say broken)
> implementation of views pre-7.1. However, it's hard to tell for sure
> because I can't reproduce your problem. Are you sure you are running
> 7.0.2 and not something older? Can you provide a self-contained
> example? My test went like this:
>
> play=> select version();
> version
> ------------------------------------------------------------------
> PostgreSQL 7.0.2 on hppa2.0-hp-hpux10.20, compiled by gcc 2.95.2
> (1 row)
>
> play=> create table foo (f1 int, f2 int);
> CREATE
> play=> insert into foo values(1,2);
> INSERT 873546 1
> play=> insert into foo values(1,3);
> INSERT 873547 1
> play=> insert into foo values(2,4);
> INSERT 873548 1
> play=> insert into foo values(2,5);
> INSERT 873549 1
> play=> create view v2 as select f1,sum(f2) from foo where f1 notnull group by f1;
> CREATE 873571 1
> play=> select * from v2 ;
> f1 | sum
> ----+-----
> 1 | 5
> 2 | 9
> (2 rows)
>
> play=> select * from v2 where f1 isnull;
> f1 | sum
> ----+-----
> (0 rows)
>
> regards, tom lane

--

http://mark.stosberg.com/