Taking the following example:
WITH tbl as (
select 1, 1e2, 1.2e-4, 1.235, null, 0b01, if(1=1, 1, 2) union
select 1, 1e2, 1.2e-4, 1.235, null, 0b01, if(1=1, 1, 2.4) union
select 1, 1e2, 1.2e-4, 1.235, null, 0b01, if(1=1, 1, 'a')
)
select * from tbl
If I only include the first select statement in the cte, the type will be int. If I include the first and second, the type will be DECIMAL, and if I include all three, the type will be cast to VARCHAR (regardless of position of the three statements).
How does mysql determine which type to cast it to — where can I find the ‘type hierarchy’? Do most other DBMS handle this the same, or is it different for each?
Advertisement
Answer
First, to ask the question about other databases, you need to express the SQL using standard SQL — that is using a case expression rather than if:
with tbl as (
select 1, 1e2, 1.2e-4, 1.235, null, 0b01, (case when 1=1 then 1 else 2 end) union
select 1, 1e2, 1.2e-4, 1.235, null, 0b01, (case when 1=1 then 1 else 2.4 end) union
select 1, 1e2, 1.2e-4, 1.235, null, 0b01, (case when 1=1 then 1 else 'a' end)
)
select * from tbl;
In almost any database, the third row is going to generate an error. The case expression has two paths, one returns an int and one returns a string. By the rules of standard SQL, the type of the case expression is an int and you get a type conversion error. The type of the expression has nothing to do with which value is returned.
MySQL, however, deviates from the standard. In this case, it allows the type to be a string.
A similar thing occurs with the union. It verifies that the types are compatible and determines the appropriate type. Because most databases return an error for the third subquery, the type conversion is irrelevant in that case.
However, it is relevant when you consider the first two. The types are different in the first two (int versus numeric/decimal). These are generally compatible, so the numeric/decimal is the returned type from the union — and there is no error.
I should note that some databases play tricks. SQL Server, for instance, would allow your code but it would return a decimal. Why? It eliminates the case expression during the parsing phase, so it never considers returning a string. That said, it would generate an error if you using 1=0.