CODEWITHSUNDEEP
mysqlsql
Frazz
Frazz

Reputation: 3043

How to SELECT a constant column of a specific data type in MySQL

I have this (simplified) query, that is not working in MySQL (5.7.17):

SELECT a.*, CAST(NULL AS BLOB) AS fake_column FROM table1 a

What I am trying to achieve is to SELECT some real columns from a database and add to them a costant column of a specific type.

This works correctly in SQLite and seems to be the right syntax also in MS-SQL Server, as documented in this question.

But in MySQL it fails (just using the MySQL Workbench) with error code 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...). Actually, all of these fail the same way:

CAST(NULL AS BIGINT) AS fake_column
CAST(0 AS BIGINT) AS fake_column
CAST(999 AS BIGINT) AS fake_column
CAST(NULL AS SMALLINT) AS fake_column
CAST(0 AS SMALLINT) AS fake_column
CAST(999 AS SMALLINT) AS fake_column
CAST(NULL AS BIT) AS fake_column
CAST(0 AS BIT) AS fake_column
CAST(1 AS BIT) AS fake_column

The standard CAST and CONVERT functions are supported by MySQL, but there seems to be some limitation with the types they accept, or in the fact I'm casting a constant. With strings it is better. CAST to CHAR(500), for example, works.

Are there known workarounds?

Update:

I have tried to define this user function:

DELIMITER $$

CREATE FUNCTION null_blob RETURNS blob
BEGIN
    RETURN NULL;
END$$

DELIMITER ;

But I still get exactly the same error code 1064.

Upvotes: 2

Views: 2851

Answers (2)

kmoser
kmoser

Reputation: 9273

There doesn't seem to be a way to cast to BIT, BIGINT, or SMALLINT but you can cast to SIGNED or UNSIGNED (I just tested with MySQL 5.7.7).

When you add those constants to real data from other fields, MySQL should automatically treat those constants as the appropriate data type when doing the addition.

Also, see How can I cast an int to a bit in MySQL 5.1?

Upvotes: 2

kenfire
kenfire

Reputation: 1355

Did you try CONVERT instead ?

https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_convert

Upvotes: 0

Related Questions