[DRILL-1029] select column from a `select *` sub-query fails for schema-less file

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 0.5.0
• Component/s: Client - ODBC
• Labels: Tableau
• Assignee: DrillCommitter
• Reporter: Xiaomeng Huang
• Due: 15/Aug/14
• Created: 19/Jun/14 19:17
• Updated: 23/Apr/15 21:29
• Resolved: 29/Aug/14 17:55

Description

When using the following custom SQL to import data in Tableau,

select * from dfs.`default`.`/opt/drill/raw-files/click1.json`

Tableau will generate a query like

SELECT `TableauSQL`.`address` AS `address`,
  `TableauSQL`.`br` AS `br`,
  `TableauSQL`.`first_visit` AS `first_visit`,
  `TableauSQL`.`gender` AS `gender`,
  `TableauSQL`.`id` AS `id`,
  `TableauSQL`.`la` AS `la`,
  `TableauSQL`.`name` AS `name`,
  `TableauSQL`.`os` AS `os`,
  `TableauSQL`.`st` AS `st`,
  `TableauSQL`.`user_category` AS `user_category`
FROM (
  select * from dfs.`default`.`/opt/drill/raw-files/click1.json`
) `TableauSQL`

but this query will fail.

It seems happened in all schema-less file types when you run query in the form of select column from a `select *` sub-query.

For example, we observed this in SQLline:

0: jdbc:drill:local=localhost:31010> select L_ORDERKEY from cp.`tpch/lineitem.parquet` limit 1;
+------------+
| L_ORDERKEY |
+------------+
| 1          |
+------------+
1 row selected (0.835 seconds)
0: jdbc:drill:local=localhost:31010> select L_ORDERKEY from (select * from cp.`tpch/lineitem.parquet` limit 1);
Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "3a387934-9cca-4f3a-8f37-74cdca365113"
endpoint {
  address: "localhost"
  user_port: 31010
  control_port: 31011
  data_port: 31012
}
error_type: 0
message: "Failure while parsing sql. < ValidationException:[ org.eigenbase.util.EigenbaseContextException: From line 1, column 8 to line 1, column 17 ] < EigenbaseContextException:[ From line 1, column 8 to line 1, column 17 ] < SqlValidatorException:[ Column 'L_ORDERKEY' not found in any table ]"
]
Error: exception while executing query (state=,code=0)

This will block using custom SQL for schema-less file in Tableau.

Issue Links

• Is contained by: DRILL-931 Wildcard queries cause problems in certain schemaless situations
• is related to: DRILL-884 Query used to derive result set metadata for Tableau's "Custom SQL" does not run