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