I think CALCITE-1264 could definitely explain the lack of the predicate push down I'm seeing. There's still the issue of the join condition though. I can open a ticket for that.
> The join condition should be =($0, $1). If that's the case it seems to me that the expectations of join conditions in RelBuilderTest are wrong. For example: https://github.com/apache/calcite/blob/branch-1.7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L795 In this case we're joining EMP 8th col (of 8 cols), to DEPT 1st column (of 3 cols). So the condition should be =($7, $8). On Tue, May 31, 2016 at 6:34 AM, Julian Hyde <[email protected]> wrote: > PS Jordan logged a JIRA case. > https://issues.apache.org/jira/browse/CALCITE-1262 < > https://issues.apache.org/jira/browse/CALCITE-1262> > > > On May 30, 2016, at 9:27 PM, Julian Hyde <[email protected]> wrote: > > > > 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] <mailto: > [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] <mailto: > [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] > <mailto:[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 > >>> > > > >
