Re: enable_incremental_sort changes query behavior

On Sat, Sep 26, 2020 at 2:49 PM Jaime Casanova
<jaime.casanova@2ndquadrant.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