>> Would it make sense to catch a qual with unassigned collation
>> somewhere in the planner, where the qual's operator family is
>> estatblished, by checking if the operator family behavior is sensitive
>> to collations?

I’m not sure how to do that.  pg_opfamily doesn’t seem to have a field for 
that.  Can you recommend how I would proceed there?



There may be operators other than = and != that are worth thinking about, but 
for now I’m really only thinking about those two.  It is hard for me to see how 
we could ignore a collation mismatch for any other operator, but maybe I’m just 
not thinking about it the right way.

So, for = and !=, I’m looking at the definition of texteq, and it calls 
check_collation_set as one of the very first things it does.  That’s where the 
error that annoys me comes out.  But I don’t think it really needs to be doing 
this.  It should first be determining if collation *matters*.  It can’t do that 
right now, because it gets that information from this line:

>     if (lc_collate_is_c(collid) ||
>         collid == DEFAULT_COLLATION_OID ||
>         pg_newlocale_from_collation(collid)->deterministic)

Which obviously won’t work if collid hasn’t been set.  So three approaches come 
to my mind:

1) Somewhere upstream from calling texteq, figure out that we don’t actually 
care about the collation stuff, because both the left and right side of the 
comparison use deterministic collations and the comparison we’re calling is 
equality, and then pass down a dummy collation such as “C” even though that 
isn’t actually true.

The problem with (1) that I see is that it could lead to the wrong collation 
being mentioned in error messages, though I haven’t looked, and that it’s 
enough of a hack that it might make coding in this area harder in the future.

2) Somewhere upstream from calling texteq, pass in a new boolean flag that 
specifies whether collation matters, and extend texteq to take an additional 
argument.

This also seems very hacky to me, but for different reasons.

3) Extend the concept of collations to collation sets.  Right now, I’m only 
thinking about a collation set as having two values, the lefthand and the 
righthand side, but maybe there are other cases like (Left, (Left,Right)) that 
get built up and need to work.  Anyway, at the point in the executor that the 
collations don’t match, instead of passing NULL down the line, pass in a 
collation set (Left, Right), and functions like texteq can see that they’re 
dealing with two different collations and decide if they can deal with that or 
if they need to throw an error.

I bet if we went with (3), the error being thrown in the example I used to 
start this thread would go away, without breaking anything else.  I’m going to 
go poke at that a bit, but I’d still appreciate any comments/concerns about my 
analysis.

> According to Tom:
> 
>> (BTW, before v12 the text '=' operator indeed did not care for collation,
>> so this example would've worked.  But the change in behavior is a
>> necessary consequence of having invented nondeterministic collations,
>> not a bug.)
> 
> I’m still struggling with that, because the four collations I used in the 
> example are all deterministic.  I totally understand why having more than one 
> collation matters if you ask that your data be in sorted order, as the system 
> needs to know which ordering to use.  But for equality, I would think that 
> deterministic collations are all interchangeable, because they all agree on 
> whether A = B, regardless of the collation defined on column A and/or on 
> column B.  Maybe I’m wrong about that.  But that’s my reading of the 
> definition of “deterministic collation” given in the docs:
> 
>> A deterministic collation uses deterministic comparisons, which means that 
>> it considers strings to be equal only if they consist of the same byte 
>> sequence. 
> 
> I’m reading that as “If and only if”, and maybe I’m wrong to do so.  Maybe 
> that’s my error.  But assuming that part is ok, it would seem to be 
> sufficient to know that the columns being joined use deterministic 
> collations, and you wouldn’t need them to be the *same* collations, nor even 
> remember which collations they were.  You’d just need information passed down 
> that collations can be ignored for this comparison, or that a built-in 
> byte-for-byte equality comparator should be used rather than the collation’s 
> equality comparator, or some such solution.

I’m starting to think that “consequence of having invented nondeterministic 
collations” in Tom’s message really should read “consequence of having invented 
nondeterministic collations without reworking these other interfaces”, but once 
again, I’m hoping to be corrected if I’ve gone off in the wrong direction here.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company





Reply via email to