[MDEV-20263] sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB Created: 2019-08-06  Updated: 2019-08-06  Resolved: 2019-08-06

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: 10.3.18, 10.4.8

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-19632 Replication aborts with ER_SLAVE_CONV... Closed

 Description   

Oracle database has the following syntax for the BLOB data type:

{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]

The default BLOB length is two gigabytes (2,147,483,647).

To satisfy the default length of 2Gb, when running with sql_mode=ORACLE, MariaDB translates:

CREATE TABLE t1 (a BLOB);

to

CREATE TABLE t1 (a LONGBLOB);

But it also translates:

CREATE TABLE t1 (a BLOB(65535));

to

CREATE TABLE t1 (a LONGBLOB);

The latter looks wrong. When explicit length is specified, it should try to preserve the BLOB data type, corresponding to MYSQL_TYPE_BLOB (which uses 2 bytes per length).

This script demonstrates the problem:

SET sql_mode=ORACLE;
 
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
 
SHOW CREATE TABLE t1;


+-------+---------------------------------------------------+
 
| Table | Create Table                                      |
 
+-------+---------------------------------------------------+
 
| t1    | CREATE TABLE "t1" (
 
  "a" longblob DEFAULT NULL
 
) |
 
+-------+---------------------------------------------------+

The expected output in the last script (with sql_mode=ORACLE) should be:

+-------+---------------------------------------------------+
 
| Table | Create Table                                      |
 
+-------+---------------------------------------------------+
 
| t1    | CREATE TABLE "t1" (
 
  "a" blob(65535) DEFAULT NULL
 
) |
 
+-------+---------------------------------------------------+


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