[
https://issues.apache.org/jira/browse/CALCITE-3815?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17057616#comment-17057616
]
Feng Zhu commented on CALCITE-3815:
-----------------------------------
{quote}_New classes and new SqlKind to be added for these functions. Please let
me know if somehow these can be avoided._
{quote}
[~hanu.ncr], When introducing a new SqlKind, we usually need to change many
places accordingly. This PR creates SqlKind.INTERSECTION. Please also add it
into the corresponding EnumSet in SqlKind.
Since SqlKind.MIN/MAX can map to many functions, can we add a new constructor
to allow the function name for _SqlMinMaxAggFunction_?
E.g.,
{code:java}
public SqlMinMaxAggFunction(String name, SqlKind kind, SqlOperandTypeChecker
inputTypeChecker)
public static final SqlAggFunction EVERY = new SqlMinMaxAggFunction("EVERY",
SqlKind.MIN, OperandTypes.BOOLEAN);
public static final SqlAggFunction SOME = new SqlMinMaxAggFunction("SOME",
SqlKind.MAX, OperandTypes.BOOLEAN);
{code}
{quote}_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._
{quote}
I agree with you. If SOME can also be recognized as a function, this test case
should pass the parser phase.
I personally incline to make some efforts to put them in ReservedFunctionName.
[~julianhyde] WDYT?
> 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: 1.5h
> 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)
