Hi!
I tried to execute query the following query against calcite:
SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0),
t.typtypmod) as base_type_name, d.description
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL
AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR
et.typcategory <> 'C')
For some reason, filtering by c.relkind is applied after joining.
LogicalProject(oid=[$0], oid0=[$0], typname=[$1], typnamespace=[$2],
typowner=[$3], typlen=[$4], typbyval=[$5], typtype=[$6], typcategory=[$7],
typispreferred=[$8], typisdefined=[$9], typdelim=[$10], typrelid=[$11],
typelem=[$12], typarray=[$13], typinput=[$14], typoutput=[$15],
typreceive=[$16], typsend=[$17], typmodin=[$18], typmodout=[$19],
typanalyze=[$20], typalign=[$21], typstorage=[$22], typnotnull=[$23],
typbasetype=[$24], typtypmod=[$25], typndims=[$26], typcollation=[$27],
typdefaultbin=[$28], typdefault=[$29], typacl=[$30], relkind=[$78],
base_type_name=[format_type(CASE(=($24, 0), null:INTEGER, $24), $25)],
description=[$98])
LogicalFilter(condition=[AND(IS NOT NULL($1), OR(IS NULL($78), =($78,
_UTF-16LE'c')), OR(IS NULL($38), <>($38, _UTF-16LE'C')))])
LogicalJoin(condition=[=($0, $95)], joinType=[left])
LogicalJoin(condition=[=($62, $11)], joinType=[left])
LogicalJoin(condition=[=($31, $12)], joinType=[left])
LogicalTableScan(table=[[default, pg_catalog, pg_type]])
LogicalTableScan(table=[[default, pg_catalog, pg_type]])
LogicalTableScan(table=[[default, pg_catalog, pg_class]])
LogicalTableScan(table=[[default, pg_catalog, pg_description]])
Postgres plan generates the following plan for the same query
Nested Loop Left Join (cost=22.47..44.73 rows=1 width=322)
-> Nested Loop Left Join (cost=22.18..43.94 rows=1 width=261)
Filter: ((et.typcategory IS NULL) OR (et.typcategory <>
'C'::"char"))
-> Hash Left Join (cost=21.91..43.52 rows=1 width=261)
Hash Cond: (t.typrelid = c.oid)
Filter: (c.relkind IS NULL)
-> Seq Scan on pg_type t (cost=0.00..20.01 rows=601
width=260)
Filter: (typname IS NOT NULL)
-> Hash (cost=16.96..16.96 rows=396 width=5)
-> Seq Scan on pg_class c (cost=0.00..16.96 rows=396
width=5)
-> Index Scan using pg_type_oid_index on pg_type et
(cost=0.28..0.41 rows=1 width=5)
Index Cond: (oid = t.typelem)
-> Index Scan using pg_description_o_c_o_index on pg_description d
(cost=0.28..0.77 rows=1 width=29)
Index Cond: (objoid = t.oid)
(14 rows)
Is it a bug or maybe there is a setting that changes this behaviour ?
