You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "benj (Jira)" <ji...@apache.org> on 2019/09/19 07:53:00 UTC
[jira] [Created] (DRILL-7378) Allowing less outer/inner select
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)