You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Query planner throws an error for the following query:
SELECTkendat.cibin, kendat.dal, kendat.cutdatFROM kendat
WHEREkendat.dinr=57ANDkendat.kind=179ANDkendat.cibin NOT IN (SELECT DISTINCTtimdat.cibinFROM timdat
WHEREtimdat.dinr=57ANDkendat.kind=179ANDtimdat.lormnr='E1D299B260FB1C1A2A0196A6AADC039B');
ERROR: unsupported subplan type for SkipScan: Result
ERROR does not occur without timescaledb or disabled SkipScan.
TimescaleDB version affected
2.15.2
PostgreSQL version used
15.7
What operating system did you use?
Debian 12.2.0-14
What installation method did you use?
Deb/Apt
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
(gdb) b errfinish
Breakpoint 1 at 0x558ae4274e50: file ./build/../src/backend/utils/error/elog.c, line 510.
(gdb) bt
#0 0x00007f17a7929de3 in epoll_wait (epfd=4, events=0x558ae533e380, maxevents=1, timeout=timeout@entry=-1) at ../sysdeps/unix/sysv/linux/epoll_wait.c:30#1 0x0000558ae411f0ae in WaitEventSetWaitBlock (nevents=1, occurred_events=0x7ffe65a0c5d0, cur_timeout=-1, set=0x558ae533e308) at ./build/../src/backend/storage/ipc/latch.c:1495#2 WaitEventSetWait (set=0x558ae533e308, timeout=timeout@entry=-1, occurred_events=occurred_events@entry=0x7ffe65a0c640, nevents=nevents@entry=1, wait_event_info=wait_event_info@entry=100663296) at ./build/../src/backend/storage/ipc/latch.c:1441#3 0x0000558ae4016ca5 in secure_read (port=0x558ae5337440, ptr=0x558ae4550380 <PqRecvBuffer>, len=8192) at ./build/../src/backend/libpq/be-secure.c:186#4 0x0000558ae401d857 in pq_recvbuf () at ./build/../src/backend/libpq/pqcomm.c:955#5 0x0000558ae401e4d5 in pq_getbyte () at ./build/../src/backend/libpq/pqcomm.c:1001#6 0x0000558ae41439f1 in SocketBackend (inBuf=0x7ffe65a0c830) at ./build/../src/backend/tcop/postgres.c:351#7 ReadCommand (inBuf=0x7ffe65a0c830) at ./build/../src/backend/tcop/postgres.c:474#8 PostgresMain (dbname=<optimized out>, username=<optimized out>) at ./build/../src/backend/tcop/postgres.c:4530#9 0x0000558ae40c16e1 in BackendRun (port=0x558ae5337440) at ./build/../src/backend/postmaster/postmaster.c:4514#10 BackendStartup (port=0x558ae5337440) at ./build/../src/backend/postmaster/postmaster.c:4242#11 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1809#12 0x0000558ae40c26b5 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0x558ae52aaf20) at ./build/../src/backend/postmaster/postmaster.c:1481#13 0x0000558ae3e28f1b in main (argc=5, argv=0x558ae52aaf20) at ./build/../src/backend/main/main.c:202
(gdb) cont
Continuing.
Breakpoint 1, errfinish (filename=0x7f179ba45000 "./tsl/src/nodes/skip_scan/planner.c", lineno=107, funcname=0x7f179ba45030 <__func__.0>"skip_scan_plan_create") at ./build/../src/backend/utils/error/elog.c:510
510 ./build/../src/backend/utils/error/elog.c: No such file or directory.
(gdb) bt
#0 errfinish (filename=0x7f179ba45000 "./tsl/src/nodes/skip_scan/planner.c", lineno=107, funcname=0x7f179ba45030 <__func__.0> "skip_scan_plan_create") at ./build/../src/backend/utils/error/elog.c:510#1 0x00007f179b9f2d66 in skip_scan_plan_create (root=<optimized out>, relopt=<optimized out>, best_path=0x558ae542dee8, tlist=0x558ae543cbe0, clauses=<optimized out>, custom_plans=0x558ae543d4f0) at ./tsl/src/nodes/skip_scan/planner.c:107#2 0x0000558ae407996e in create_customscan_plan (scan_clauses=<optimized out>, tlist=0x558ae543cbe0, best_path=0x558ae542dee8, root=0x558ae54a7a58) at ./build/../src/backend/optimizer/plan/createplan.c:4272#3 create_scan_plan (root=0x558ae54a7a58, best_path=0x558ae542dee8, flags=<optimized out>) at ./build/../src/backend/optimizer/plan/createplan.c:773#4 0x0000558ae4077140 in create_upper_unique_plan (flags=1, best_path=0x558ae543c600, root=0x558ae54a7a58) at ./build/../src/backend/optimizer/plan/createplan.c:2277#5 create_plan_recurse (root=0x558ae54a7a58, best_path=0x558ae543c600, flags=1) at ./build/../src/backend/optimizer/plan/createplan.c:470#6 0x0000558ae4078bef in create_plan (root=root@entry=0x558ae54a7a58, best_path=<optimized out>) at ./build/../src/backend/optimizer/plan/createplan.c:347#7 0x0000558ae408b7f7 in make_subplan (isTopQual=false, testexpr=0x558ae54a7078, subLinkId=<optimized out>, subLinkType=<optimized out>, orig_subquery=<optimized out>, root=<optimized out>) at ./build/../src/backend/optimizer/plan/subselect.c:236#8 process_sublinks_mutator (node=<optimized out>, context=<optimized out>) at ./build/../src/backend/optimizer/plan/subselect.c:1945#9 0x0000558ae4038d0f in expression_tree_mutator (node=node@entry=0x558ae54a6c28, mutator=mutator@entry=0x558ae408b550 <process_sublinks_mutator>, context=context@entry=0x7ffe65a0b7f0) at ./build/../src/backend/nodes/nodeFuncs.c:3171#10 0x0000558ae408b663 in process_sublinks_mutator (node=0x558ae54a6c28, context=0x7ffe65a0b900) at ./build/../src/backend/optimizer/plan/subselect.c:2047#11 0x0000558ae403890c in expression_tree_mutator (node=node@entry=0x558ae54a6bf8, mutator=mutator@entry=0x558ae408b550 <process_sublinks_mutator>, context=context@entry=0x7ffe65a0b900) at ./build/../src/backend/nodes/nodeFuncs.c:3314#12 0x0000558ae408b663 in process_sublinks_mutator (node=0x558ae54a6bf8, context=context@entry=0x7ffe65a0b9d0) at ./build/../src/backend/optimizer/plan/subselect.c:2047#13 0x0000558ae408b61b in process_sublinks_mutator (node=<optimized out>, context=context@entry=0x7ffe65a0ba30) at ./build/../src/backend/optimizer/plan/subselect.c:2011#14 0x0000558ae408d07d in SS_process_sublinks (root=root@entry=0x558ae5338f38, expr=<optimized out>, isQual=isQual@entry=true) at ./build/../src/backend/optimizer/plan/subselect.c:1918#15 0x0000558ae407dfdb in preprocess_expression (root=0x558ae5338f38, expr=<optimized out>, kind=0) at ./build/../src/backend/optimizer/plan/planner.c:1154#16 0x0000558ae407e0fb in preprocess_qual_conditions (root=root@entry=0x558ae5338f38, jtnode=0x558ae5339398) at ./build/../src/backend/optimizer/plan/planner.c:1199#17 0x0000558ae4085bce in subquery_planner (glob=glob@entry=0x558ae5436638, parse=parse@entry=0x558ae5433c38, parent_root=parent_root@entry=0x0, hasRecursion=hasRecursion@entry=false, tuple_fraction=tuple_fraction@entry=0) at ./build/../src/backend/optimizer/plan/planner.c:815#18 0x0000558ae40866be in standard_planner (parse=parse@entry=0x558ae5433c38, query_string=query_string@entry=0x558ae52ec688 "EXPLAIN SELECT kendat.cibin, kendat.dal, kendat.cutdat\n FROM kendat", ' ' <repeats 45 times>, "\n WHERE kendat.dinr = 57\n AND kendat.kind = 179", ' ' <repeats 14 times>, "\n AND kendat.cib"..., cursorOptions=cursorOptions@entry=2048, boundParams=boundParams@entry=0x0) at ./build/../src/backend/optimizer/plan/planner.c:408#19 0x00007f179bac6e38 in timescaledb_planner (parse=0x558ae5433c38, query_string=0x558ae52ec688 "EXPLAIN SELECT kendat.cibin, kendat.dal, kendat.cutdat\n FROM kendat", ' ' <repeats 45 times>, "\n WHERE kendat.dinr = 57\n AND kendat.kind = 179", ' ' <repeats 14 times>, "\n AND kendat.cib"..., cursor_opts=2048, bound_params=0x0) at ./src/planner/planner.c:543#20 0x0000558ae4142b20 in pg_plan_query (querytree=querytree@entry=0x558ae5433c38, query_string=query_string@entry=0x558ae52ec688 "EXPLAIN SELECT kendat.cibin, kendat.dal, kendat.cutdat\n FROM kendat", ' ' <repeats 45 times>, "\n WHERE kendat.dinr = 57\n AND kendat.kind = 179", ' ' <repeats 14 times>, "\n AND kendat.cib"..., cursorOptions=<optimized out>, boundParams=boundParams@entry=0x0) at ./build/../src/backend/tcop/postgres.c:883#21 0x0000558ae3f6f795 in ExplainOneQuery (query=0x558ae5433c38, cursorOptions=<optimized out>, into=0x0, es=0x558ae548db38, queryString=0x558ae52ec688 "EXPLAIN SELECT kendat.cibin, kendat.dal, kendat.cutdat\n FROM kendat", ' ' <repeats 45 times>, "\n WHERE kendat.dinr = 57\n AND kendat.kind = 179", ' ' <repeats 14 times>, "\n AND kendat.cib"..., params=0x0, queryEnv=<optimized out>) at ./build/../src/backend/commands/explain.c:397#22 0x0000558ae3f6ff0f in ExplainQuery (pstate=<optimized out>, stmt=<optimized out>, params=<optimized out>, dest=<optimized out>) at ./build/../src/backend/commands/explain.c:281#23 0x0000558ae414835c in standard_ProcessUtility (pstmt=0x558ae53d6398, queryString=0x558ae52ec688 "EXPLAIN SELECT kendat.cibin, kendat.dal, kendat.cutdat\n FROM kendat", ' ' <repeats 45 times>, "\n WHERE kendat.dinr = 57\n AND kendat.kind = 179", ' ' <repeats 14 times>, "\n AND kendat.cib"..., readOnlyTree=<optimized out>, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=<optimized out>, qc=<optimized out>) at ./build/../src/backend/tcop/utility.c:870#24 0x00007f179ba9de42 in prev_ProcessUtility (args=args@entry=0x7ffe65a0c340) at ./src/process_utility.c:97#25 0x00007f179ba9e964 in timescaledb_ddl_command_start (pstmt=0x558ae53d6398, query_string=<optimized out>, readonly_tree=<optimized out>, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=<optimized out>, dest=<optimized out>, completion_tag=<optimized out>) at ./src/process_utility.c:4468#26 0x0000558ae4146671 in PortalRunUtility (portal=portal@entry=0x558ae5384658, pstmt=0x558ae53d6398, isTopLevel=isTopLevel@entry=true, setHoldSnapshot=setHoldSnapshot@entry=true, dest=dest@entry=0x558ae5423e50, qc=qc@entry=0x7ffe65a0c450) at ./build/../src/backend/tcop/pquery.c:1158#27 0x0000558ae4146a37 in FillPortalStore (portal=portal@entry=0x558ae5384658, isTopLevel=isTopLevel@entry=true) at ./build/../src/backend/tcop/pquery.c:1031#28 0x0000558ae4146d7f in PortalRun (portal=portal@entry=0x558ae5384658, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x558ae53d6428, altdest=altdest@entry=0x558ae53d6428, qc=<optimized out>) at ./build/../src/backend/tcop/pquery.c:763#29 0x0000558ae4142fbd in exec_simple_query (query_string=<optimized out>) at ./build/../src/backend/tcop/postgres.c:1250#30 0x0000558ae4143cf9 in PostgresMain (dbname=<optimized out>, username=<optimized out>) at ./build/../src/backend/tcop/postgres.c:4598#31 0x0000558ae40c16e1 in BackendRun (port=0x558ae5337440) at ./build/../src/backend/postmaster/postmaster.c:4514#32 BackendStartup (port=0x558ae5337440) at ./build/../src/backend/postmaster/postmaster.c:4242#33 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1809#34 0x0000558ae40c26b5 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0x558ae52aaf20) at ./build/../src/backend/postmaster/postmaster.c:1481#35 0x0000558ae3e28f1b in main (argc=5, argv=0x558ae52aaf20) at ./build/../src/backend/main/main.c:202
(gdb) cont
Continuing.
How can we reproduce the bug?
CREATE TABLE IF NOT EXISTS timdat (
dinr integer NOT NULL DEFAULT 0,
lormnr character(32) NOT NULL DEFAULT ''::character(1),
cibin character(16) NOT NULL DEFAULT ''::character(1),
tloan character(12) NOT NULL DEFAULT ''::character(1),
dust_timdat character(48) NOT NULL DEFAULT ''::character(1),
PRIMARY KEY (dinr, lormnr, cibin)
);
INSERT INTO timdat (dinr,lormnr,cibin,tloan,dust_timdat)
SELECT
floor(random() * 255 + 1)::int,
upper(substr(md5(random()::text), 1, 32)),
upper(substr(md5(random()::text), 1, 10)),
upper(substr(md5(random()::text), 1, 10)),
upper(substr(md5(random()::text), 1, 48))
FROM generate_series(1,3000) n
RETURNING *;
CREATE TABLE IF NOT EXISTS kendat (
dinr integer NOT NULL DEFAULT 0,
kind integer NOT NULL DEFAULT 0,
dal character(15) NOT NULL DEFAULT ''::character(1),
cibin character(10) NOT NULL DEFAULT ''::character(1),
cutdat character(10) NOT NULL DEFAULT ''::character(1),
PRIMARY KEY (dinr, cibin)
);
CREATE INDEX kendat_dinr_cutdat_idx ON kendat (dinr, cutdat);
INSERT INTO kendat (dinr,kind,dal,cibin,cutdat)
SELECT
floor(random() * 255 + 1)::int,
floor(random() * 255 + 1)::int,
upper(substr(md5(random()::text), 1, 15)),
upper(substr(md5(random()::text), 1, 10)),
upper(substr(md5(random()::text), 1, 10))
FROM generate_series(1,3000) n
RETURNING *;
execute query/explain
We tried reordering the Index for the DISTINCT column without success.
The text was updated successfully, but these errors were encountered:
What type of bug is this?
Unexpected error
What subsystems and features are affected?
Query planner
What happened?
Query planner throws an error for the following query:
ERROR does not occur without timescaledb or disabled SkipScan.
TimescaleDB version affected
2.15.2
PostgreSQL version used
15.7
What operating system did you use?
Debian 12.2.0-14
What installation method did you use?
Deb/Apt
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
How can we reproduce the bug?
execute query/explain
We tried reordering the Index for the DISTINCT column without success.
The text was updated successfully, but these errors were encountered: