Обсуждение: '= NULL' is not the same as 'IS NULL'

Hi,

I managed to fix my write conflict problems in Ms Access. Now I already
stumble upon another issue:

I have a psqlODBC linked table 'tarticles' in my Ms Access 2000 application,
and a form 'Artikels' to make changes to this table. One peculiarity of this
form is, that one of the varchar fields gets set programmatically.

For example, when I create a new record, and save it (by moving to a new empty
record), I find this in the commlog:
conn=995a250, query='INSERT INTO  "public"."tarticles"
("vendorid","reference","category","type","islot","amount","invoicecurrency",
"invoiceprice","priceunit","pricecustomer","invoiceid","check","label","imgname","amountlot")
VALUES
(235,'00-00007',2,7,'1','100',1,'100','1','0',921,'0','Diverse',NULL,'100')'

The field imgname was set to NULL programmatically.
When I move one record back in the form, to the record that I just added, it
will show #deleted# in every field.

This move back is represented in the commlog by:
conn=995a250, query='declare "SQL_CUR0995ED90" cursor with hold for
SELECT "public"."tarticles"."articleid" FROM "public"."tarticles"
WHERE "vendorid" = 235 AND "reference" = '00-00007' AND "category" = 2
AND "type" = 7 AND "islot" = '1' AND "amount" = '100' AND "invoicecurrency" =
1 AND "invoiceprice" = '100' AND "priceunit" = '1' AND "pricecustomer" = '0'
AND "invoiceid" = 921 AND "check" = '0' AND "label" = 'Diverse' AND
"imgname" = NULL AND "amountlot" = '100''

I tried this query directly in phpPgSQL, and indeed it returns no results. On
the other hand, if I modify "imgname" = NULL to "imgname" IS NULL, the query
works fine.

I found references via google that '= NULL' and 'IS NULL' are not the same
thing as far as PostgreSQL is concerned, which I understand.
They even provide a runtime command to override this:
SET transform_null_equals TO ON;
Unfortunatly it seems this should be called before each query that is
affected. It's not set globally.

So I'm not sure how I can solve this issue. I could work around it and make
sure no NULL values can happen, but I wonder if there might be an easier way
by means of a preference setting in Access or psqlODBC or PostgreSQL.


Has anybody else stumbled upon this issue ?


Thanks,

Geert
--
Kobalt W.I.T.
Web & Information Technology
Brusselsesteenweg 152
1850 Grimbergen

Tel  : +32 479 339 655
Email: info@kobaltwit.be

Geert Janssens wrote:

> I tried this query directly in phpPgSQL, and indeed it returns no results. On
> the other hand, if I modify "imgname" = NULL to "imgname" IS NULL, the query
> works fine.
[...]
> Has anybody else stumbled upon this issue ?

I'm afraid so, in Joe Celko's "SQL for Smarties" book, after which I simply
accepted that that was the way SQL worked. He also comments on the IS TRUE etc.
form which I see PostgreSQL now supports, also in the relevant section of the
manual I see IS DISTINCT FROM which might help.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

> I'm afraid so, in Joe Celko's "SQL for Smarties" book, after which I simply
> accepted that that was the way SQL worked.

Yes for what I recall from my reading that he mentions that there are specific reasons to allow
nulls.  During Table design, if one cannot see how these specific rules apply to the data to of a
column, then the column should be constrained as NOT NULL, with a DEFAULT value as '{left blank}'
or 0 or some other value that would produce this effect.

But I am sure that this is just one particular philosophy of table design.

Regards,

Richard Broersma Jr.

I have a question for the mailing list.
More generally, let us assume that when in Access, you change a record focus (after a delete, insert, update. upon opening a form, or moving the Access cursor), it may be reasonable to execute one or more SELECT queries to refresh the form.
Why does it tend to use the WHERE clause to match each field value, instead of a primary key for a match? What are the conditions that force primary key usages vs. "match every field" syntax?

And it appears Geert may have found a bug perhaps (in pgODBC or in Access)? Where the WHERE clause match for NULL values tries to use an =NULL syntax instead of IS NULL .
When I turn on ODBC tracing , from ODBC Administrator -- outside of the pgODBC DSN, the trace shows parameterized ODBC syntax. I am not sure if the basic query (for example:
"SELECT ? WHERE emp_id=? AND customer_id=? and item_code=?" comes from Access/Jet or from the pgODBC driver. It seems like it would come from the client (Access) and mere mortals would be helpless to change it. The fact that a parameter has a NULL value (resulting in perhaps customer =NULL, which of course is not valid SQL) does not change that basic query. A fix would require the driver to understand a NULL value and change the syntax from =NULL or <>NULL to IS NULL or NOT IS NULL on the fly.

I am afraid I might be speculating over my head. I really am seeking to understand. So anybody who can shed light on these things please pitch in.




Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg.campbell@us.michelin.com

Geert Janssens <info@kobaltwit.be>

Geert Janssens <info@kobaltwit.be> Sent by: pgsql-odbc-owner@postgresql.org 09/06/2006 09:55

To pgsql-odbc@postgresql.org cc Subject [ODBC] '= NULL' is not the same as 'IS NULL'

greg.campbell@us.michelin.com wrote:
> I have a question for the mailing list.
> More generally, let us assume that when in Access, you change a record
> focus (after a delete, insert, update. upon opening a form, or moving the
> Access cursor), it may be reasonable to execute one or more SELECT queries
> to refresh the form.
> Why does it tend to use the WHERE clause to match each field value, instead
> of a primary key for a match? What are the conditions that force primary
> key usages vs. "match every field" syntax?
>

 From ancient times it's programmers' routine work to verify if the
target record was changed
or not before updat/deleting the record. MS seems to prefer the way to
verify and update/delete
at a time and executes an update/delete query with  WHERE clause to
match each field value.
If the query update/deletes exactly one row it means that both the
verification and the execution
are OK. If it update/deletes no row it probably meas that  someone
changed the record somewhere.
IMHO it's a lot simpler than the way to get the record with lock and
verify the change by the
program by itself and update/delete the record.

regarsds,
Hiroshi Inoue

Hi greg,

greg.campbell@us.michelin.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