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

7 files changed, 51 insertions, 6 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;
diff --git a/sql/field.cc b/sql/field.cc
index 842f92e858f..fc5ca7675ac 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -8593,7 +8593,11 @@ void Field_blob::sql_type(String &res) const
   }
   res.set_ascii(str,length);
   if (charset() == &my_charset_bin)
+  {
     res.append(STRING_WITH_LEN("blob"));
+    if (packlength == 2 && (get_thd()->variables.sql_mode & MODE_ORACLE))
+      res.append(STRING_WITH_LEN("(65535)"));
+  }
   else
   {
     res.append(STRING_WITH_LEN("text"));
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index f5aecf37ecc..d5e579e9df5 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -7086,7 +7086,12 @@ field_type_lob:
             Lex->charset=&my_charset_bin;
             $$.set(&type_handler_blob, $2);
           }
-        | BLOB_ORACLE_SYM opt_field_length opt_compressed
+        | BLOB_ORACLE_SYM field_length opt_compressed
+          {
+            Lex->charset=&my_charset_bin;
+            $$.set(&type_handler_blob, $2);
+          }
+        | BLOB_ORACLE_SYM opt_compressed
           {
             Lex->charset=&my_charset_bin;
             $$.set(&type_handler_long_blob);
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index ef7f7d92c0a..45f770b0c88 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -6984,7 +6984,12 @@ field_type_lob:
             Lex->charset=&my_charset_bin;
             $$.set(&type_handler_blob, $2);
           }
-        | BLOB_ORACLE_SYM opt_field_length opt_compressed
+        | BLOB_ORACLE_SYM field_length opt_compressed
+          {
+            Lex->charset=&my_charset_bin;
+            $$.set(&type_handler_blob, $2);
+          }
+        | BLOB_ORACLE_SYM opt_compressed
           {
             Lex->charset=&my_charset_bin;
             $$.set(&type_handler_long_blob);