Update View with UNION changes TINYINT to BIGINT

Question

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?

Why do you need to maintain the columns as `TINYINT`? As long as the values are always in the range of `TINYINT` you shouldn't notice a difference. — Barmar, Sep 06 '19 at 16:08
MySQL is very forgiving about data types. It only complains if a value is too big to fit into a place where it's being stored, not if the data types don't match. — Barmar, Sep 06 '19 at 16:09
[here](https://www.db-fiddle.com/f/gJorWjQkD6nPoWXM5d9Rmo/0) is one way :-) — Paul Spiegel, Sep 06 '19 at 16:17
@PaulSpiegel HACKS i like it :-) One step futher if you use `CREATE temporary TABLE test LIKE tmp2;` after it becomes a native `TINYINT` datetype -> https://www.db-fiddle.com/f/gJorWjQkD6nPoWXM5d9Rmo/1 — Raymond Nijland, Sep 06 '19 at 16:35
@RaymondNijland I don't get it.. It's just a ["copy" of tmp2](https://www.db-fiddle.com/f/om3XTRStpWp7EbkMikuPoU/0) — Paul Spiegel, Sep 06 '19 at 16:40
indeed @PaulSpiegel mine point is then you drop tmp2 and you can use test without the need of a select `(select isRequired FROM tableA where isRequired = 1 limit 1)` to do a "implict type cast" in there to "optimize" .. — Raymond Nijland, Sep 06 '19 at 16:46
Well - I doubt that this is of any value :-) - It's still not clear what exactly is the problem with BIGINT. — Paul Spiegel, Sep 06 '19 at 16:46
Well @PaulSpiegel imagine is you have millions / billions of bigint records which all needs to be converted... i agree this can be "micro" optimisation with a low number of records. . — Raymond Nijland, Sep 06 '19 at 16:47
@PaulSpiegel *"It's still not clear what exactly is the problem with BIGINT. "* Data mapper (types) issues maybe on the client side? No ideaaa what else.. — Raymond Nijland, Sep 06 '19 at 16:50
@RaymondNijland - Correct. The Data mapping on the client side requires that I do not alter the data type. — REAL O G, Sep 06 '19 at 17:39

Paul Spiegel · Accepted Answer · 2019-09-06T18:32:36.830

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

Update View with UNION changes TINYINT to BIGINT

1 Answers1