Informatica Powercenter : UNION in Lookup SQL Override

A couple of weeks ago I came across something strange when using a SQL Override in a Lookup that contained a Union.

For example :   SELECT VARCHAR_A VAR
,          NUMBER_B  NUM
FROM TABLE_A
UNION
SELECT VARCHAR_C VAR
,          NUMBER_D NUM
FROM TABLE_B

When you use VAR in your lookup condition, Powercenter rewrites the query when it runs the transformation.
You will only come across this statement when you check the session log.
Powercenter somehow needs te column that is used in the condition at the end of the select.

This is wat the new query will be:

SELECT NUMBER_B  NUM
,          VARCHAR_A VAR
FROM TABLE_A
UNION
SELECT VARCHAR_C VAR
,          NUMBER_D NUM
FROM TABLE_B

In this case it will result in an error when you run your mapping because the datatypes are incompatible.
However when you have several columns of the same datatype in your union this query will run fine but you will get a bunch of bad data in your lookup !
The way to fix this is to build an additional select around your base query:

SELECT VAR
,          NUM
FROM (
SELECT VARCHAR_A VAR
,          NUMBER_B  NUM
FROM TABLE_A
UNION
SELECT VARCHAR_C VAR
,           NUMBER_D NUM
FROM TABLE_B
)