Re: Different results from identical matviews

On Thu, Jul 2, 2020 at 8:44 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> A plausible explanation for how things got that way is that citext's
> equality operator wasn't in your search_path when you created the original
> matview, but it is in view when you make the new one, allowing that
> equality operator to capture the interpretation of USING. Unfortunately,
> since the reverse-listing of this join is just going to say "USING
> (email)", there's no way to detect from human-readable output that the
> interpretation of the USING clauses is different. (We've contemplated
> introducing not-SQL-standard syntax to allow flagging such cases, but
> haven't pulled the trigger on that.)
>

The citext extension seems to have been installed into the public schema as
well which could introduce the CVE-2018-1058 fix as a potential moving part.

It seems a bit odd though since the textual query does specify "DISTINCT
mails_contacts_opens.email::public.citext" so it does seem to be
search_path induced as the view couldn't exist if the extension was simply
missing not extension specific equality operator were present to match in
front of the default equality operator. But then those casts also make me
question whether the source tables are defined using text instead of citext
in which case the joins using text equality would be expected and their
using citext equality in the new queries potentially suspect.

David J.