cockroachdb / cockroachdb/cockroach

sql: EXECUTE arguments should be evaluated only once

Open
#108,027 0 comments 0 reactions 0 assignees View on GitHub
A-prepared-stmts C-bug S-3-erroneous-edge-case T-sql-queries
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

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.