Thread: row wise comparison broken
Following result seems strange since it is not what the standard expects: test=# select (1,1) > (0,10);?column? ----------f (1 row) On the other hand I see following in the doc: ----------------------------------------- 9.17.5. Row-wise Comparison (expression [, expression ...]) operator (expression [, expression ...]) Each side is a list of scalar expressions; the two lists must be of the same length. Each side is evaluated and they are compared row-wise. Presently, only = and <> operators are allowed in row-wise comparisons. ----------------------------------------- I guess this means that PostgreSQL does not fully support SQL's row-wise comparison. We need to add this to the TODO list? -- Tatsuo Ishii
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> I guess this means that PostgreSQL does not fully support SQL's
> row-wise comparison. We need to add this to the TODO list?
I thought it was there already ... certainly this has been discussed
before ...
regards, tom lane
> Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > I guess this means that PostgreSQL does not fully support SQL's > > row-wise comparison. We need to add this to the TODO list? > > I thought it was there already ... certainly this has been discussed > before ... This was discussed (including a possible implementation of the correct behavior) at some length on the performance list a little over a month ago. I don't think it made it to Bruce who probably would have added to the todo list...the consensus was the current behavior is wrong but there were some concerns about backwards compatibility. Merlin
> > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > I guess this means that PostgreSQL does not fully support SQL's > > > row-wise comparison. We need to add this to the TODO list? > > > > I thought it was there already ... certainly this has been discussed > > before ... > > This was discussed (including a possible implementation of the correct > behavior) at some length on the performance list a little over a month > ago. I don't think it made it to Bruce who probably would have added to > the todo list...the consensus was the current behavior is wrong but Yes, I found the following thread started by you in the pgsql-performance list: Subject: [PERFORM] best way to fetch next/prev record based on index Date: Tue, 27 Jul 2004 06:18:43 -0700 > there were some concerns about backwards compatibility. IMO 8.0 is a good chance to fix it. Maybe we could add a GUC switch to fall back to pre-8.0 behavior... -- Tatsuo Ishii
Tatsuo Ishii wrote: > > > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > > I guess this means that PostgreSQL does not fully support SQL's > > > > row-wise comparison. We need to add this to the TODO list? > > > > > > I thought it was there already ... certainly this has been discussed > > > before ... > > > > This was discussed (including a possible implementation of the correct > > behavior) at some length on the performance list a little over a month > > ago. I don't think it made it to Bruce who probably would have added to > > the todo list...the consensus was the current behavior is wrong but > > > Yes, I found the following thread started by you in the > pgsql-performance list: > > Subject: [PERFORM] best way to fetch next/prev record based on index > Date: Tue, 27 Jul 2004 06:18:43 -0700 > > > there were some concerns about backwards compatibility. > > IMO 8.0 is a good chance to fix it. Maybe we could add a GUC switch to > fall back to pre-8.0 behavior... I remember the thread but didn't see a TODO in there at the time. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I remember the thread but didn't see a TODO in there at the time.
* Make row-wise comparisons work per SQL spec
regards, tom lane
> Yes, I found the following thread started by you in the > pgsql-performance list: > > Subject: [PERFORM] best way to fetch next/prev record based on index > Date: Tue, 27 Jul 2004 06:18:43 -0700 > > > there were some concerns about backwards compatibility. > > IMO 8.0 is a good chance to fix it. Maybe we could add a GUC switch to > fall back to pre-8.0 behavior... > -- > Tatsuo Ishii Here is a link to Tom's thoughts on possible approaches to implementing a fix, and why this is A Lot More Complicated Than It Seems (tm), particularly if taken to the next logical step where the planner could make use of the row-wise comparison to make more intelligent decisions about index selection. http://archives.postgresql.org/pgsql-performance/2004-07/msg00218.php Still, I think the current behavior is wrong...and is exactly the kind of trick question that a sql compliance benchmark might ask. Merlin
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > http://archives.postgresql.org/pgsql-performance/2004-07/msg00218.php > Still, I think the current behavior is wrong...and is exactly the kind > of trick question that a sql compliance benchmark might ask. It undoubtedly is wrong. But it has been wrong forever, or at least since Tom Lockhart put in the syntax, back in 1997. We've gotten few complaints --- which means to me that this is not a high-priority bug fix. I think we should leave it as a to-do item for a future release cycle, rather than implement some hasty solution for 8.0. regards, tom lane
Added to TODO: * Make row-wise comparisons work per SQL spec --------------------------------------------------------------------------- Tom Lane wrote: > "Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > > http://archives.postgresql.org/pgsql-performance/2004-07/msg00218.php > > > Still, I think the current behavior is wrong...and is exactly the kind > > of trick question that a sql compliance benchmark might ask. > > It undoubtedly is wrong. But it has been wrong forever, or at least > since Tom Lockhart put in the syntax, back in 1997. We've gotten few > complaints --- which means to me that this is not a high-priority bug > fix. I think we should leave it as a to-do item for a future release > cycle, rather than implement some hasty solution for 8.0. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073