pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedbehavior of ' = NULL' vs. MySQL vs. Standards
19+ messages / 9 participants
[nested] [flat]
* behavior of ' = NULL' vs. MySQL vs. Standards
@ 2001-06-07 01:00 Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 19+ messages in thread
From: Mark Stosberg @ 2001-06-07 01:00 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
Hello,
I'm a long time Postgres user who uses MySQL when I have to. I recently
ran into an issue with MySQL where this construct didn't do what I expect:
WHERE date_column = NULL
I expected it to work like "date_column IS NULL" like it does it
Postgres 7.0.2, but instead it returned an empty result set.
After conversing with some folks on the MySQL list, it was mentioned that:
* "NULL is *NOT* a value. It's an absence of a value, and doing *any*
comparisons with NULL is invalid (the result must always be NULL, even
if you say "foo = NULL")."
* Postgres handling is non-standard (even if it's intuitive.)
My questions then are: 1.) What IS the standard for handling NULLs? and
then 2.) If Postgres handling is different than the standard, what's the
reason?
To me, having " = NULL" be the same as " IS NULL" is intuitive and thus
useful, but I also like appeal of using standards when possible. :)
Thanks!
-mark
http://mark.stosberg.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
@ 2001-06-07 01:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Stephan Szabo @ 2001-06-07 01:12 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-sql@postgresql.org
On Wed, 6 Jun 2001, Mark Stosberg wrote:
>
> Hello,
>
> I'm a long time Postgres user who uses MySQL when I have to. I recently
> ran into an issue with MySQL where this construct didn't do what I expect:
>
> WHERE date_column = NULL
>
> I expected it to work like "date_column IS NULL" like it does it
> Postgres 7.0.2, but instead it returned an empty result set.
>
> After conversing with some folks on the MySQL list, it was mentioned that:
>
> * "NULL is *NOT* a value. It's an absence of a value, and doing *any*
> comparisons with NULL is invalid (the result must always be NULL, even
> if you say "foo = NULL")."
>
> * Postgres handling is non-standard (even if it's intuitive.)
>
> My questions then are: 1.) What IS the standard for handling NULLs? and
> then 2.) If Postgres handling is different than the standard, what's the
> reason?
>
> To me, having " = NULL" be the same as " IS NULL" is intuitive and thus
> useful, but I also like appeal of using standards when possible. :)
Yes, column = NULL should *never* return true according to the spec (it
should always return NULL in fact as stated). The reason for breaking
with the spec is AFAIK to work with broken microsoft clients that seem to
think that =NULL is a meaningful test and generate queries using that.
In general, =NULL should be avoided in favor of IS NULL by users that are
generating their own queries.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-06-07 02:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 03:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 13:41 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Thomas Lockhart <lockhart@fourpalms.org>
2001-06-07 15:53 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Josh Berkus <josh@agliodbs.com>
2001-06-07 19:17 ` Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Ivar Helbekkmo <tih@kpnQwest.no>
0 siblings, 4 replies; 19+ messages in thread
From: Tom Lane @ 2001-06-07 02:19 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org; pgsql-hackers
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Yes, column = NULL should *never* return true according to the spec (it
> should always return NULL in fact as stated). The reason for breaking
> with the spec is AFAIK to work with broken microsoft clients that seem to
> think that =NULL is a meaningful test and generate queries using that.
Microsoft Access is the guilty party, IIRC. I recently tried to stir up
some interest in changing this behavior back to the standard, but
apparently there are still too many people using broken versions of
Access.
A compromise answer might be to offer a SET variable that selects the
Microsoft-compatible misimplementation. Would that fly?
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-07 03:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 10:00 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 14:40 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
3 siblings, 2 replies; 19+ messages in thread
From: Stephan Szabo @ 2001-06-07 03:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org; pgsql-hackers
On Wed, 6 Jun 2001, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Yes, column = NULL should *never* return true according to the spec (it
> > should always return NULL in fact as stated). The reason for breaking
> > with the spec is AFAIK to work with broken microsoft clients that seem to
> > think that =NULL is a meaningful test and generate queries using that.
>
> Microsoft Access is the guilty party, IIRC. I recently tried to stir up
> some interest in changing this behavior back to the standard, but
> apparently there are still too many people using broken versions of
> Access.
>
> A compromise answer might be to offer a SET variable that selects the
> Microsoft-compatible misimplementation. Would that fly?
It would for me. I'd rather have the default be the spec correct behavior
and let people configure their server to follow the misinterpretation.
Is the conversion just the hack in the grammar rules for
a_expr '=' a_expr?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 03:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-06-07 10:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 19+ messages in thread
From: Tom Lane @ 2001-06-07 10:00 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org; pgsql-hackers
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> A compromise answer might be to offer a SET variable that selects the
>> Microsoft-compatible misimplementation. Would that fly?
> It would for me. I'd rather have the default be the spec correct behavior
> and let people configure their server to follow the misinterpretation.
Yes, I agree.
> Is the conversion just the hack in the grammar rules for
> a_expr '=' a_expr?
AFAIK that's it. It might be a little tricky to do cleanly,
though, since gram.y shouldn't look at SET vars. Really, the
conversion would have to be done in analyze.c instead.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 03:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-06-07 14:40 ` Mark Stosberg <mark@summersault.com>
2001-06-07 14:59 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards ANDREW PERRIN <aperrin@email.unc.edu>
1 sibling, 1 reply; 19+ messages in thread
From: Mark Stosberg @ 2001-06-07 14:40 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@postgresql.org; pgsql-hackers
Stephan Szabo wrote:
>
> On Wed, 6 Jun 2001, Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > > Yes, column = NULL should *never* return true according to the spec (it
> > > should always return NULL in fact as stated). The reason for breaking
> > > with the spec is AFAIK to work with broken microsoft clients that seem to
> > > think that =NULL is a meaningful test and generate queries using that.
> I'd rather have the default be the spec correct behavior
> and let people configure their server to follow the misinterpretation.
I like that idea as well. Someone like me who didn't know that this
feature was in there for M$ could have assumed it _was_ standard
behavior, and started using it as a habit. Then when I started porting
my code to another database, I'd have an extra surprise in for me. :)
Rather than being an option targeted at just this piece of grammer,
perhaps it could a piece of a potentially larger option of "stricter
standards compliance." I realize there are a number of useful extensions
to the SQL standard in Postgres (which I like and use.), but it seems
like there would be uses for minimizing non-standard behavior, as well.
Thank you all for your contributions to Postgres-- I use it everyday. :)
-mark
http://mark.stosberg.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 03:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 14:40 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
@ 2001-06-07 14:59 ` ANDREW PERRIN <aperrin@email.unc.edu>
0 siblings, 0 replies; 19+ messages in thread
From: ANDREW PERRIN @ 2001-06-07 14:59 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@postgresql.org; pgsql-hackers
Interesting - my experience is that Access, at least, generally treats
NULL's correctly:
(This was done under Access 2000):
create table foo (name text(20))
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values ("bar");
insert into foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);
insert into foo values (NULL);
select count(*) from foo where name=NULL;
returns 0
select count(*) from foo where name is null;
returns 4
select count(*) from foo where name <> "bar";
returns 0
Cheers,
Andy
---------------------------------------------------------
Andrew J. Perrin - Assistant Professor of Sociology
University of North Carolina, Chapel Hill
269 Hamilton Hall CB#3210, Chapel Hill, NC 27599-3210 USA
andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
On Thu, 7 Jun 2001, Mark Stosberg wrote:
> Stephan Szabo wrote:
> >
> > On Wed, 6 Jun 2001, Tom Lane wrote:
> >
> > > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > > > Yes, column = NULL should *never* return true according to the spec (it
> > > > should always return NULL in fact as stated). The reason for breaking
> > > > with the spec is AFAIK to work with broken microsoft clients that seem to
> > > > think that =NULL is a meaningful test and generate queries using that.
>
> > I'd rather have the default be the spec correct behavior
> > and let people configure their server to follow the misinterpretation.
>
> I like that idea as well. Someone like me who didn't know that this
> feature was in there for M$ could have assumed it _was_ standard
> behavior, and started using it as a habit. Then when I started porting
> my code to another database, I'd have an extra surprise in for me. :)
>
> Rather than being an option targeted at just this piece of grammer,
> perhaps it could a piece of a potentially larger option of "stricter
> standards compliance." I realize there are a number of useful extensions
> to the SQL standard in Postgres (which I like and use.), but it seems
> like there would be uses for minimizing non-standard behavior, as well.
>
> Thank you all for your contributions to Postgres-- I use it everyday. :)
>
> -mark
>
> http://mark.stosberg.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-07 13:41 ` Thomas Lockhart <lockhart@fourpalms.org>
3 siblings, 0 replies; 19+ messages in thread
From: Thomas Lockhart @ 2001-06-07 13:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org; pgsql-hackers
> > Yes, column = NULL should *never* return true according to the spec (it
> > should always return NULL in fact as stated). The reason for breaking
> > with the spec is AFAIK to work with broken microsoft clients that seem to
> > think that =NULL is a meaningful test and generate queries using that.
> Microsoft Access is the guilty party, IIRC. I recently tried to stir up
> some interest in changing this behavior back to the standard, but
> apparently there are still too many people using broken versions of
> Access.
Since according to the standard "column = NULL" is a near-useless
construct (equivalent to "FALSE") it does not seem to pollute the
grammar much to allow an M$ compatible interpretation. I was not happy
having it added (much better to ask that responsive, customer-focused
company to fix their language compliance) but now that it is there it
seems to be an isolated and manageable feature.
- Thomas
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-07 15:53 ` Josh Berkus <josh@agliodbs.com>
2001-06-08 14:53 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 19+ messages in thread
From: Josh Berkus @ 2001-06-07 15:53 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
Tom,
>Microsoft Access is the guilty party, IIRC. I recently tried to stir
> up
> some interest in changing this behavior back to the standard, but
> apparently there are still too many people using broken versions of
> Access.
Which verison? I work with MSAccess 97 a lot, and the supported syntax
is "IS NULL".
> A compromise answer might be to offer a SET variable that selects the
> Microsoft-compatible misimplementation. Would that fly?
Sounds good to me. By default, I'd like to see "=NULL" turned OFF, as
I've been writing my SQL on the idea that "=NULL" will return NULL, and
if "=NULL" starts returning a value it's going to screw several things
up ...
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 15:53 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Josh Berkus <josh@agliodbs.com>
@ 2001-06-08 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-08 15:33 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 2001-06-08 14:53 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql@postgresql.org
"Josh Berkus" <josh@agliodbs.com> writes:
>> Microsoft Access is the guilty party, IIRC.
> Which verison? I work with MSAccess 97 a lot, and the supported syntax
> is "IS NULL".
In our last go-round on this (pghackers around 14 Jan 2001),
Mike Mascari wrote:
: I don't have Office 2000, but I can confirm Access 97 generates such
: queries. The query-builder doesn't generate the 'key = NULL' query, but the
: use of the Forms interface does.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 15:53 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Josh Berkus <josh@agliodbs.com>
2001-06-08 14:53 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-08 15:33 ` Josh Berkus <josh@agliodbs.com>
2001-06-08 22:01 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Josh Berkus @ 2001-06-08 15:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql@postgresql.org
Tom, Mike:
> In our last go-round on this (pghackers around 14 Jan 2001),
> Mike Mascari wrote:
>
> : I don't have Office 2000, but I can confirm Access 97 generates
> such
> : queries. The query-builder doesn't generate the 'key = NULL' query,
> but the
> : use of the Forms interface does.
As someone who develops professionally for MSAccess<->ODBC Servers
(PostgreSQL and MS SQL Server) I'd say that we can ignore this. There
are a *lot* of circumstances where the "Filter by Form" interface breaks
down; I don't think that it works properly with MS SQL Server 7.0,
either.
At most, rather than modifying the query parser and building extra
compile-time options, how about talking to the Great bridge folks about
modifying PgODBC? That seems like the appropriate place to tell the
system that "= NULL" is equivalent to "IS NULL" -- if anywhere.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 15:53 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Josh Berkus <josh@agliodbs.com>
2001-06-08 14:53 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
2001-06-08 15:33 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Josh Berkus <josh@agliodbs.com>
@ 2001-06-08 22:01 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Tom Lane @ 2001-06-08 22:01 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql@postgresql.org
"Josh Berkus" <josh@agliodbs.com> writes:
>> : I don't have Office 2000, but I can confirm Access 97 generates such
>> : queries. The query-builder doesn't generate the 'key = NULL' query,
>> : but the use of the Forms interface does.
> As someone who develops professionally for MSAccess<->ODBC Servers
> (PostgreSQL and MS SQL Server) I'd say that we can ignore this. There
> are a *lot* of circumstances where the "Filter by Form" interface breaks
> down; I don't think that it works properly with MS SQL Server 7.0,
> either.
This is an interesting comment. Does anyone else want to confirm or
refute it? If Access' forms interface is so badly broken that few
people use it anyway, then I'd say that we should not break standards
compatibility just to support it.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-07 19:17 ` Tom Ivar Helbekkmo <tih@kpnQwest.no>
3 siblings, 0 replies; 19+ messages in thread
From: Tom Ivar Helbekkmo @ 2001-06-07 19:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org; pgsql-hackers
Tom Lane <tgl@sss.pgh.pa.us> writes:
> A compromise answer might be to offer a SET variable that selects the
> Microsoft-compatible misimplementation. Would that fly?
I'd say that's the best way to handle stuff like this. If you
implement something that breaks the standard, to be compatible with
arrogant programmers at Microsoft who feel that they are above such
things, you're encouraging users to develop bad habits. Better to
make them go "SET MICROSOFT BUG COMPATIBILITY ON" first.
To see the effect of leniency in re standards, just try to browse
around the web with a browser that rejects incorrect HTML! :-)
-tih
--
The basic difference is this: hackers build things, crackers break them.
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards
@ 2001-06-07 19:36 Mike Mascari <mascarm@mascari.com>
2001-06-07 19:42 ` Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Ivar Helbekkmo <tih@kpnQwest.no>
2001-06-08 15:04 ` Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Mike Mascari @ 2001-06-07 19:36 UTC (permalink / raw)
To: 'Tom Ivar Helbekkmo' <tih@kpnQwest.no>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>; pgsql-hackers
The best solution would be to have the ODBC translate instances of '=
NULL' into IS NULL before submitting the query to PostgreSQL. I'm
sure this is how other vendors, like Oracle handle the issue. Well,
probably sure... :-)
Mike Mascari
mascarm@mascari.com
-----Original Message-----
From: Tom Ivar Helbekkmo [SMTP:tih@kpnQwest.no]
Sent: Thursday, June 07, 2001 3:18 PM
To: Tom Lane
Cc: Stephan Szabo; Mark Stosberg; pgsql-sql@postgresql.org;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Re: [SQL] behavior of ' = NULL' vs. MySQL vs.
Standards
Tom Lane <tgl@sss.pgh.pa.us> writes:
> A compromise answer might be to offer a SET variable that selects
the
> Microsoft-compatible misimplementation. Would that fly?
I'd say that's the best way to handle stuff like this. If you
implement something that breaks the standard, to be compatible with
arrogant programmers at Microsoft who feel that they are above such
things, you're encouraging users to develop bad habits. Better to
make them go "SET MICROSOFT BUG COMPATIBILITY ON" first.
To see the effect of leniency in re standards, just try to browse
around the web with a browser that rejects incorrect HTML! :-)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 19:36 RE: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Mike Mascari <mascarm@mascari.com>
@ 2001-06-07 19:42 ` Tom Ivar Helbekkmo <tih@kpnQwest.no>
1 sibling, 0 replies; 19+ messages in thread
From: Tom Ivar Helbekkmo @ 2001-06-07 19:42 UTC (permalink / raw)
To: mascarm@mascari.com <mascarm@mascari.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>; pgsql-hackers
Mike Mascari <mascarm@mascari.com> writes:
> The best solution would be to have the ODBC translate instances of '=
> NULL' into IS NULL before submitting the query to PostgreSQL. I'm
> sure this is how other vendors, like Oracle handle the issue. Well,
> probably sure... :-)
<applause>
That's the intelligent solution, of course.
-tih
--
The basic difference is this: hackers build things, crackers break them.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 19:36 RE: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Mike Mascari <mascarm@mascari.com>
@ 2001-06-08 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-08 16:53 ` Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 19+ messages in thread
From: Tom Lane @ 2001-06-08 15:04 UTC (permalink / raw)
To: mascarm@mascari.com <mascarm@mascari.com>; +Cc: 'Tom Ivar Helbekkmo' <tih@kpnQwest.no>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>; pgsql-hackers
Mike Mascari <mascarm@mascari.com> writes:
> The best solution would be to have the ODBC translate instances of '=
> NULL' into IS NULL before submitting the query to PostgreSQL.
Does Access only talk to Postgres via ODBC? (Seems plausible, but I
don't know.) What about people copying-and-pasting queries generated
by Access; is it even possible?
I doubt that this'd really help much, in any case; it merely moves the
necessary switch from the backend to ODBC.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-07 19:36 RE: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Mike Mascari <mascarm@mascari.com>
2001-06-08 15:04 ` Re: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-08 16:53 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 19+ messages in thread
From: Josh Berkus @ 2001-06-08 16:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; mascarm@mascari.com <mascarm@mascari.com>; +Cc: 'Tom Ivar Helbekkmo' <tih@kpnQwest.no>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>; pgsql-hackers
Tom,
> Does Access only talk to Postgres via ODBC? (Seems plausible, but I
> don't know.)
Yes.
What about people copying-and-pasting queries generated
> by Access; is it even possible?
Yes, but MS Access uses some much non-standard SQL that "= Null" would
hardly be the only problem (try to build an UPDATE ... FROM in Access)
> I doubt that this'd really help much, in any case; it merely moves
> the
> necessary switch from the backend to ODBC.
Yes, but it does avoid cluttering up the PgSQL source with switches that
are only useful to a handful of users.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards
@ 2001-06-08 15:24 Mike Mascari <mascarm@mascari.com>
2001-06-14 08:01 ` Re: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards Hiroshi Inoue <Inoue@tpf.co.jp>
0 siblings, 1 reply; 19+ messages in thread
From: Mike Mascari @ 2001-06-08 15:24 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 'Tom Ivar Helbekkmo' <tih@kpnQwest.no>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-hackers
> > The best solution would be to have the ODBC translate instances
of '=
> > NULL' into IS NULL before submitting the query to PostgreSQL.
Tom Lane wrote:
> Does Access only talk to Postgres via ODBC? (Seems plausible, but
I
> don't know.) What about people copying-and-pasting queries
generated
> by Access; is it even possible?
1. Yes, Access only talkes to Postgres via ODBC.
2. The queries generate by Access which use the '= NULL' vs. 'IS
NULL' language are only generated through the use of Access Forms,
when some attempts to fetch a record through the forms interface
where the key of the linked table is an empty edit control. When
access generates queries, it correctly generates 'IS NULL' language.
It is a *very specific problem* WRT the user of Access Forms.
> I doubt that this'd really help much, in any case; it merely moves
the
> necessary switch from the backend to ODBC.
It seems to me that is where it belongs. The ODBC driver is already
doing translation of some Access functions not found in PostgreSQL
such as LCASE->lower, etc. The kludge isn't found in any other RDBMS
of which I am aware (certainly not Oracle), and yet Access works fine
with them,
Just MHO of course,
Mike Mascari
mascarm@mascari.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards
2001-06-08 15:24 RE: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards Mike Mascari <mascarm@mascari.com>
@ 2001-06-14 08:01 ` Hiroshi Inoue <Inoue@tpf.co.jp>
0 siblings, 0 replies; 19+ messages in thread
From: Hiroshi Inoue @ 2001-06-14 08:01 UTC (permalink / raw)
To: mascarm@mascari.com <mascarm@mascari.com>; +Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'Tom Ivar Helbekkmo' <tih@kpnQwest.no>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; Mark Stosberg <mark@summersault.com>; pgsql-hackers
Mike Mascari wrote:
>
> > > The best solution would be to have the ODBC translate instances
> of '=
> > > NULL' into IS NULL before submitting the query to PostgreSQL.
>
> Tom Lane wrote:
>
> > Does Access only talk to Postgres via ODBC? (Seems plausible, but
> I
> > don't know.) What about people copying-and-pasting queries
> generated
> > by Access; is it even possible?
>
> 1. Yes, Access only talkes to Postgres via ODBC.
>
> 2. The queries generate by Access which use the '= NULL' vs. 'IS
> NULL' language are only generated through the use of Access Forms,
> when some attempts to fetch a record through the forms interface
> where the key of the linked table is an empty edit control. When
> access generates queries, it correctly generates 'IS NULL' language.
> It is a *very specific problem* WRT the user of Access Forms.
>
Could you send me an example using Access Forms ?
DAO seems to translate '= NULL' into 'IS NULL' properly.
Hmm parameter bindings( expr = ? ) could be the cause.
> > I doubt that this'd really help much, in any case; it merely moves
> the
> > necessary switch from the backend to ODBC.
>
> It seems to me that is where it belongs. The ODBC driver is already
> doing translation of some Access functions not found in PostgreSQL
> such as LCASE->lower, etc.
It doesn't seem that easy.
Detecting '= NULL' isn't sufficient.
'SET .. expr = NULL' mustn't be tranlated into
'SET .. expr IS NULL'. The '= NULL' must be inside
a where clause.
Unfortunately the current psqlodbc driver parses
little.
regards,
Hiroshi Inoue
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2001-06-14 08:01 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-07 01:00 behavior of ' = NULL' vs. MySQL vs. Standards Mark Stosberg <mark@summersault.com>
2001-06-07 01:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 02:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 03:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-07 10:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 14:40 ` Mark Stosberg <mark@summersault.com>
2001-06-07 14:59 ` ANDREW PERRIN <aperrin@email.unc.edu>
2001-06-07 13:41 ` Thomas Lockhart <lockhart@fourpalms.org>
2001-06-07 15:53 ` Josh Berkus <josh@agliodbs.com>
2001-06-08 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-08 15:33 ` Josh Berkus <josh@agliodbs.com>
2001-06-08 22:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-07 19:17 ` Tom Ivar Helbekkmo <tih@kpnQwest.no>
2001-06-07 19:36 RE: [HACKERS] Re: behavior of ' = NULL' vs. MySQL vs. Standards Mike Mascari <mascarm@mascari.com>
2001-06-07 19:42 ` Tom Ivar Helbekkmo <tih@kpnQwest.no>
2001-06-08 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-08 16:53 ` Josh Berkus <josh@agliodbs.com>
2001-06-08 15:24 RE: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards Mike Mascari <mascarm@mascari.com>
2001-06-14 08:01 ` Hiroshi Inoue <Inoue@tpf.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox