Re: operator does not exist: character varying[] <> character[]

On 12/9/14, 5:06 PM, Jim Nasby wrote:
> On 12/9/14, 4:30 PM, Tom Lane wrote:
>> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
>>> On 12/9/14, 4:19 PM, Jim Nasby wrote:
>>>> Is there any particular reason we don't allow comparing char and varchar arrays? If not I'll submit a patch.
>>
>>> We're also missing operators on text and varchar arrays.
>>
>> Adding operators would be an incorrect fix.
>
> Right, I'm assuming this is a problem somewhere else (haven't looked into it yet).
>
> I just wanted confirmation that this is unexpected before I try and fix it. I'll take your silence on that point as confirmation that this is a bug. :)

I've tracked down what's going on here; array_eq is lazy about finding an equality operator. It asks lookup_type_cache for TYPECACHE_EQ_OPR_FINFO, which means it looks first for a Btree Opclass, then a Hash Opclass. If neither is found then we fail.

OTOH, the path taken in transformAExprOp is very different. It ends up at oper(), which looks for an exact operator match; if that fails we look for binary operators we can coerce to. That's the path that allows this to work in the non-array case.

The question is why. :)

array_eq's call to lookup_type_cache was created in 2003 [1] and hasn't been touched since. Previously it called equality_oper, which called compatible_oper, which called oper (same as transforAExprOp does).

I'd say that array_eq (and probably _cmp) just needs to be taught to fall back to what oper() does, but this part of the commit message gives me pause:

"Change the operator search algorithms to look for appropriate btree or hash index opclasses, instead of assuming operators named '<' or '=' have the right semantics."

I can see where there are many places where we don't want to just assume than an oprname of = actually means =, but does that apply to arrays? If the user says "array = array", isn't it safe to assume that that's the same thing as if tried to compare two values of the respective typelem's? Wouldn't the same be true for row comparison as well?

[1] https://github.com/postgres/postgres/blame/master/src/backend/utils/adt/arrayfuncs.c#L3231
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com