Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail - Mailing list pgsql-bugs

From tomas@tuxteam.de
Subject Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Date
Msg-id 20100605042608.GA12924@tomas
Whole thread Raw
In response to Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail  (Farid Zidan <farid@zidsoft.com>)
List pgsql-bugs
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Jun 04, 2010 at 06:15:09PM -0400, Farid Zidan wrote:

[...]
> Let me reiterate the example, maybe it was too terse and you did not
> read it carefully,<br>

No. I think most readers here have understood your problem perfectly.
Don't underestimate the folks here.

[...]

> Now this not rocket science, it's simple insert statement where we do
> not want duplicates inserted. Works on 10 other DBMSs.<br>

Except on those "other 10 DBMSs" you are most probably getting
(silently!) something different as you'd expect (DSTINCT interpreted as
text, whereas you are "seeing" timestamps). How is that better?

> FAA stuff and other is not related to this bug. I would think the FAA
> and other organizations want a standard-compliant DBMS system that
> knows how to convert a simple ISO-formatted valid string literal to a
> timestamp value in more than one variation of sql statement.<br>

Except that the behaviour of those "other 10 DBMSs" is *beyond
standard*, the standard just rules the case where you state explicitly
the type of the constant. You will find multitude of cases where DMBSs
differ on those cases beyond standard -- that's due to different design
decisions.

What Kevin was trying to convey is that PostgreSQL's design decisions
allow its users to do things other DBMSs can't -- and that's the price
we'll have to pay. Note that behaviour is still within the standard
(and not, as you seem to suggest), so not really a problem: you can
write the query in a way which will work on "all those 11 DBMSs": just
stick to the standard.

> You can ignore this bug report and do whatever you want, just do not
> say this is an accepted, standard or desired behavior of the server or
> is by design. It's not by design that the error happens it is by faulty
> handling of the distinct keyword.<br>

Accepted -- by whom? Standard -- which standard? (because it is not
required by ISO/ANSI, and there is no other "SQL standard" that I'm
aware of).

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFMCdHgBcgs9XrR2kYRAjfsAJ0WVvm3AiFfN2jqIc24dqHVbyXM0QCeJqiQ
I31OBlckZ7go48bXZx+YRpQ=
=a7Pw
-----END PGP SIGNATURE-----

pgsql-bugs by date:

Previous
From: Greg Stark
Date:
Subject: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Next
From: Craig Ringer
Date:
Subject: Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail