Athena Dynamic Partition Projection Issue

Question

I have a paritioned table in Athena that uses dynamic partition projection. I enabled this using the following 2 table properties:

```
  'projection.latlong.type'='injected', 
  'storage.location.template'='s3://my_bucket/prefix/latlong=${latlong}'
```
Running a static select query specifying the filter on `latlong` partition key works just fine, e.g. :

```
SELECT count(*) FROM dtx_injected where latlong in ('50.076678_-5.235831', '50.100533_-5.259398')
```
However trying to build a dynamic list results in an error, where the minimal reproducible example is as folows:

```
SELECT count(*) FROM dtx_injected where latlong in (select '50.076678_-5.235831' union select '50.100533_-5.259398')
```
The error I get is the same as not providing a filter at all when trying to query a table that is setup with a dynamic projection.

```
CONSTRAINT_VIOLATION: Injected projected partition column latlong must have only (and at least one) equality conditions in the WHERE clause!
```

Has anynone encountered this issue and know the solution to this problem? I'm using Athena engine version 3 if that helps.

Answer

Is this issue still valid, is there any resolution or workaround on this?

Answer

This is expected behaviour and also a known issue. As mentioned in [1] “Queries with multiple values for a filter expression on an injected column succeed only if the values are disjunct”.

This sentence can be interpreted as, while using injected columns , queries work with a static array of values, however they do not work with subquery in where clause.

————————

Reference:

[1] https://docs.aws.amazon.com/athena/latest/ug/partition-projection-supported-types.html#partition-projection-injected-type

Athena Dynamic Partition Projection Issue

Contenuto pertinente