烧死10亿脑细胞的SQL长啥样？

1 前言

今天在生产中碰到了一个让我十分费解的 SQL，十分有趣。

2 现象

SQL 很好复现，就是逻辑看起来有点唬人

postgres=# create table test(id1 int,id2 int);
CREATE TABLE
postgres=# insert into test values(1,3),(2,1),(3,1),(3,3);
INSERT 0 4
postgres=# select * from test;
 id1 | id2 
-----+-----
   1 |   3
   2 |   1
   3 |   1
   3 |   3
(4 rows)

业务 SQL 如下👇🏻 此处用 test 表替代，真实情况表中字段存在一个父子关系，根据 parent_id 查找子 id

postgres=# select (exists (select 1 as one from test a where (test.id1 = a.id2))) as b from test;
 b 
---
 t
 f
 t
 t
(4 rows)

postgres=# explain select (exists (select 1 as one from test a where (test.id1 = a.id2))) as b from test;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on test  (cost=0.00..3.14 rows=4 width=1)
   SubPlan 2
     ->  Seq Scan on test a  (cost=0.00..1.04 rows=4 width=4)
(3 rows)

SQL 是 self-join ，a 是 test 表的一个别名。

让我们把子查询单独摘出来执行一下

postgres=# select 1 as one from test a where (test.id1 = a.id2);
ERROR:  invalid reference to FROM-clause entry for table "test"
LINE 1: select 1 as one from test a where (test.id1 = a.id2);
                                           ^
HINT:  Perhaps you meant to reference the table alias "a".

可以看到报错了，说明此处的 test 是取自外层的 test（即 from test࿰