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

Cannot cast to other data types after using flatten + convert_from('json')

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 0.6.0, 0.7.0
    • Fix Version/s: 0.9.0
    • Component/s: Functions - Drill
    • Labels:
      None

      Description

      1. This SQL looks good.

      select cast(row_key as int) as b, flatten(convert_from(mat.i.n , 'json')) as d from dfs.root.`table/mat` as mat;
      +------------+------------+
      |     b      |     d      |
      +------------+------------+
      | 100        | 10         |
      | 100        | 1000       |
      | 101        | 20         |
      | 101        | 1200       |
      +------------+------------+
      4 rows selected (0.196 seconds)
      

      2. Can not cast column 'b' to other data type.

      with tmp as
      (select cast(row_key as int) as b, flatten(convert_from(mat.i.n , 'json')) as d from dfs.root.`table/mat` as mat)
      select * from tmp where cast(tmp.d as int)=10;
       
      Query failed: Failure while running fragment., Failure while trying to materialize incoming schema.  Errors:
      Error in expression at index -1.  Error: Missing function implementation: [castINT(MAP-REQUIRED)].  Full expression: --UNKNOWN EXPRESSION--.. [ 744bffba-5ad9-40f4-a47e-25dc83565716 on n4a:31010 ]
        (org.apache.drill.exec.exception.SchemaChangeException) Failure while trying to materialize incoming schema.  Errors:
      Error in expression at index -1.  Error: Missing function implementation: [castINT(MAP-REQUIRED)].  Full expression: --UNKNOWN EXPRESSION--..
       org.apache.drill.exec.physical.impl.filter.FilterRecordBatch.generateSV2Filterer():194
       org.apache.drill.exec.physical.impl.filter.FilterRecordBatch.setupNewSchema():114   org.apache.drill.exec.record.AbstractSingleRecordBatch.buildSchema():110
       org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.buildSchema():80   org.apache.drill.exec.physical.impl.svremover.RemovingRecordBatch.buildSchema():64    org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.buildSchema():80
      org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.buildSchema():269    org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.buildSchema():80
      org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.buildSchema():95
          org.apache.drill.exec.work.fragment.FragmentExecutor.run():111
          org.apache.drill.exec.work.WorkManager$RunnableWrapper.run():249
          .......():0
      
      Error: exception while executing query: Failure while executing query. (state=,code=0)
      

      3. Still can not change data type after creating the view.

      create or replace view testview as select cast(row_key as int) as b, flatten(convert_from(mat.i.n , 'json')) as d from dfs.root.`table/mat` as mat;
       
      describe testview;
      +-------------+------------+-------------+
      | COLUMN_NAME | DATA_TYPE  | IS_NULLABLE |
      +-------------+------------+-------------+
      | b           | INTEGER    | NO          |
      | d           | ANY        | NO          |
      +-------------+------------+-------------+
      2 rows selected (0.505 seconds)
      
      select * from testview where cast(d as int)=10;
       
      Query failed: Failure while running fragment., Failure while trying to materialize incoming schema.  Errors:
      Error in expression at index -1.  Error: Missing function implementation: [castINT(MAP-REQUIRED)].  Full expression: --UNKNOWN EXPRESSION--.. [ e3a92573-3947-416e-b0ea-aa6dc4d47a20 on n4a:31010 ]
        (org.apache.drill.exec.exception.SchemaChangeException) Failure while trying to materialize incoming schema.  Errors:
      Error in expression at index -1.  Error: Missing function implementation: [castINT(MAP-REQUIRED)].  Full expression: --UNKNOWN EXPRESSION--..
       org.apache.drill.exec.physical.impl.filter.FilterRecordBatch.generateSV2Filterer():194
       org.apache.drill.exec.physical.impl.filter.FilterRecordBatch.setupNewSchema():114
       org.apache.drill.exec.record.AbstractSingleRecordBatch.buildSchema():110
       org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.buildSchema():80
       org.apache.drill.exec.physical.impl.svremover.RemovingRecordBatch.buildSchema():64
       org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.buildSchema():80
       org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.buildSchema():269
       org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.buildSchema():80    org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.buildSchema():95
          org.apache.drill.exec.work.fragment.FragmentExecutor.run():111
          org.apache.drill.exec.work.WorkManager$RunnableWrapper.run():249
          .......():0
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                mehant Mehant Baid
                Reporter:
                haozhu Hao Zhu
              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: