Correctly identify which EC members are computable at a plan node.

find_computable_ec_member() had the wrong mental model of what
its primary caller prepare_sort_from_pathkeys() would do with
the selected EquivalenceClass member expression.  We will not
compute the EC expression in a plan node atop the one returning
the passed-in targetlist; rather, the EC expression will be
computed as an additional column of that targetlist.  So any
Var or quasi-Var used in the given tlist is also available to the
EC expression.  In simple cases this makes no difference because
the given tlist is just a list of Vars or quasi-Vars --- but if
we are considering an appendrel member produced by flattening
a UNION ALL, the tlist may contain expressions, resulting in
failure to match and a "could not find pathkey item to sort"
error.

To fix, we can flatten both the tlist and the EC members with
pull_var_clause(), and then just check for subset-ness, so
that the code is actually shorter than before.

While this bug is quite old, the present patch only works back to
v13.  We could possibly make it work in v12 by back-patching parts
of 375398244.  On the whole though I don't like the risk/reward
ratio of that idea.  v12's final release is next month, meaning
there would be no chance to correct matters if the patch causes a
regression.  Since this failure has escaped notice for 14 years,
it's likely nobody will hit it in the field with v12.

Per bug #18652 from Alexander Lakhin.

Andrei Lepikhov and Tom Lane

Discussion: https://postgr.es/m/18652-deaa782ebcca85d1@postgresql.org

2 files changed, 38 insertions, 0 deletions

diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 20973f96959..889b2ada6da 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1543,6 +1543,36 @@ select min(1-id) from matest0;
 
 reset enable_seqscan;
 reset enable_parallel_append;
+explain (verbose, costs off)  -- bug #18652
+select 1 - id as c from
+(select id from matest3 t1 union all select id * 2 from matest3 t2) ss
+order by c;
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Result
+   Output: ((1 - t1.id))
+   ->  Merge Append
+         Sort Key: ((1 - t1.id))
+         ->  Index Scan using matest3i on public.matest3 t1
+               Output: t1.id, (1 - t1.id)
+         ->  Sort
+               Output: ((t2.id * 2)), ((1 - (t2.id * 2)))
+               Sort Key: ((1 - (t2.id * 2)))
+               ->  Seq Scan on public.matest3 t2
+                     Output: (t2.id * 2), (1 - (t2.id * 2))
+(11 rows)
+
+select 1 - id as c from
+(select id from matest3 t1 union all select id * 2 from matest3 t2) ss
+order by c;
+  c  
+-----
+ -11
+  -9
+  -5
+  -4
+(4 rows)
+
 drop table matest0 cascade;
 NOTICE:  drop cascades to 3 other objects
 DETAIL:  drop cascades to table matest1
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index e9ae438b48a..c1d66b56685 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -543,6 +543,14 @@ select min(1-id) from matest0;
 reset enable_seqscan;
 reset enable_parallel_append;
 
+explain (verbose, costs off)  -- bug #18652
+select 1 - id as c from
+(select id from matest3 t1 union all select id * 2 from matest3 t2) ss
+order by c;
+select 1 - id as c from
+(select id from matest3 t1 union all select id * 2 from matest3 t2) ss
+order by c;
+
 drop table matest0 cascade;
 
 --