Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      As per mailing list.

      ```SQL
      select * from emps join depts on emps.name = depts. name;
      ```

      ```
      org.eigenbase.relopt.RelOptPlanner$CannotPlanException: Node [rel(#193 | FLINK-193):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).ENUMERABLE.[]] could not be implemented; planner state:

      Root: rel(#193 | FLINK-193):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).ENUMERABLE.[]
      Original rel:
      AbstractConverter(subset=[rel(#193 | FLINK-193):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).ENUMERABLE.[]], convention=[ENUMERABLE], sort=[[]]): rowcount = 1.7976931348623157E308, cumulative cost =

      {inf}, id = 194
      AggregateRel(subset=[rel(#189 | FLINK-189):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).NONE.[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307 rows, 0.0 cpu, 0.0 io}, id = 188
      ProjectRel(subset=[rel(#187 | FLINK-187):Subset([#3|https://github.com/JulianHyde/optiq/issues/3] | FLINK-3).NONE.[]], $f0=[0]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu, 0.0 io}, id = 186
      JoinRel(subset=[rel(#185 | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[]], condition=[=(CAST($3):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($0):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary")], joinType=[inner]): rowcount = 1500.0, cumulative cost = {1500.0 rows, 0.0 cpu, 0.0 io}, id = 184
      EnumerableTableAccessRel(subset=[rel(#182 | FLINK-182):Subset([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0).ENUMERABLE.[]], table=[[SALES, TDATA]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 177
      EnumerableTableAccessRel(subset=[rel(#183 | FLINK-183):Subset([#1|https://github.com/JulianHyde/optiq/issues/1] | FLINK-1).ENUMERABLE.[]], table=[[SALES, TDATA2]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 178

      Sets:
      Set(#0 | FLINK-0), type: RecordType(JavaType(class java.lang.String) id, JavaType(class java.lang.String) aString, JavaType(class java.lang.String) time)
      rel(#182 | FLINK-182):Subset([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0).ENUMERABLE.[], best=rel(#177 | FLINK-177), importance=0.6561000000000001
      rel(#177 | FLINK-177):EnumerableTableAccessRel.ENUMERABLE.[](table=[SALES, TDATA]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
      Set(#1 | FLINK-1), type: RecordType(JavaType(class java.lang.String) id, JavaType(class java.lang.String) aString, JavaType(class java.lang.String) time)
      rel(#183 | FLINK-183):Subset([#1|https://github.com/JulianHyde/optiq/issues/1] | FLINK-1).ENUMERABLE.[], best=rel(#178 | FLINK-178), importance=0.6561000000000001
      rel(#178 | FLINK-178):EnumerableTableAccessRel.ENUMERABLE.[](table=[SALES, TDATA2]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
      Set(#2 | FLINK-2), type: RecordType(JavaType(class java.lang.String) id, JavaType(class java.lang.String) aString, JavaType(class java.lang.String) time, JavaType(class java.lang.String) id0, JavaType(class java.lang.String) aString0, JavaType(class java.lang.String) time0)
      rel(#185 | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[], best=null, importance=0.9112500000000001
      rel(#184 | FLINK-184):JoinRel.NONE.[](left=rel([#182|https://github.com/JulianHyde/optiq/issues/182] | FLINK-182):Subset([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0).ENUMERABLE.[],right=rel([#183|https://github.com/JulianHyde/optiq/issues/183] | FLINK-183):Subset([#1|https://github.com/JulianHyde/optiq/issues/1] | FLINK-1).ENUMERABLE.[],condition==(CAST($3):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($0):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"),joinType=inner), rowcount=1500.0, cumulative cost={inf}

      rel(#207 | FLINK-207):ProjectRel.NONE.[](child=rel([#206|https://github.com/JulianHyde/optiq/issues/206] | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[],id=$3,aString=$4,time=$5,id0=$0,aString0=$1,time0=$2), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      rel(#216 | FLINK-216):ProjectRel.NONE.[](child=rel([#185|https://github.com/JulianHyde/optiq/issues/185] | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[],id=$0,aString=$1,time=$2,id0=$3,aString0=$4,time0=$5), rowcount=1.7976931348623157E308, cumulative cost={inf}

      rel(#223 | FLINK-223):CalcRel.NONE.[](child=rel([#185|https://github.com/JulianHyde/optiq/issues/185] | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5=

      {inputs},id=$t0,aString=$t1,time=$t2,id0=$t3,aString0=$t4,time0=$t5), rowcount=1.7976931348623157E308, cumulative cost={inf}
      rel(#242 | FLINK-242):CalcRel.NONE.[](child=rel([#206|https://github.com/JulianHyde/optiq/issues/206] | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5={inputs}

      ,id=$t3,aString=$t4,time=$t5,id0=$t0,aString0=$t1,time0=$t2), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      rel(#199 | FLINK-199):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).ENUMERABLE.[], best=null, importance=0.81
      rel(#200 | FLINK-200):AbstractConverter.ENUMERABLE.[](child=rel([#185|https://github.com/JulianHyde/optiq/issues/185] | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[],convention=ENUMERABLE,sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}

      rel(#235 | FLINK-235):EnumerableCalcRel.ENUMERABLE.[](child=rel([#199|https://github.com/JulianHyde/optiq/issues/199] | FLINK-199):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).ENUMERABLE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5=

      {inputs},id=$t0,aString=$t1,time=$t2,id0=$t3,aString0=$t4,time0=$t5), rowcount=1.7976931348623157E308, cumulative cost={inf}
      rel(#251 | FLINK-251):EnumerableCalcRel.ENUMERABLE.[](child=rel([#220|https://github.com/JulianHyde/optiq/issues/220] | FLINK-220):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).ENUMERABLE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5={inputs}

      ,id=$t3,aString=$t4,time=$t5,id0=$t0,aString0=$t1,time0=$t2), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      Set(#3 | FLINK-3), type: RecordType(INTEGER $f0)
      rel(#187 | FLINK-187):Subset([#3|https://github.com/JulianHyde/optiq/issues/3] | FLINK-3).NONE.[], best=null, importance=1.0
      rel(#186 | FLINK-186):ProjectRel.NONE.[](child=rel([#185|https://github.com/JulianHyde/optiq/issues/185] | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[],$f0=0), rowcount=1.7976931348623157E308, cumulative cost={inf}

      rel(#195 | FLINK-195):CalcRel.NONE.[](child=rel([#185|https://github.com/JulianHyde/optiq/issues/185] | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5=

      {inputs},expr(#6 | FLINK-6)=0,$f0=$t6), rowcount=1.7976931348623157E308, cumulative cost={inf}
      rel(#211 | FLINK-211):ProjectRel.NONE.[](child=rel([#206|https://github.com/JulianHyde/optiq/issues/206] | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[],$f0=0), rowcount=1.7976931348623157E308, cumulative cost={inf}
      rel(#218 | FLINK-218):CalcRel.NONE.[](child=rel([#206|https://github.com/JulianHyde/optiq/issues/206] | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5={inputs}

      ,expr(#6 | FLINK-6)=0,$f0=$t6), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      rel(#196 | FLINK-196):Subset([#3|https://github.com/JulianHyde/optiq/issues/3] | FLINK-3).ENUMERABLE.[], best=null, importance=0.9
      rel(#197 | FLINK-197):AbstractConverter.ENUMERABLE.[](child=rel([#187|https://github.com/JulianHyde/optiq/issues/187] | FLINK-187):Subset([#3|https://github.com/JulianHyde/optiq/issues/3] | FLINK-3).NONE.[],convention=ENUMERABLE,sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}

      rel(#201 | FLINK-201):EnumerableCalcRel.ENUMERABLE.[](child=rel([#199|https://github.com/JulianHyde/optiq/issues/199] | FLINK-199):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).ENUMERABLE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5=

      {inputs},expr(#6 | FLINK-6)=0,$f0=$t6), rowcount=1.7976931348623157E308, cumulative cost={inf}
      rel(#222 | FLINK-222):EnumerableCalcRel.ENUMERABLE.[](child=rel([#220|https://github.com/JulianHyde/optiq/issues/220] | FLINK-220):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).ENUMERABLE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5={inputs}

      ,expr(#6 | FLINK-6)=0,$f0=$t6), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      Set(#4 | FLINK-4), type: RecordType(BIGINT EXPR$0)
      rel(#189 | FLINK-189):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).NONE.[], best=null, importance=1.0
      rel(#188 | FLINK-188):AggregateRel.NONE.[](child=rel([#187|https://github.com/JulianHyde/optiq/issues/187] | FLINK-187):Subset([#3|https://github.com/JulianHyde/optiq/issues/3] | FLINK-3).NONE.[],group={},EXPR$0=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}

      rel(#193 | FLINK-193):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).ENUMERABLE.[], best=null, importance=1.0
      rel(#194 | FLINK-194):AbstractConverter.ENUMERABLE.[](child=rel([#189|https://github.com/JulianHyde/optiq/issues/189] | FLINK-189):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).NONE.[],convention=ENUMERABLE,sort=[]), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      rel(#198 | FLINK-198):EnumerableAggregateRel.ENUMERABLE.[](child=rel([#196|https://github.com/JulianHyde/optiq/issues/196] | FLINK-196):Subset([#3|https://github.com/JulianHyde/optiq/issues/3] | FLINK-3).ENUMERABLE.[],group={},EXPR$0=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}

      Set(#5 | FLINK-5), type: RecordType(JavaType(class java.lang.String) id, JavaType(class java.lang.String) aString, JavaType(class java.lang.String) time, JavaType(class java.lang.String) id0, JavaType(class java.lang.String) aString0, JavaType(class java.lang.String) time0)
      rel(#206 | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[], best=null, importance=0.8201250000000002
      rel(#204 | FLINK-204):JoinRel.NONE.[](left=rel([#183|https://github.com/JulianHyde/optiq/issues/183] | FLINK-183):Subset([#1|https://github.com/JulianHyde/optiq/issues/1] | FLINK-1).ENUMERABLE.[],right=rel([#182|https://github.com/JulianHyde/optiq/issues/182] | FLINK-182):Subset([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0).ENUMERABLE.[],condition==(CAST($0):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($3):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"),joinType=inner), rowcount=1500.0, cumulative cost=

      {inf}
      rel(#210 | FLINK-210):ProjectRel.NONE.[](child=rel([#185|https://github.com/JulianHyde/optiq/issues/185] | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[],id=$3,aString=$4,time=$5,id0=$0,aString0=$1,time0=$2), rowcount=1.7976931348623157E308, cumulative cost={inf}

      rel(#213 | FLINK-213):ProjectRel.NONE.[](child=rel([#206|https://github.com/JulianHyde/optiq/issues/206] | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[],id=$0,aString=$1,time=$2,id0=$3,aString0=$4,time0=$5), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      rel(#224 | FLINK-224):CalcRel.NONE.[](child=rel([#185|https://github.com/JulianHyde/optiq/issues/185] | FLINK-185):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5={inputs},id=$t3,aString=$t4,time=$t5,id0=$t0,aString0=$t1,time0=$t2), rowcount=1.7976931348623157E308, cumulative cost={inf}

      rel(#236 | FLINK-236):CalcRel.NONE.[](child=rel([#206|https://github.com/JulianHyde/optiq/issues/206] | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5=

      {inputs},id=$t0,aString=$t1,time=$t2,id0=$t3,aString0=$t4,time0=$t5), rowcount=1.7976931348623157E308, cumulative cost={inf}
      rel(#220 | FLINK-220):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).ENUMERABLE.[], best=null, importance=0.7290000000000001
      rel(#221 | FLINK-221):AbstractConverter.ENUMERABLE.[](child=rel([#206|https://github.com/JulianHyde/optiq/issues/206] | FLINK-206):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).NONE.[],convention=ENUMERABLE,sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
      rel(#234 | FLINK-234):EnumerableCalcRel.ENUMERABLE.[](child=rel([#199|https://github.com/JulianHyde/optiq/issues/199] | FLINK-199):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).ENUMERABLE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5={inputs}

      ,id=$t3,aString=$t4,time=$t5,id0=$t0,aString0=$t1,time0=$t2), rowcount=1.7976931348623157E308, cumulative cost=

      {inf}
      rel(#252 | FLINK-252):EnumerableCalcRel.ENUMERABLE.[](child=rel([#220|https://github.com/JulianHyde/optiq/issues/220] | FLINK-220):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).ENUMERABLE.[],expr([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0)..5={inputs},id=$t0,aString=$t1,time=$t2,id0=$t3,aString0=$t4,time0=$t5), rowcount=1.7976931348623157E308, cumulative cost={inf}

      ```

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/69
      Created by: codek
      Labels:
      Created at: Mon Oct 28 17:34:30 CET 2013
      State: closed

        Activity

        Hide
        github-import GitHub Import added a comment -

        [Date: Wed Oct 30 04:51:39 CET 2013, Author: sujee81]

        I'm currently experimenting with Optiq. I noticed the same when I changed the join column from int to string, it fails with above error.

        Also fails with following sub-query but here not due to string

        select d.NAME, (SELECT SUM(AGE) FROM EMPS e WHERE e.DEPTNO = d.DEPTNO) from DEPTS d

        If I rewrite this query as below, it works fine.

        select d.NAME, e1.age from DEPTS d LEFT JOIN (SELECT e.DEPTNO, SUM(AGE) AS age FROM EMPS e GROUP BY e.DEPTNO) e1 ON d.DEPTNO = e1.DEPTNO

        Show
        github-import GitHub Import added a comment - [Date: Wed Oct 30 04:51:39 CET 2013, Author: sujee81 ] I'm currently experimenting with Optiq. I noticed the same when I changed the join column from int to string, it fails with above error. Also fails with following sub-query but here not due to string select d.NAME, (SELECT SUM(AGE) FROM EMPS e WHERE e.DEPTNO = d.DEPTNO) from DEPTS d If I rewrite this query as below, it works fine. select d.NAME, e1.age from DEPTS d LEFT JOIN (SELECT e.DEPTNO, SUM(AGE) AS age FROM EMPS e GROUP BY e.DEPTNO) e1 ON d.DEPTNO = e1.DEPTNO
        Hide
        github-import GitHub Import added a comment -

        [Date: Thu Oct 31 00:58:22 CET 2013, Author: julianhyde]

        Thanks @sujee81. I'll take a look at your sub-query issue when I look into this bug.

        Show
        github-import GitHub Import added a comment - [Date: Thu Oct 31 00:58:22 CET 2013, Author: julianhyde ] Thanks @sujee81. I'll take a look at your sub-query issue when I look into this bug.
        Hide
        github-import GitHub Import added a comment -

        [Date: Sun Nov 24 11:07:36 CET 2013, Author: julianhyde]

        This will be fixed in optiq-csv when we upgrade it to optiq-0.4.16 in a day or two.

        Show
        github-import GitHub Import added a comment - [Date: Sun Nov 24 11:07:36 CET 2013, Author: julianhyde ] This will be fixed in optiq-csv when we upgrade it to optiq-0.4.16 in a day or two.

          People

          • Assignee:
            Unassigned
            Reporter:
            github-import GitHub Import
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development