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)
