[
https://issues.apache.org/jira/browse/CALCITE-3531?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16982859#comment-16982859
]
Rui Wang commented on CALCITE-3531:
-----------------------------------
I also tried to help search the definition of current_timestamp (or
current_date, or something similar), but didn't have a luck. So maybe SQL
standard does not define such functions?
In addition, I found the following from ISO/IEC 9075-2:2011(E).
{code:java}
4.17 Determinism
In general, an operation is deterministic if that operation assuredly computes
identical results when repeated
with identical input values. For an SQL-invoked routine, the values in the
argument list are regarded as the
input; otherwise, the SQL-data and the set of privileges by which they are
accessed is regarded as the input.
Differences in the ordering of rows, as permitted by General Rules that specify
implementation-dependent
behavior, are not regarded as significant to the question of determinism.
NOTE 47 — Transaction isolation levels have a significant impact on
determinism, particularly transaction isolation levels other
than SERIALIZABLE. However, this International Standard does not address that
impact, particularly because of the difficulty
in clearly specifying that impact without appearing to mandate implementation
techniques (such as row or page locking) and
because different SQL-implementations almost certainly resolve the issue in
significantly different ways.
Recognizing that an operation is deterministic is a difficult task, it is in
general not mandated by this International
Standard. SQL-invoked routines are regarded as deterministic if the routine is
declared to be DETERMINISTIC;
that is, the SQL-implementation trusts the definer of the SQL-invoked routine
to correctly declare that the
routine is deterministic. For other operations, this International Standard
does not label an operation as deterministic; instead it identifies certain
operations as “possibly non-deterministic”. Specific definitions can be
found in other subclauses relative to <value expression>, <table reference>,
<table primary>, <query specification>,<query expression>, and <SQL procedure
statement>.
Certain s are identified as “retrospectively deterministic”. A retrospectively
deterministic has the property that if it is True at one point time, then it
is True for all
later points in time if re-evaluated for the identical SQL-data by an arbitrary
user with the identical set of
privileges. The precise definition is found in Subclause 6.35, “<boolean value
expression>”.
{code}
> AggregateProjectPullUpConstantsRule should not remove deterministic function
> group key if the function is dynamic
> -----------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-3531
> URL: https://issues.apache.org/jira/browse/CALCITE-3531
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.21.0
> Reporter: Danny Chen
> Assignee: Danny Chen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> Now AggregateProjectPullUpConstantsRule simplify the query:
> {code:sql}
> select hiredate
> from sales.emp
> where sal is null and hiredate = current_timestamp
> group by sal, hiredate
> having count(*) > 3
> {code}
> from plan:
> {code:xml}
> LogicalProject(HIREDATE=[$1])
> LogicalFilter(condition=[>($2, 3)])
> LogicalAggregate(group=[{0, 1}], agg#0=[COUNT()])
> LogicalProject(SAL=[$5], HIREDATE=[$4])
> LogicalFilter(condition=[AND(IS NULL($5), =($4, CURRENT_TIMESTAMP))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> to plan:
> {code:xml}
> LogicalProject(HIREDATE=[$1])
> LogicalFilter(condition=[>($2, 3)])
> LogicalProject(SAL=[$0], HIREDATE=[CURRENT_TIMESTAMP], $f2=[$1])
> LogicalAggregate(group=[{0}], agg#0=[COUNT()])
> LogicalProject(SAL=[$5], HIREDATE=[$4])
> LogicalFilter(condition=[AND(IS NULL($5), =($4,
> CURRENT_TIMESTAMP))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> which is unsafe, because for stream sql, we need to group data by dateTime,
> also the result is wrong if a batch job runs across days.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
