PostgreSQL SQL写出变态味，可以！（附带两个哑谜）

AustinDatabases

发布于 2024-12-30 13:26:47

5700

代码可运行

文章被收录于专栏：AustinDatabasesAustinDatabases

运行总次数：0

代码可运行

1 丧心病狂的SQL 的分析

2 为什么会产生这个错误

开始说SQL，说一个丧心病狂的SQL，（认为本文是说SQL撰写的问题，那就大错特错了，咱们是另一个角度）先看这个SQL。

SELECT
   stage.id,
   stage.pipeline_id,
   stage.environment_id,
   stage.name,
   (
    SELECT EXISTS (
     SELECT 1 FROM task
     LEFT JOIN LATERAL (
      SELECT COALESCE(
       (SELECT
        task_run.status
       FROM task_run
       WHERE task_run.task_id = task.id
       ORDER BY task_run.id DESC
       LIMIT 1
       ), 'NOT_STARTED'
      ) AS status
     ) AS latest_task_run ON TRUE
     WHERE task.pipeline_id = stage.pipeline_id
     AND task.stage_id <= stage.id
     AND NOT (
      COALESCE((task.payload->>'skipped')::BOOLEAN, FALSE) IS TRUE
      OR latest_task_run.status = 'DONE'
     )
    )
   ) AS active
  FROM stage
  WHERE %s ORDER BY id ASC

在看完这个SQL后，我不知道各位看官怎么想，我的想法是，够狠，够虐，够聪明。从多年的SQL观察和优化的角度，这个SQL的撰写者并不一般，这是一个知名的软件中我们运行出现问题的SQL，执行中报错，报can not add sublink to placeholder_list。

为什么说这个SQL是丧心病狂的

1 这个SQL撰写成这样是有原因的，明显是为了软件不被破译而做的手段。

2 撰写者非常的狡猾，利用了一个SQL完成了一个简单的业务逻辑的判断程序。

结论：现在SQL都能这么玩了，SQL现在有防盗，防贼，防闺蜜的能力了。

那么我们现在来破译一下，这个SQL的原理和他到底要干什么。

SELECT 1 FROM task
     LEFT JOIN LATERAL (
      SELECT COALESCE(
       (SELECT
        task_run.status
       FROM task_run
       WHERE task_run.task_id = task.id
       ORDER BY task_run.id DESC
       LIMIT 1
       ), 'NOT_STARTED'
      ) AS status
     ) AS latest_task_run ON TRUE
     WHERE task.pipeline_id = 106
     AND task.stage_id <= 106
     AND NOT (
      COALESCE((task.payload->>'skipped')::BOOLEAN, FALSE) IS TRUE
      OR latest_task_run.status = 'DONE'
     )

这段语句可能一般的人，一时半刻解不开这里面到底是什么意思，这里我先做一个解释。这段代码虽然是SQL，但不要把他当SQL看，一段程序，一段具有多个判断能力的程序。

我们逐级的将这段SQL拆开。

核心部分 1

先说这段，这段的意思是，从task_run 表中取值与另一个表task,将两者的id进行比对，然后得到task_run的status,然后取其中的最后一条数据，如果这条数据有，则通过coalesce将他的状态展示，如果根本就没有数据，那么就直接显示 not_started 作为状态。

SELECT COALESCE(
 (SELECT
 task_run.status
 FROM task_run
 WHERE task_run.task_id = task.id
 ORDER BY task_run.id DESC
 LIMIT 1
 ), 'NOT_STARTED'
 ) AS status

扩大部分 2

SELECT 1 FROM task
     LEFT JOIN LATERAL (
      SELECT COALESCE(
       (SELECT
        task_run.status
       FROM task_run
       WHERE task_run.task_id = task.id
       ORDER BY task_run.id DESC
       LIMIT 1
       ), 'NOT_STARTED'
      ) AS status
     ) AS latest_task_run ON TRUE

这里我看看到了主表 task,而 left join lateral 的意思为for each ,意思是task表中的每一行，与下面的子查询返回的值进行循环，ON TRUE 是建立无条件的连接。最终结果返回1。

