Re: 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: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Date: 2010-06-05 13:39:56
Message-ID: 4C0A53AC.20904@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">
Craig<br>
<br>
I am not asking you to re-write my sql so the bug will not show. I am
presenting you with sql that fails and shows the bug. If every time
someone reported a bug you ask them to re-write their sql so the bug is
not hit, that would not eliminate the bug.<br>
<br>
Also, you are using different timestamp string literals in your
subquery. I am using the same <i>constant</i> datetime string literal
in my example that the query processor does not need to cast to
timestamp or anything to do the distinct part and eliminate duplicates.<br>
<pre>insert into test_insert
(col1, col2)
select distinct
'b',
cast('2010-04-30 00:00:00' as timestamp)

</pre>
&gt;This works as expected. However is not an option because it is not
generic sql. In PG timestamp data type is called 'timestamp' but in
another DBMS it may be called&nbsp; 'datetime', etc.<br>
<br>
<blockquote type="cite">
<pre wrap="">... which is why your example is unsafe, and even if it appears to work
on other databases it is buggy. Instead, write:</pre>
</blockquote>
My example is safe and is cross-dbms. I am not doing anything
extra-ordinary just select distinct where a constant string expression
is used in the select list.<br>
<pre wrap="">select distinct
'b',
'2010-04-30 00:00:00'

</pre>
Why is the sql above unsafe? It is not. It is simple select statement
with two constant string expressions and distinct keyword. Now use the
result of the sql above as source for inserting into test_table (col1,
col2):<br>
<pre>insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'

</pre>
There is nothing unsafe here. You have a resultset that has one row
with the values 'b',<br>
'2010-04-30 00:00:00' being used to insert int col1, col2. Why would
you say that's unsafe? '2010-04-30 00:00:00' is an ISO string literal
being inserted into col2 whose data type is timestamp, perfectly safe. <br>
<br>
Farid<br>
<br>
On 6/5/2010 3:26 AM, Craig Ringer wrote:
<blockquote cite="mid:4C09FC41(dot)4000304(at)postnewspapers(dot)com(dot)au"
type="cite">
<pre wrap="">On 05/06/10 06:15, Farid Zidan wrote:
</pre>
<blockquote type="cite">
<pre wrap="">insert into test_insert
(col1, col2)
select *distinct*
'b',
'2010-04-30 00:00:00'

</pre>
<blockquote type="cite">
<pre wrap="">Does not work. That's a bug.
</pre>
</blockquote>
</blockquote>
<pre wrap="">
Not really.

select distinct * from (VALUES
('b','2010-04-30 00:00:00'),
('b','2010-04-30 00:00:00'),
('b','20100430 000000')
) AS x(a,b);

Does that produce the result you expected? It certainly didn't
deduplicate the timestamps, yet it's doing exactly the correct thing.

So this won't work:

create table test_insert (
col1 char(8) not null,
col2 TIMESTAMP not null default CURRENT_TIMESTAMP,
UNIQUE(col2)
);

insert into test_insert
(col1, col2)
select a, b::timestamp from (
select distinct * from (VALUES
('b','2010-04-30 00:00:00'),
('b','2010-04-30 00:00:00'),
('b','20100430 000000')
) AS x(a,b)) AS y;

... which is why your example is unsafe, and even if it appears to work
on other databases it is buggy. Instead, write:

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

... which will be safe on any database, is (AFAIK) perfectly standard,
and is fuss free.

</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 Dimitri Fontaine 2010-06-05 20:02:21 Re: BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Previous Message Craig Ringer 2010-06-05 07:26:57 Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail