I had a table, table2 that was a straight
SELECT * from table1
where table1 had a raw reflection. For some reason when building a reflection on table2 it did not use table1’s reflection. However when I spelled out all the columns explicitely:
SELECT col1, col2, col3 from table1
It suddenly DID start using the reflection. Is this intended behavior?
On further experimentation it seems like its not using the reflection when I include one specific field from the query (col1) for example, however this field is not unique in any way, and it is covered in the previous raw reflection for table1. It is an int field.
If I change the table2 query context to the same directory as the table1 then the problem goes away. Are reflections path dependent?
Kindly provide us with the profile when it did not choose the reflection to accelerate table2
I have seen some issues with * operator, moving queries from data warehouse where they work fine. My workaround was to include a bunch of column lists and CAST statements to ensure data types are exactly the same thus rewriting from
SELECT * from table1 UNION ALL SELECT * form table2;
to
SELECT col1, cast(col2 as decimal) from table1
UNION ALL
SELECT col1, cast(col2 as decimal) from table2;
will edit in future to reply with what error messages I think they were a mix of Illegal Exceptions etc.