Re: BUG #4748: hash join and sort-merge join make different results

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Roman Kononov <kononov(at)ftml(dot)net>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4748: hash join and sort-merge join make different results
Date: 2009-04-03 19:57:48
Message-ID: 21030.1238788668@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Roman Kononov wrote:
>> Description: hash join and sort-merge join make different results
>> Details:
>>
>> test-std=# create table t(s int,i interval);
>> CREATE TABLE
>> test-std=# insert into t values (0,'30 days'), (1,'1 month');
>> INSERT 0 2
>> test-std=# select * from t as a, t as b where a.i=b.i;

> Reproducible in 8.2.13 as well ..

The problem is that interval_cmp_internal(), and hence interval_eq(),
says that '1 month' = '30 days'. But they don't hash to the same thing.

I think we could fix this by having interval_hash() duplicate the
total-span calculation done by interval_cmp_internal, and then return
the hash of the resulting TimeOffset. This is going to break existing
hash indexes on intervals, but there seems little choice...

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2009-04-03 20:18:25 Re: BUG #4748: hash join and sort-merge join make different results
Previous Message Dickson S. Guedes 2009-04-03 19:46:51 Re: BUG #4748: hash join and sort-merge join make different results