Re: enable_incremental_sort changes query behavior

On Sat, Sep 26, 2020 at 2:49 PM Jaime Casanova
<jaime(dot)casanova(at)2ndquadrant(dot)com> wrote:
>
> Hi,
>
> With sqlsmith I found a query that gives this error:
> ERROR: ORDER/GROUP BY expression not found in targetlist
>
> I noted the query (sql query below, sorry it uses custom tables i
> couldn't replicate with regression tables) because it doesn't include
> an ORDER/GROUP BY clause.
>
> --- 0 ----
> select distinct
> subq_0.c1 as c0,
> ref_0.radi_usua_radi as c1,
> ref_0.radi_nume_asoc as c2,
> subq_0.c1 as c3,
> case when (cast(null as pg_lsn) >=
> pg_catalog.pg_last_wal_receive_lsn())
> and (true = pg_catalog.pg_rotate_logfile_old()) then
> ref_0.radi_usua_rem else ref_0.radi_usua_rem end
> as c4,
> cast(nullif((select hist_codi from public.hist_eventos_2
> limit 1 offset 4)
> ,
> pg_catalog.pg_stat_get_buf_alloc()) as int8) as c5
> from
> public.radicado_2 as ref_0,
> lateral (select
> ref_0.radi_text_temp as c0,
> ref_0.radi_usua_actu as c1
> from
> public.hist_eventos_1 as ref_1
> where cast(nullif(cast(null as float4),
> cast(null as float4)) as float4) >= pg_catalog.pi()) as subq_0
> where ref_0.radi_usua_dest is not NULL;
> --- 0 ----
>
> Attached the stack trace produced until de elog that produces the message.
>
> But if I set enable_incremental_sort to off the query gets executed
> without problems (attached the explain produced for that case)

Thanks for the report.

Is there by an chance an index on ref_0.radi_text_temp?

And if you set enable_hashagg = off what plan do you get (or error)?

Thanks,
James