Why do I get a LEFT OUTER JOIN error msg when i'm not even using a left join?

0
votes

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

Why do I get the above BQ error msg when running the below query? I'm not using any left outer join. Thanks!

SELECT *
FROM
   (SELECT
        *
      , (select sum(transaction_amount) from `analytics-111.tmp.coll` where transaction_date_est <= mq.transaction_date_est) RunningTotal      
   FROM `analytics-111.tmp.coll` mq) mq2
WHERE mq2.RunningTotal < 1000 ```
1
sqlgoogle-bigquerybq
The select sum subquery is probably converted to a left join (internally). - jarlh
Never met bq, my guess is try aliases for every table. - Serg

1 Answers

1
votes

The subquery is just another way of expressing a left join. The optimizer recognizes the issue after rewriting the query -- and the error message doesn't know if the original code is a LEFT JOIN or correlated subquery. The error message could be fixed.

Happily, you can calculate this using a cumulative sum:

SELECT mq.*,
       SUM(mq.transaction_amount) OVER (ORDER BY transaction_amount) as RunningTotal      
FROM `analytics-111.tmp.coll` mq
QUALIFY mq.RunningTotal < 1000;

Note that this uses the new QUALIFY clause which allows you to filter on window functions.