Nested field query improvement

[dai-chen]

Is your feature request related to a problem? Please describe.
Nested field query is supported by following PartiQL specification: https://github.com/opensearch-project/sql/blob/main/docs/user/beyond/partiql.rst. There are limitations regarding the nested levels and query types that needs improvement.

1. When using * in select, myIndex.field.subFieldA > myIndex.field.subFieldB fails opendistro-for-elasticsearch/sql#1055: Comparison between nested field
2. SELECT nested() cannot get sub properties opendistro-for-elasticsearch/sql#963: ?
3. Matching multiple nested fields opendistro-for-elasticsearch/sql#797: Nested field involved in join
4. SELECT * return extra column for nested field opendistro-for-elasticsearch/sql#399: Select *
5. Aggregating over nested field doesn't work for default format opendistro-for-elasticsearch/sql#398: Aggregate over nested field
6. Support LEFT JOIN, WHERE and GROUP BY on Nested Field. opendistro-for-elasticsearch/sql#245: Nested field involved in join
7. Aggregation query with nested field returns garbage opendistro-for-elasticsearch/sql#155: Aggregate over nested field
8. Lack of support for nested documents with more than one level of nesting opendistro-for-elasticsearch/sql#152: Deep nested field
9. Where clause in nested queries does not return expected results. opendistro-for-elasticsearch/sql#87: Filter nested field

Describe the solution you'd like
N/A

Describe alternatives you've considered
N/A

Additional context
Add any other context or screenshots about the feature request here.

[FreCap]

@dai-chen

Is nested supported at all in the new engine right now excluding legacy fallback?

I was running very simple queries and it seems that nested queries are falling over the legacy engine because nested keyword cannot be found.

Could you please confirm this is the case?

[2021-05-28T13:58:01,520][INFO ][c.a.o.s.l.p.RestSqlAction] [elasticsearch_grid1273_0] [7d7ea27d-482f-4bcd-9ccd-8bbcb1c35faf] Incoming request /_opendistro/_sql?pretty=true: ( SELECT identifier, identifier, identifier FROM table WHERE nested(identifier, identifier = boolean_literal) LIMIT number )
[2021-05-28T13:58:01,527][WARN ][stderr                   ] [elasticsearch_grid1273_0] line 2:109 extraneous input 'nested' expecting {'CASE', 'CAST', 'FALSE', 'FIRST', 'LAST', 'NOT', 'NULL', 'RIGHT', 'TRUE', 'AVG', 'COUNT', 'MAX', 'MIN', 'SUM', 'SUBSTRING', 'TRIM', 'FULL', 'INTERVAL', 'MICROSECOND', 'SECOND', 'MINUTE', 'HOUR', 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR', 'ABS', 'ACOS', 'ASIN', 'ATAN', 'ATAN2', 'CEIL', 'CEILING', 'CONCAT', 'CONCAT_WS', 'CONV', 'COS', 'COT', 'CRC32', 'DATE', 'DATE_FORMAT', 'DATE_ADD', 'DATE_SUB', 'DAYOFMONTH', 'DAYOFWEEK', 'DAYOFYEAR', 'DAYNAME', 'DEGREES', 'E', 'EXP', 'FLOOR', 'FROM_DAYS', 'IF', 'IFNULL', 'ISNULL', 'LENGTH', 'LN', 'LOG', 'LOG10', 'LOG2', 'LOWER', 'LTRIM', 'MONTHNAME', 'NULLIF', 'PI', 'POW', 'POWER', 'RADIANS', 'RAND', 'ROUND', 'RTRIM', 'SIGN', 'SIN', 'SQRT', 'SUBDATE', 'TAN', 'TIME', 'TIME_TO_SEC', 'TIMESTAMP', 'TRUNCATE', 'TO_DAYS', 'UPPER', 'D', 'T', 'TS', 'DENSE_RANK', 'RANK', 'ROW_NUMBER', 'FIELD', 'SUBSTR', 'STRCMP', 'ADDDATE', '+', '-', 'MOD', '.', '(', '0', '1', '2', STRING_LITERAL, DECIMAL_LITERAL, REAL_LITERAL, ID, DOUBLE_QUOTE_ID, BACKTICK_QUOTE_ID}

Even if I search for nested in core not much comes up.

[FreCap]

@dai-chen do you know if nested support will be prioritized any time soon?

[GumpacG]

Also related to #1104