Error using prepared statement when connecting to druid

Hi,

I am using avatica 1.13.0 to connect to druid 1.12.0. I get this error when using Prepared Statement to run a query. It works fine when I use Statement instead of Prepared Statements.

Please take a look and suggest how to fix this? I have logged a bug with calcite.

Test code

String sql = “SELECT __time, host FROM canarytest WHERE (host = ?)”;
PreparedStatement st = connection.prepareStatement(sql);
st.setString(1, “i-01e588977b1b23b88”);
data = st.executeQuery();
while (data.next()) {
System.out.println(data.getInt("__time") + " " + data.getString(“host”));
}

**Exception:**

Exception in thread "main" org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: SELECT __time, host FROM canarytest WHERE (host = ?)

at org.apache.calcite.avatica.Helper.createException(Helper.java:54)

at org.apache.calcite.avatica.Helper.createException(Helper.java:41)

at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:358)

at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)

at DruidTest.main(DruidTest.java:70)

org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node [rel#1412768:Subset#2.DRUID.[]] could not be implemented; planner state:

Root: rel#1412768:Subset#2.DRUID.[]

Original rel:

LogicalFilter(subset=[rel#1412768:Subset#2.DRUID.[]], condition=[=($1, ?0)]): rowcount = 15.0, cumulative cost = {15.0 rows, 100.0 cpu, 0.0 io}, id = 1412766

  LogicalProject(subset=[rel#1412765:Subset#1.NONE.[]], __time=[$0], host=[$2]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io}, id = 1412764

    LogicalTableScan(subset=[rel#1412763:Subset#0.NONE.[]], table=[[druid, canarytest]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1412753

Sets:

Set#0, type: RecordType(TIMESTAMP(3) __time, BIGINT count, VARCHAR host)

rel#1412763:Subset#0.NONE.[], best=null, importance=0.7290000000000001

rel#1412753:LogicalTableScan.NONE.[](table=[druid, canarytest]), rowcount=100.0, cumulative cost={inf}

rel#1412777:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"canarytest"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","count","host"],"legacy":false,"context":{},"descending":false,"granularity":{"type":"all"}},signature={__time:LONG, count:LONG, host:STRING}), rowcount=1.0, cumulative cost={inf}

rel#1412781:Subset#0.DRUID.[], best=rel#1412780, importance=0.36450000000000005

rel#1412780:DruidQueryRel.DRUID.[](query={"queryType":"scan","dataSource":{"type":"table","name":"canarytest"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","count","host"],"legacy":false,"context":{},"descending":false,"granularity":{"type":"all"}},signature={__time:LONG, count:LONG, host:STRING}), rowcount=1.0, cumulative cost={1.0 rows, 0.0 cpu, 0.0 io}

Set#1, type: RecordType(TIMESTAMP(3) __time, VARCHAR host)

rel#1412765:Subset#1.NONE.[], best=null, importance=0.81

rel#1412764:LogicalProject.NONE.[](input=rel#1412763:Subset#0.NONE.[],__time=$0,host=$2), rowcount=100.0, cumulative cost={inf}

rel#1412779:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"canarytest"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","host"],"legacy":false,"context":{},"descending":false,"granularity":{"type":"all"}},signature={__time:LONG, host:STRING}), rowcount=1.0, cumulative cost={inf}

rel#1412784:Subset#1.DRUID.[], best=rel#1412783, importance=0.405

rel#1412783:DruidQueryRel.DRUID.[](query={"queryType":"scan","dataSource":{"type":"table","name":"canarytest"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","host"],"legacy":false,"context":{},"descending":false,"granularity":{"type":"all"}},signature={__time:LONG, host:STRING}), rowcount=1.0, cumulative cost={1.002 rows, 0.0 cpu, 0.0 io}

Set#2, type: RecordType(TIMESTAMP(3) __time, VARCHAR host)

rel#1412767:Subset#2.NONE.[], best=null, importance=0.9

rel#1412766:LogicalFilter.NONE.[](input=rel#1412765:Subset#1.NONE.[],condition==($1, ?0)), rowcount=15.0, cumulative cost={inf}

rel#1412773:LogicalProject.NONE.[](input=rel#1412772:Subset#3.NONE.[],__time=$0,host=$2), rowcount=15.0, cumulative cost={inf}

rel#1412768:Subset#2.DRUID.[], best=null, importance=1.0

rel#1412769:AbstractConverter.DRUID.[](input=rel#1412767:Subset#2.NONE.[],convention=DRUID,sort=[]), rowcount=15.0, cumulative cost={inf}

Set#3, type: RecordType(TIMESTAMP(3) __time, BIGINT count, VARCHAR host)

rel#1412772:Subset#3.NONE.[], best=null, importance=0.81

rel#1412770:LogicalFilter.NONE.[](input=rel#1412763:Subset#0.NONE.[],condition==($2, ?0)), rowcount=15.0, cumulative cost={inf}

LogicalFilter(subset=[rel#1412797:Subset#6.BINDABLE.[]], condition=[=($1, ?0)]): rowcount = 15.0, cumulative cost = {15.0 rows, 100.0 cpu, 0.0 io}, id = 1412795

  LogicalProject(subset=[rel#1412794:Subset#5.NONE.[]], __time=[$0], host=[$2]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io}, id = 1412793

    LogicalTableScan(subset=[rel#1412792:Subset#4.NONE.[]], table=[[druid, canarytest]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1412753

Sets:

Set#4, type: RecordType(TIMESTAMP(3) __time, BIGINT count, VARCHAR host)

rel#1412792:Subset#4.NONE.[], best=null, importance=0.7290000000000001

rel#1412753:LogicalTableScan.NONE.[](table=[druid, canarytest]), rowcount=100.0, cumulative cost={inf}

rel#1412813:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"canarytest"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","count","host"],"legacy":false,"context":{},"descending":false,"granularity":{"type":"all"}},signature={__time:LONG, count:LONG, host:STRING}), rowcount=1.0, cumulative cost={inf}

rel#1412827:Subset#4.BINDABLE.[], best=null, importance=0.81

rel#1412826:InterpretableConverter.BINDABLE.[](input=rel#1412792:Subset#4.NONE.[]), rowcount=100.0, cumulative cost={inf}

Set#5, type: RecordType(TIMESTAMP(3) __time, VARCHAR host)

rel#1412794:Subset#5.NONE.[], best=null, importance=0.81

rel#1412793:LogicalProject.NONE.[](input=rel#1412792:Subset#4.NONE.[],__time=$0,host=$2), rowcount=100.0, cumulative cost={inf}

rel#1412814:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"canarytest"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","host"],"legacy":false,"context":{},"descending":false,"granularity":{"type":"all"}},signature={__time:LONG, host:STRING}), rowcount=1.0, cumulative cost={inf}

rel#1412817:Subset#5.BINDABLE.[], best=null, importance=0.9

rel#1412821:InterpretableConverter.BINDABLE.[](input=rel#1412794:Subset#5.NONE.[]), rowcount=100.0, cumulative cost={inf}

rel#1412830:BindableProject.BINDABLE.[](input=rel#1412827:Subset#4.BINDABLE.[],__time=$0,host=$2), rowcount=100.0, cumulative cost={inf}

Set#6, type: RecordType(TIMESTAMP(3) __time, VARCHAR host)

rel#1412796:Subset#6.NONE.[], best=null, importance=0.9

rel#1412795:LogicalFilter.NONE.[](input=rel#1412794:Subset#5.NONE.[],condition==($1, ?0)), rowcount=15.0, cumulative cost={inf}

rel#1412803:LogicalProject.NONE.[](input=rel#1412802:Subset#7.NONE.[],__time=$0,host=$2), rowcount=15.0, cumulative cost={inf}

rel#1412797:Subset#6.BINDABLE.[], best=null, importance=1.0

rel#1412798:AbstractConverter.BINDABLE.[](input=rel#1412796:Subset#6.NONE.[],convention=BINDABLE,sort=[]), rowcount=15.0, cumulative cost={inf}

rel#1412799:InterpretableConverter.BINDABLE.[](input=rel#1412796:Subset#6.NONE.[]), rowcount=15.0, cumulative cost={inf}

rel#1412812:BindableProject.BINDABLE.[](input=rel#1412811:Subset#7.BINDABLE.[],__time=$0,host=$2), rowcount=15.0, cumulative cost={inf}

rel#1412818:BindableFilter.BINDABLE.[[]](input=rel#1412817:Subset#5.BINDABLE.[],condition==($1, ?0)), rowcount=15.0, cumulative cost={inf}

Set#7, type: RecordType(TIMESTAMP(3) __time, BIGINT count, VARCHAR host)

rel#1412802:Subset#7.NONE.[], best=null, importance=0.81

rel#1412800:LogicalFilter.NONE.[](input=rel#1412792:Subset#4.NONE.[],condition==($2, ?0)), rowcount=15.0, cumulative cost={inf}

rel#1412811:Subset#7.BINDABLE.[], best=null, importance=0.9

rel#1412816:InterpretableConverter.BINDABLE.[](input=rel#1412802:Subset#7.NONE.[]), rowcount=15.0, cumulative cost={inf}

rel#1412831:BindableFilter.BINDABLE.[[]](input=rel#1412827:Subset#4.BINDABLE.[],condition==($2, ?0)), rowcount=15.0, cumulative cost={inf}

at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:437)

at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:296)

at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:662)

at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:339)

at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:358)

at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:337)

at org.apache.druid.sql.calcite.planner.DruidPlanner.planWithBindableConvention(DruidPlanner.java:281)

at org.apache.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:133)

... 27 more

Hey Puneet,

How are you? Can you confirm your Druid version? Are you running 0.12 or 0.13?

Hi Daniel,

I am fine, how are you?

We are using druid 0.12 (sorry about the typo in the previous mail), and trying to connect using calcite-avatica.

Thanks,

-Puneet

Unfortunately, I think https://github.com/apache/incubator-druid/pull/6974 adds the functionality you need. This is assuming, based on your error message, that you are using an avatica jdbc connection to Druid, and not connecting to Calcite’s Druid adapter. Druid’s SQL implementation is originally based on, but independent from the implementation that calcite provides, so I’m unsure if the bug report you file there is correct if you are indeed using Druid’s SQL engine.

Thanks Clint. I will close the bug with calcite.