MySQL UNION not removing duplicates
I have a historical dataset (HIST) and one that's updated daily (DAILY). The DAILY contains the past 3 days worth of data.
So the statement:
SELECT * FROM DAILY
SELECT * FROM HIST
...should produce a combined dataset, with no duplicate rows. But, I'm getting duplicate rows in my output as if I had used a UNION ALL instead. I know how to work around it, but I'm not understanding why it's happening in the first place. The HIST set was created from the DAILY with no transforms.
Here's what's interesting, after it's ran and creates 3 days of duplicate data - if I run it again it doesn't create any more records. So, I interjected a transform to delete the past 2 days from HIST (leaving 1) and ran the dataflow again. It didn't duplicate anything at all. Ok, so I run it one last time (importing the past 3 days into a set that already has all 3 days in it) and expect no change. Nope, it duplicated all 3 days again.
Again, I'm just going to use a `SELECT DISTINCT` after the union to work around this, but I don't understand why this is occuring in Domo, but not on my local MySQL.