Confusing inner join behavior with unions, string literals, and char fields

0

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):

  1. 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.
  2. Removing either side of the union
  3. 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.
  4. Removing the string literal 'transactiontype' field
  5. Reordering the fields in the union to be currency, transactiontype
  6. 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.

  1. Cast currency as varchar in the join condition
  2. Remove dates and other condition
  3. Casting transactiontype to varchar
  4. The explain plans show no notable differences.
  5. 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!

  • Matt
asked 5 years ago206 views
3 Answers
0

Do you have any type of constraints defined on tables?

A-D
answered 5 years ago
0

I was going to provide simplified DDL and INSERTs needed to recreate the issue, but I wasn't able to get my simple case to ignore the join properly. Then I realized the query from yesterday runs fine now. Our maintenance period was yesterday a few hours after creating this post, so maybe the reboot or upgrade fixed something.

I'll close this for now, and reopen if it comes back.

answered 5 years ago
0

Hi Matt,

The issue has been identified and fixed recently. Sorry for the inconvenience and thanks for bringing this to our attention.

Regards,
Meng

answered 5 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions