[jira] [Commented] (CALCITE-4256) RexSimplify should not simplify P AND P to P, if it contains a call to RAND or RAND_INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17196863#comment-17196863
]
Thomas Rebele commented on CALCITE-4256:
Personally, if I write a query with RAND()<0.5 AND RAND()<0.5, then I would
expect less rows than if the query filters by RAND()<0.5.
I don't know whether this case is specified in the SQL standard. Different
database vendors handle it differently, according to
[http://sqlfiddle.com|http://sqlfiddle.com/]. I've tried the following (only
syntax I could make work with Oracle):
{code:java}
create table t( i integer)//
insert into t values (1)//
insert into t values (2)//
insert into t values (3)//
insert into t values (4)//
insert into t values (5)//
insert into t values (6)//
insert into t values (7)//
insert into t values (8)//
insert into t values (9)//
insert into t values (10)//
{code}
and the right query for each DBMS
{code:java}
select i, rand() from t where rand()<.5 and rand()<.5 and rand()<.5 and
rand()<.5 and rand()<.5
select i, random() from t where random()<.5 and random()<.5 and random()<.5 and
random()<.5 and random()<.5
select i, dbms_random.value from t where dbms_random.value<.5 and
dbms_random.value<.5 and dbms_random.value<.5 and dbms_random.value<.5 and
dbms_random.value<.5
{code}
Observations:
- MySQL 5.6: as I would expect (i.e., executing the query several times the
result is either empty or just 1-2 rows)
- Oracle 11g R2: seems to simplify the where to rand()<.5, and evaluates it
just once for the query. So either the result is empty or it contains all rows.
- PostgreSQL 9.6 and 9.3: as I would expect
- SQLite (SQL.js): as I would expect
- MS SQL Server 2017: like Oracle; the rand in the select clause gets
evaluated only once for the query.
> RexSimplify should not simplify P AND P to P, if it contains a call to RAND
> or RAND_INTEGER
> ---
>
> Key: CALCITE-4256
> URL: https://issues.apache.org/jira/browse/CALCITE-4256
> Project: Calcite
> Issue Type: Bug
>Reporter: Thomas Rebele
>Priority: Major
>
> Example: RAND_INTEGER() = 1 AND RAND_INTEGER() = 1 is false with a higher
> probability than RAND_INTEGER() = 1
> Here a test case for RexProgramTest:
> {code}
> @Test void testSimplifyRandomAnd() {
> checkSimplifyUnchanged(
> and(
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1)),
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1))
> ));
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4256) RexSimplify should not simplify P AND P to P, if it contains a call to RAND or RAND_INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17196294#comment-17196294
]
Julian Hyde commented on CALCITE-4256:
--
Ok, let me push back on this a bit.
When we optimize, are we under any obligation not to change probabilities?
> RexSimplify should not simplify P AND P to P, if it contains a call to RAND
> or RAND_INTEGER
> ---
>
> Key: CALCITE-4256
> URL: https://issues.apache.org/jira/browse/CALCITE-4256
> Project: Calcite
> Issue Type: Bug
>Reporter: Thomas Rebele
>Priority: Major
>
> Example: RAND_INTEGER() = 1 AND RAND_INTEGER() = 1 is false with a higher
> probability than RAND_INTEGER() = 1
> Here a test case for RexProgramTest:
> {code}
> @Test void testSimplifyRandomAnd() {
> checkSimplifyUnchanged(
> and(
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1)),
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1))
> ));
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4256) RexSimplify should not simplify P AND P to P, if it contains a call to RAND or RAND_INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17195960#comment-17195960
]
Thomas Rebele commented on CALCITE-4256:
The function RexUtil#flattenAnd removes the AND and the second comparison. It
adds all RexNodes of the AND into a set. However, as the two RexNodes for the
RAND_INTEGER call are considered equivalent, one of them gets removed. So it
seems to me that RexUtil#flattenAnd needs to be adapted, too. Is there already
a method that checks if two RexNode expressions are equivalent, taking
non-deterministic function calls into account?
> RexSimplify should not simplify P AND P to P, if it contains a call to RAND
> or RAND_INTEGER
> ---
>
> Key: CALCITE-4256
> URL: https://issues.apache.org/jira/browse/CALCITE-4256
> Project: Calcite
> Issue Type: Bug
>Reporter: Thomas Rebele
>Priority: Major
>
> Example: RAND_INTEGER() = 1 AND RAND_INTEGER() = 1 is false with a higher
> probability than RAND_INTEGER() = 1
> Here a test case for RexProgramTest:
> {code}
> @Test void testSimplifyRandomAnd() {
> checkSimplifyUnchanged(
> and(
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1)),
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1))
> ));
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4256) RexSimplify should not simplify P AND P to P, if it contains a call to RAND or RAND_INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17195833#comment-17195833
]
Chunwei Lei commented on CALCITE-4256:
--
Agree with [~danny0405]. In our system, we treat SqlRandFunction as a
non-deterministic function.
> RexSimplify should not simplify P AND P to P, if it contains a call to RAND
> or RAND_INTEGER
> ---
>
> Key: CALCITE-4256
> URL: https://issues.apache.org/jira/browse/CALCITE-4256
> Project: Calcite
> Issue Type: Bug
>Reporter: Thomas Rebele
>Priority: Major
>
> Example: RAND_INTEGER() = 1 AND RAND_INTEGER() = 1 is false with a higher
> probability than RAND_INTEGER() = 1
> Here a test case for RexProgramTest:
> {code}
> @Test void testSimplifyRandomAnd() {
> checkSimplifyUnchanged(
> and(
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1)),
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1))
> ));
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4256) RexSimplify should not simplify P AND P to P, if it contains a call to RAND or RAND_INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17195816#comment-17195816
]
Danny Chen commented on CALCITE-4256:
-
It might be wrong that {{SqlRandFunction.isDeterministic}} returns true.
> RexSimplify should not simplify P AND P to P, if it contains a call to RAND
> or RAND_INTEGER
> ---
>
> Key: CALCITE-4256
> URL: https://issues.apache.org/jira/browse/CALCITE-4256
> Project: Calcite
> Issue Type: Bug
>Reporter: Thomas Rebele
>Priority: Major
>
> Example: RAND_INTEGER() = 1 AND RAND_INTEGER() = 1 is false with a higher
> probability than RAND_INTEGER() = 1
> Here a test case for RexProgramTest:
> {code}
> @Test void testSimplifyRandomAnd() {
> checkSimplifyUnchanged(
> and(
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1)),
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1))
> ));
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4256) RexSimplify should not simplify P AND P to P, if it contains a call to RAND or RAND_INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17195664#comment-17195664
]
Julian Hyde commented on CALCITE-4256:
--
Let's frame this in terms of whether the operator is deterministic. See
{{SqlOperator.isDeterministic()}}. The fix certainly should not reference
particular operators.
Related: {{SqlRandFunction.isDynamicFunction()}} is overridden to return true,
and I'm not sure whether that would be necessary if it were flagged
non-deterministic.
> RexSimplify should not simplify P AND P to P, if it contains a call to RAND
> or RAND_INTEGER
> ---
>
> Key: CALCITE-4256
> URL: https://issues.apache.org/jira/browse/CALCITE-4256
> Project: Calcite
> Issue Type: Bug
>Reporter: Thomas Rebele
>Priority: Major
>
> Example: RAND_INTEGER() = 1 AND RAND_INTEGER() = 1 is false with a higher
> probability than RAND_INTEGER() = 1
> Here a test case for RexProgramTest:
> {code}
> @Test void testSimplifyRandomAnd() {
> checkSimplifyUnchanged(
> and(
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1)),
> eq(rexBuilder.makeCall(SqlStdOperatorTable.RAND_INTEGER),
> literal(1))
> ));
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
