What do you mean by saying "bug"? Do you mean that "c.relkind IS NULL OR c.relkind = 'c'" should be pushed through the first join?
Calcite could do this now in FilterJoinRule[1]. [1] https://github.com/apache/calcite/blob/c945b7f49b99538748c871557f6ac80957be2b6e/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java#L58 Dmitry Sysolyatin <[email protected]> 于2022年10月28日周五 00:52写道： > 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 ? > -- Best, Benchao Li
