Hello,
I just tried Drill with MS SQL Server and I found that Drill treats table names
case-sensitively, contrary to https://drill.apache.org/docs/lexical-structure/
which indicates that table names are "case-insensitive unless enclosed in
double quotation marks”. This presents a problem for users and existing SQL
scripts that expect table names to be case-insensitive.
This works: select * from mysandbox.dbo.AD_Role
This does not work: select * from mysandbox.dbo.ad_role
I will paste the verbose error log below.
Regards,
Paul Mogren
2016-02-29 17:43:15,754 [292b367c-61e3-5ae5-9b8a-2dadb12f47e1:foreman] INFO
o.a.drill.exec.work.foreman.Foreman - Query text for query id
292b367c-61e3-5ae5-9b8a-2dadb12f47e1: select * from mysandbox.dbo.ad_role
2016-02-29 17:43:16,288 [292b367c-61e3-5ae5-9b8a-2dadb12f47e1:foreman] INFO
o.a.d.e.planner.sql.DrillSqlWorker - User Error Occurred
org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: From line
1, column 15 to line 1, column 23: Table 'mysandbox.dbo.ad_role' not found
[Error Id: 6f4c3d19-c8d2-4ae1-8dab-0d0e46c9480f ]
at
org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:543)
~[drill-common-1.5.0.jar:1.5.0]
at
org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:202)
[drill-java-exec-1.5.0.jar:1.5.0]
at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:924)
[drill-java-exec-1.5.0.jar:1.5.0]
at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:250)
[drill-java-exec-1.5.0.jar:1.5.0]
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[na:1.8.0_40]
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[na:1.8.0_40]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_40]
Caused by: org.apache.calcite.tools.ValidationException:
org.apache.calcite.runtime.CalciteContextException: From line 1, column 15 to
line 1, column 23: Table 'mysandbox.dbo.ad_role' not found
at
org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:189)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.prepare.PlannerImpl.validateAndGetType(PlannerImpl.java:198)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:451)
~[drill-java-exec-1.5.0.jar:1.5.0]
at
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:198)
~[drill-java-exec-1.5.0.jar:1.5.0]
at
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:167)
~[drill-java-exec-1.5.0.jar:1.5.0]
at
org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:199)
[drill-java-exec-1.5.0.jar:1.5.0]
... 5 common frames omitted
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1,
column 15 to line 1, column 23: Table 'mysandbox.dbo.ad_role' not found
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method) ~[na:1.8.0_40]
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
~[na:1.8.0_40]
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
~[na:1.8.0_40]
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
~[na:1.8.0_40]
at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:714)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:702)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3931)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:106)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:877)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:863)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2778)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2763)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2986)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:877)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:863)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:210)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:837)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:551)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at
org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:187)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
... 10 common frames omitted
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Table
'mysandbox.dbo.ad_role' not found
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method) ~[na:1.8.0_40]
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
~[na:1.8.0_40]
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
~[na:1.8.0_40]
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
~[na:1.8.0_40]
at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10]
