MDEV-28077 'Wrong create options' error with 'big_tables' enabled

The cause of the bug is overflow of uint16 KEY_PART_INFO::length and/or
uint16 KEY_PART_INFO::store_length. The solution is to increase the size
of those variables to the 'uint' type (which is 32-bit long)

4 files changed, 75 insertions, 0 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 2fef3361617..3b8af811a4b 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5575,4 +5575,23 @@ SET sql_buffer_result=1;
 SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
 grp	GROUP_CONCAT(c ORDER BY 2)
 DROP TABLE t1;
+#
+# Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+#
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+Warnings:
+Note	1071	Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+3
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+DROP TABLE t1;
 End of 10.0 tests
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 9e35af9cb78..a50df7d6081 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5586,6 +5586,25 @@ SET sql_buffer_result=1;
 SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
 grp	GROUP_CONCAT(c ORDER BY 2)
 DROP TABLE t1;
+#
+# Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+#
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+Warnings:
+Note	1071	Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+3
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+DROP TABLE t1;
 End of 10.0 tests
 set join_cache_level=default;
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 2fef3361617..3b8af811a4b 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5575,4 +5575,23 @@ SET sql_buffer_result=1;
 SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
 grp	GROUP_CONCAT(c ORDER BY 2)
 DROP TABLE t1;
+#
+# Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+#
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+Warnings:
+Note	1071	Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+3
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+DROP TABLE t1;
 End of 10.0 tests
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index ea213be9fea..0878cbf8306 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -4698,4 +4698,22 @@ SET sql_buffer_result=1;
 SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
 DROP TABLE t1;
 
+--echo #
+--echo # Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+--echo #
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+
+# big_tables are deprecated after some version
+--disable_warnings
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+EXECUTE stmt;
+
+--enable_warnings
+DROP TABLE t1;
+
 --echo End of 10.0 tests