BigQueryで相関サブクエリがうまく動かなかった話

BigQuery

相関サブクエリ自体よくないやり方ではあるけれど、それを使ってみたらわかりにくい挙動をしていた


例えばこんな感じのクエリ

SELECT
  (
    SELECT
      1
    FROM
      rdb.users
    WHERE
      users.total_pay  < payments.pay
  )
FROM
  rdb.payments

payments.payに応じてusersを絞り込んでごねごねしたくて書くと、
invalidQuery: LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
というエラーがでる

どうも、BigQueryでは効率をあげるために、サブクエリはJOINに変換されるらしく、相関サブクエリ内のWHEREが不等号だとエラー起きるっぽい
BigQuery Subqueries Efficient Join - Stack Overflow

バグじゃないかと思うけど、とりあえずの対処としては、相関サブクエリを書かないようにすること（CROSS JOINなどして条件絞り込みする）、そのためにサブクエリを書くときはWITHで分離できるときは分離してしまうこと、かな