Injected projected partitions cannot participate in JOINs?

Question

Is there any way around this?
```
SELECT * FROM event_data
JOIN event_index ON(
    event_data.systemid = event_index.systemid AND 
    event_data.partition_day = event_index.partition_day)
limit 10;
```

In the schema, `partition_day` is an `injected` partition in `event_data`. It's just a regular column in `event_index`.

Result:
> CONSTRAINT_VIOLATION: Injected projected partition column partition_day must have only (and at least one) equality conditions in the WHERE clause!
This query ran against the "test-xxxx" database, unless qualified by the query. Please post the error message on our forum  or contact customer support  with Query Id: a78ce597-fcd8-469a-b402-f8a3b6e613a8

I've been struggling to try to force Athena to use partition pruning on `partition_day` on a very complex query, and this is just the latest failed attempt.

Answer

#### UPDATED QUERY:
Hey AlexR, could you please try running this query?

```sql
WITH systemid_partition_day AS (
    SELECT systemid, partition_day
    FROM event_index
)
SELECT ed.*
FROM event_data ed
JOIN systemid_partition_day spd ON ed.systemid = spd.systemid
WHERE ed.partition_day = spd.partition_day
LIMIT 10;
```
> This query joins **`event_data`** with **`event_index`** based on **`systemid`** and **`partition_day`**, filtering **`event_data`** to include only rows where the **`partition_day`** matches the **`partition_day`** associated with each **`systemid`** in **`event_index`**, limiting the result to 10 rows.

or

```sql
SELECT ed.*
FROM event_data ed
JOIN (
    SELECT systemid, partition_day
    FROM event_index
) spd ON ed.systemid = spd.systemid
WHERE ed.partition_day = spd.partition_day
LIMIT 10;
```
Sources:
- [Parameters](https://docs.aws.amazon.com/athena/latest/ug/select.html#select-parameters)

Answer

Hi,
I was looking at your earlier question on this topic too - i wonder how it behaves if you explicitly set the

event_data.partition_day=xxxxx
event_index.partition_day=xxxxx
as well as doing the join

It (should) be able to just work this out for itself as it's implicit by the join statement - but maybe being explicit helps it work out the more efficient pruned plan?

Cheers,
Rich

Injected projected partitions cannot participate in JOINs?

UPDATED QUERY:

Relevanter Inhalt