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

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