I'm using a project on both sides before the join, so there is only one column on each side. So in that case should the join condition be ($0, $1)? Or is ($0, $0) correct since it's joining the first left column to the first right column?
In either case the result set is still not correct, so I'll do some more digging there. As for the JdbcFilterRule, how is that set? On the BasicDataSource? I couldn't find that being used in a test. On Sat, May 28, 2016 at 3:00 AM, Julian Hyde <[email protected]> wrote: > 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 >
