[jira] [Created] (DRILL-1736) Cannot cast to other data types after using flatten + convert_from('json')

["Hao Zhu (JIRA)" <ji...@apache.org>]

Hao Zhu created DRILL-1736:
------------------------------

             Summary: Cannot cast to other data types after using flatten + convert_from('json')
                 Key: DRILL-1736
                 URL: https://issues.apache.org/jira/browse/DRILL-1736
             Project: Apache Drill
          Issue Type: Bug
          Components: Functions - Drill
    Affects Versions: 0.6.0, 0.7.0
            Reporter: Hao Zhu


1. This SQL looks good.
{code}
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)
{code}

2. Can not cast column 'b' to other data type.
{code}
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)
{code}

3.  Still can not change data type after creating the view.
{code}
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
{code}




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)