Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail - Mailing list pgsql-bugs

<meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">



Hello Kevin,

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.

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.

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 any
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).

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.

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.

Farid


On 6/4/2010 1:36 PM, Kevin Grittner wrote:
<blockquote cite="mid:4C08F33E0200002500031FBA@gw.wicourts.gov"
 type="cite">

    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





--

Signature

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