Re: Wrong Results from SP-GiST with Collations

On Mon, Apr 16, 2018 at 8:16 AM, Emre Hasegeli <emre@hasegeli.com> wrote:
> I think the only fix is to remove support for those operators from the
> operator class.  The logic on match_special_index_operator() doesn't
> seem to need any change.  Patch attached.  As far as I understand, we
> cannot back-patch catalog changes.  In this case, we can leave it
> broken and apply the documentation part.

Has the operator class really been completely broken since SP-GiST was
first introduced? I tend to doubt that. spg_text_inner_consistent()
has the following code, which appears to acknowledge the problem with
non-C collations:

        for (j = 0; j < in->nkeys; j++)
        {
            StrategyNumber strategy = in->scankeys[j].sk_strategy;
            text       *inText;
            int         inSize;
            int         r;

            /*
             * If it's a collation-aware operator, but the collation is C, we
             * can treat it as non-collation-aware.  With non-C collation we
             * need to traverse whole tree :-( so there's no point in making
             * any check here.  (Note also that our reconstructed value may
             * well end with a partial multibyte character, so that applying
             * any encoding-sensitive test to it would be risky anyhow.)
             */
            if (SPG_IS_COLLATION_AWARE_STRATEGY(strategy))
            {
                if (collate_is_c)
                    strategy -= SPG_STRATEGY_ADDITION;
                else
                    continue;
            }

Maybe this is a simple, relatively benign bug. Maybe this problem is
due to commit 710d90da, which is only a couple of weeks old. Have you
reproduced the problem on v10 of Postgres?

-- 
Peter Geoghegan