Dremio JDBC Driver with CONTAINS

1

Dremio JDBC has problems with multiple fields specified on Contains

sqlline

!connect jdbc:dremio:direct=localhost:31010 master secret com.dremio.jdbc.Driver

create view siren.Companies as select * from localES.company.Company where contains(city:*San* or founded_month:*San*);

0: jdbc:dremio:direct=localhost:31010> create view siren.Companies as select * from localES.company.Company where contains(city:*San* or founded_month:*San*);
Error: VALIDATION ERROR: No match found for function signature CONTAINS(<CHARACTER>, <NUMERIC>, <CHARACTER>)

Sql Query SELECT *
FROM "localES"."company"."Company"
WHERE "contains"("city", "founded_month", '$0 : *San*  or $1 : *San*')
startLine 3
startColumn 7
endLine 3
endColumn 70

[Error Id: 7deea38b-101e-42bf-9e1d-49ae86cd1b9f on localhost:31010]

  (org.apache.calcite.runtime.CalciteContextException) From line 3, column 7 to line 3, column 70: No match found for function signature CONTAINS(<CHARACTER>, <NUMERIC>, <CHARACTER>)
    sun.reflect.GeneratedConstructorAccessor270.newInstance():-1
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
    java.lang.reflect.Constructor.newInstance():423
    org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
    org.apache.calcite.sql.SqlUtil.newContextException():798
    org.apache.calcite.sql.SqlUtil.newContextException():786
    org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
    org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction():1615
    org.apache.calcite.sql.SqlFunction.deriveType():278
    org.apache.calcite.sql.SqlFunction.deriveType():223
    org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4445
    org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4432
    org.apache.calcite.sql.SqlCall.accept():137
    org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1531
    org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1516
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn():3529
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause():3516
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3080
    org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
    org.apache.calcite.sql.validate.AbstractNamespace.validate():84
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
    org.apache.calcite.sql.SqlSelect.validate():208
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
    org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
    com.dremio.exec.planner.sql.SqlConverter.validate():187
    com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():167
    com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
    com.dremio.exec.planner.sql.handlers.commands.HandlerToPreparePlan.plan():85
    com.dremio.exec.work.foreman.AttemptManager.run():306
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (org.apache.calcite.sql.validate.SqlValidatorException) No match found for function signature CONTAINS(<CHARACTER>, <NUMERIC>, <CHARACTER>)
    sun.reflect.GeneratedConstructorAccessor273.newInstance():-1
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
    java.lang.reflect.Constructor.newInstance():423
    org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
    org.apache.calcite.runtime.Resources$ExInst.ex():572
    org.apache.calcite.sql.SqlUtil.newContextException():798
    org.apache.calcite.sql.SqlUtil.newContextException():786
    org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
    org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction():1615
    org.apache.calcite.sql.SqlFunction.deriveType():278
    org.apache.calcite.sql.SqlFunction.deriveType():223
    org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4445
    org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4432
    org.apache.calcite.sql.SqlCall.accept():137
    org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1531
    org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1516
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn():3529
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause():3516
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3080
    org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
    org.apache.calcite.sql.validate.AbstractNamespace.validate():84
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
    org.apache.calcite.sql.SqlSelect.validate():208
    org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
    org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
    com.dremio.exec.planner.sql.SqlConverter.validate():187
    com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():167
    com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
    com.dremio.exec.planner.sql.handlers.commands.HandlerToPreparePlan.plan():85
    com.dremio.exec.work.foreman.AttemptManager.run():306
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748 (state=,code=0)
0: jdbc:dremio:direct=localhost:31010> 
Error: Statement is already closed. (state=,code=0)
2

Does this query work from the Dremio SQL console?

Does it work for you without the create view clause? It does for me on 1.2.

Kelly

3

Hi Kelly,
Yes, on the Dremio UI SQL console works fine (without create view), but fail with JDBC connection (with or without create view).

4

Thanks. We are tracking this and will follow up with additional questions or information about when this issue will be addressed.

5

Hi Kelly,

Getting the same issue - classpath is OK
any insight ?

6

Sorry folks, I missed the thread. Currently it is not possible to create a view using CONTAINS.

7

No Problem,

The real issue that I had was installing the additional - unix odbc dev package

The ODBC wasn’t connecting on ubuntu without installing only the unix odbc package on ubuntu

Thanks !