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
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------
HashAggregate (cost=585169544090.96..675589722275.63 rows=202007 width=65)
Group Key: ref_0.radi_usua_actu, ref_0.radi_usua_radi, ref_0.radi_nume_asoc,
ref_0.radi_usua_actu, CASE WHEN (pg_rotate_logfile_old() AND NULL::boolean)
THEN ref_0.radi_usua_rem ELSE ref_0.radi_usua_rem END, NULLIF($0, pg_stat_get
_buf_alloc())
Planned Partitions: 16
InitPlan 1 (returns $0)
-> Limit (cost=0.10..0.12 rows=1 width=8)
-> Seq Scan on hist_eventos_2 (cost=0.00..118555.22 rows=4844222
width=8)
-> Result (cost=0.01..88461384682.66 rows=3857927451714 width=65)
One-Time Filter: (NULLIF(NULL::real, NULL::real) >=
'3.141592653589793'::double precision)
-> Nested Loop (cost=0.01..59526928794.81 rows=3857927451714
width=30)
-> Seq Scan on radicado_2 ref_0 (cost=0.00..53449.74
rows=798774 width=30)
Filter: (radi_usua_dest IS NOT NULL)
-> Materialize (cost=0.00..156323.17 rows=4829811 width=0)
-> Seq Scan on hist_eventos_1 ref_1
(cost=0.00..118024.11 rows=4829811 width=0)
(13 rows)
#0 get_sortgroupref_tle (sortref=1, targetList=0x563fd6160f80) at tlist.c:371
l__state = {l = 0x0, i = 0}
l = 0x563fd616e948
__func__ = "get_sortgroupref_tle"
#1 0x0000563fd49cac41 in get_sortgroupclause_tle (sgClause=0x563fd617c928,
targetList=0x563fd6160f80) at tlist.c:392
No locals.
#2 0x0000563fd49cac66 in get_sortgroupclause_expr (sgClause=0x563fd617c928,
targetList=0x563fd6160f80) at tlist.c:403
tle = 0x1d613d940
#3 0x0000563fd496ee90 in make_pathkeys_for_sortclauses (root=0x563fd617cc40,
sortclauses=0x563fd617c890, tlist=0x563fd6160f80) at pathkeys.c:1138
sortcl = 0x563fd617c928
sortkey = 0x563fd617c890
pathkey = 0x563fd617c890
l__state = {l = 0x563fd617c890, i = 0}
pathkeys = 0x0
l = 0x563fd617c8a8
#4 0x0000563fd498be89 in standard_qp_callback (root=0x563fd617cc40,
extra=0x7ffc8bbff920) at planner.c:3629
parse = 0x563fd61a01c8
qp_extra = 0x7ffc8bbff920
tlist = 0x563fd6160f80
activeWindows = 0x0
#5 0x0000563fd4984f20 in query_planner (root=0x563fd617cc40,
qp_callback=0x563fd498bd4d <standard_qp_callback>, qp_extra=0x7ffc8bbff920) at
planmain.c:205
parse = 0x563fd61a01c8
joinlist = 0x563fd616e870
final_rel = 0x563fd6160f80
__func__ = "query_planner"
#6 0x0000563fd4988c00 in grouping_planner (root=0x563fd617cc40,
inheritance_update=false, tuple_fraction=0) at planner.c:2058
sort_input_targets = 0x563fd61662e8
sort_input_target_parallel_safe = false
grouping_target = 0x563fd61663b0
scanjoin_target = 0x7ffc8bbffa80
activeWindows = 0x0
qp_extra = {activeWindows = 0x0, groupClause = 0x0}
sort_input_targets_contain_srfs = 0x563fd613d940
have_grouping = false
wflists = 0x0
gset_data = 0x0
sort_input_target = 0x7ffc8bbffab0
grouping_targets = 0x40
grouping_target_parallel_safe = false
scanjoin_targets = 0x3d4c6a91b
scanjoin_target_parallel_safe = false
grouping_targets_contain_srfs = 0x563fd6166398
scanjoin_targets_contain_srfs = 0x7ffc8bbff960
scanjoin_target_same_exprs = false
agg_costs = {numAggs = 0, numOrderedAggs = 0, hasNonPartial = false,
hasNonSerial = false, transCost = {startup = 0, per_tuple = 0}, finalCost =
{startup = 0, per_tuple = 0}, transitionSpace = 0}
parse = 0x563fd61a01c8
offset_est = 0
count_est = 0
limit_tuples = -1
have_postponed_srfs = false
final_target = 0x563fd617d080
final_targets = 0x7f008bbff9c0
final_targets_contain_srfs = 0x563fd4c6a91b <palloc+273>
final_target_parallel_safe = false
current_rel = 0x563fd617d100
final_rel = 0x0
extra = {limit_needed = 240, limit_tuples = 2.4940433802066126e-320,
count_est = 140722653100576, offset_est = 94832174716752}
lc = 0x563fd4986fe4 <preprocess_qual_conditions+221>
__func__ = "grouping_planner"
#7 0x0000563fd4986d88 in subquery_planner (glob=0x563fd617cb28,
parse=0x563fd61a01c8, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at
planner.c:1015
root = 0x563fd617cc40
newWithCheckOptions = 0x0
newHaving = 0x0
hasOuterJoins = false
hasResultRTEs = false
final_rel = 0x0
l = 0x0
#8 0x0000563fd49855bd in standard_planner (parse=0x563fd61a01c8,
query_string=0x563fd6053390 "explain \nselect distinct\n subq_0.c1 as
c0,\n ref_0.radi_usua_radi as c1,\n ref_0.radi_nume_asoc as
c2,\n subq_0.c1 as c3,\n case when (cast(null as pg_lsn) >=
"..., cursorOptions=256, boundParams=0x0) at planner.c:405
result = 0x563fd4c5c6a0 <check_enable_rls+158>
glob = 0x563fd617cb28
tuple_fraction = 0
root = 0x563fd4c6fd79 <ResourceOwnerForgetRelationRef+39>
final_rel = 0x7ffc8bbffcd0
best_path = 0x7ffc8bbffd90
top_plan = 0x7ffc8bbffcf0
lp = 0x7f8cfceca798
lr = 0xad6096bb8
#9 0x0000563fd4985368 in planner (parse=0x563fd61a01c8,
query_string=0x563fd6053390 "explain \nselect distinct\n subq_0.c1 as
c0,\n ref_0.radi_usua_radi as c1,\n ref_0.radi_nume_asoc as
c2,\n subq_0.c1 as c3,\n case when (cast(null as pg_lsn) >=
"..., cursorOptions=256, boundParams=0x0) at planner.c:275
result = 0xfceca798
#10 0x0000563fd4aa9fdd in pg_plan_query (querytree=0x563fd61a01c8,
query_string=0x563fd6053390 "explain \nselect distinct\n subq_0.c1 as
c0,\n ref_0.radi_usua_radi as c1,\n ref_0.radi_nume_asoc as
c2,\n subq_0.c1 as c3,\n case when (cast(null as pg_lsn) >=
"..., cursorOptions=256, boundParams=0x0) at postgres.c:875
plan = 0x0
#11 0x0000563fd47e02e0 in ExplainOneQuery (query=0x563fd61a01c8,
cursorOptions=256, into=0x0, es=0x563fd619f9a0, queryString=0x563fd6053390
"explain \nselect distinct\n subq_0.c1 as c0,\n
ref_0.radi_usua_radi as c1,\n ref_0.radi_nume_asoc as c2,\n
subq_0.c1 as c3,\n case when (cast(null as pg_lsn) >= "...,
params=0x0, queryEnv=0x0) at explain.c:391
planstart = {tv_sec = 240551, tv_nsec = 82138627}
bufusage = {shared_blks_hit = 140722653101632, shared_blks_read =
94832149226653, shared_blks_dirtied = 94832174957000, shared_blks_written =
94832174811856, local_blks_hit = 140722653101776, local_blks_read =
94832150521575, local_blks_dirtied = 94832174955952, local_blks_written =
94832174957000, temp_blks_read = 94832174811856, temp_blks_written =
94828582928385, blk_read_time = {tv_sec = 94832174811768, tv_nsec =
94828582928385}, blk_write_time = {tv_sec = 94832174955064, tv_nsec =
94832174957000}}
plan = 0x100563fd6118dd0
planduration = {tv_sec = 7886995912, tv_nsec = 94832174957000}
bufusage_start = {shared_blks_hit = 0, shared_blks_read =
94832174810832, shared_blks_dirtied = 140722653101552, shared_blks_written =
94832152717595, local_blks_hit = 140722653101568, local_blks_read = 64,
local_blks_dirtied = 94832174811856, local_blks_written = 94832174553408,
temp_blks_read = 140722653101600, temp_blks_written = 94832149225555,
blk_read_time = {tv_sec = 140243515516824, tv_nsec = 970662608897},
blk_write_time = {tv_sec = 94832174811856, tv_nsec = 94832174811856}}
#12 0x0000563fd47dfe2f in ExplainQuery (pstate=0x563fd613dc30,
stmt=0x563fd6124b70, params=0x0, dest=0x563fd613db98) at explain.c:275
l__state = {l = 0x563fd617cad0, i = 0}
l = 0x563fd617cae8
es = 0x563fd619f9a0
tstate = 0x80
rewritten = 0x563fd617cad0
lc = 0x0
timing_set = false
summary_set = false
__func__ = "ExplainQuery"
#13 0x0000563fd4ab2bd5 in standard_ProcessUtility (pstmt=0x563fd6124c38,
queryString=0x563fd6053390 "explain \nselect distinct\n subq_0.c1 as
c0,\n ref_0.radi_usua_radi as c1,\n ref_0.radi_nume_asoc as
c2,\n subq_0.c1 as c3,\n case when (cast(null as pg_lsn) >=
"..., context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x563fd613db98, qc=0x7ffc8bc00170) at utility.c:829
parsetree = 0x563fd6124b70
isTopLevel = true
isAtomicContext = false
pstate = 0x563fd613dc30
readonly_flags = 7
__func__ = "standard_ProcessUtility"
#14 0x0000563fd4ab2397 in ProcessUtility (pstmt=0x563fd6124c38,
queryString=0x563fd6053390 "explain \nselect distinct\n subq_0.c1 as
c0,\n ref_0.radi_usua_radi as c1,\n ref_0.radi_nume_asoc as
c2,\n subq_0.c1 as c3,\n case when (cast(null as pg_lsn) >=
"..., context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x563fd613db98, qc=0x7ffc8bc00170) at utility.c:524
No locals.
#15 0x0000563fd4ab1149 in PortalRunUtility (portal=0x563fd60b6b70,
pstmt=0x563fd6124c38, isTopLevel=true, setHoldSnapshot=true,
dest=0x563fd613db98, qc=0x7ffc8bc00170) at pquery.c:1157
utilityStmt = 0x563fd6124b70
snapshot = 0x563fd6079298
#16 0x0000563fd4ab0e3b in FillPortalStore (portal=0x563fd60b6b70,
isTopLevel=true) at pquery.c:1017
treceiver = 0x563fd613db98
qc = {commandTag = CMDTAG_UNKNOWN, nprocessed = 0}
__func__ = "FillPortalStore"
#17 0x0000563fd4ab07ac in PortalRun (portal=0x563fd60b6b70,
count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x563fd6175340,
altdest=0x563fd6175340, qc=0x7ffc8bc00340) at pquery.c:751
_save_exception_stack = 0x7ffc8bc00450
_save_context_stack = 0x0
_local_sigjmp_buf = {{__jmpbuf = {0, -1677167531114338007,
94832145850304, 140722653104512, 0, 0, -1677167531191932631,
-4954170892889200343}, __mask_was_saved = 0, __saved_mask = {__val =
{20771494778, 94832174781224, 128, 94832173593200, 94832174553408,
140722653102752, 29592, 94832174553952, 94832174778048, 140722653102784,
94832152718016, 13, 112, 94832174781360, 112, 2344616656}}}}
_do_rethrow = false
result = false
nprocessed = 94832174781248
saveTopTransactionResourceOwner = 0x563fd60806c0
saveTopTransactionContext = 0x563fd6079140
saveActivePortal = 0x0
saveResourceOwner = 0x563fd60806c0
savePortalContext = 0x0
saveMemoryContext = 0x563fd6079140
__func__ = "PortalRun"
#18 0x0000563fd4aaa5f7 in exec_simple_query (query_string=0x563fd6053390
"explain \nselect distinct\n subq_0.c1 as c0,\n
ref_0.radi_usua_radi as c1,\n ref_0.radi_nume_asoc as c2,\n
subq_0.c1 as c3,\n case when (cast(null as pg_lsn) >= "...) at
postgres.c:1239
snapshot_set = true
per_parsetree_context = 0x0
plantree_list = 0x563fd61752e8
parsetree = 0x563fd6124ba8
commandTag = CMDTAG_EXPLAIN
qc = {commandTag = CMDTAG_UNKNOWN, nprocessed = 0}
querytree_list = 0x563fd6175290
portal = 0x563fd60b6b70
receiver = 0x563fd6175340
format = 0
parsetree_item__state = {l = 0x563fd6124be0, i = 0}
dest = DestRemote
oldcontext = 0x563fd6079140
parsetree_list = 0x563fd6124be0
parsetree_item = 0x563fd6124bf8
save_log_statement_stats = false
was_logged = false
use_implicit_block = false
msec_str =
"L\000\000\000\002\000\000\000\274\003\000\000\000\000\000\000\220\063\005\326?V\000\000\266\003\000\000\006\000\000"
__func__ = "exec_simple_query"
#19 0x0000563fd4aae950 in PostgresMain (argc=1, argv=0x563fd607ec38,
dbname=0x563fd607eb50 "postgres", username=0x563fd604fdd8 "jcasanov") at
postgres.c:4315
query_string = 0x563fd6053390 "explain \nselect distinct\n
subq_0.c1 as c0,\n ref_0.radi_usua_radi as c1,\n
ref_0.radi_nume_asoc as c2,\n subq_0.c1 as c3,\n case when
(cast(null as pg_lsn) >= "...
firstchar = 81
input_message = {data = 0x563fd6053390 "explain \nselect distinct\n
subq_0.c1 as c0,\n ref_0.radi_usua_radi as c1,\n
ref_0.radi_nume_asoc as c2,\n subq_0.c1 as c3,\n case when
(cast(null as pg_lsn) >= "..., len = 951, maxlen = 1024, cursor = 951}
local_sigjmp_buf = {{__jmpbuf = {0, -1677167531057714903,
94832145850304, 140722653104512, 0, 0, -1677167531078686423,
-4954170894004229847}, __mask_was_saved = 1, __saved_mask = {__val = {0, 0,
30064771072, 94832173598312, 0, 94832173593200, 94832152669620, 1024,
94832173598336, 140722653103392, 94832152674093, 94832173598336,
94832173593200, 34359672832, 94832173598336, 94832173598312}}}}
send_ready_for_query = false
disable_idle_in_transaction_timeout = false
__func__ = "PostgresMain"
#20 0x0000563fd49f895b in BackendRun (port=0x563fd6078350) at postmaster.c:4536
av = 0x563fd607ec38
maxac = 2
ac = 1
i = 1
__func__ = "BackendRun"
#21 0x0000563fd49f80d3 in BackendStartup (port=0x563fd6078350) at
postmaster.c:4220
bn = 0x563fd6077350
pid = 0
__func__ = "BackendStartup"
#22 0x0000563fd49f4526 in ServerLoop () at postmaster.c:1739
port = 0x563fd6078350
i = 2
rmask = {fds_bits = {256, 0 <repeats 15 times>}}
selres = 1
now = 1601137237
readmask = {fds_bits = {448, 0 <repeats 15 times>}}
nSockets = 9
last_lockfile_recheck_time = 1601137207
last_touch_time = 1601136306
__func__ = "ServerLoop"
#23 0x0000563fd49f3d75 in PostmasterMain (argc=3, argv=0x563fd604dd50) at
postmaster.c:1412
opt = -1
status = 0
userDoption = 0x563fd60701e0 "data"
listen_addr_saved = true
i = 64
output_config_variable = 0x0
__func__ = "PostmasterMain"
#24 0x0000563fd48f69ac in main (argc=3, argv=0x563fd604dd50) at main.c:210
do_check_root = true