继续扩大 3

这段是对执行的结果进行判断，如果表中的数据任务符合where条件的假设，

task.pipeline_id = stage.pipeline_id
task.stage_id <= stage.id
NOT (COALESCE((task.payload->>'skipped')::BOOLEAN, FALSE)
IS TRUE
OR latest_task_run.status = 'DONE'

那么select exists 就是 true，如果没有符合的记录出现，那么打印的就是 false。

SELECT EXISTS (
     SELECT 1 FROM task
     LEFT JOIN LATERAL (
      SELECT COALESCE(
       (SELECT
        task_run.status
       FROM task_run
       WHERE task_run.task_id = task.id
       ORDER BY task_run.id DESC
       LIMIT 1
       ), 'NOT_STARTED'
      ) AS status
     ) AS latest_task_run ON TRUE
     WHERE task.pipeline_id = stage.pipeline_id
     AND task.stage_id <= stage.id
     AND NOT (
      COALESCE((task.payload->>'skipped')::BOOLEAN, FALSE) IS TRUE
      OR latest_task_run.status = 'DONE'
     )
    )

所以这个语句最后呈现的影子应该是这样，与此同时SQL作者在这里还有一个where 带的变量条件，让这个SQL变换莫测。

            SELECT
   stage.id,
   stage.pipeline_id,
   stage.environment_id,
   stage.name,
            （True/False）
            FROM stage
      WHERE %s ORDER BY id ASC

通过这个SQL是可以完全得出，这个软件的设计者很鸡贼，尽力保护自己的程序不被破译，这应该是这个软件流程方面核心的一部分。

Bravo, Bravo, Bravo !!!!

（因为程序员对SQL的理解并不清楚，所以这个程序员通过SQL来对自己的程序进行加密，程序员多会一门语言是多么的重要！！明明可以写成程序，但不，他要用SQL来写他的程序）

说完这个SQL的巧妙，我们进入下一个话题，PostgreSQL报错了，且还是PG14.12 不报错，PG14.13报错（也没有这么简单，后面再说，实际上也不是纯种的开源PG）

错误的产生可能来自， “无法将子链接添加到占位符列表” 错误发生是因为在用 WHERE 子句中子链接（LATERAL连接内部的子查询），特别是在COALESCE函数内部。PostgreSQL 在这种情况下不允许子链接。

对于这样的问题，我们可以对这个SQL进行改写来改变其中的可能产生报错的问题，那么以上语句可以用修改语句的方式来解决遇到的问题，比如将子查询改为CTE

WITH latest_status AS (
    SELECT
        task.id AS task_id,
        COALESCE(
            (SELECT task_run.status
             FROM task_run
             WHERE task_run.task_id = task.id
             ORDER BY task_run.id DESC
             LIMIT 1), 'NOT_STARTED'
        ) AS status
    FROM task
)
SELECT task.*, latest_status.status
FROM task
LEFT JOIN latest_status ON task.id = latest_status.task_id
WHERE task.pipeline_id = $1
  AND task.stage_id <= $2
  AND NOT (
      COALESCE((task.payload->>'skipped')::BOOLEAN, FALSE) IS TRUE
      OR latest_status.status = 'DONE'
  );

这样可以解决问题，现实中，我仅仅动用了一个位置就解决了问题。将语句改为join 而不是left join，语句就顺利的运行了。这里提醒在修改这个部分的时候，要明确你修改后是否会影响业务的逻辑，这里简单的分析应该是不影响业务逻辑。

虽然修改了语句解决了问题，但还是觉得不妥，所以询问了这个数据库的出品方，然后在鬼使神差的情况下，这个问题就好了，修改了参数 lazy_process_sublink参数。

那么这篇文章出了两个谜语，这个SQL属于当今那个火热的软件，运行这个SQL的数据库又是那个？猜的出来吗？

总结：这样撰写SQL，只能是开发人员写出来的高阶预防程序软件被破译的门槛，正常的人员一般不会这样撰写SQL，不过今天也让我涨了见识。SQL 还可以这样写。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-12-29，如有侵权请联系 cloudcommunity@tencent.com 删除

程序