pingcap / pingcap/tidb

Predicate with a random value(floor(rand()*100000)) need to push down

Open
#35,207 0 comments 0 reactions 0 assignees View on GitHub
severity/minor sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
create table t(id int primary key,name varchar(10));
insert into sbtest1 values(1,'name1');
insert into sbtest1 values(2,'name2');
insert into sbtest1 values(3,'name3');
insert into sbtest1 values(4,'name4');
insert into sbtest1 values(5,'name5');
insert into sbtest1 values(6,'name6');
insert into sbtest1 values(7,'name7');
insert into sbtest1 values(8,'name7');

### 2. What did you expect to see? (Required)
MySQL [test]> explain select * from test.sbtest1 where id=cast(1.11 as UNSIGNED);
+-------------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| Point_Get_5 | 1.00 | root | table:sbtest1 | handle:1 |
+-------------+---------+------+---------------+---------------+
### 3. What did you see instead (Required)
MySQL [test]> explain select * from test.sbtest1 where id=cast(rand()*10 as UNSIGNED);
+-------------------------+-----------+-----------+---------------+------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+-----------+-----------+---------------+------------------------------------------------------------------------+
| Selection_5 | 80000.00 | root | | eq(test.sbtest1.id, cast(mul(rand(), 10), bigint(22) UNSIGNED BINARY)) |
| └─TableReader_7 | 100000.00 | root | | data:TableFullScan_6 |
| └─TableFullScan_6 | 100000.00 | cop[tikv] | table:sbtest1 | keep order:false |
+-------------------------+-----------+-----------+---------------+------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

MySQL [test]> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Scalar function 'rand'(signature: Rand, return type: double) is not supported to push down to storage layer now. |
+---------+------+------------------------------------------------------------------------------------------------------------------+

### 4. What is your TiDB version? (Required)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.