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: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, 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 21:04:06
Message-ID: 4C096A46.8030506@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">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello Greg,<br>
<br>
<blockquote type="cite">
<pre wrap="">I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
</pre>
</blockquote>
That's a good point. I think you are correct. When the query parser is
in the nested subselect it only sees string literals for the timestamp
column values (does not know it is a timestamp yet). However, when it
gets to do the insert it then must convert the string literals to
timestamp values because at that point it knows that the string literal
is to be inserted into a timestamp column.<br>
<br>
Since I am using a <i>constant </i>string literal for the timestamp
it really does not matter when the conversion takes place.<br>
<br>
select distinct<br>
&lt;col1&gt;,<br>
&lt;col2&gt;,<br>
..<br>
'2010-04-30 00:00:00'<br>
from<br>
....<br>
<br>
the timestamp string literal is a <i>constant </i>and really does not
affect the distinct resultset in anyway. I do need to stamp all the
inserts with a specific timestamp value and that's why I am using a
constant string literal. If I used an expression such as
current_timestamp/(ODBC {fn now()}, then that would factor into the
distinct clause and pollute the distinctness of subquery reulsultset.<br>
<br>
Here is actual statements I am running and like I said they work for
all 9+ DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax
for the current user ID):<br>
<pre>insert into
in_sync_node_toolbar
(node_no, sync_cd, toolbar_cd,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id)
select distinct
isnr.node_no,
case
when isr.rs_type_cd = 'TABLELS' then 'CMPTS'
when isr.rs_type_cd = 'PROCLS' then 'CMPPROCS'
when isr.rs_type_cd = 'SEQLS' then 'CMPSEQS'
else null
end,
'TBCSCPT1',
'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }
from
in_sync_node_resultset isnr,
in_sync_object_pattern isop,
in_sync_resultset isr
where
(isnr.rs_oid = isr.rs_oid or
isnr.rs_oid_other = isr.rs_oid) and
isr.rs_oid = isop.rs_oid and
isr.rs_type_cd in ('TABLELS', 'PROCLS', 'SEQLS');

insert into
in_sync_node_toolbar
(node_no, sync_cd, toolbar_cd,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id)
select distinct
isnr.node_no,
case
when isr.rs_type_cd = 'TBLVIEW' then 'CMPTABLE'
when isr.rs_type_cd = 'PROC' then 'CMPPROC'
when isr.rs_type_cd = 'SEQ' then 'CMPSEQ'
else null
end,
'TBCSCPT1',
'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }
from
in_sync_node_resultset isnr,
in_sync_object iso,
in_sync_resultset isr
where
(isnr.rs_oid = isr.rs_oid or
isnr.rs_oid_other = isr.rs_oid) and
isr.rs_oid = iso.rs_oid and
isr.rs_type_cd in ('TBLVIEW', 'PROC', 'SEQ');

<blockquote type="cite"><pre wrap="">This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.
</pre></blockquote></pre>
All the other DBMSs doing is a select distinct on the subquery that has
the <i>constant</i> timestamp string literals. There is nothing
non-standard or ambiguous there. As far as the DBMS is concerned the
constant string expression is just a string literal and can represent
anything.<br>
<br>
Now the issue is that when the other DBMSs get to do the insert part
they are able, as one would expect, to convert the subquery resultset
string literal column to a timestamp column. I think PostreSQL is doing
the first part (subquery with distinct clause correctly), but when it
gets to use the resultset of the subquery in the insert it "forgets"
how to convert <br>
'2010-04-30 00:00:00' to timestamp value (but forgets only when
'distinct' is used in the subquery!)<br>
<br>
Farid<br>
<br>
On 6/4/2010 4:18 PM, Greg Stark wrote:
<blockquote
cite="mid:AANLkTilLvx4m4TlHxeFERL60Xubiz0IhTjUXqTkoH6Le(at)mail(dot)gmail(dot)com"
type="cite">
<pre wrap="">On Fri, Jun 4, 2010 at 7:18 PM, 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="">If a simple SQL statement works on 9+ different databases
</pre>
</blockquote>
<pre wrap="">
For what it's worth are you sure it works as you expect in these other
databases?

I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
this does:

select cast(x as timestamp with time zone) from (select distinct x
from (values ('2010-01-01 12:00PM UTC'), ('2010-01-01 7:00AM EST')) as
x(x)) as y;

If you inserted those values into a table with a timestamp with time
zone column you would get duplicate values even with the distinct.

This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.

</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 5.7 KB

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Haas 2010-06-04 21:04:41 Re: superuser unable to modify settings of a system table
Previous Message Tom Lane 2010-06-04 20:59:02 Re: BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading