Skip site navigation (1) Skip section navigation (2)

Re: '= NULL' is not the same as 'IS NULL'

From: Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>
To: greg(dot)campbell(at)us(dot)michelin(dot)com
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: '= NULL' is not the same as 'IS NULL'
Date: 2006-09-08 16:12:52
Message-ID: 45019684.9010902@tpf.co.jp (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi greg,

greg(dot)campbell(at)us(dot)michelin(dot)com wrote:
> Thanks Hiroshi  for the insight and the history lesson.
>
> In light of this I decided to further my education by doing some testing
> against a simple table.
>
> CREATE TABLE test
> (
>   emp_id serial NOT NULL,
>   last_name varchar(20) NOT NULL,
>   first_name varchar(20) NOT NULL,
>   "level" int4,
>   CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
> )
>
> In my table I have a field named "level" which is allowed to be NULL.
>
> I set the DSN to do row versioning, and ran the pgODBC CommLog at the same
> time as the ODBC Trace (because a good masochist loves to observe the
> statement handles).
> In general I did not find the MSAcess to do WHERE match querying before an
> update, but it did do one after an update to apparently count the number of
> rows, to see if what it just updated took hold.
>   
I can see insert examples but can't see update ones here.
Insert operations are pretty different from update ones.
We have little to do before insert operations.

> There is a general pattern of
> 1.DML Executable (INSERT, UPDATE)
> 2. Select on primary_key =  NULL (should fetch 0 rows)
> 3. Commit
> 4. Select on primary_key =  NULL (should fetch 0 rows)
> 5. Select with the WHERE (exact match each non NULL field) to count the
> exact match, should be 1 ? or more?
> 6. Select using the primary key, (used to refresh the screen I think.)
>   

I understand what MSACCESS means a little but not wholly.
> CommLog===============================================
> conn=147538008, query='INSERT INTO  "public"."test"
> ("last_name","first_name") VALUES ('Rubble','Barney')'
>   

The serious problem here is that the primary key was not given in the 
above INSERT statement.
Firstly MSAccess guesses the primary key might have been NULL because 
ommitted values
are generally NULL (MSAccess doesn't know inside PostgreSQL at all) and 
issues the following
command.

> conn=147538008, query='SELECT
> "emp_id","last_name","first_name","level","xmin"  FROM "public"."test"
> WHERE "emp_id" IS NULL'
>     [ fetched 0 rows ]
> conn=147538008, query='COMMIT'
>   
I don't know why MSAccess tries the same operation twice.


> conn=147538008, query='SELECT
> "emp_id","last_name","first_name","level","xmin"  FROM "public"."test"
> WHERE "emp_id" IS NULL'
>     [ fetched 0 rows ]
>   
Here MSAccess gives up the direct primary key approach and searches key 
of the
inserted record using known item values expecting that the matching 
records are a few.
> conn=147538008, query='SELECT "public"."test"."emp_id" FROM "public"."test"
> WHERE "last_name" = 'Rubble' AND "first_name" = 'Barney''
>     [ fetched 1 rows ]
>   

Fortunately just 1 record was returned in this example.
What MSAccess has really wanted to know was the whole content of the 
inserted record.
So it issues the following command finally.

> conn=147538008, query='SELECT
> "emp_id","last_name","first_name","level","xmin"  FROM "public"."test"
> WHERE "emp_id" = 3'
>     [ fetched 1 rows ]

regards,
Hirshi Inoue

pgsql-odbc by date

Next:From: Wladimir Torres Correa .Date: 2006-09-08 17:24:18
Subject: unsuscribe
Previous:From: Zubkovsky, SergeyDate: 2006-09-08 13:04:57
Subject: COPY ... FROM STDIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group