Hi,
The following CTAS query fails with Col not found error.
CREATE table <table_name>
with(
format='PARQUET'
, write_compression='SNAPPY'
, partitioned_by=ARRAY["yearMonth"]
, external_location = <s3-location>
)
AS SELECT
sum("list_price") as total_list_price_1
, sum("total_list_price") as total_list_price_2
, sum("total_effective_cost") as total_effective_cost_1
, "yearMonth"
, "clientid"
, "productcode"
from <view-name>
where "yearmonth" = '202301'
and "clientid" = '<some id>'
group by "yearmonth", "clientid", "productcode"
Error - COLUMN_NOT_FOUND: line 5:27: Column 'yearmonth' cannot be resolved or requester is not authorized to access requested resources. You may need to manually clean the data at location 's3://athena-604727574140-prism-frontend-wg-query-results/Unsaved/2023/10/30/tables/6b9a2284-037b-498a-a337-718f26d80230' before retrying. Athena will not delete data in your account.
the column 'yearmonth' is present in the view.
The error doesnot occur if the option partitioned_by is disabled.