加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 百科 > 正文

PostgreSQL嵌套CTE和UNION

发布时间:2020-12-13 16:36:08 所属栏目:百科 来源:网络整理
导读:我正在尝试学习SQL,使用PostgreSQL 9.1.3。我想了解一些令我震惊的行为是不一致的。以机智: 这样做: WITH innermost AS (SELECT 2)SELECT * FROM innermostUNION SELECT 3; 我得到这个: ?column? ---------- 2 3 这样做: WITH outmost AS ( (WITH inner
我正在尝试学习SQL,使用PostgreSQL 9.1.3。我想了解一些令我震惊的行为是不一致的。以机智:

这样做:

WITH innermost AS (SELECT 2)
SELECT * FROM innermost
UNION SELECT 3;

我得到这个:

?column? 
----------
        2
        3

这样做:

WITH outmost AS (
        (WITH innermost AS (SELECT 2)
         SELECT * FROM innermost)
)                                
SELECT * FROM outmost;

结果:

?column? 
----------
        2

这也可以:

WITH outmost AS (
  SELECT 1
  UNION (WITH innermost AS (SELECT 2)
         SELECT * FROM innermost)
)
SELECT * FROM outmost;

我得到这个:

?column? 
----------
        1
        2

但这不行:

WITH outmost AS (
  SELECT 1
  UNION (WITH innermost as (SELECT 2)
         SELECT * FROM innermost
         UNION SELECT 3)
)
SELECT * FROM outmost;

结果:

ERROR:  relation "innermost" does not exist
LINE 4:          SELECT * FROM innermost

对于我的思维方式,最后一个应该成功,还有一个应该失败。我看不到图案。有没有一些一般规则,可以使我预测嵌套CTE和UNION的组合将会或不会起作用?

这个奥秘解决了:我所观察到的行为是一个已知的错误。我将相同的原始帖子发送到PostgreSQL特定的列表,并得到这个答案:

This is a bug :-(. The parse analysis code seems to think that WITH
can only be attached to the top level or a leaf-level SELECT within a
set operation tree; but the grammar follows the SQL standard which
says no such thing. The WITH gets accepted,and attached to the
intermediate-level UNION which is where syntactically it should go,
and then it’s entirely ignored during parse analysis. Will see about
fixing it.

06000

http://archives.postgresql.org/pgsql-novice/2012-07/msg00113.php

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读