Re: Virtual generated columns

On Mon, Feb 10, 2025 at 11:54 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> On Sun, Feb 9, 2025 at 7:02 PM Zhang Mingli <zmlpostgres(at)gmail(dot)com> wrote:
> > On Feb 9, 2025 at 16:00 +0800, Alexander Lakhin <exclusion(at)gmail(dot)com>, wrote:
> > Please look at a planner error with a virtual generated column triggered
> > by the following script:
> > CREATE TABLE t(a int, b int GENERATED ALWAYS AS (a * 1));
> >
> > SELECT SUM(CASE WHEN t.b = 1 THEN 1 ELSE 1 END) OVER (PARTITION BY t.a)
> > FROM t AS t1 LEFT JOIN T ON true;
> >
> > ERROR: XX000: wrong varnullingrels (b) (expected (b 3)) for Var 2/1
> > LOCATION: search_indexed_tlist_for_var, setrefs.c:2901
>
> > During the parse stage, we set the Var->varnullingrels in the parse_analyze_fixedparams function.
> > Later, when rewriting the parse tree in pg_rewrite_query() to expand virtual columns, we replace the expression column b with a new Var that includes a, since b is defined as a * 1.
> > Unfortunately, we overlooked updating the Var->varnullingrels at this point.
> > As a result, when we enter search_indexed_tlist_for_var, it leads to a failure.
> > While we do have another target entry with the correct varnullingrels, the expression involving the virtual column generates another column reference, which causes the error.
> > Currently, I don't have a solid fix.
> > One potential solution is to correct the Vars at or after the rewrite stage by traversing the parse tree again using markNullableIfNeeded.
> > However, this approach may require exposing the ParseState, which doesn't seem ideal.
> > It appears that the virtual column generation function during the rewrite stage does not account for the Var field settings, leading to the errors we are encountering.
>
> Hmm, would it be possible to propagate any varnullingrels into the
> replacement expression in ReplaceVarsFromTargetList_callback()?
>
in ReplaceVarsFromTargetList_callback,
we have
``if (var->varlevelsup > 0)``
``if (var->varreturningtype != VAR_RETURNING_DEFAULT)``

we can also have.
``if (var->varnullingrels != NULL)``

please check attached.

> BTW, I was curious about what happens if the replacement expression is
> constant, so I tried running the query below.
>
> CREATE TABLE t (a int, b int GENERATED ALWAYS AS (1 + 1));
> INSERT INTO t VALUES (1);
> INSERT INTO t VALUES (2);
>
> # SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
> a | b
> ---+---
> | 2
> | 2
> (2 rows)
>
> Is this the expected behavior? I was expecting that t2.b should be
> all NULLs.
>
SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
should be same as
SELECT t2.a, 2 as b FROM t t1 LEFT JOIN t t2 ON FALSE;
so i think this is expected.