My bad. Everything is OK. The problem was inside my tables. It is a tricky query that DBeaver sends in order to get primitive types.
it turned out that pg_class table must not have a record with oid = 0, otherwise c.relkind will never be NULL after LEFT JOIN On Fri, Oct 28, 2022 at 5:46 AM Benchao Li <[email protected]> wrote: > 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 >
