Re: BUG #15795: ERROR: could not find pathkey item to sort

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: suresh(dot)arsenal29(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15795: ERROR: could not find pathkey item to sort
Date: 2019-05-09 19:24:20
Message-ID: 26224.1557429860@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I wrote:
> Now, how is that happening given that convert_subquery_pathkeys
> is specifically checking that the column is emitted by the subquery?
> The reason is that *it's checking the wrong thing*. It's looking
> at what the native output of the subquery is, not at what the
> SubqueryScan that we're going to stack atop it will produce.
> ...
> I haven't decided what to do about this. The minimally invasive
> fix would be to teach convert_subquery_pathkeys that it can't emit
> anything not listed in rel->reltarget. That seems like it might
> lose useful information, though perhaps the consequences are minimal
> given how hard it is to hit this bug.

I concluded that that should be a reasonable fix. The outer query
doesn't really have any use for sort keys that are not relevant to
either mergejoins or sorting/grouping, and in either of those cases
the sort keys would have been seen to be needed above the scan level
so they'd be included in the reltarget list.

Hence, attached is a draft patch for that part against v10. (I started
there since we don't have a test case to show this bug in HEAD.)
I realized that the existing tests for !resjunk are just a half-baked
version of "is the column available in the outer query", so I folded
those into the improved checks.

Notice that with this in place, we don't get the funny extra
sort key in the MergeAppend, since convert_subquery_pathkeys doesn't
try to create that pathkey. That's good for plan quality I guess
but it means that we have no live test case for the bug in
create_merge_append_plan --- which is still a bug nonetheless.

Anyway, I plan to go ahead with applying the combination of these
fixes. If we find better test cases we can add them later, but
right now I'm not that hopeful about that.

regards, tom lane

Attachment Content-Type Size
make-convert_subquery_pathkeys-more-cautious-v10.patch text/x-diff 8.7 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-05-09 23:22:39 Re: inconsistent results querying table partitioned by date
Previous Message Alan Jackson 2019-05-09 16:45:55 inconsistent results querying table partitioned by date