[MDEV-20472] Document MDEV-18156 - sql_mode dependency in vcol expressions Created: 2019-09-03  Updated: 2022-03-12  Resolved: 2019-09-30

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Fix Version/s: 10.2.27, 10.3.18, 10.4.8, 10.5.0

Type: Task Priority: Blocker
Reporter: Alexander Barkov Assignee: Kenneth Dyer (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-18156 Assertion `0' failed or `btr_validate... Closed

 Description   

I have pushed two patches for MDEV-18156:

Part 1:
https://github.com/MariaDB/server/commit/dc719597ee0b11da722e9813639e8b48018a8c10

Part 2:
https://github.com/MariaDB/server/commit/ef00ac4c86daf3294c46a45358da636763fb0049

Please add documentation for MDEV-18156.

Short description:

The fix disallows generated column expressions that depend on:

  • sql_mode flag PAD_CHAR_TO_FULL_LENGTH
  • sql_mode flag NO_UNSIGNED_SUBTRACTION

if a generated columns is:

  • a virtual column used in an index
  • a stored column (indexes do not matter)

Note, virtual columns that depend on the mentioned flags,
but are not used in any indexes, are still allowed.

These table modification statements:

  • CREATE TABLE
  • ALTER TABLE
  • CREATE INDEX

introducing a new "bad" generated column are not allowed
any more and return an error.

The server can still open old tables (created by older server versions)
with "bad" generated columns. A warning about bad expression is issued.
Such columns can be used in most SQL statements without any limitations,
but in case of the mentioned modification statements, the result table
must not have "bad" generated columns.

This patch fixes the problem for two sql_mode flags only.
Other affected sql_mode flags will be addressed separately later.

Thanks.



 Comments   
Comment by Alexander Barkov [ 2019-09-07 ]

Update:
To avoid compatibility issues, it was decided to disallow "bad" generated columns starting from 10.5 only.
So the above behaviour applies to 10.5.

In earlier versions only a warning is issued, even in the table modification statements.

The third patch is here:
https://github.com/MariaDB/server/commit/5a9e2b77d40427d99a29a96551a0f31fdf39edac

Generated at Thu Feb 08 08:59:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.