[SOLVED] Update View with UNION changes TINYINT to BIGINT

Update View with UNION changes TINYINT to BIGINT

I'm adding information to a view via UNION. I currently have booleans in the table represented by TINYINT. I need to maintain these columns as TINYINT. The following information in the UNION alters the datatype to BIGINT:

<PREVIOUS SELECT (Type of isRequired = TINYINT)>
    SELECT isRequired
    FROM tableA
    UNION
<NEW SELECT (After this, isRequired = BIGINT)>
    SELECT
    1 AS isRequired
    FROM tableB

Apparently, MYSQL CAST() will not convert to TINYINT. How can I preserve the TINYINT in the original view?

Solution

I don't know why you "need to maintain these columns as TINYINT". However - One workaround would be to define a custom function which returns a TINYINT value.

create function cast2tinyint(v bigint)
  returns tinyint
  deterministic no sql
  return v;

Then your query would be

SELECT isRequired
FROM tableA
UNION
SELECT
cast2tinyint(1) AS isRequired
FROM tableA

You can test it storing the result into a (temporary) table.

Original query:

create temporary table tmp1
    SELECT isRequired
    FROM tableA
    UNION
    SELECT
    1 AS isRequired
    FROM tableA
;

show create table tmp1;

Result:

CREATE TEMPORARY TABLE `tmp1` (
  `isRequired` bigint(20) DEFAULT NULL
)

Using custom function:

create temporary table tmp2
    SELECT isRequired
    FROM tableA
    UNION
    SELECT
    cast2tinyint(1) AS isRequired
    FROM tableA
;

show create table tmp2;

Result:

CREATE TEMPORARY TABLE `tmp2` (
  `isRequired` tinyint(4) DEFAULT NULL
)

View on DB Fiddle