expr: give a clear definition of the "volatility" for all expressions

[BugenZhao]

Recently there're more and more features that require expressions to be categorized, here're some examples:

• Whether to materialize the Project output for indeterministic expressions on retractable stream. (feat(frontend): add StreamProjectMaterialize PlanNode #8843)
• For functional indexes (feat(frontend): support functional indexes creation #8976) and non-NULL default values (Non-Null Default Value for DML #8999), only "pure" expressions are allowed.
• Which functions are allowed to be constant-folded.

I think it's time for us to give a clear definition of the "volatility" for all expressions, just like the definition in Postgres: https://www.postgresql.org/docs/current/xfunc-volatility.html.

Every function has a volatility classification, with the possibilities being VOLATILE, STABLE, or IMMUTABLE. VOLATILE is the default if the CREATE FUNCTION command does not specify a category. The volatility category is a promise to the optimizer about the behavior of the function:

A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. A query using a volatile function will re-evaluate the function at every row where its value is needed.

A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.)

An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ... WHERE x = 4, because the function underlying the integer addition operator is marked IMMUTABLE.

Based on these definitions, we have:

• Currently, our expression framework is only able to execute IMMUTABLE expressions (except for UDF).
• For STABLE and VOLATILE functions, we need to persist the result in case of deletes.
• Only IMMUTABLE functions are allowed in functional indexes and non-NULL default values.

cc @xiangjinwu @wangrunji0408 @chenzl25

[st1page]

STABLE and VOLATILE functions can also be supported to

• batch query
• append-only stream
• functional indexes on append-only table

[CAJan93]

Do/can we check if a user provided the correct category in CREATE FUNCTION? Can a user mark a function that manipulates data as IMMUTABLE?

[xxchan]

can we check if a user provided the correct category ...

No we can't 😄

[xxchan]

Which functions are allowed to be constant-folded.

How is volatility related with constant folding? I think it's done in frontend at runtime, so it should be fine. Oh, unless we have plan cache?

Ignore me, I'm stupid. I get it:

select RANDOM() from (values (1),(2)) t;
+---------------------+
| random              |
|---------------------|
| 0.24088808981450427 |
| 0.42239150263949554 |
+---------------------+

select NOW() from (values (1),(2)) t;
+-------------------------------+
| now                           |
|-------------------------------|
| 2023-04-12 23:33:34.738809+02 |
| 2023-04-12 23:33:34.738809+02 |
+-------------------------------+

(So NOW is STABLE and RANDOM is VOLATILE

I'm asking this while wondering how many optimizations should consider volatility, after seeing #9133.

BTW, CSE should be one. 🥸 https://twitter.com/zty0826/status/1609584138490904577

[BugenZhao]

#9133 has roughly divided all expressions into impure and pure. Based on this property, several optimizations have become more serious these days, and it seems good enough. Let's distinguish STABLE and VOLATILE if we find it needed in the future.

[xxchan]

I guess currently impure = VOLATILE, so it's conservative and maybe good enough.