When running TPC-DS query 17 we found that build and probe side of the join is decided based on the stat(cardinality), which completely ignores the join order specified in the query.
Also, it starts joining with the tables for which there is no direct join condition specified in the query and as result of this re-ordering the query fails to complete with
bad_alloc error message.
As we can see in the attached plan that there is no direct join between catalog_sales and item table but was inferred from the query, which is because of the cardinality of catalog_sales is more that store_sales and store_returns for this query.
As can be seen from query that all dimension table join happens with store_sales(except date_dim) and then it is being joined with other table.
Taking the same query and moving all the joins specific to a fact table in inline view lets the query run successfully.
Both version of the query and plan is attached.