--

-- regression test: check a case where join_clause_is_movable_into() gives

-- an imprecise result

-- an imprecise result, causing an assertion failure

--

analyze pg_enum;

explain (costs off)

select anname, outname, enumtypid

from

(select pa.proname as anname, coalesce(po.proname, typname) as outname

from pg_type t

left join pg_proc po on po.oid = t.typoutput

join pg_proc pa on pa.oid = t.typanalyze) ss,

pg_enum,

pg_type t2

where anname = enumlabel and outname = t2.typname and enumtypid = t2.oid;

QUERY PLAN

-----------------------------------------------------------------------

Nested Loop

Join Filter: (pg_enum.enumtypid = t2.oid)

-> Nested Loop Left Join

-> Hash Join

Hash Cond: ((t.typanalyze)::oid = pa.oid)

-> Seq Scan on pg_type t

-> Hash

-> Hash Join

Hash Cond: (pa.proname = pg_enum.enumlabel)

-> Seq Scan on pg_proc pa

-> Hash

-> Seq Scan on pg_enum

-> Index Scan using pg_proc_oid_index on pg_proc po

Index Cond: (oid = (t.typoutput)::oid)

-> Index Scan using pg_type_typname_nsp_index on pg_type t2

Index Cond: (typname = COALESCE(po.proname, t.typname))

(16 rows)

select anname, outname, enumtypid

select count(*)

from

(select pa.proname as anname, coalesce(po.proname, typname) as outname

from pg_type t

left join pg_proc po on po.oid = t.typoutput

join pg_proc pa on pa.oid = t.typanalyze) ss,

pg_enum,

pg_type t2

where anname = enumlabel and outname = t2.typname and enumtypid = t2.oid;

anname | outname | enumtypid

--------+---------+-----------

(0 rows)

(select t3.tenthous as x1, coalesce(t1.stringu1, t2.stringu1) as x2

from tenk1 t1

left join tenk1 t2 on t1.unique1 = t2.unique1

join tenk1 t3 on t1.unique2 = t3.unique2) ss,

tenk1 t4,

tenk1 t5

where t4.thousand = t5.unique1 and ss.x1 = t4.tenthous and ss.x2 = t5.stringu1;

count

-------

1000

(1 row)

--

-- Clean up

analyze pg_enum;

explain (costs off)

select anname, outname, enumtypid

(selectpa.pronameas anname, coalesce(po.proname, typname)as outname

from pg_type t

left join pg_proc poonpo.oid=t.typoutput

join pg_proc paonpa.oid=t.typanalyze) ss,

pg_enum,

pg_type t2

where anname= enumlabeland outname=t2.typnameand enumtypid=t2.oid;

select anname, outname, enumtypid

selectcount(*)

(selectpa.pronameasanname, coalesce(po.proname, typname)asoutname

joinpg_proc paonpa.oid=t.typanalyze) ss,

pg_enum,

whereanname=enumlabelandoutname=t2.typnameandenumtypid=t2.oid;

(selectt3.tenthousasx1, coalesce(t1.stringu1,t2.stringu1)asx2

jointenk1 t3ont1.unique2=t3.unique2) ss,

tenk1 t4,

wheret4.thousand=t5.unique1andss.x1=t4.tenthousandss.x2=t5.stringu1;