cockroachdb / cockroachdb/cockroach
sql: EXECUTE arguments should be evaluated only once
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
When replacing placeholders of prepared statements with constant values, we [evaluate the placeholder's expression each time the placeholder is present in the optimizer expression](https://github.com/cockroachdb/cockroach/blob/f59f0f408f95b50a9300545e966b3ef9a5db7f72/pkg/sql/opt/norm/factory.go#L355). This makes no semantic difference for leakproof, immutable, or stable placeholder expressions, but it does cause unexpected behavior when the expression is volatile.
For example, in **CRDB** the placeholder `$1` has two different values during `EXECUTE` below:
```sql
PREPARE p AS SELECT $1::float UNION ALL SELECT $1::FLOAT + 1;
-- PREPARE
EXECUTE p(random());
-- float8
-- -----------------------
-- 0.24101213558621915
-- 1.7880754437670263
-- (2 rows)
```
In **Postgres** the `random()` function is evaluated only once:
```sql
PREPARE p AS SELECT $1::float UNION ALL SELECT $1::FLOAT + 1;
-- PREPARE
EXECUTE p(random());
-- float8
-- ---------------------
-- 0.32451202753803443
-- 1.3245120275380344
-- (2 rows)
```
An example of bizarre behavior is the `double` prepared statement below. You'd expect that it always yields an even number, but it does not.
```sql
PREPARE double AS SELECT $1::INT + $1::INT;
-- PREPARE
EXECUTE double(floor(random()*100));
-- ?column?
-- ------------
-- 133
-- (1 row)
```
Jira issue: CRDB-30293
Contributor guide
Assessment
This issue has not been assessed yet.