vtgate: the session's SET_VAR hint reaches only the statement root, not the backend queries the planner derives from it

[arthurschreiber]

Not looking for external contributions on this one. We're already working in this area and intend to fix it ourselves. Please don't open a PR against this issue — it's filed to track the problem and record the analysis, not as an invitation. Comments and additional repro cases are very welcome.

Overview of the Issue

vtgate applies the session's sql_mode (and the other SET_VAR-capable system variables) to backend queries with a SET_VAR optimizer hint that the normalizer injects into the root of the statement. Queries the planner derives from that statement do not carry it, so they run under whatever mode the backend is configured with:

• A UNION planned as a Concatenate of separate routes: only the route built from the original first SELECT carries the hint. Every later route is a new statement without it. The first route also carries the hint twice, once at its top level and once inside the derived table it wraps the original select in, and MySQL only honors the top-level one.
• The owned-vindex pre-reads of an UPDATE or DELETE (select ... from t where ... for update) and other statements the engine builds at plan time from its own SQL templates.
• The shard-side evaluation query of a SET whose expression vtgate cannot compute itself (Tracking: gate-side evaluation should honor the statement's effective sql_mode (session + SET_VAR hint) #20892 tracks that one).

When the session's mode differs from the backend's, those queries evaluate expressions and enforce strictness under the wrong mode, before or alongside the hinted statement of the same request.

Reproduction Steps

set sql_mode = 'ANSI_QUOTES,STRICT_TRANS_TABLES';
select id from user where id = 1 union select id from user where id = 3;

With the executor test vschema (user sharded by id), shard -20 receives

select /*+ SET_VAR(sql_mode = 'ANSI_QUOTES,STRICT_TRANS_TABLES') */ dt.c0 as id, weight_string(dt.c0) from (select /*+ SET_VAR(sql_mode = 'ANSI_QUOTES,STRICT_TRANS_TABLES') */ distinct id from `user` where id = 1) as dt(c0)

and shard 40-60 receives

select dt.c0 as id, weight_string(dt.c0) from (select distinct id from `user` where id = 3) as dt(c0)

Raised in review of #20880 (#20880 (comment) and #20880 (comment)).

Fix direction

Attach the hint where a backend statement's text is produced, from the session's value at that moment, rather than in the normalizer on the client's statement: either when the planner serializes a route or a generated query, or at the scatter send. Every emitted statement then carries the hint exactly once, at its top level. Doing it at send time is also what would let plans stop embedding session values (today the plan cache is keyed on the rendered SET_VAR comment), which is a larger change and can come separately.

Binary Version

main (after #20880)

Operating System and Environment details

n/a

Log Fragments

n/a