Thread: AW: [HACKERS] SELECT BUG

AW: [HACKERS] SELECT BUG

From
Andreas Zeugswetter
Date:
> But if it is correct, then we need to turn off oprcanhash for bpchareq.
> Odd that no one has noticed this before.

Currently it works for constants, because they are blank padded.
It does not work for the char(8) = char(16) comparison with two
table columns.

Eighter the hash function for bpchar itself should be trailing blank 
insensitive, or the bpchar would need to be padded or truncated before 
computing the hash.

Andreas


Re: AW: [HACKERS] SELECT BUG

From
Tom Lane
Date:
Andreas Zeugswetter <andreas.zeugswetter@telecom.at> writes:
>> But if it is correct, then we need to turn off oprcanhash for bpchareq.
>> Odd that no one has noticed this before.

> Currently it works for constants, because they are blank padded.
> It does not work for the char(8) = char(16) comparison with two
> table columns.

The case that can fail is equality across two different-width char
columns from different tables.  If they're in the same table, or if
it's field = constant, then no join is involved so there's no risk
of hashjoin being used.  So this might be a relatively rare case
after all.  And I think the 6.5 optimizer is more prone to choose
hashjoin than it was in prior releases.  Maybe it's not so odd that
this wasn't noticed sooner.

> Eighter the hash function for bpchar itself should be trailing blank
> insensitive, or the bpchar would need to be padded or truncated before
> computing the hash.

We don't currently have datatype-dependent hash functions; it's one-
size-fits-all.  I thought a little bit about adding type-specific
hashing back when I did the last round of removing unsafe "oprcanhash"
marks, but it didn't seem worth the trouble.  Unfortunately I missed
bpchareq because I didn't know it does blank stripping...
        regards, tom lane