MDEV-17363 - Compressed columns cannot be restored from dump

In collaboration with Sergey Vojtovich <svoj@mariadb.org>

The COMPRESSED clause is now a part of the data type and goes immediately
after the data type and length, but before the CHARACTER SET clause,
and before column attributes such as DEFAULT, COLLATE, ON UPDATE,
SYSTEM VERSIONING, engine specific column attributes.

In the old reduction, the COMPRESSED clause was a column attribute.

New syntax:
  <varchar or text data type> <length> <compression> <character set> <column attributes>
  <varbinary or blob data type> <length> <compression> <column attributes>

New syntax examples:
  VARCHAR(1000) COMPRESSED CHARACTER SET latin1 DEFAULT ''
  BLOB COMPRESSED DEFAULT ''

Deprecate syntax examples:
  VARCHAR(1000) CHARACTER SET latin1 COMPRESSED DEFAULT ''
  TEXT          CHARACTER SET latin1 DEFAULT '' COMPRESSED
  VARBINARY(1000) DEFAULT '' COMPRESSED

As a side effect:
- COMPRESSED is not valid as an SP label name in SQL/PSM routines any more
  (but it's still valid as an SP label name in sql_mode=ORACLE)

- COMPRESSED is now allowed in combination with GENERATED ALWAYS AS:

  TEXT COMPRESSED GENERATED ALWAYS AS REPEAT('a',1000)

2 files changed, 160 insertions, 0 deletions

diff --git a/mysql-test/include/column_compression_syntax_varbinary.inc b/mysql-test/include/column_compression_syntax_varbinary.inc
new file mode 100644
index 00000000000..b609969fc14
--- /dev/null
+++ b/mysql-test/include/column_compression_syntax_varbinary.inc
@@ -0,0 +1,61 @@
+--echo #
+--echo # The following statements run without warnings.
+--echo #
+
+--eval CREATE TABLE t1 (a $type COMPRESSED)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # The following statements run without warnings.
+--echo # They have extra column attributes (or GENERATED) after COMPRESSED.
+--echo #
+
+--eval CREATE TABLE t1 (a $type COMPRESSED DEFAULT '')
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED NULL)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--eval CREATE TABLE t1 (a $type COMPRESSED GENERATED ALWAYS AS (REPEAT('a',10)))
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # The following statements return deprecated syntax warnings
+--echo #
+
+--eval CREATE TABLE t1 (a $type DEFAULT '' COMPRESSED)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type NULL COMPRESSED)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # The following statements fail by the grammar,
+--echo # because COMPRESSED immediately follows  'field_type'.
+--echo #
+
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED COMPRESSED)
+
+--echo #
+--echo # The following statements are not prohibited by the *.yy grammar,
+--echo # because the sequence `field_type attribute COMPRESSED` is allowed
+--echo # (notice there is at least one attribute after `field_type`).
+--echo # The first COMPRESSED is parsed inside `field_type`.
+--echo # The second COMPRESSED passes through the parser but then is caught
+--echo # inside Column_definition::set_compressed_deprecated_with_type_check()
+--echo # and a syntax error is raised.
+--echo #
+
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED DEFAULT '' COMPRESSED)
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED NULL COMPRESSED)
+--error ER_PARSE_ERROR
diff --git a/mysql-test/include/column_compression_syntax_varchar.inc b/mysql-test/include/column_compression_syntax_varchar.inc
new file mode 100644
index 00000000000..6b96440c511
--- /dev/null
+++ b/mysql-test/include/column_compression_syntax_varchar.inc
@@ -0,0 +1,99 @@
+--echo #
+--echo # The following statements run without warnings.
+--echo # The `compressed opt_binary` grammar sequence is covered.
+--echo #
+
+--eval CREATE TABLE t1 (a $type COMPRESSED)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED BINARY)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED BINARY ASCII)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED BYTE)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED ASCII)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED UNICODE)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED CHARACTER SET utf8)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # The following statements run without warnings.
+--echo # They have extra column attributes (or GENERATED) after COMPRESSED.
+--echo #
+
+--eval CREATE TABLE t1 (a $type COMPRESSED BYTE DEFAULT '')
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED BINARY DEFAULT '')
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED ASCII DEFAULT '')
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type COMPRESSED CHARACTER SET utf8 DEFAULT '')
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--eval CREATE TABLE t1 (a $type COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100)))
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # The following statements return deprecated syntax warnings
+--echo #
+
+--eval CREATE TABLE t1 (a $type BINARY COMPRESSED)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type ASCII COMPRESSED)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--eval CREATE TABLE t1 (a $type BYTE COMPRESSED)
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # The following statements fail by the grammar,
+--echo # because COMPRESSED immediately follows  'field_type'.
+--echo #
+
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED BYTE COMPRESSED)
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED BINARY COMPRESSED)
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED ASCII COMPRESSED)
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED CHARACTER SET utf8 COMPRESSED)
+
+
+--echo #
+--echo # The following statements are not prohibited by the *.yy grammar,
+--echo # because the sequence `field_type attribute COMPRESSED` is allowed
+--echo # (notice there is at least one attribute after `field_type`).
+--echo # The first COMPRESSED is parsed inside `field_type`.
+--echo # The second COMPRESSED passes through the parser but then is caught
+--echo # inside Column_definition::set_compressed_deprecated_with_type_check()
+--echo # and a syntax error is raised.
+--echo #
+
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED BYTE DEFAULT '' COMPRESSED)
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED BINARY DEFAULT '' COMPRESSED)
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED ASCII DEFAULT '' COMPRESSED)
+--error ER_PARSE_ERROR
+--eval CREATE TABLE t1 (a $type COMPRESSED CHARACTER SET utf8 DEFAULT '' COMPRESSED)