Обсуждение: fyi

previous table definition guess no not null of default ''

curreent table definitionCREATE TABLE apps
(
Id      int not null unique,    -- AppId
Sym     varchar(8) not null default '', -- app symbol name
Nam     varchar(30) not null default '',    -- app full name
BitPriv varchar(10) not null default '',    -- app bit priv
NamPriv varchar(255) not null default '',   -- app nam priv
AppDir  varchar(80) not null default '',    -- app dir nam
AppPag  varchar(32),                -- app page nam
Aka     int,                    -- ptr to real name
Uid     int not null default 0, -- ptr to las mod user id
Dlm     timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE apps
(
Id      int not null unique,    -- AppId
Sym     varchar(8) not null default '', -- app symbol name
Nam     varchar(30) not null default '',    -- app full name
BitPriv varchar(10) not null default '',    -- app bit priv
NamPriv varchar(255) not null default '',   -- app nam priv
AppDir  varchar(80) not null default '',    -- app dir nam
AppPag  varchar(32) not null default '',    -- app page nam
Aka     int,                    -- ptr to real name
Uid     int not null default 0, -- ptr to las mod user id
Dlm     timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

query on pqsql postgresql7-7.0.2.k62-8 redhat 7.2
select * from apps where (not (apppag = null or apppage = ''));
returns data not sure if current table actualy has not null default ''
on apppag field
but I know that was the reason for the query being as it was

query on pgsql postgresql 7.3.2-3 redhat 9.0 with new table definition
select * from apps where (not (apppag = null or apppag = ''))
returns nothing 0 rows
select * from apps where (not apppag = null);
returns nothing when it should return every thing
select * from apps where (not apppag = '');
returns data as expected

Lessons learned as programer always check database definitions only
check for what is possible. (not my style I like to write paranoid code)
(never trust the dba)

lessens learned as a dba always prevent as much illeagle, unwanted data
as possible. (I try when ever I get the enough time to spend on being a
dba)

and finialy the programer and the dba should talk often to optimize code
espesialy when there the same person.

    Arno

On Thu, 31 Jul 2003, maillist wrote:

> select * from apps where (not apppag = null);
> returns nothing when it should return every thing

No.  It should return nothing.  apppag = null returns unknown for every
row.  Use IS NULL to test if a value is null.

Stephan Szabo wrote:
>
> On Thu, 31 Jul 2003, maillist wrote:
>
> > select * from apps where (not apppag = null);
> > returns nothing when it should return every thing
>
> No.  It should return nothing.  apppag = null returns unknown for every
> row.  Use IS NULL to test if a value is null.
hmmm now i'm confused when i set a value i don't use the phrase to null
and '= null' has always worked in the past, and if '= null' doesn't work
it should be an invalid query because i can see no other use for it, and
for the most part is, to are ignored anyways.

so would the correct query be
select * from apps where (not apppag = is null)
or
select * from apps where (not apppag is null)
both of these look silly, but I do use null in other fields on purpuse
to represent uninitailized fields. So I definitlay am going to have to
grep my code for "=*null" and check to see what needs to be done. Ijust
thought it was a bug, that should not have been found, because the query
had un needed checks. I need to do more testing before I allow
production servers to upgrade

    Arno

On Fri, 1 Aug 2003, maillist wrote:

> Stephan Szabo wrote:
> >
> > On Thu, 31 Jul 2003, maillist wrote:
> >
> > > select * from apps where (not apppag = null);
> > > returns nothing when it should return every thing
> >
> > No.  It should return nothing.  apppag = null returns unknown for every
> > row.  Use IS NULL to test if a value is null.
> hmmm now i'm confused when i set a value i don't use the phrase to null
> and '= null' has always worked in the past, and if '= null' doesn't work
> it should be an invalid query because i can see no other use for it, and
> for the most part is, to are ignored anyways.

Old versions converted an explicit token sequence like = NULL into IS NULL
for you (due to broken clients). Newer versions have a compatibility GUC
configuration option transform_null_equals which turns it on (which I
usually forget about).  However, the option should generally be
discouraged because it is inconsistent (= NULL is converted, but comparing
two variables where the second is NULL does not for example and not
foo=null works but foo!=null doesn't). The change was made to better
fit the SQL specification.

As for =NULL in general, it's pretty meaningless but meets our syntactic
constraints.

> so would the correct query be
> select * from apps where (not apppag = is null)
> or
> select * from apps where (not apppag is null)

Actually since you're doing not, I'd suggest
where (apppag is not null)

> both of these look silly, but I do use null in other fields on purpuse
> to represent uninitailized fields. So I definitlay am going to have to
> grep my code for "=*null" and check to see what needs to be done. Ijust
> thought it was a bug, that should not have been found, because the query
> had un needed checks. I need to do more testing before I allow
> production servers to upgrade

You can turn on the option as a short term thing, although I'd suggest
changing the queries in any case.