[DRILL-427] select <columns> from <table> where <column> = '<VARCHAR>' fails

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.4.0
• Assignee: Unassigned
• Reporter: Xiaomeng Huang
• Created: 19/Mar/14 17:49
• Updated: 31/Jul/14 06:32
• Resolved: 23/Mar/14 03:38

Description

We need to filter the result which is essential for querying metadata if the user specify the catalog, schema_name, table_name and so on.

But the where clause will fail when compare with a column with VARCHAR type. It seems a cast coding problem as shown in the sqlline log

0: jdbc:drill:zk=local> select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'VIEWS';
Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "1c68bfdc-3390-4f1a-88cc-d93f9e65e002"
endpoint {
  address: "localhost"
  user_port: 31010
  control_port: 31011
  data_port: 31012
}
error_type: 0
message: "Failure while parsing sql. < ClassCastException:[ org.eigenbase.util.NlsString cannot be cast to java.lang.String ]"
]
Error: exception while executing query (state=,code=0)

The sqlline log is

0:39:28.570 [WorkManager Event Thread] DEBUG o.apache.drill.exec.work.WorkManager - Starting pending task org.apache.drill.exec.work.foreman.Foreman@4ec35eb5
10:39:28.571 [Client-1] DEBUG o.a.d.e.rpc.user.QueryResultHandler - Received QueryId part1: 7946799108619977974
part2: -7560732490996708466
 succesfully.  Adding listener org.apache.drill.jdbc.DrillResultSet$Listener@7c7ee578
10:39:28.590 [WorkManager-3] DEBUG o.a.d.e.planner.logical.DrillOptiq - RexCall =(CAST($2):VARCHAR(5) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, 'VIEWS'), {}
10:39:28.591 [WorkManager-3] DEBUG o.a.d.e.planner.logical.DrillOptiq - Binary
10:39:28.592 [WorkManager-3] DEBUG o.a.d.e.planner.logical.DrillOptiq - RexCall CAST($2):VARCHAR(5) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, {}
10:39:28.592 [WorkManager-3] DEBUG o.a.d.e.planner.logical.DrillOptiq - Special
10:39:28.594 [WorkManager-3] ERROR o.a.drill.exec.work.foreman.Foreman - Error c43534ea-b7d8-41ea-9d16-d96eb6cc20a0: Failure while parsing sql.
java.lang.ClassCastException: org.eigenbase.util.NlsString cannot be cast to java.lang.String
        at org.apache.drill.exec.planner.logical.DrillOptiq$RexToDrill.visitLiteral(DrillOptiq.java:191) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillOptiq$RexToDrill.visitLiteral(DrillOptiq.java:63) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.eigenbase.rex.RexLiteral.accept(RexLiteral.java:599) ~[optiq-core-0.4.18.jar:na]
        at org.apache.drill.exec.planner.logical.DrillOptiq$RexToDrill.visitCall(DrillOptiq.java:90) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillOptiq$RexToDrill.visitCall(DrillOptiq.java:63) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.eigenbase.rex.RexCall.accept(RexCall.java:106) ~[optiq-core-0.4.18.jar:na]
        at org.apache.drill.exec.planner.logical.DrillOptiq.toDrill(DrillOptiq.java:60) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillFilterRel.implement(DrillFilterRel.java:56) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillImplementor.visitChild(DrillImplementor.java:75) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillProjectRel.implement(DrillProjectRel.java:77) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillImplementor.visitChild(DrillImplementor.java:75) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillScreenRel.implement(DrillScreenRel.java:72) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]
        at org.apache.drill.exec.planner.logical.DrillImplementor.go(DrillImplementor.java:62) ~[drill-java-exec-1.0.0-m2-incubating-SNAPSHOT-rebuffed.jar:1.0.0-m2-incubating-SNAPSHOT]

For the reference, the table is like

0: jdbc:drill:zk=local> select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.COLUMNS;
+------------+-------------+------------------+
| TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION |
+------------+-------------+------------------+
| VIEWS      | TABLE_CATALOG | 0                |
| VIEWS      | TABLE_SHEMA | 1                |
| VIEWS      | TABLE_NAME  | 2                |
| VIEWS      | VIEW_DEFINITION | 3                |
| COLUMNS    | TABLE_CATALOG | 0                |
| COLUMNS    | TABLE_SCHEMA | 1                |
| COLUMNS    | TABLE_NAME  | 2                |
| COLUMNS    | COLUMN_NAME | 3                |
| COLUMNS    | ORDINAL_POSITION | 4                |
| COLUMNS    | IS_NULLABLE | 5                |
| COLUMNS    | DATA_TYPE   | 6                |
| COLUMNS    | CHARACTER_MAXIMUM_LENGTH | 7                |
| COLUMNS    | NUMERIC_PRECISION_RADIX | 8                |
| COLUMNS    | NUMERIC_SCALE | 9                |
| COLUMNS    | NUMERIC_PRECISION | 10               |
| TABLES     | TABLE_CATALOG | 0                |
| TABLES     | TABLE_SCHEMA | 1                |
| TABLES     | TABLE_NAME  | 2                |
| TABLES     | TABLE_TYPE  | 3                |
| CATALOGS   | CATALOG_NAME | 0                |
| CATALOGS   | CATALOG_DESCRIPTION | 1                |
| CATALOGS   | CATALOG_CONNECT | 2                |
| SCHEMATA   | CATALOG_NAME | 0                |
| SCHEMATA   | SCHEMA_NAME | 1                |
| SCHEMATA   | SCHEMA_OWNER | 2                |
+------------+-------------+------------------+

Issue Links

• is related to: DRILL-411 select "string" does not work