Share MySQL value conversion between INSERT and table rebuilds #572

Problem

MySQL uses one set of rules to store a value in a column, whether it comes from INSERT, UPDATE, or the row copy of ALTER TABLE. The driver has a partial copy of these rules for INSERT and UPDATE in cast_value_for_saving(). Table rebuilds only cast values in text and binary columns (#565). This leaves three kinds of gaps.

1. Legacy values in numeric columns break table rebuilds

The legacy driver (2.2.x) saved strings to integer and real columns without converting them. For example, it stored '' or 'abc' in an INT column as SQLite TEXT. In non-strict mode, which WordPress uses, MySQL would have stored 0.

These tables aren't STRICT, and copying such values into a rebuilt STRICT table fails:

-- Created and saved by the legacy driver 2.2.23:
CREATE TABLE t (id INT PRIMARY KEY, n INT);
INSERT INTO t VALUES (1, '');

-- With the current driver:
ALTER TABLE t ADD COLUMN note TEXT;
-- SQLSTATE[23000]: Integrity constraint violation: 19 cannot store TEXT value in INTEGER column _wp_sqlite_tmp_t_<suffix>.n

OPTIMIZE TABLE and REPAIR TABLE report the same error. The legacy driver also stored decimal strings, such as '1.5', in integer columns as REAL, which fail too.

No plugin is known to trigger this yet. The tables that the temporal-default migration (#542) rebuilds, such as those of H5P, convert their integers before saving them.

2. ALTER TABLE type changes ignore the SQL mode

When a column type changes, MySQL converts each value like an INSERT under the session's SQL mode. The driver copies the values as they are, in both modes, and SQLite rejects what doesn't fit:

The driver errors are SQLite's cannot store ... value and NOT NULL constraint failed.

3. INSERT and UPDATE conversions differ from MySQL

cast_value_for_saving() is close to MySQL, but not equal:

Dates are converted with SQLite's DATETIME(), which drops fractional seconds (#527). The TODOs in the function also list binary values in numeric columns and column length checks.

Direction

Use one conversion for INSERT, UPDATE, and table rebuilds:

1. Fix the shared conversion first. A rebuild must not change valid values, so dates need DATETIME(n) / TIMESTAMP(n) columns drop fractional seconds #527. For numbers, the conversion can use CAST(value AS NUMERIC), round half away from zero, and saturate out-of-range integers. ROUND() loses precision for numbers of 2^52 or more in older SQLite versions, such as 3.27, so it should skip them.
2. Use it in table rebuilds:
   • Changed columns (type, nullability, or length): convert under the session's SQL mode, like an INSERT of the old value.
   • Legacy values that don't fit their column: always convert them as in non-strict mode. MySQL can't hold such values, and the temporal-default migration runs in strict mode, so it must not fail.
   • Unchanged columns of STRICT tables: copy them as they are. Their values already fit, and casting every value made the row copy about 50% slower in a quick benchmark.

MySQL also re-checks unchanged columns when an ALTER uses the COPY algorithm. For example, in strict mode with NO_ZERO_DATE, a zero date in an unchanged DATETIME column makes MODIFY n BIGINT fail, but not ADD COLUMN or OPTIMIZE TABLE. Which algorithm MySQL uses depends on its version, so emulating this isn't worth it.

Related

• ALTER TABLE fails when legacy BLOB columns contain TEXT values #546 and Fix rebuilds of legacy text and binary columns #565: rebuild casts for text and binary columns.
• Implement INSERT and UPDATE value type casting #276: added cast_value_for_saving() for INSERT and UPDATE.
• DATETIME(n) / TIMESTAMP(n) columns drop fractional seconds #527: DATETIME(n) and TIMESTAMP(n) columns drop fractional seconds.
• YEAR values '0' and '00' are stored as 0000 instead of 2000 #545: YEAR string zeros in cast_value_for_saving().
• Validate and normalize literal column defaults like MySQL #551: converting literal column defaults to the column type uses the same rules.
• Refactor data type maps and fine tune column metadata #259: consolidating the data type maps.