I'm not sure why it doesn't work for the case where you have a nested function, since the function definition in sys.functions indicates some sort of late binding of the data types:
0: jdbc:drill:drillbit=kk127> select * from sys.functions where name like '%ypeOf%'; +--------------+----------------+-------------+-----------+-----------+ | name | signature | returnType | source | internal | +--------------+----------------+-------------+-----------+-----------+ | drillTypeOf | LATE-REQUIRED | VARCHAR | built-in | false | | sqlTypeOf | LATE-REQUIRED | VARCHAR | built-in | false | | typeOf | LATE-REQUIRED | VARCHAR | built-in | false | +--------------+----------------+-------------+-----------+-----------+ Especially for the cast function, you are already aware that the return type is INT. So, it isn't like the schema is being discovered "late". (https://github.com/apache/drill/blob/master/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/scan/project/SchemaLevelProjection.java#L46) Perhaps someone else with more knowledge might be able to explain the discrepancy and if this is an expected behavior. ~ Kunal On 1/18/2019 1:36:02 PM, benj.dev <[email protected]> wrote: Hi, As the request of Kunal Khatua, I push this curiosity found in Drill 1.15.0 The Jira ticket is : https://issues.apache.org/jira/browse/DRILL-6978 I realized that TypeOf (drillTypeOf and sqlTypeOf) functions works when request files but doesn't work in certain case on "generated" data This first request works : SELECT typeof(md5), drillTypeOf(md5), sqlTypeOf(md5) FROM dfs.tmp.`mytable.csv` LIMIT 2; +----------+----------+--------------------+ | EXPR$0 | EXPR$1 | EXPR$2 | +----------+----------+--------------------+ | VARCHAR | VARCHAR | CHARACTER VARYING | | VARCHAR | VARCHAR | CHARACTER VARYING | +----------+----------+--------------------+ But this second doesn't : SELECT typeOf(a) FROM (SELECT CAST (5 AS int) AS a) x; Error: SYSTEM ERROR: IllegalArgumentException: Can not set org.apache.drill.exec.vector.complex.reader.FieldReader field org.apache.drill.exec.expr.fn.impl.UnionFunctions$GetType.input to org.apache.drill.exec.expr.holders.IntHolder And in a surprising way this third query works : SELECT md5, typeof(t), drillTypeOf(t), sqlTypeOf(t) FROM ((SELECT 'foo' AS t) UNION (SELECT 'bar' AS t)) x; +-------+----------+----------+--------------------+ | md5 | EXPR$1 | EXPR$2 | EXPR$3 | +-------+----------+----------+--------------------+ | foo | VARCHAR | VARCHAR | CHARACTER VARYING | | bar | VARCHAR | VARCHAR | CHARACTER VARYING | +-------+----------+----------+--------------------+ Thanks for any explanation and/or correction if necessary.
