1

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.

Frazz
  • 2,995
  • 2
  • 19
  • 33

2 Answers2

1

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?

kmoser
  • 8,780
  • 3
  • 24
  • 40
  • And I suppose BLOB (which is the one I really need) is out of the question :( My expression is just a constant NULL, I need an empty column. There is no other column involved in the expression. But the user function could maybe be a way to solve this. – Frazz Aug 29 '17 at 07:49
  • It's not clear what you're trying to do. Your original question said you want to _"SELECT some real columns from a database and add to them a costant column of a specific type"_. How do you add something to a BLOB? Do you mean string concatenation? – kmoser Aug 29 '17 at 22:34
  • If the table contains N columns... I want the result set to have those N columns plus a BLOB column initialized with NULL constant values. – Frazz Aug 30 '17 at 15:55
  • If the contents of that BLOB column will be NULL, why not just select NULL for it: ``SELECT a.*, NULL AS fake_column FROM table1 a`` – kmoser Aug 30 '17 at 21:21
  • Because if it is not typed correctly, then the client software will not map it to the correct type of field object and I will not be able to obtain the behavior I need from it. A null VARCHAR column is not seen the same as a null BLOB column. What bothers me is that this works correctly with other databases. – Frazz Aug 31 '17 at 22:14
  • I know this is not an ideal solution but why not just create those dummy columns and set them to NULL and be done with it? – kmoser Sep 01 '17 at 05:50
  • That's impossible. I'm not the owner of the database. – Frazz Sep 15 '17 at 13:50
  • Do you have permission to create temporary tables? – kmoser Sep 16 '17 at 05:04
0

Did you try CONVERT instead ?

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

kenfire
  • 1,305
  • 12
  • 23