2

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.

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Hydrargyrum
  • 141
  • 1
  • 3
  • I looked at https://dba.stackexchange.com/questions/12569/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. – Hydrargyrum Dec 11 '17 at 06:37
  • @EvanCarroll, why are you listing as a possible duplicate a question that I cited in my comment above yours, and already explained why it doesn’t answer my question? – Hydrargyrum Dec 11 '17 at 08:23
  • 1
    I don't always agree ;) Likely, I agree with you that the chosen answer there is wrong. It doesn't address the question at all. But, the question itself is a duplicate of this. You're both asking the same thing. – Evan Carroll Dec 11 '17 at 08:25
  • 1
    Ah, I see what you mean. The accepted answer over there is rubbish but a CORRECT answer to that question would answer this also. – Hydrargyrum Dec 11 '17 at 08:28
  • On that note, a non-dupe would be to ask another question looking for a workaround for the "strongly-typed client" that may be more fun. (btw I upvoted this even though I think it's a dupe) – Evan Carroll Dec 11 '17 at 08:28
  • Hey Hydragyrum, My answer (https://dba.stackexchange.com/a/12570/877) is meant to bypass CAST and CONVERT altogether. IF(expression, TRUE, FALSE) still gets victimized if you want a boolean. This is why I suggested using IF to assign string values rather than try to stress over getting a boolean value. My answer was just a workaround. – RolandoMySQLDBA Dec 11 '17 at 15:23
  • You could just change EXISTS(...) with IF(EXISTS(...),1,0). Seems cheesy, but Hey this is MYSQL. Therefore, Evan's answer is quite valid and is a workaround of a more permanent nature. – RolandoMySQLDBA Dec 11 '17 at 15:37

1 Answers1

4

This is currently impossible.

  • MySQL doesn't have a real BOOLEAN type, (or a real array type.. or a real JSON type). It has an alias for TINYINT.
  • Any condition returns an integer. This is the fastest datatype for a CPU, and presumably this implementation detail is reflected here. For instance, 'true' IS TRUE and 1=1 both return 1 as an int.
  • CAST does not provide a TINYINT format.

I would highly suggest migrating to PostgreSQL. It's so much less terrifying... and liberating.

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
  • 2
    I am sorry MySQL has a lot of cheesy things to it. Your answer here is quite understandable (+1). – RolandoMySQLDBA Dec 11 '17 at 16:03
  • I tried everything too but I couldn't make the cast I wanted. In my case using Dapper I was unable to associate the boolean type to the constructor of a class and therefore I had to use the long type and subsequently convert to bool – Lorenzo Belfanti Dec 05 '22 at 14:02