Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-7378

Allowing less outer/inner select

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.16.0
    • Fix Version/s: None
    • Component/s: Functions - Drill
    • Labels:
      None

      Description

      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:

      CONVERT_FROM('{"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}}','JSON') j;
      +----------------------------------------------------------+
      |                            j                             |
      +----------------------------------------------------------+
      | {"Tuesday":{"close":"22:00"},"Friday":{"close":"23:00"}} |
      +----------------------------------------------------------+
      

      But it's not possible to simply do

      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--.
      

      It's only possible to do

      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"}}] |
      +----------------------------------------------------------------------------------+
      

      Its possible to cascade with flatten:

      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"}}  |
      +---------------------------------------------+
      

      But it's not possible to use directly use the result of flatten to select key or value

      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
      

      You have to inner/outer select like:

      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  |
      +---------+
      

      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.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              benj641 benj
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: