Hello, I just came across an issue where an inner join isn't producing the results I expect. Seems like a bug.
Here's the distilled and redacted query. It joins exchange rate details to the union of two transaction tables.
SELECT *
FROM (SELECT 'CHARGE' AS transactiontype, currency
FROM schema.chargetable
WHERE trxdate BETWEEN '2019-02-01' AND '2019-02-01 23:59:59'
AND id = '123456789'
UNION ALL
SELECT 'REFUND' AS transactiontype, currency
FROM schema.refundtable
WHERE refunddate BETWEEN '2019-02-01' AND '2019-02-01 23:59:59'
AND id = '123456789') AS sub
JOIN (SELECT currencycode AS destinationcurrency, AVG(exchangerate) AS exchangerate
FROM schema.exchangerate
WHERE "date" >= '2019-02-01' and "date" < '2019-02-02'
GROUP BY destinationcurrency) fx
ON fx.destinationcurrency = sub.currency;
This returns 0 records even though the "currency" fields from the union clearly match a "destinationcurrency" from the exchangerate table.
All three tables use char(3) for those fields, and contain a currency code. The field is not encoded on the refund table, but is encoded on the other tables as zstd.
Doing ANY of the following causes this query to magically work (meaning, it returns expected data):
- Casting the currency from the first part of the union to varchar, "SELECT 'CHARGE' AS transactiontype, currency::varchar". btrim works as well. Seems like anything that converts it to a varchar is good.
Note that casting the currency field in the second part of the union only partially works. It appears that this causes only the second part of the union's data to be joined properly.
- Removing either side of the union
- Changing the inner join to a left join. Note that when doing this the joined table (exchangerate) fields are not null, so the join condition did succeed. This is confusing behavior.
- Removing the string literal 'transactiontype' field
- Reordering the fields in the union to be currency, transactiontype
- Changing 'SELECT *' to 'SELECT sub.currency' returns data! but changing to 'SELECT sub.transactiontype' doesn't... This just keeps getting stranger.
I also tried the following, but none worked.
- Cast currency as varchar in the join condition
- Remove dates and other condition
- Casting transactiontype to varchar
- The explain plans show no notable differences.
- Manually inspecting and comparing currency, currency::varchar, btrim(currency), etc show no differences.
From what I can see, it appears to be related to the interaction between the inner join, the char fields in the join condition, the union, and somehow the string literal transactiontype field. Maybe leading a union with a string literal field causes subsequent char fields to be interpreted wrong, but only for inner joins?
Anyway. that's all I got.
AWS people, let me know if you need more specific table structure and query details and I can PM it.
Thanks!