This question already has an answer here:
I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view:
CREATE VIEW `ItemView` AS SELECT Item.ID ,ItemProperty1 ,ItemProperty2 ,ItemProperty3 ,EXISTS(SELECT ItemComponent.ItemID FROM ItemComponent WHERE ItemComponent.ItemID = Item.ID) AS Operation1Required ,Item.Property4 <> ItemType.Property AS Operation2Required FROM Item INNER JOIN ItemType ON Item.TypeID = ItemType.ID ORDER BY ItemProperty2 ASC
At the moment, MariaDB metadata says that the type for the "Operation1Required" and "Operation2Required" columns is INT, even though they are both expressions with fundamentally Boolean results.
I want them to be BOOLEAN/TINYINT(1), because the view is being read by a strongly-typed client which is expecting Boolean values for these columns.
But the CAST() and CONVERT() functions in MySQL/MariaDB 10.1 don't seem to be able to convert to TINYINT(1).
Is there another way I can specify the correct data type for these calculated columns in the View?
I looked at How to cast an integer to a boolean in a MySQL SELECT clause? and tried it out, but the results of
IF(expression, TRUE, FALSE) still seem to be
INT according to phpMyAdmin's report of the view's structure.