Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r43gr-007BqM-QN for pgsql-general@arkaria.postgresql.org; Fri, 17 Nov 2023 18:35:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r43gq-00EzyI-EK for pgsql-general@arkaria.postgresql.org; Fri, 17 Nov 2023 18:35:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r43go-00Ezw3-JX for pgsql-general@lists.postgresql.org; Fri, 17 Nov 2023 18:35:40 +0000 Received: from wout3-smtp.messagingengine.com ([64.147.123.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r43gk-006wZe-IV for pgsql-general@postgresql.org; Fri, 17 Nov 2023 18:35:38 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.west.internal (Postfix) with ESMTP id 3F3483200A54; Fri, 17 Nov 2023 13:35:31 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Fri, 17 Nov 2023 13:35:31 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to; s=fm3; t=1700246130; x=1700332530; bh=wH L6Y0wUkhUulKjGE0CLcpXRscPj5gm/rl32oQuUh0I=; b=D9i1/Ny/SnOI5zZDJS Z74baCjtRn/41wxYaRecqV7Vn6YoloAC7L93zbw+vG4h1YmM/GXj08uFkp3TJu4p 3svumeTU0Zcf7mw9pvlj5La69RG+gySr20DdaV170Bvi8AXqV013My/YT0APUCpw iwQZfvMq5Un+8fW7axB9rxisIwUmm4WvAzvdqieXgxnFvEW6eDs3hn3/vyijTRLX Xmitj3n1cl9NXhChp8N+/hMpX4BT8R+RpMZGAzuR85zWBkX8b99pOiflywLxbrtc bSts0gR8PB1ihja000dg7aFj/Zr9jgV5zfulRCjr2udEySs0/4R9kMBptL2WfIxG ivhw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; t=1700246130; x=1700332530; bh=wHL6Y0wUkhUul KjGE0CLcpXRscPj5gm/rl32oQuUh0I=; b=FkULB/HgX5AOUX2luEfJyjrzPlVUy FFtuEVpCku54geLySh03NYOXLf1g+JSBgy0n3uQSoYNAQyCgAKd94KFwCjBL4LO1 AcDWda/SG2eP+urs84TYGGEN2++F5TgYvlN+U2zB1ZzNLMahbNA6MyOpUHfAmzQD 8TRolHtk3uJ6EaanEQ5A6vHMaWQm7xRBWTmvOeAgIB7k3H0ngvdkPhWm2YPIh99P IOK1kTvZWtnYgH/MYcjggj0OMsTS2NmrjlYgicZXe3oX1RWG/mdmR+zjknCu+eg3 7JeHobzJPJ/qYgfPkWx7GKkqNeNiuW9cBQChKubD6QjnCe/IOBlOyHStA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrudegtddguddugecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpegtkfffgggfuffvfhfhjgesrgdtreertddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeehvedugfefieefhfehjeelveeggfelgfehieejffeu geefjeeftdeifffhffekleenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 17 Nov 2023 13:35:29 -0500 (EST) Content-Type: multipart/alternative; boundary="------------Epwyw6cZVjlgSbEjJUcr0CCP" Message-ID: Date: Fri, 17 Nov 2023 10:35:22 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Unused CTE affects result set To: =?UTF-8?Q?Gabriel_S=C3=A1nchez?= , Postgres General References: Content-Language: en-US From: Adrian Klaver In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Epwyw6cZVjlgSbEjJUcr0CCP Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/17/23 10:30 AM, Gabriel Sánchez wrote: > Dear Postgres community, > > I'm seeing unintuitive behavior with a query structured as follows: > > WITH used_cte (SELECT a, b, c FROM t1 ...) > , unused_cte (SELECT d, e, f FROM t2 ...) > SELECT * FROM used_cte ... > -- returns no results > > WITH used_cte (SELECT a, b, c FROM t1 ...) > -- , unused_cte (SELECT d, e, f FROM t2 ...) > SELECT * FROM used_cte ... > -- returns results > > I don't understand why the presence of a CTE that ends up not being > included in the final query affects the result set. Could this be a > bug, or is there something I don't understand? I would say it has to do with what '...' is doing? In other words I don't see this behavior here for a simple case. > > PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, > compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit > > Thanks, > Gabriel > > --------------Epwyw6cZVjlgSbEjJUcr0CCP Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 11/17/23 10:30 AM, Gabriel Sánchez wrote:
Dear Postgres community,

I'm seeing unintuitive behavior with a query structured as follows:

WITH used_cte (SELECT a, b, c FROM t1 ...)
, unused_cte (SELECT d, e, f FROM t2 ...)
SELECT * FROM used_cte ...
-- returns no results

WITH used_cte (SELECT a, b, c FROM t1 ...)
-- , unused_cte (SELECT d, e, f FROM t2 ...)
SELECT * FROM used_cte ...
-- returns results

I don't understand why the presence of a CTE that ends up not being included in the final query affects the result set.  Could this be a bug, or is there something I don't understand?


I would say it has to do with what '...' is doing?

In other words I don't see this behavior here for a simple case.


PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

Thanks,
Gabriel


--------------Epwyw6cZVjlgSbEjJUcr0CCP--