While drill selects data from the directory and detects data types on-the-fly
it is possible that one field will be of several data types .
1. Create an input file as follows
20K rows with the following -
200 rows with the following -
2. CTAS as follows
CREATE TABLE dfs.`tmp`.`tp` as select * from dfs.`data.json` t
In this case will be created parquet table as the folder with two files.
3. Select the data
select t.others.additional from dfs.`tmp`.`tp` t
The result of selecting will be mix of EXPR$0<INT(OPTIONAL)> and EXPR$0<VARCHAR(OPTIONAL)>.
It happens because Drill defines column data type per file.
The same result with json files.
Since streaming aggregate does not support schema changes this issue makes impossible of using aggregate functions with query results.
- is related to
DRILL-3806 add metadata for untyped null and simple type promotion
- relates to
DRILL-3577 Counting nested fields on CTAS-created-parquet file/s reports inaccurate results