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 >>> >
