The plan output has a problem: LogicalJoin(condition=[=($0, $0)], joinType=[inner])
You are joining column 0 to column 0. You are not combining column 0 from the left side with column 0 from the right side. Column 0 from the right side would be, say, 5 if the left side has 5 columns. Your RelBuilder code looks correct, in particular the line builder.field(2, 1, "id") ought to reference the 0th column of the right input to the join. I'm not sure why RelBuilder.join is creating references to the wrong fields. It might be a bug in RelBuilder. I'd expect it to push the filter down to the JDBC data source: there would be a JdbcFilter in the plan. Is JdbcFilterRule enabled? Julian On Thu, May 26, 2016 at 9:45 AM, Chris Baynes <[email protected]> wrote: > I'm joining datasets from different sources (using the newly implemented > qualified scan), however the following INNER join query returns many more > rows than I would expect (it returns all combinations of rows as an OUTER > join would): > > builder.scan("source1", "article_facts") > .filter(builder.call(SqlStdOperatorTable.EQUALS, builder.field(1, 0, > "property_id"), builder.literal(5))) > .project(builder.field(1, 0, "article_id")) > .scan("source2", "articles") > .project(builder.field(1, 0, "id")) > .join(JoinRelType.INNER, builder.call(SqlStdOperatorTable.EQUALS, > builder.field(2, 0, "article_id"), > builder.field(2, 1, "id"))) > .build() > > The plan output appears correct: > > LogicalJoin(condition=[=($0, $0)], joinType=[inner]) > LogicalProject(article_id=[$0]) > LogicalFilter(condition=[=($1, 5)]) > LogicalTableScan(table=[[source1, article_facts]]) > LogicalProject(id=[$0]) > LogicalTableScan(table=[[source2, articles]]) > > I have tried reproducing this as a test case in RelBuilderTest, but if I > call executeQuery on a statement containing a join I get: > > Internal error: Error while applying rule EnumerableJoinRule, args > [rel#40:LogicalJoin.NONE.[](left=rel#38:Subset#1.NONE.[0],right=rel#39:Subset#2.NONE.[0],condition==($7, > $0),joinType=inner)] > > I presume this is due to some limitation of the test environment, so right > now I'm unsure how to get this to work. > > One more thing I noticed is that the filter predicate (== 5) is not pushed > down to the database (Postgres in this case). Instead calcite used `select > * from article_facts` and applied the filter afterwards. Is that expected > behaviour for the RelBuilder? > > Thanks! > > Chris
