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">


Hello Greg,


  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


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.

Since I am using a constant string literal for the timestamp
it really does not matter when the conversion takes place.

select distinct
<col1>,
<col2>,
..
'2010-04-30 00:00:00'
from
....

the timestamp string literal is a constant 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.

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):
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');

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.

All the other DBMSs doing is a select distinct on the subquery that has
the constant 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.

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
'2010-04-30 00:00:00' to timestamp value (but forgets only when
'distinct' is used in the subquery!)

Farid

On 6/4/2010 4:18 PM, Greg Stark wrote:
<blockquote
 cite="mid:AANLkTilLvx4m4TlHxeFERL60Xubiz0IhTjUXqTkoH6Le@mail.gmail.com"
 type="cite">
  On Fri, Jun 4, 2010 at 7:18 PM, Farid Zidan <farid@zidsoft.com> wrote:


    If a simple SQL statement works on 9+ different databases



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.




--

Signature

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