search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
16.82k stars 852 forks source link

ERROR: unsupported subplan type for SkipScan: Result #7024

Open cd-gumo opened 3 weeks ago

cd-gumo commented 3 weeks ago

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:

SELECT kendat.cibin, kendat.dal, kendat.cutdat
    FROM kendat                                             
   WHERE kendat.dinr = 57
   AND kendat.kind = 179              
     AND kendat.cibin NOT IN (SELECT DISTINCT timdat.cibin
    FROM timdat                                         
   WHERE timdat.dinr =  57
   AND kendat.kind = 179              
     AND timdat.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.

antekresic commented 3 weeks ago

I am kind of confused why you have this filter in the subquery since you have the exact same filter in the top query

   AND kendat.kind = 179

Removing that resolves the issue, does that give you the expected results? I tried this on your repro case and get no rows returned.

cd-gumo commented 3 weeks ago

You are right, this filter is useless and can be omitted. Query is successful without this filter, thank you.

Still, it is unexpected that timescaledb results in an error here with valid SQL syntax where PostgreSQL does not. Therefore I consider this as a bug.

antekresic commented 3 weeks ago

I'd agree, will put it on the backlog.

Thanks you for your report!