[
https://issues.apache.org/jira/browse/CALCITE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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)
