Hi, Alena!
On Sat, Mar 29, 2025 at 9:03 PM Alena Rybakina
<a.rybak...@postgrespro.ru> wrote:
> On 29.03.2025 14:03, Alexander Korotkov wrote:
>> One thing I have to fix: we must do
>> IncrementVarSublevelsUp() unconditionally for all expressions as Vars
>> could be deeper inside.
>
> Yes, I'm looking at it too, I've just understood that it was needed for
> subqueries - they can contain var elements which needs decrease the sublevel
> parameter.
>
> for example for the query:
>
> EXPLAIN (COSTS OFF)
> SELECT ten FROM onek t
> WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
> WHERE c.unique2 = t.unique1))::integer));
>
> We are interested in this element: ((2 IN (SELECT unique2 FROM onek c WHERE
> c.unique2 = t.unique1))
>
> It is funcexpr object with RabgeTblEntry variable. I highlighted
>
> WARNING: 1{FUNCEXPR :funcid 2558 :funcresulttype 23 :funcretset false
> :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args
> ({SUBLINK :subLinkType 2 :subLinkId 0 :testexpr {OPEXPR :opno 96 :opfuncid 65
> :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({CONST
> :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true
> :constisnull false :location -1 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]} {PARAM
> :paramkind 2 :paramid 1 :paramtype 23 :paramtypmod -1 :paramcollid 0
> :location -1}) :location -1} :operName ("=") :subselect {QUERY :commandType 1
> :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs
> false :hasWindowFuncs false :hasTargetSRFs false :hasSubLinks false
> :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate
> false :hasRowSecurity false :hasGroupRTE false :isReturn false :cteList <>
> :rtable ({RANGETBLENTRY :alias {ALIAS :aliasname c :colnames <>} :eref {ALIAS
> :aliasname c :colnames ("unique1" "unique2" "two" "four" "ten" "twenty"
> "hundred" "thousand" "twothousand" "fivethous" "tenthous" "odd" "even"
> "stringu1" "stringu2" "string4")} :rtekind 0 :relid 32795 :inh true :relkind
> r :rellockmode 1 :perminfoindex 1 :tablesample <> :lateral false :inFromCl
> true :securityQuals <>}) :rteperminfos ({RTEPERMISSIONINFO :relid 32795 :inh
> true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9) :insertedCols (b)
> :updatedCols (b)}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 1})
> :quals {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false
> :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 23
> :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0
> :varreturningtype 0 :varnosyn 1 :varattnosyn 2 :location -1} {VAR :varno 1
> :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b)
> :varlevelsup 2 :varreturningtype 0 :varnosyn 1 :varattnosyn 1 :location -1})
> :location -1}} :mergeActionList <> :mergeTargetRelation 0 :mergeJoinCondition
> <> :targetList ({TARGETENTRY :expr {VAR :varno 1 :varattno 2 :vartype 23
> :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0
> :varreturningtype 0 :varnosyn 1 :varattnosyn 2 :location -1} :resno 1
> :resname unique2 :ressortgroupref 0 :resorigtbl 32795 :resorigcol 2 :resjunk
> false}) :override 0 :onConflict <> :returningOldAlias <> :returningNewAlias
> <> :returningList <> :groupClause <> :groupDistinct false :groupingSets <>
> :havingQual <> :windowClause <> :distinctClause <> :sortClause <>
> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <> :setOperations <>
> :constraintDeps <> :withCheckOptions <> :stmt_location -1 :stmt_len -1}
> :location -1}) :location -1}
>
>
> I highlighted in bold the var we need - since it is in a subquery in the in
> expression will be flattened, all elements contained in it should decrease
> the level number by one, since they will belong to the subtree located above
> it. Because of that condition, this did not happen.
>
> I generally agree with you that it is better to remove that condition. The
> function IncrementVarSublevelsUp essentially goes through the structures
> below and will decrease the level of only the vars for which this needs to be
> done, and the condition with 1 will protect us from touching those vars that
> should not. So the varlevelsup for this var should be 1.
>
> I am currently investigating whether this transformation will be fair for all
> cases; I have not found any problems yet.
Thank you for your feedback. I appreciate you're also looking for the
potential problems. On thing to highlight: doing
IncrementVarSublevelsUp() unconditionally is required not just for
subqueries. Consider the following example.
SELECT * FROM t WHERE val1 IN (VALUES (val2), (val2 +1));
The second value contain Var, which needs IncrementVarSublevelsUp(),
but the top node is OpExpr.
------
Regards,
Alexander Korotkov
Supabase
