Full Outer Join on ST_Intersects gives error but worked sometimes-CodePudding

I am getting the intermittent error: FULL OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join. BigQuery ran it for a few hours yesterday but is now giving me this error again.

I confirmed using the Personal History that the query that succeeded yesterday gives this error today. Either way this is the query that's giving the error and I am not able to come up with another query that gives this same result. I've seen the following post but my geometries don't have unique ids that I'm aware of.

SELECT * FROM 
`segments`  AS s
FULL OUTER JOIN `zones` AS z
ON ST_Intersects(s.linestring, z.geog)

CodePudding user response：

I found a similar problem in query with FULL OUTER JOIN and spatial condition (and yes, query can also indeed be completed successfully), but now I still don't fully understand the reasons.

While as a "quick" fix, try using instead FULL OUTER JOIN joins with UNION ALL:

SELECT * FROM segments s
LEFT OUTER JOIN zones z
ON ST_Intersects(s.linestring, z.geog)
UNION ALL
SELECT * FROM segments s
LEFT OUTER JOIN zones z
ON ST_Intersects(s.linestring, z.geog)