Bug #120306 ALGORITHM=INPLACE allows creating a VIRTUAL GENERATED COLUMN with invalid expressions for existing data, causing subsequ

• Submitted: 21 Apr 2:23
• Modified: 21 Apr 5:21
• Reporter: fei yang
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.43
• OS: Any
• CPU Architecture: Any

[21 Apr 2:23] fei yang

Description:
There is a consistency discrepancy between ALGORITHM=INPLACE and ALGORITHM=COPY when adding a VIRTUAL GENERATED COLUMN that involves mathematical functions (like POW(col, -1)) where existing data would cause an out-of-range error (e.g., division by zero).
When using ALGORITHM=COPY, MySQL validates existing rows during the copy process and correctly throws ERROR 1690 (22003) if an invalid calculation occurs.
When using ALGORITHM=INPLACE, MySQL only updates the metadata and returns "Query OK". However, any subsequent SELECT * on that table will fail with ERROR 1690, effectively making the table partially unreadable.
While VIRTUAL columns are calculated on-the-fly, INPLACE should arguably perform a data validation pass if the user wants to ensure data integrity, or at least the behavior should be consistent with COPY.

How to repeat:
-- 1. Create table and insert a row with 0
CREATE TABLE `test_bug` (
  `id` int PRIMARY KEY,
  `val` tinyint
);
INSERT INTO `test_bug` VALUES (1, 0);

-- 2. This succeeds but "poisons" the table
ALTER TABLE `test_bug` ADD COLUMN gen1 DOUBLE 
GENERATED ALWAYS AS (pow(`val`, -1)) VIRTUAL, ALGORITHM=INPLACE;

-- 3. This fails, making the table unusable for standard SELECT
SELECT * FROM test_bug;
-- Error: ERROR 1690 (22003): DOUBLE value is out of range in 'pow(`val`,-(1))'

-- 4. Contrast with COPY (which correctly catches the error upfront)
ALTER TABLE `test_bug` DROP COLUMN gen1;
ALTER TABLE `test_bug` ADD COLUMN gen1 DOUBLE 
GENERATED ALWAYS AS (pow(`val`, -1)) VIRTUAL, ALGORITHM=COPY;
-- Error: ERROR 1690 (22003): DOUBLE value is out of range...

Expected Results：
1. Perform a validation scan (if possible) when adding a generated column to ensure existing data doesn't break the expression.
2. Or, at the very least, provide a warning that existing data has not been validated for the new virtual column expression.

Actual Results:
INPLACE successfully adds the column, but the table becomes un-selectable due to the un-validated virtual expression.