Major Description
create or replace temporary view INT8_TBL as select * from (values (123, 456), (123, 4567890123456789), (4567890123456789, 123), (4567890123456789, 4567890123456789), (4567890123456789, -4567890123456789)) as v(q1, q2); select * from int8_tbl t1 left join (select q1 as x, 42 as y from int8_tbl t2) ss on t1.q2 = ss.x where 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1) order by 1,2;
PostgreSQL:
postgres=# select * from
postgres-# int8_tbl t1 left join
postgres-# (select q1 as x, 42 as y from int8_tbl t2) ss
postgres-# on t1.q2 = ss.x
postgres-# where
postgres-# 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
postgres-# order by 1,2;
q1 | q2 | x | y
------------------+------------------+------------------+----
123 | 4567890123456789 | 4567890123456789 | 42
123 | 4567890123456789 | 4567890123456789 | 42
123 | 4567890123456789 | 4567890123456789 | 42
4567890123456789 | 123 | 123 | 42
4567890123456789 | 123 | 123 | 42
4567890123456789 | 4567890123456789 | 4567890123456789 | 42
4567890123456789 | 4567890123456789 | 4567890123456789 | 42
4567890123456789 | 4567890123456789 | 4567890123456789 | 42
(8 rows)
Spark SQL:
spark-sql> select * from
> int8_tbl t1 left join
> (select q1 as x, 42 as y from int8_tbl t2) ss
> on t1.q2 = ss.x
> where
> 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
> order by 1,2;
Error in query: Correlated scalar subqueries must be aggregated: GlobalLimit 1
+- LocalLimit 1
+- Project [1 AS 1#169]
+- Filter isnotnull(outer(y#167))
+- SubqueryAlias `t3`
+- SubqueryAlias `int8_tbl`
+- Project [q1#164L, q2#165L]
+- Project [col1#162L AS q1#164L, col2#163L AS q2#165L]
+- SubqueryAlias `v`
+- LocalRelation [col1#162L, col2#163L]
;;
