What problem does this PR solve?

Issue Number: close #58400

Problem Summary:

There are two problems related to genearted column.

For Update:

When building UpdateLists for update statements, generated columns should be updated if their dependent columns have changed. However, we ignore the ON UPDATE NOW flag when extracting dependent columns. This could make certain generated columns not being updated correctly.

For Insert:

In SQL like INSERT INTO xxx VALUES xxx ON DUPLICATE KEY UPDATE xxx, the generated columns to be updated are solely determined by the assignments in UPDATE. For example:

CREATE TABLE tmv (
  J1 json, J2 json GENERATED ALWAYS AS (j1) VIRTUAL,
  UNIQUE KEY i1 ((cast(j1 as signed array))),
  KEY i2 ((cast(j2 as signed array)))
);

INSERT INTO tmv SET j1 = '[1]' ON DUPLICATE KEY UPDATE j1 = '[2]';

The dependencies of each column are listed below. Since j1 is updated, j2 and _V$_i1_0 are also updated. However, due to chain dependencies, _V$_i2_0 should also be updated too.

j2 <-- j1
_V$_i1_0 <-- j1
_V$_i2_0 <-- j2

What changed and how does it work?

In summary, when extracting generated columns, we have to consider both chain dependencies and on-update-now flag too.

Check List

Tests

• Unit test
• Integration test
• Manual test (add detailed scripts or steps below)
• No need to test

  • I checked and no code files have been changed.

Side effects

• Performance regression: Consumes more CPU
• Performance regression: Consumes more Memory
• Breaking backward compatibility

Documentation

• Affects user behaviors
• Contains syntax changes
• Contains variable changes
• Contains experimental features
• Changes MySQL compatibility

Release note

Please refer to Release Notes Language Style Guide to write a quality release note.

None