benj created DRILL-7378: --------------------------- Summary: Allowing less outer/inner select Key: DRILL-7378 URL: https://issues.apache.org/jira/browse/DRILL-7378 Project: Apache Drill Issue Type: Improvement Components: Functions - Drill Affects Versions: 1.16.0 Reporter: benj
Currently, it's not possible to exploit the result of some function like _kvgen_ or _flatten_ and an inner/outer select is needed for some operations. It will be easiest to allow the use of the results of theses functions directly. Example: {code:sql} CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}','JSON') j; +----------------------------------------------------------+ | j | +----------------------------------------------------------+ | {"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}} | +----------------------------------------------------------+ {code} But it's not possible to simply do {code:sql} SELECT kvgen(CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}','JSON')); Error: PLAN ERROR: Failure while materializing expression in constant expression evaluator [CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}', 'JSON')]. Errors: Error in expression at index -1. Error: Only ProjectRecordBatch could have complex writer function. You are using complex writer function convert_fromJSON in a non-project operation!. Full expression: --UNKNOWN EXPRESSION--. {code} It's only possible to do {code:sql} SELECT kvgen(c) AS k FROM (SELECT CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}','JSON') c); +----------------------------------------------------------------------------------+ | k | +----------------------------------------------------------------------------------+ | [{"key":"Tuesday","value":{"close":"22:00"}},{"key":"Friday","value":{"close":"23:00"}}] | +----------------------------------------------------------------------------------+ {code} Its possible to cascade with flatten: {code:sql} SELECT flatten(kvgen(c)) f FROM (SELECT CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}','JSON') c); +---------------------------------------------+ | f | +---------------------------------------------+ | {"key":"Tuesday","value":{"close":"22:00"}} | | {"key":"Friday","value":{"close":"23:00"}} | +---------------------------------------------+ {code} But it's not possible to use directly use the result of flatten to select key or value {code:sql} SELECT (flatten(kvgen(r.c))).key f FROM (SELECT CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}','JSON') c) r; Error: VALIDATION ERROR: From line 1, column 9 to line 1, column 27: Incompatible types {code} You have to inner/outer select like: {code:sql} SELECT r.f.key k FROM (SELECT flatten(kvgen(c)) f FROM (SELECT CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}','JSON') c)) r; +---------+ | k | +---------+ | Tuesday | | Friday | +---------+ {code} it would be useful to be able to write/read shorter and simpler queries with limiting when it's possible the need of inner/outer SELECT. -- This message was sent by Atlassian Jira (v8.3.4#803005)