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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.3.18, 10.4.8
Component/s: Data types
Labels:
None

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);

CREATE TABLE t1 (a LONGBLOB);

But it also translates:

CREATE TABLE t1 (a BLOB(65535));

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

) |

+-------+---------------------------------------------------+

Attachments

Issue Links

blocks

MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2019-08-06 11:41

Alexander Barkov made changes - 2019-08-06 11:42

Field	Original Value	New Value
Description	Oracle database has the following syntax for the BLOB data type: {code:sql} { BLOB \| BINARY LARGE OBJECT } [ ( length [{K \|M \|G }] ) ] {code} A the default BLOB length is two gigabytes (2,147,483,647). To satisfy the default value of 2Gb, MariaDB translates: {code:sql} CREATE TABLE t1 (a BLOB); {code} to {code:sql} CREATE TABLE t1 (a LONGBLOB); {code} But it also translates: {code:sql} CREATE TABLE t1 (a BLOB(65535)); {code} to {code:sql} CREATE TABLE t1 (a LONGBLOB); {code} This looks wrong. It should preserve the BLOB data type, corresponding to MYSQL_TYPE_BLOB (which uses 2 bytes per length). This script demonstrates the problem: {code:sql} SET sql_mode=ORACLE; CREATE OR REPLACE TABLE t1 (a BLOB(65535)); SHOW CREATE TABLE t1; {code} {noformat} +-------+---------------------------------------------------+ \| Table \| Create Table \| +-------+---------------------------------------------------+ \| t1 \| CREATE TABLE "t1" ( "a" longblob DEFAULT NULL ) \| +-------+---------------------------------------------------+ {noformat}	Oracle database has the following syntax for the BLOB data type: {code:sql} { BLOB \| BINARY LARGE OBJECT } [ ( length [{K \|M \|G }] ) ] {code} A the default BLOB length is two gigabytes (2,147,483,647). To satisfy the default value of 2Gb, MariaDB translates: {code:sql} CREATE TABLE t1 (a BLOB); {code} to {code:sql} CREATE TABLE t1 (a LONGBLOB); {code} But it also translates: {code:sql} CREATE TABLE t1 (a BLOB(65535)); {code} to {code:sql} CREATE TABLE t1 (a LONGBLOB); {code} This 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: {code:sql} SET sql_mode=ORACLE; CREATE OR REPLACE TABLE t1 (a BLOB(65535)); SHOW CREATE TABLE t1; {code} {noformat} +-------+---------------------------------------------------+ \| Table \| Create Table \| +-------+---------------------------------------------------+ \| t1 \| CREATE TABLE "t1" ( "a" longblob DEFAULT NULL ) \| +-------+---------------------------------------------------+ {noformat}

Alexander Barkov made changes - 2019-08-06 11:42

Link

This issue blocks ~~MDEV-19632~~ [ ~~MDEV-19632~~ ]

Alexander Barkov made changes - 2019-08-06 11:43

Description

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
A the default BLOB length is two gigabytes (2,147,483,647).

To satisfy the default value of 2Gb, MariaDB translates:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
This 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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
A the default BLOB length is two gigabytes (2,147,483,647).

To satisfy the default value of 2Gb, MariaDB translates:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

Alexander Barkov made changes - 2019-08-06 11:44

Description

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
A the default BLOB length is two gigabytes (2,147,483,647).

To satisfy the default value of 2Gb, MariaDB translates:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
The default BLOB length is two gigabytes (2,147,483,647).

To satisfy the default value of 2Gb, MariaDB translates:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

Alexander Barkov made changes - 2019-08-06 11:44

Description

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
The default BLOB length is two gigabytes (2,147,483,647).

To satisfy the default length of 2Gb, MariaDB translates:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

Alexander Barkov made changes - 2019-08-06 11:45

Description

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
The default BLOB length is two gigabytes (2,147,483,647).

To satisfy the default length of 2Gb, MariaDB translates:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
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:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

Alexander Barkov made changes - 2019-08-06 12:47

Description

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
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:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

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

{code:sql}
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
{code}
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:
{code:sql}
CREATE TABLE t1 (a BLOB);
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}

But it also translates:
{code:sql}
CREATE TABLE t1 (a BLOB(65535));
{code}
to
{code:sql}
CREATE TABLE t1 (a LONGBLOB);
{code}
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:

{code:sql}
SET sql_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (a BLOB(65535));
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" longblob DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

The expected output in the last script (with sql_mode=ORACLE) should be:
{noformat}
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"a" blob(65535) DEFAULT NULL
) |
+-------+---------------------------------------------------+
{noformat}

Alexander Barkov made changes - 2019-08-06 14:02

Fix Version/s		10.3.18 [ 23719 ]
Fix Version/s		10.4.8 [ 23721 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:50

Workflow

MariaDB v3 [ 98740 ]

MariaDB v4 [ 156576 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-08-06 11:41

Updated:: 2019-08-06 14:02

Resolved:: 2019-08-06 14:02

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration