MySQL and union

For a GAMS model I need to read data from a MySQL database. Some of the data can be easily imported into a GAMS gdx file using an SQL UNION construct:

SELECT id,xxx FROM tableA
UNION
SELECT `obj’,xxx FROM tableB

Now the interesting part is that ids are INTs. So basically the first column looks like

1	xxx
2	xxx
3	xxx
obj	xxx

On my MySQL installation, the first column gets a correct type of VARCHAR(11) as the integer type was INT(10) – the extra char is for the sign I think (although we have only positive ids). However when the client was testing this, it went terribly wrong. Turned out that their MySQL version created a type of VARBINARY(11), a BLOB like type. This created havoc downstream. Luckily I was using $LOADDC in GAMS to read the GDX file produced by SQL2GMS, so we caught a domain error.

No doubt this was bad SQL produced by me. I tried something similar with SQL Server, and SQL Server complains directly about not being able to put a string into integer type. The fix: using a cast as in:

SELECT cast(id as char(10)),xxx FROM tableA
UNION
SELECT `obj’,xxx FROM tableB

A little bit troublesome that different versions of MySQL behave differently on this.