The join condition should be =($0, $1). JdbcFilterRule is instantiated when JdbcRules.rules() is called. It is called in two places: from JdbcConvention.register and from PlannerTest.MockJdbcTableScan.register.
Did you see Jacques Nadeau’s recent email? [1] Very likely you are seeing the same problem. Julian [1] https://mail-archives.apache.org/mod_mbox/calcite-dev/201605.mbox/%3CCAKa9qDmuNU%3DvBa1wT51n3WbaPqq9v70WSYuNonQFbDDKGVK5jw%40mail.gmail.com%3E <https://mail-archives.apache.org/mod_mbox/calcite-dev/201605.mbox/%3CCAKa9qDmuNU=vba1wt51n3wbapqq9v70wsyunonqfbddkgvk...@mail.gmail.com%3E> > On May 30, 2016, at 2:41 AM, Chris Baynes <[email protected]> wrote: > > 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 >>
