Subject: Re: ERROR: left and right pathkeys do not match in mergejoin
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <5dad9160-4632-0e47-e120-8e2082000c01@postgrespro.ru>
 <20796.1519322593@sss.pgh.pa.us>
From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
Message-ID: <886b103b-7ecf-ca2e-6159-8c355258d936@postgrespro.ru>
Date: Fri, 23 Feb 2018 02:05:10 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: <20796.1519322593@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

El 22/02/18 a las 21:03, Tom Lane escribió:
> The third possibility is to decide that create_mergejoin_plan is being
> overly paranoid and it's okay to extract merge details from a "redundant"
> path key even though it specifies the opposite sort order from what the
> current merge clause seems to need.

This check looks necessary to me, because merge join can't work with 
such a combination of outer pathkeys and merge clauses. The order of 
outer and inner column must be the same. Here, two outer columns with 
different orders are compared to the same inner column, so there will be 
mismatch one way or another. Consider the original query with some 
sample data:

     outer     inner
   desc  asc   desc
    i2   k2     i1
------------------
-> 1    0      1 <-
    1    1      0
    1    2
    0

The merge join starts at tuples marked with arrows. It finds that the 
outer tuple is less than the inner, so it advances the inner pointer, 
and loses the tuple with i1 = 1.

So, if we can't do a merge join on these clauses with these pathkeys 
anyway, it is OK to discard some of them in 
find_mergeclauses_for_pathkey. This helps the case when we use the 
existing order of outer relation. Also, we should take care not to 
create such combinations in select_outer_pathkeys_for_merge, when it 
tries to match root->query_pathkeys.

-- 
Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company