enable_incremental_sort changes query behavior

Jaime Casanova <jaime.casanova@2ndquadrant.com>

From: Jaime Casanova <jaime.casanova@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2020-09-26T18:49:13Z
Lists: pgsql-hackers

Attachments

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)

-- 
Jaime Casanova                      www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. Disallow SRFs when considering sorts below Gather Merge

  2. Error out when Gather Merge input is not sorted

  3. Fix get_useful_pathkeys_for_relation for volatile expressions

  4. Guard against core dump from uninitialized subplan.