MDEV-20263 sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB

4 files changed, 35 insertions, 4 deletions

diff --git a/mysql-test/suite/compat/oracle/r/column_compression.result b/mysql-test/suite/compat/oracle/r/column_compression.result
index 1b828ca17b0..7a12c7b8233 100644
--- a/mysql-test/suite/compat/oracle/r/column_compression.result
+++ b/mysql-test/suite/compat/oracle/r/column_compression.result
@@ -515,7 +515,7 @@ CREATE TABLE t1 (a TEXT COMPRESSED BYTE);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" blob /*!100301 COMPRESSED*/ DEFAULT NULL
+  "a" blob(65535) /*!100301 COMPRESSED*/ DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a TEXT COMPRESSED ASCII);
@@ -547,7 +547,7 @@ CREATE TABLE t1 (a TEXT COMPRESSED BYTE DEFAULT '');
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" blob /*!100301 COMPRESSED*/ DEFAULT ''
+  "a" blob(65535) /*!100301 COMPRESSED*/ DEFAULT ''
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a TEXT COMPRESSED BINARY DEFAULT '');
@@ -605,7 +605,7 @@ Warning	1287	'<data type> <character set clause> ... COMPRESSED...' is deprecate
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" blob /*!100301 COMPRESSED*/ DEFAULT NULL
+  "a" blob(65535) /*!100301 COMPRESSED*/ DEFAULT NULL
 )
 DROP TABLE t1;
 #
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 8fa49c4092a..2d4244110f9 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -2072,7 +2072,7 @@ t1	CREATE TABLE "t1" (
   "tables_table_name" varchar(64) CHARACTER SET utf8 DEFAULT NULL,
   "tables_table_rows" bigint(21) unsigned DEFAULT NULL,
   "processlist_info" longtext CHARACTER SET utf8 DEFAULT NULL,
-  "processlist_info_binary" blob DEFAULT NULL
+  "processlist_info_binary" blob(65535) DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/type_blob.result b/mysql-test/suite/compat/oracle/r/type_blob.result
index fe0a78e6ceb..27740947a10 100644
--- a/mysql-test/suite/compat/oracle/r/type_blob.result
+++ b/mysql-test/suite/compat/oracle/r/type_blob.result
@@ -6,3 +6,21 @@ t1	CREATE TABLE "t1" (
   "a" longblob DEFAULT NULL
 )
 DROP TABLE t1;
+#
+# MDEV-20263 sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB
+#
+CREATE TABLE t1 (
+c1 BLOB(100),
+c2 BLOB(65535),
+c3 BLOB(16777215),
+c4 BLOB(16777216)
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "c1" tinyblob DEFAULT NULL,
+  "c2" blob(65535) DEFAULT NULL,
+  "c3" mediumblob DEFAULT NULL,
+  "c4" longblob DEFAULT NULL
+)
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/type_blob.test b/mysql-test/suite/compat/oracle/t/type_blob.test
index 2bfaa77560d..989581ee4f6 100644
--- a/mysql-test/suite/compat/oracle/t/type_blob.test
+++ b/mysql-test/suite/compat/oracle/t/type_blob.test
@@ -2,3 +2,16 @@ SET sql_mode=ORACLE;
 CREATE TABLE t1 (a BLOB);
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-20263 sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB
+--echo #
+
+CREATE TABLE t1 (
+  c1 BLOB(100),
+  c2 BLOB(65535),
+  c3 BLOB(16777215),
+  c4 BLOB(16777216)
+);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;