[
https://issues.apache.org/jira/browse/CALCITE-3815?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17056651#comment-17056651
]
Hanumath Rao Maduri commented on CALCITE-3815:
----------------------------------------------
Thanks [~julianhyde] for the review.
* In commit message, use brackets not colon
_[done]_
* Double-check that parser changes are necessary. Maybe adding SOME and EVERY
to ReservedFunctionNames is sufficient.
_I tried this approach, here are pros and cons as compared to current
approach._
_pros:_
_Not many changes in the parser. Use the existing ReservedFunctionNames to add
new functions._
_cons_
_New classes and new SqlKind to be added for these functions. Please let me
know if somehow these can be avoided._
_SqlKind for SOME function interferes with SOME quantifier. To avoid this
"SOME" can passed as argument instead of SqlKind.name() to the registered
class. However, fixing this way might introduce subtle bugs._
_There is a test case in SqlParserTest "select * from emp where name like some
(^select^ name from emp)"; which is passing with this approach._
_Ideally the above test case should pass, I will open another JIRA to track
this issue (test assumes it should fail). This bug is not related to any of the
approach. It just happens that in the ReservedFunctionNames' approach it
succeeds because the parser translates SOME in the query to the function name
instead of a quantifier. Even though this query works fine in the parser test,
it fails in the validation._
_Hence, I didn't opt the ReservedFunctionNames' approach. Please let me know
if I am missing anything._
* In SqlStdOperatorTable put one line between definitions (not 2, not 0).
_[done]_
* In BuiltInMethod, COLLECTION_INTERSECTION should be COLLECTION_RETAIN_ALL.
It is named after the Java method.
_[done]_
* In SqlToRelConverterTest, I'd drop 2 tests, and combine the other 2 tests
into one. We don't need that bloat.
_[done]_
* Add a SqlParserTest.
_[done]_
* In SqlValidatorTest, check that SOME and INTERSECT and EVERY act as
aggregate functions and implicitly make a query an aggregate query, like
testAggregateInNonGroupBy
_[done]_
* In reference.md, the arguments to SOME and EVERY should not be optional.
_[done]_
* Add one or two queries to agg.iq.
_[done]_
> Add missing SQL standard aggregate functions: EVERY, SOME, INTERSECTION
> -----------------------------------------------------------------------
>
> Key: CALCITE-3815
> URL: https://issues.apache.org/jira/browse/CALCITE-3815
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Hanumath Rao Maduri
> Priority: Major
> Labels: pull-request-available
> Time Spent: 1h
> Remaining Estimate: 0h
>
> Add missing SQL standard aggregate functions: EVERY, SOME, INTERSECTION.
> Examples:
> {code}
> SELECT deptno,
> EVERY(sal < 3000) AS es, SOME(sal < 1000) AS ss,
> EVERY(comm < 500) AS ec, SOME(comm < 500) AS sc
> FROM emp
> GROUP BY deptno;
> +--------+-------+-------+-------+------+
> | DEPTNO | ES | SS | EC | SC |
> +--------+-------+-------+-------+------+
> | 10 | FALSE | FALSE | | |
> | 20 | FALSE | TRUE | | |
> | 30 | TRUE | TRUE | FALSE | TRUE |
> +--------+-------+-------+-------+------+
> {code}
> {{EVERY}} and {{SOME}} can be implemented by translating to {{MIN}}, {{MAX}}:
> * {{EVERY(condition)}} is equivalent to {{MIN(condition)}};
> * {{SOME(condition)}} is equivalent to {{MAX(condition)}}
> where {{condition}} is a {{BOOLEAN}} expression, possibly allowing {{NULL}}
> values).
> {{INTERSECTION}} computes the intersection of collections (arrays and
> multisets). (Compare to {{FUSION}}, which computes the union of collections.)
> {{FUSION}} is in the operator table but there is no code-generation for it.
> This task should implement {{FUSION}} and {{INTERSECTION}} so that we can run
> queries in Enumerable mode.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
