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

<meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">


Greg,

Obviously I do not agree. When 14 different databases by 14 different
DBMS vendors from the largest to the smallest in the market can do a
simple thing as a using a subquery that has distinct keyword and your
DBMS can't, I would say your DBMS is at fault and is not better, rather
is lacking in this respect. I am not expecting favors from the DBMS by
its doing what I expect it to do.

I do not want to beat an already dead horse, but if you review my
example, you will see that it is very simple, PG already does
conversion correctly from ISO string to timestamp column for inserting
so you can't say we removed all conversions and that is a good thing,
it is not. Basic feature of DBMS is allowing data entry into different
data type columns using plain string literals. PG already does that and
all other DBMS do that as well. For reference, although ODBC is not a
DBMS, ODBC specification requires that an ODBC driver can
convert all source DBMS data types from/to chars. This is not
by accident, it is a necessity and is by design. I can understand that
having multiple data formats for conversion to native data types from
text can cause bugs and that's why we have established standards such
as ISO for datetime/timestamp string formats and PG supports the
conversion already.

The issue is the PG is not doing it correctly when 'distinct' keyword
is used in the select statement. There is nothing buggy with using ISO
datetime string literals to insert into a table timestamp column. There
is no behind the scene magic going on.

1 Execute subquery: string literals are just that can be 'aa', 'bb',
'2010-04-30 00:00:00', whatever, it does not matter what the string
literal is.

2 Eliminate duplicates

3 Now a string literal is being inserted into a timestamp column, you
have a string literal and you are asked to insert into a timestamp
colum -> convert string literal to timestamp and do the insert

As you can see there is nothing buggy or heinous here, just simple
select with distinct keyword in step 1, 2 and conversion from string
literal to timestamp value in step 3

There is no ambiguity or magic to happen. Obviously in PG case there is
some design or fault somewhere in this use-case when distinct keyword
is used and is processed in step 2, that's all.

Farid

On 6/4/2010 10:41 PM, Greg Stark wrote:
<blockquote
 cite="mid:AANLkTinvpWLi3CoBWYgNPxVdFbbLhEHZuYdp9buvDubV@mail.gmail.com"
 type="cite">
  On Fri, Jun 4, 2010 at 11:15 PM, Farid Zidan <farid@zidsoft.com> wrote:


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




I find usually when one person is arguing something is complex and
someone else is arguing it's simple it's the person who's claiming
it's simple who is wrong.

The other databases are not, I believe, preventing duplicates from
being inserted as you describe. They are removing duplicates from the
string constants and then silently converting to a different datatype
before inserting. When postgres removed these default casts to text it
turned up many instances where users had buggy code and Postgres had
been hiding from them by silently using string operators which was not
what users were expecting. In other words, while it might not matter
in this case, in general if you code in this style your code is buggy
and these other database implementations are not doing you any favours
by making it appear to work correctly most of the time.




--

Signature

www.zidsoft.com
CompareData:  compare
and synchronize SQL DBMS data visually <font
 size="-1">between two databases
using ODBC drivers