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

From: Farid Zidan <farid(at)zidsoft(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Date: 2010-06-04 16:23:24
Message-ID: 4C09287C.7070402@zidsoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
On 6/4/2010 11:53 AM, Tom Lane wrote:
<blockquote cite="mid:18303(dot)1275666785(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">DISTINCT forces
the parser to assign a data type to the constants (otherwise there
is no way to understand what duplicate-elimination means) and what
it will fall back to is "text"</pre>
</blockquote>
I am including the column list for the insert, so parser knows col2
data type is TIMESTAMP and it has to convert from text to timestamp to
do the insert.<br>
<br>
It should be able to do that without generating an error. It is the
same select list, the same data types, nothing has changed except using
the 'distinct' keyword to eliminate duplicates. The parse behavior
after duplicates have been eliminated should be the same as when
'distinct' is not used.<br>
<br>
Whether 'distinct' is used or not should not affect the semantics of
the insert statement (it should only remove duplicate rows). <br>
<br>
I have used this statement in Firebrid, MS SQL Server, Oracle, MySQL,
SQLAnywhere, DB2, Derby, Informix, etc, and all of them do not generate
an error
because I need to use 'distinct' to eliminate duplicates from being
inserted.<br>
<br>
<blockquote type="cite">
<pre wrap="">If we were strictly complying with the SQL
standard, </pre>
</blockquote>
Considering the statement works in all the 9 DBMS systems+ that I have
tested so far as mentioned above, I would say PostgreSQL is not
compliant with SQL standard in this regard.<br>
<br>
I guess, what I am saying, is that what the parser is doing is not the
desired behavior. I understand there are technical things going on
behind
the scene, but that's what needs to be fixed to ensure PostgreSQL
compatibility with SQL standard and interoperability with generic sql
statements. <br>
<br>
best regards,<br>
Farid<br>
<br>
On 6/4/2010 11:57 AM, Kevin Grittner wrote:
<blockquote cite="mid:4C08DC140200002500031F7C(at)gw(dot)wicourts(dot)gov"
type="cite">
<pre wrap="">"Farid Zidan" <a class="moz-txt-link-rfc2396E" href="mailto:farid(at)zidsoft(dot)com">&lt;farid(at)zidsoft(dot)com&gt;</a> wrote:

</pre>
<blockquote type="cite">
<pre wrap="">insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'

ERROR: column "col2" is of type timestamp without time zone but
expression is of type text
LINE 16: '2010-04-30 00:00:00'
^
HINT: You will need to rewrite or cast the expression.
</pre>
</blockquote>
<pre wrap="">
Try using a timestamp literal instead of a bare literal:

insert into test_insert
(col1, col2)
select distinct
'b',
timestamp '2010-04-30 00:00:00'

This is actually working as intended in all the cases you showed, so
it isn't a bug. If we were strictly complying with the SQL
standard, your first example would also fail, but we are more
lenient than the standard where we can be, to allow an unadorned
literal to be an UNKNOWN type until something causes it to be
resolved, to allow people to omit the type decoration in many cases.
To determine that something is a distinct value, you have to
determine a type for it (otherwise you won't know if '2010-04-30
00:00:00' is the same as '2010-04-30 00:00:00.0', for example), so
if you don't tell it otherwise, it will assume text -- leading to
the behavior you saw.

-Kevin

</pre>
</blockquote>
<br>
<div class="moz-signature">-- <br>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
<title>Signature</title>
<meta name="author" content="Farid Z">
<font size="-1"><a href="http://www.zidsoft.com/">www.zidsoft.com</a>
CompareData: &nbsp;</font><font size="-1">compare
and synchronize SQL DBMS data </font><font size="-1">visually </font><font
size="-1">between two databases
using ODBC drivers</font>
</div>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 3.9 KB

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2010-06-04 16:52:17 Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Previous Message Kevin Grittner 2010-06-04 15:57:24 Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail