BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
The following bug has been logged online:
Bug reference: 5490
Logged by: Farid Zidan
Email address: farid@zidsoft.com
PostgreSQL version: 8.4.1
Operating system: Windows XP 32-bit
Description: Using distinct for select list causes insert of
timestamp string literal to fail
Details:
If you use keyword 'distinct' for the source select of the insert statement
the insert fails. Insert succeeds if 'distinct' is not used in select list.
Example,
create table test_insert (
col1 char(8) not null,
col2 TIMESTAMP not null default CURRENT_TIMESTAMP);
insert into
test_insert
(col1, col2) values
('a', '2010-04-30 00:00:00');
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.
********** Error **********
ERROR: column "col2" is of type timestamp without time zone but expression
is of type text
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 282
"Farid Zidan" <farid@zidsoft.com> writes:
If you use keyword 'distinct' for the source select of the insert statement
the insert fails. Insert succeeds if 'distinct' is not used in select list.
This isn't a bug, it's a consequence of the fact that you're not
specifying the types of the literal constants. 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". Try attaching an explicit cast,
eg
'2010-04-30 00:00:00'::timestamp
regards, tom lane
"Farid Zidan" <farid@zidsoft.com> wrote:
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.
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
<!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.1275666785@sss.pgh.pa.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@gw.wicourts.gov"
type="cite">
<pre wrap="">"Farid Zidan" <a class="moz-txt-link-rfc2396E" href="mailto:farid@zidsoft.com"><farid@zidsoft.com></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: </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>
Farid Zidan <farid@zidsoft.com> wrote:
If we were strictly complying with the SQL standard,
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.
The SQL standard is a document published by the International
Standards Organization (ISO) and also adopted by the American
National Standards Institute (ANSI). Those documents don't require
a query in either of the forms you presented to work. Because of
the convenience factor, most database products have non-standard
extensions to omit type specification in some places. PostgreSQL's
extensions are oriented more toward user-installable data types
(such as geometric shapes or global coordinates), so the particulars
of our non-standard extensions differ so that use of those features
is as easy as practicable. That does result in some non-standard
extensions which work in other products not working in PostgreSQL.
I think you'll find that the syntax I suggested (using the standard
timestamp literal instead of a bare character string literal) will
work in all of the databases you mentioned; if you want portable
code, it is best to follow the standard rather than some inferred
popular convention.
I hope this helps.
-Kevin
<!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 Kevin,<br>
<br>
My bug report is about using 'distinct' in the select list which is
causing a side-effect. That's why I classify this as a bug. Distinct
should not have unintended side-effects.<br>
<br>
This side-effect is implementation-dependent and is manifested in the
current PostgreSQL query processing but can be eliminated by
appropriately handling the distinct keyword and does not have to occur.
<br>
<br>
The ISO-datetime string literal format I am using the most
general/standard for datetime/timestamp and is not the issue here. The
'distinct' keyword is causing the error.<br>
<br>
Farid<br>
<br>
On 6/4/2010 12:52 PM, Kevin Grittner wrote:
<blockquote cite="mid:4C08E8F10200002500031FAD@gw.wicourts.gov"
type="cite">
<blockquote type="cite">
<pre wrap="">Farid Zidan <a class="moz-txt-link-rfc2396E" href="mailto:farid@zidsoft.com"><farid@zidsoft.com></a> wrote:
</pre>
</blockquote>
<pre wrap="">
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">If we were strictly complying with the SQL standard,
</pre>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap="">
The SQL standard is a document published by the International
Standards Organization (ISO) and also adopted by the American
National Standards Institute (ANSI). Those documents don't require
a query in either of the forms you presented to work. Because of
the convenience factor, most database products have non-standard
extensions to omit type specification in some places. PostgreSQL's
extensions are oriented more toward user-installable data types
(such as geometric shapes or global coordinates), so the particulars
of our non-standard extensions differ so that use of those features
is as easy as practicable. That does result in some non-standard
extensions which work in other products not working in PostgreSQL.
I think you'll find that the syntax I suggested (using the standard
timestamp literal instead of a bare character string literal) will
work in all of the databases you mentioned; if you want portable
code, it is best to follow the standard rather than some inferred
popular convention.
I hope this helps.
-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: </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>
Farid Zidan <farid@zidsoft.com> wrote:
can be eliminated by appropriately handling the distinct keyword
and does not have to occur.
Based on previous discussions around our approaching data types, I
don't think any of the regular PostgreSQL developers are likely to
agree with you; but if you see a way to make it work, feel free to
submit a patch. See this page for the process:
http://wiki.postgresql.org/wiki/Submitting_a_Patch
The ISO-datetime string literal format I am using the most
general/standard for datetime/timestamp and is not the issue here.
The format in your string literal is the portable one; however, a
timestamp literal requires the TIMESTAMP keyword ahead of the string
literal, which you have chosen to omit. Did you try the query with
a proper timestamp literal, as I suggested, against all these
databases? If using standard syntax works, why not use it?
The 'distinct' keyword is causing the error.
No, non-standard syntax is causing the error in the case of
DISTINCT, because our extension to the standard does not cover that
case, even though it covers the other. There are good reasons for
that, which you'll probably discover in short order if you work on a
patch for the issue.
-Kevin
<!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">
Hello Kevin,<br>
<br>
I strongly disagree with your analysis of this issue. Like I said, this
syntax works with 9 different databases, so obviously whatever
PosgreSQL query procesor is doing in this case is not the desired
behavior.<br>
<br>
To ensure PosgreSQL success, the query processor must behave in a
compliant manner with established standards whether those standards are
set by SQL ISO specs or are de facto standards.<br>
<br>
It is too much asking developers to change their sql to overcome
implementation-dependent side-effects of PostgreSQL query processor. If
a simple SQL statement works on 9+ different databases, then it should
also work in PostreSQL with no need for developers to special-code for
PostgreSQL. Very basic feature is converting a string literal to a
datetime/timestamp value and developers should not do <i>any</i>
special coding to accomplish this simple conversion. '2010-04-30
00:00:00' should convert to timestamp in PostgreSQL with no other flags
or syntax
decoration (it already does except when 'distinct' is used).<br>
<br>
Compatibility is very high on desired features for a DBMS and is a
requirement for smooth porting of applications from other databases to
PostreSQL and cross-dbms applications. It really boils down to making
it work, technical details are what developers love and I am sure
PostgreSQL developers can make this simple sql insert work on PostreSQL
just like all the other developers have done for the other DBMSs.<br>
<br>
Anyway, I have reported this issue because I encountered it and it
negatively impacts my project. I don't
expect it to be fixed right now, that's something that PostgreSQL
developers can debate and prioritize. I only ask that this issue is
identified, since it does not work in my case when the target dbms is
PostgreSQL and I am sure it can impact other developers projects and it
would need to be addressed at
some point in the future with a solution where it just work like it
does in all the other DBMSs.<br>
<br>
Farid<br>
<br>
<br>
On 6/4/2010 1:36 PM, Kevin Grittner wrote:
<blockquote cite="mid:4C08F33E0200002500031FBA@gw.wicourts.gov"
type="cite">
<blockquote type="cite">
<pre wrap="">Farid Zidan <a class="moz-txt-link-rfc2396E" href="mailto:farid@zidsoft.com"><farid@zidsoft.com></a> wrote:
</pre>
</blockquote>
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">can be eliminated by appropriately handling the distinct keyword
and does not have to occur.
</pre>
</blockquote>
<pre wrap="">
Based on previous discussions around our approaching data types, I
don't think any of the regular PostgreSQL developers are likely to
agree with you; but if you see a way to make it work, feel free to
submit a patch. See this page for the process:
<a class="moz-txt-link-freetext" href="http://wiki.postgresql.org/wiki/Submitting_a_Patch">http://wiki.postgresql.org/wiki/Submitting_a_Patch</a>
</pre>
<blockquote type="cite">
<pre wrap="">The ISO-datetime string literal format I am using the most
general/standard for datetime/timestamp and is not the issue here.
</pre>
</blockquote>
<pre wrap="">
The format in your string literal is the portable one; however, a
timestamp literal requires the TIMESTAMP keyword ahead of the string
literal, which you have chosen to omit. Did you try the query with
a proper timestamp literal, as I suggested, against all these
databases? If using standard syntax works, why not use it?
</pre>
<blockquote type="cite">
<pre wrap="">The 'distinct' keyword is causing the error.
</pre>
</blockquote>
<pre wrap="">
No, non-standard syntax is causing the error in the case of
DISTINCT, because our extension to the standard does not cover that
case, even though it covers the other. There are good reasons for
that, which you'll probably discover in short order if you work on a
patch for the issue.
-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: </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>
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.
--
greg
<!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>
<col1>,<br>
<col2>,<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@mail.gmail.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@zidsoft.com"><farid@zidsoft.com></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: </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>
Farid Zidan <farid@zidsoft.com> wrote:
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
Not really. In versions prior to 8.3 it did automagically convert
like that. PostgreSQL has some pretty fancy features involving
custom data types where this magic caused problems, so a deliberate
decision was taken to no longer provide automatic casts from text to
other data types.
(but forgets only when 'distinct' is used in the subquery!)
That is because (as I tried to explain earlier, but apparently
didn't do a good job of communicating), an unadorned literal in
single quotes is *not* taken to be a character string in PostgreSQL.
Its type is held as "unknown" until it is forced to be resolved in
some operation. This allows easier coding of custom data types, but
does create a few deviations from standard behavior in corner cases,
and breaks from the non-standard "conventional" behavior of many
other databases. Because of this design choice, for example, the
FAA can more easily write the code they use to map their runways and
other airport facilities.
The cost is that in situations such as you describe, you need to
force the type before it is used in the comparisons necessary to
determine a distinct value. The only way to get the behavior you
want without breaking a great many useful cases, would be to
determine where the result was going to be later used, and use that
information to force the type to something other than text (the
default, when no other information is available). That would be a
*major* and destabilizing change.
For those reasons, the chance of getting *anybody* here to consider
this a bug are close to nil. The choice to more conveniently handle
advanced cases at the expense of occasionally needing to specify a
type is unlikely to be reversed, to put it mildly.
I can't help but wonder why you resist using the standard syntax.
The reason the standard exists is to help those trying to write
portable code, so they don't have to count on the vagaries of
"parallel evolution."
-Kevin
<!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 Kevin,<br>
<br>
<blockquote type="cite">
<pre wrap="">I can't help but wonder why you resist using the standard syntax.
</pre>
</blockquote>
I am using the standard syntax. Single quote in sql denotes a string.
so '2010-04-30 00:00:00' is string literal. That's universal. Now you
want me to use PG-specific timestamps and that's like I said is not
standard/cross-dbms.<br>
<br>
I have just finished testing with Ingre 9.2 and it works there too.
That's 10 DBMSs systems that use single quotes to denote a string
literal and can covert ISO-standard datetime string literal to
timestamp.<br>
<br>
You can't not interpret string literals one way in one statement and
just because user uses the word 'distinct' decide to switch paradigms.
That's not good design or planning. Of course you can decide to do
whatever you want, just do not expect developers to start
special-coding just for PostreSQL because you decide to cast correctly
or not correctly depending on whim.<br>
<br>
Let me reiterate the example, maybe it was too terse and you did not
read it carefully,<br>
<br>
<pre wrap="">create table test_insert (
col1 char(8) not null,
col2 TIMESTAMP not null default CURRENT_TIMESTAMP);
</pre>
>create the test table. No issue.<br>
<br>
<pre wrap="">insert into
test_insert
(col1, col2) values
('a', '2010-04-30 00:00:00');
</pre>
>Works like expected, PG correctly converts standard ISO-datetime
string literal to timestamp. No issue.<br>
<br>
<pre wrap="">insert into test_insert
(col1, col2)
select
'b',
'2010-04-30 00:00:00'
</pre>
>That works too. No issue.<br>
<br>
<pre wrap="">insert into test_insert
(col1, col2)
select <b>distinct</b>
'b',
'2010-04-30 00:00:00'
</pre>
>Does not work. That's a bug.<br>
<br>
Now this not rocket science, it's simple insert statement where we do
not want duplicates inserted. Works on 10 other DBMSs.<br>
<br>
FAA stuff and other is not related to this bug. I would think the FAA
and other organizations want a standard-compliant DBMS system that
knows how to convert a simple ISO-formatted valid string literal to a
timestamp value in more than one variation of sql statement.<br>
<br>
You can ignore this bug report and do whatever you want, just do not
say this is an accepted, standard or desired behavior of the server or
is by design. It's not by design that the error happens it is by faulty
handling of the distinct keyword.<br>
<br>
I think you have all the information you need to debate and resolve
this issue. If you need any other information you can contact me and I
will be happy to oblige.<br>
<br>
Farid<br>
<br>
<br>
On 6/4/2010 5:40 PM, Kevin Grittner wrote:
<blockquote cite="mid:4C092C650200002500032027@gw.wicourts.gov"
type="cite">
<pre wrap="">I can't help but wonder why you resist using the standard syntax.
The reason the standard exists is to help those trying to write
portable code, so they don't have to count on the vagaries of
"parallel evolution."
</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: </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>
On Fri, 4 Jun 2010, Farid Zidan wrote:
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):'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }
If you're into using standard ODBC escapes for portability, shouldn't you
be using {ts '2010-04-30 00:00:00'}?
http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx
Kris Jurka
On Fri, Jun 4, 2010 at 11:15 PM, Farid Zidan <farid@zidsoft.com> wrote:
Now this not rocket science, it's simple insert statement where we do not
want duplicates inserted. Works on 10 other DBMSs.
I find usually when one person is arguing something is complex and
someone else is arguing it's simple it's the person who's claiming
it's simple who is wrong.
The other databases are not, I believe, preventing duplicates from
being inserted as you describe. They are removing duplicates from the
string constants and then silently converting to a different datatype
before inserting. When postgres removed these default casts to text it
turned up many instances where users had buggy code and Postgres had
been hiding from them by silently using string operators which was not
what users were expecting. In other words, while it might not matter
in this case, in general if you code in this style your code is buggy
and these other database implementations are not doing you any favours
by making it appear to work correctly most of the time.
--
greg
<!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">
I only use ODBC escape sequences when necessary. Obviously I want to
use standard sql syntax as much as possible. {fn user() } is handy
because it works in all the databases that I work with and there is no
substitute standard sql function for getting current userid that is
cross-dbms.<br>
<br>
I also use {fn now()} which works across most ODBC drivers, but I can't
in this case because I need to use a constant timestamp value so as not
to change distinctness of the subquery that is the source for the
insert.<br>
<br>
The datetime ISO-standard string format I am using works in all the
databases I use 14+ (including PG), except in this case where
'distinct' is used with subquery in PG.<br>
<br>
Also not all PG clients use ODBC, so other PG clients will encounter
this issue using standard ISO datetime string format when not using
ODBC. I don't want to limit users to using ODBC for loading/updating
the database by running sql scripts (which is what the sql for this
issue is used for) so almost all of the database update/load scripts
use generic sql where timestamp/datetime values are are written as ISO
datetime format strings same format as '2010-04-30 00:00:00'<br>
<br>
BTW, I have also tested the sql in question with SQLite, MS Access, MS
Excel and Sybase Adaptive Server 15 and it works with no error, so now
that's 14 different DBMSs that have no issue with the ISO standard
string format and distinct keyword.<br>
<br>
I guess I can find some workaround for this to work with ODBC just for
the specific sql statements causing errors with PG, but that does not
resolve the issue for PG clients not using ODBC.<br>
<br>
Like I said, I am reporting this issue so it can be identified and
hopefully addressed at some point in the future, it is not critical for
me for it to work right now, but that would be nice otherwise user will
see a bunch of one-time errors and lose some ease of use but otherwise
will not be too badly affected.<br>
<br>
Farid<br>
<br>
On 6/4/2010 9:42 PM, Kris Jurka wrote:
<blockquote
cite="mid:alpine.BSO.2.00.1006042140040.6416@leary.csoft.net"
type="cite"><br>
<br>
On Fri, 4 Jun 2010, Farid Zidan wrote:
<br>
<br>
<blockquote type="cite">Here is actual statements I am running and
like I said they work for all 9+
<br>
DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax for the
<br>
current user ID):
<br>
<br>
'2010-04-30 00:00:00',
<br>
'2010-04-30 00:00:00',
<br>
{fn user() }
<br>
<br>
</blockquote>
<br>
If you're into using standard ODBC escapes for portability, shouldn't
you be using {ts '2010-04-30 00:00:00'}?
<br>
<br>
<a class="moz-txt-link-freetext" href="http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx">http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx</a>
<br>
<br>
Kris Jurka
<br>
<br>
<br>
</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: </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>
<!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">
Greg,<br>
<br>
Obviously I do not agree. When 14 different databases by 14 different
DBMS vendors from the largest to the smallest in the market can do a
simple thing as a using a subquery that has distinct keyword and your
DBMS can't, I would say your DBMS is at fault and is not better, rather
is lacking in this respect. I am not expecting favors from the DBMS by
its doing what I expect it to do.<br>
<br>
I do not want to beat an already dead horse, but if you review my
example, you will see that it is very simple, PG already does
conversion correctly from ISO string to timestamp column for inserting
so you can't say we removed all conversions and that is a good thing,
it is not. Basic feature of DBMS is allowing data entry into different
data type columns using plain string literals. PG already does that and
all other DBMS do that as well. For reference, although ODBC is not a
DBMS, ODBC specification <i>requires </i>that an ODBC driver can
convert <i>all</i> source DBMS data types from/to chars. This is not
by accident, it is a necessity and is by design. I can understand that
having multiple data formats for conversion to native data types from
text can cause bugs and that's why we have established standards such
as ISO for datetime/timestamp string formats and PG supports the
conversion already.<br>
<br>
The issue is the PG is not doing it correctly when 'distinct' keyword
is used in the select statement. There is nothing buggy with using ISO
datetime string literals to insert into a table timestamp column. There
is no behind the scene magic going on.<br>
<br>
1 Execute subquery: string literals are just that can be 'aa', 'bb',
'2010-04-30 00:00:00', whatever, it does not matter what the string
literal is. <br>
<br>
2 Eliminate duplicates<br>
<br>
3 Now a string literal is being inserted into a timestamp column, you
have a string literal and you are asked to insert into a timestamp
colum -> convert string literal to timestamp and do the insert<br>
<br>
As you can see there is nothing buggy or heinous here, just simple
select with distinct keyword in step 1, 2 and conversion from string
literal to timestamp value in step 3<br>
<br>
There is no ambiguity or magic to happen. Obviously in PG case there is
some design or fault somewhere in this use-case when distinct keyword
is used and is processed in step 2, that's all.<br>
<br>
Farid <br>
<br>
On 6/4/2010 10:41 PM, Greg Stark wrote:
<blockquote
cite="mid:AANLkTinvpWLi3CoBWYgNPxVdFbbLhEHZuYdp9buvDubV@mail.gmail.com"
type="cite">
<pre wrap="">On Fri, Jun 4, 2010 at 11:15 PM, Farid Zidan <a class="moz-txt-link-rfc2396E" href="mailto:farid@zidsoft.com"><farid@zidsoft.com></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Now this not rocket science, it's simple insert statement where we do not
want duplicates inserted. Works on 10 other DBMSs.
</pre>
</blockquote>
<pre wrap="">
I find usually when one person is arguing something is complex and
someone else is arguing it's simple it's the person who's claiming
it's simple who is wrong.
The other databases are not, I believe, preventing duplicates from
being inserted as you describe. They are removing duplicates from the
string constants and then silently converting to a different datatype
before inserting. When postgres removed these default casts to text it
turned up many instances where users had buggy code and Postgres had
been hiding from them by silently using string operators which was not
what users were expecting. In other words, while it might not matter
in this case, in general if you code in this style your code is buggy
and these other database implementations are not doing you any favours
by making it appear to work correctly most of the time.
</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: </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>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, Jun 04, 2010 at 06:15:09PM -0400, Farid Zidan wrote:
[...]
Let me reiterate the example, maybe it was too terse and you did not
read it carefully,<br>
No. I think most readers here have understood your problem perfectly.
Don't underestimate the folks here.
[...]
Now this not rocket science, it's simple insert statement where we do
not want duplicates inserted. Works on 10 other DBMSs.<br>
Except on those "other 10 DBMSs" you are most probably getting
(silently!) something different as you'd expect (DSTINCT interpreted as
text, whereas you are "seeing" timestamps). How is that better?
FAA stuff and other is not related to this bug. I would think the FAA
and other organizations want a standard-compliant DBMS system that
knows how to convert a simple ISO-formatted valid string literal to a
timestamp value in more than one variation of sql statement.<br>
Except that the behaviour of those "other 10 DBMSs" is *beyond
standard*, the standard just rules the case where you state explicitly
the type of the constant. You will find multitude of cases where DMBSs
differ on those cases beyond standard -- that's due to different design
decisions.
What Kevin was trying to convey is that PostgreSQL's design decisions
allow its users to do things other DBMSs can't -- and that's the price
we'll have to pay. Note that behaviour is still within the standard
(and not, as you seem to suggest), so not really a problem: you can
write the query in a way which will work on "all those 11 DBMSs": just
stick to the standard.
You can ignore this bug report and do whatever you want, just do not
say this is an accepted, standard or desired behavior of the server or
is by design. It's not by design that the error happens it is by faulty
handling of the distinct keyword.<br>
Accepted -- by whom? Standard -- which standard? (because it is not
required by ISO/ANSI, and there is no other "SQL standard" that I'm
aware of).
Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFMCdHgBcgs9XrR2kYRAjfsAJ0WVvm3AiFfN2jqIc24dqHVbyXM0QCeJqiQ
I31OBlckZ7go48bXZx+YRpQ=
=a7Pw
-----END PGP SIGNATURE-----
On 05/06/10 06:15, Farid Zidan wrote:
insert into test_insert
(col1, col2)
select *distinct*
'b',
'2010-04-30 00:00:00'Does not work. That's a bug.
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.
--
Craig Ringer
Tech-related writing: http://soapyfrogs.blogspot.com/
<!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>
>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 '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.4000304@postnewspapers.com.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: </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>
Farid Zidan <farid@zidsoft.com> writes:
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.
You're not reading us.
Your example makes no sense at all, and hides the problem you have. You
are failing to understand the situation because of this.
If you want to understand the problem you have, please go test your 14
databases with the meaningful tests that have been offered to you.
Until and unless you do so, I don't think posting here again on this
issue has any value, for you nor for us. You're abusing the time of lots
of people who are both busy and helpful, so please try to understand the
advices and analysis they've been offering to you.
Regards,
--
dim
