BUG: Element types don't match MySQL: query-results have inconsistent types

[samjewell]

Problem

GMS should make sure that all elements within a single column in the query-result share the same type.

It doesn't, and so is not consistent with MySQL behaviour.

Reproduction:

Given the following SQL:

CREATE TABLE people (name VARCHAR(50), age INT);
INSERT INTO people VALUES ('sam', 44), ('ben', NULL);

SELECT name, coalesce(age, 0.0) AS coalesced FROM people;

I should see the coalesced column containing floats.
And it does on SQLFiddle: https://sqlfiddle.com/mysql/online-compiler?id=8c51ae86-a4ae-4dbf-8ecd-0d56803fdb5d

But in GMS the 44 comes back as (int64) 44, while the 0 value comes back as (string) (len=3) "0.0"

I've written a failing test case for this, and pushed it up into a PR so you can take a look - see this PR: #2846

I don't know how to fix this issue, but I'm certain it doesn't match what MySQL does here.

[zachmu]

Thanks for the bug @samjewell. This does seem like bad behavior. Assigning to @max-hoffman to triage.

[max-hoffman]

Testing a bit, I think our compare.castLeftAndRight logic applies to coalesce arguments for the target output type.

If any type is string we cast to string

mysql> select coalesce(1, '0');
Type:       VAR_STRING

mysql> select coalesce(1, 'x');
Type:       VAR_STRING

Decimal is wider than int:

mysql> select coalesce(1, 0.0);
Type:       NEWDECIMAL

If we are in an arithmetic expression, then the output needs to be cast to double:

mysql> select coalesce('x')+1;
Type:       DOUBLE

[samjewell]

Thanks for the fix @max-hoffman