Hello,
Athena has been constantly making improvements to its SQL query syntax to make it more logical and user friendly, and during this process we have identified some query syntax that could have behaved in a more reasonable manner. As a result, effective December 1, 2021, we are changing the way how queries return the result for certain syntax. We encourage you to review your queries sooner than December 1, 2021 to adjust to the new behaviors that will be introduced. Read this document to familiarize yourself with the changes and how they might affect your customers.
Q: What are the syntax that will be impacted?
A: There are three behavior changes for corresponding syntax. To help you better understand how the changes look like, we have provided a simple example query for each change.
1. Evaluation of comparison between CHAR type column and a string literal will become easier. No more padding is needed in the string literal.
Example: Suppose table t has a column named col5 with data type CHAR(5), and it contains a row with value 'abcd ' (note the trailing space) for column col5. Currently, you have to use the exact same string literal in order to find a match:
SELECT 1 FROM t WHERE col5 = 'abcd '; -- the trailing space in the string literal is needed to get a match
With the new behavior, you will no longer need to provide the trailing spaces for such queries to match the condition:
SELECT 1 FROM t WHERE col5 = 'abcd'; -- there is no need to have a trailing space in the string literal to get a match
Both the above queries will get a match. We encourage you to start using the latter one, which is more natural and convenient. The current behavior will still be valid.
2. Accessing a non-existing key in a MAP type will cause an error instead of having NULL returned as the result.
Example: Suppose you want to access the value for a non-existing key ‘abc’ from the following MAP data. Currently, Athena returns NULL since the key ‘abc’ does not exist in the MAP. Now with the new change, Athena will report an error indicating "Key not present in map: abc", which is more reasonable and less error prone.
SELECT MAP(array['foo', 'bar'], array[1, 2])['abc'];
If you still prefer the query to return NULL for such cases, you can leverage the TRY function to achieve that:
SELECT TRY(MAP(array['foo', 'bar'], array[1, 2])['abc']);
3. Accessing anonymous row fields in the form of “.field[n]” is no longer supported.
Example: Suppose you want to access a field of an anonymous row. Currently, Athena returns the first field, which is 'a', for the following query. With the new behavior, Athena will report an error indicating "Column 'ROW ('a', 'b').field0' cannot be resolved".
SELECT ROW('a', 'b').field0;
It will not impact the way you access a non-anonymous row. For example, the following query still works the same way and returns 'a'.
SELECT CAST(row('a', 'b') AS ROW(col0 char, col1 char)).col0;
Q: How does this impact my existing queries?
A: We recommend you to review all your queries to find out if these changes are applicable to them. You can use above example queries to test out the new behaviors. If you notice discrepant results, double check if your queries involve any of these syntax.
Q: What if I need additional help?
A: For any questions, please contact AWS customer support [1].
[1]
https://aws.amazon.com/support
Sincerely,
Amazon Web Services
