Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-1029

select column from a `select *` sub-query fails for schema-less file

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.5.0
    • Component/s: Client - ODBC
    • Labels:

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                DrillCommitter DrillCommitter
                Reporter:
                xiaom Xiaomeng Huang
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: