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

[Farid Zidan]

Title: Signature

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: On Fri, Jun 4, 2010 at 7:18 PM, Farid Zidan <fa...@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. -- www.zidsoft.com CompareData:  compare and synchronize SQL DBMS data visually between two databases using ODBC drivers