pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / 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