pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Unused CTE affects result set 3+ messages / 3 participants [nested] [flat]
* Unused CTE affects result set @ 2023-11-17 18:30 Gabriel Sánchez <gabrielesanchez@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Gabriel Sánchez @ 2023-11-17 18:30 UTC (permalink / raw) To: pgsql-general 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? 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unused CTE affects result set @ 2023-11-17 18:35 Adrian Klaver <adrian.klaver@aklaver.com> parent: Gabriel Sánchez <gabrielesanchez@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Klaver @ 2023-11-17 18:35 UTC (permalink / raw) To: Gabriel Sánchez <gabrielesanchez@gmail.com>; pgsql-general 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 > > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unused CTE affects result set @ 2023-11-17 18:48 Tom Lane <tgl@sss.pgh.pa.us> parent: Adrian Klaver <adrian.klaver@aklaver.com> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2023-11-17 18:48 UTC (permalink / raw) To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Gabriel Sánchez <gabrielesanchez@gmail.com>; pgsql-general Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 11/17/23 10:30 AM, Gabriel Sánchez wrote: >> 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? We'd need to see a complete example to do more than speculate. The planner definitely does skip unreferenced SELECT CTEs, though. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-11-17 18:48 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-11-17 18:30 Unused CTE affects result set Gabriel Sánchez <gabrielesanchez@gmail.com> 2023-11-17 18:35 ` Adrian Klaver <adrian.klaver@aklaver.com> 2023-11-17 18:48 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox