pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed9.17.5. Row-wise Comparison
4+ messages / 3 participants
[nested] [flat]
* 9.17.5. Row-wise Comparison
@ 2005-04-07 03:48 TJ O'Donnell <tjo@acm.org>
2005-04-07 04:47 ` Re: 9.17.5. Row-wise Comparison Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: TJ O'Donnell @ 2005-04-07 03:48 UTC (permalink / raw)
To: pgsql-sql
In tbl with columns a,b,c,d.
I've been using syntax like
select a from tbl where (b,c,d) > (1,2,3)
to mean
select a from t where b>1 and b>2 and d>3
But I see in the manual at:
http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735
that only = and <> operators are supported. Does this section of the manual
not properly apply to this query? Is the manual in error, or am I
not understanding?
TJ
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 9.17.5. Row-wise Comparison
2005-04-07 03:48 9.17.5. Row-wise Comparison TJ O'Donnell <tjo@acm.org>
@ 2005-04-07 04:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-07 13:44 ` Re: 9.17.5. Row-wise Comparison TJ O'Donnell <tjo@acm.org>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-04-07 04:47 UTC (permalink / raw)
To: tjo@acm.org; +Cc: pgsql-sql
"TJ O'Donnell" <tjo@acm.org> writes:
> I've been using syntax like
> select a from tbl where (b,c,d) > (1,2,3)
> to mean
> select a from t where b>1 and b>2 and d>3
> But I see in the manual at:
> http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735
> that only = and <> operators are supported. Does this section of the manual
> not properly apply to this query? Is the manual in error, or am I
> not understanding?
PG's current code acts as you are supposing, but it is broken because it
doesn't follow the SQL spec, and we will change it as soon as someone
gets around to working on it.
The spec says that this syntax implies a column-by-column ordering,
essentially
if (b > 1) then true
else if (b = 1 and c > 2) then true
else if (b = 1 and c = 2 and d > 3) then true
else false
You can find related discussions in the archives from a few months
back. The spec's semantics correspond exactly to the sort ordering
of a multiple-column btree index, and so there are good reasons why we'd
want to provide that behavior even if it weren't mandated by the spec.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 9.17.5. Row-wise Comparison
2005-04-07 03:48 9.17.5. Row-wise Comparison TJ O'Donnell <tjo@acm.org>
2005-04-07 04:47 ` Re: 9.17.5. Row-wise Comparison Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-04-07 13:44 ` TJ O'Donnell <tjo@acm.org>
2005-04-07 15:51 ` Re: 9.17.5. Row-wise Comparison Ragnar Hafstað <gnari@simnet.is>
0 siblings, 1 reply; 4+ messages in thread
From: TJ O'Donnell @ 2005-04-07 13:44 UTC (permalink / raw)
To: pgsql-sql
I believe I started the multi-column index thread a
few months back, but now that I have it working so well,
I'm a bit nervous it might break in future.
> if (b > 1) then true
> else if (b = 1 and c > 2) then true
> else if (b = 1 and c = 2 and d > 3) then true
> else false
Your spec sql snippet is like an OR, isn't it, instead
of an AND as I'm reyling on?
After PG is to spec, will the behaviour I now see change?
Or is just that PG now allows itself the option to test in any order,
not ensuring a sorted result? As I see it, the results
will be the same according to spec, just not necessarily
in the same order.
Thanks for the info, and for getting my meaning in spite
of the typos in my sql.
TJ
Tom Lane wrote:
> "TJ O'Donnell" <tjo@acm.org> writes:
>
>>I've been using syntax like
>>select a from tbl where (b,c,d) > (1,2,3)
>> to mean
>>select a from t where b>1 and b>2 and d>3
>
>
>>But I see in the manual at:
>>http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735
>>that only = and <> operators are supported. Does this section of the manual
>>not properly apply to this query? Is the manual in error, or am I
>>not understanding?
>
>
> PG's current code acts as you are supposing, but it is broken because it
> doesn't follow the SQL spec, and we will change it as soon as someone
> gets around to working on it.
>
> The spec says that this syntax implies a column-by-column ordering,
> essentially
>
> if (b > 1) then true
> else if (b = 1 and c > 2) then true
> else if (b = 1 and c = 2 and d > 3) then true
> else false
>
> You can find related discussions in the archives from a few months
> back. The spec's semantics correspond exactly to the sort ordering
> of a multiple-column btree index, and so there are good reasons why we'd
> want to provide that behavior even if it weren't mandated by the spec.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 9.17.5. Row-wise Comparison
2005-04-07 03:48 9.17.5. Row-wise Comparison TJ O'Donnell <tjo@acm.org>
2005-04-07 04:47 ` Re: 9.17.5. Row-wise Comparison Tom Lane <tgl@sss.pgh.pa.us>
2005-04-07 13:44 ` Re: 9.17.5. Row-wise Comparison TJ O'Donnell <tjo@acm.org>
@ 2005-04-07 15:51 ` Ragnar Hafstað <gnari@simnet.is>
0 siblings, 0 replies; 4+ messages in thread
From: Ragnar Hafstað @ 2005-04-07 15:51 UTC (permalink / raw)
To: tjo@acm.org; +Cc: pgsql-sql
On Thu, 2005-04-07 at 06:44 -0700, TJ O'Donnell wrote:
> it might break in future.
>
> > if (b > 1) then true
> > else if (b = 1 and c > 2) then true
> > else if (b = 1 and c = 2 and d > 3) then true
> > else false
> Your spec sql snippet is like an OR, isn't it, instead
> of an AND as I'm reyling on?
not really.
> After PG is to spec, will the behaviour I now see change?
yes
> > "TJ O'Donnell" <tjo@acm.org> writes:
> >
> >>I've been using syntax like
> >>select a from tbl where (b,c,d) > (1,2,3)
> >> to mean
> >>select a from t where b>1 and b>2 and d>3
if b=2, c=1 and d=1 then the expression
(b,c,d) > (1,2,3)
currently evaluates to false, but according to spec,
should evaluate to true.
gnari
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-04-07 15:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-07 03:48 9.17.5. Row-wise Comparison TJ O'Donnell <tjo@acm.org>
2005-04-07 04:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-07 13:44 ` TJ O'Donnell <tjo@acm.org>
2005-04-07 15:51 ` Ragnar Hafstað <gnari@simnet.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox