[MDEV-11283] CAST(..AS VARCHAR(N)) Created: 2016-11-15  Updated: 2021-05-11

Status: Open
Project: MariaDB Server
Component/s: Parser
Fix Version/s: None

Type: Task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 2
Labels: Compatibility, Oracle

Issue Links:
PartOf
is part of MDEV-10137 Providing compatibility to other data... Open
Relates
relates to MDEV-11275 sql_mode=ORACLE: CAST(..AS VARCHAR(N)) Closed

 Description   

MDEV-11275 adds support for CAST(expr AS VARCHAR(N)) for sql_mode=ORACLE.

CAST(expr AS VARCHAR(N)) should also be supported not only in sql_mode=ORACLE, but in other modes as well, it's supported by many popular databases:

CAST(expr AS VARCHAR(N)) is supported by:

  • Oracle
  • SQL Server
  • PostgreSQL
  • SQLite
  • IBM DB2
  • Sybase/SAP ASE

CAST(expr AS VARCHAR) (without length) is supported by:

  • SQL Server
  • PostgreSQL
  • SQLite
  • Sybase/SAP ASE

 Comments   
Comment by Alexander Barkov [ 2016-11-15 ]

Monty wrote:

Yes, we should [support CAST AS VARCHAR for other sql_modes], but I am not sure how this would differ from CAST(... CHAR), except for a length check.

In particular, what should the result be for:

CREATE TABLE t1 SELECT CAST("A" AS VARCHAR(10));

Logically I assume this should create a VARCHAR(10) column, but can we really detect that in our current code without making this a special case?
(Maybe this will work automatically, but I can't test this just now)

The problem with special cases is how to handle expressions:
CONCAT(CAST("A" AS VARCHAR(10)), "")

Comment by Ian Gilfillan [ 2020-11-19 ]

Running 

SELECT CAST(123 AS VARCHAR(10)) FROM DUAL;

seems to work in non-Oracle mode from MariaDB 10.3.11.

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