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.Nasby@BlueTreble.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
confirmationthat 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
forTYPECACHE_EQ_OPR_FINFO, which means it looks first for a Btree Opclass, then a Hash Opclass. If neither is found
thenwe 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-arraycase.
 

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
thecommit message gives me pause:
 

"Change the operator search algorithms to look for appropriate btree or hash index opclasses, instead of assuming
operatorsnamed '<' 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
doesthat apply to arrays? If the user says "array = array", isn't it safe to assume that that's the same thing as if
triedto 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