search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
8.68k stars 1.04k forks source link

[YSQL][randgen] Server crash (EXC_BAD_ACCESS, address=0x0) at postgres`gather_readnext + 141 at .../src/postgres/src/backend/executor/nodeGather.c:314 #21871

Open mtakahar opened 3 months ago

mtakahar commented 3 months ago

Jira Link: DB-10769

Description

Problem

YB test# /*+ IndexScanRegexp(.*) */ SELECT table1.pk FROM t1  AS table1  WHERE col_varchar_nokey  IN (  SELECT SUBQUERY3_t1 .col_varchar_key  FROM t1  AS SUBQUERY3_t1  JOIN t1  AS SUBQUERY3_t2  ON ( SUBQUERY3_t2 .col_varchar_key  = SUBQUERY3_t2 .col_varchar_key  )  JOIN t2  AS SUBQUERY3_t3  ON SUBQUERY3_t3 .pk  = SUBQUERY3_t1 .col_int_key  OR SUBQUERY3_t2 .pk  <  ANY (  SELECT  20  UNION  ALL  SELECT  5  )  WHERE SUBQUERY3_t2 .pk  >= table1 .pk  AND SUBQUERY3_t1 .col_int_key  =  5  LIMIT  1  )   ;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
YB > \c
No database connection exists to re-use parameters from
YB > \q

The execution plan:

                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on t1 table1  (cost=0.00..1000105.24 rows=1000 width=4)
   Filter: (SubPlan 2)
   SubPlan 2
     ->  Limit  (cost=1000.00..1000.00 rows=1 width=8)
           ->  Nested Loop  (cost=1000.00..15869.61 rows=5005000 width=8)
                 Join Filter: ((subquery3_t3.pk = subquery3_t1.col_int_key) OR (SubPlan 1))
                 ->  Index Scan using t1_pkey on t1 subquery3_t2  (cost=0.00..4.11 rows=1 width=4)
                       Index Cond: (pk >= table1.pk)
                       Storage Filter: ((col_varchar_key)::text IS NOT NULL)
                 ->  Nested Loop  (cost=0.00..1365.50 rows=100000 width=16)
                       ->  Seq Scan on t2 subquery3_t3  (cost=0.00..100.00 rows=1000 width=4)
                       ->  Materialize  (cost=0.00..15.75 rows=100 width=12)
                             ->  Index Only Scan using t1_col_varchar_key_col_int_key_col_datetime_key_idx on t1 subquery3_t1  (cost=0.00..15.25 rows=100 width=12)
                                   Index Cond: (col_int_key = 5)
                 SubPlan 1
                   ->  Materialize  (cost=1000.00..1000.24 rows=2 width=4)
                         ->  Gather  (cost=1000.00..1000.23 rows=2 width=4)
                               Workers Planned: 1
                               Single Copy: true
                               ->  Parallel Append  (cost=0.00..0.03 rows=1 width=4)
                                     ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                     ->  Result  (cost=0.00..0.01 rows=1 width=4)
(22 rows)

Back trace:

* thread #1, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=1, address=0x0)
  * frame #0: 0x0000000100be91cd postgres`gather_readnext + 141 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeGather.c:314
    frame #1: 0x0000000100be8fc5 postgres`gather_getnext + 149 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeGather.c:257
    frame #2: 0x0000000100be8c40 postgres`ExecGather + 592 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeGather.c:207
    frame #3: 0x0000000100bfc5c2 postgres`ExecProcNode + 50 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:273
    frame #4: 0x0000000100bfc1d3 postgres`ExecMaterial + 547 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeMaterial.c:134
    frame #5: 0x0000000100c12812 postgres`ExecProcNode + 50 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:273
    frame #6: 0x0000000100c10f2e postgres`ExecScanSubPlan + 2654 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeSubplan.c:327
    frame #7: 0x0000000100c10166 postgres`ExecSubPlan + 486 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeSubplan.c:90
    frame #8: 0x0000000100bb5dca postgres`ExecEvalSubPlan + 58 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execExprInterp.c:3990
    frame #9: 0x0000000100baf89a postgres`ExecInterpExpr + 9290 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execExprInterp.c:1553
    frame #10: 0x0000000100c0878b postgres`ExecEvalExprSwitchContext + 59 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:345
    frame #11: 0x0000000100c08692 postgres`ExecQual + 114 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:414
    frame #12: 0x0000000100c0831a postgres`ExecNestLoop + 874 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeNestloop.c:214
    frame #13: 0x0000000100bf9432 postgres`ExecProcNode + 50 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:273
    frame #14: 0x0000000100bf8a7c postgres`ExecLimit + 412 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeLimit.c:118
    frame #15: 0x0000000100c12812 postgres`ExecProcNode + 50 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:273
    frame #16: 0x0000000100c1074e postgres`ExecScanSubPlan + 638 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeSubplan.c:325
    frame #17: 0x0000000100c10166 postgres`ExecSubPlan + 486 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeSubplan.c:90
    frame #18: 0x0000000100bb5dca postgres`ExecEvalSubPlan + 58 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execExprInterp.c:3990
    frame #19: 0x0000000100baf89a postgres`ExecInterpExpr + 9290 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execExprInterp.c:1553
    frame #20: 0x0000000100bcccfb postgres`ExecEvalExprSwitchContext + 59 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:345
    frame #21: 0x0000000100bcc982 postgres`ExecQual + 114 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:414
    frame #22: 0x0000000100bcc6b7 postgres`ExecScan + 295 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execScan.c:199
    frame #23: 0x0000000100c328c5 postgres`ExecYbSeqScan + 53 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/nodeYbSeqscan.c:251
    frame #24: 0x0000000100bc9b52 postgres`ExecProcNodeFirst + 82 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execProcnode.c:465
    frame #25: 0x0000000100bc21e2 postgres`ExecProcNode + 50 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/include/executor/executor.h:273
    frame #26: 0x0000000100bbc941 postgres`ExecutePlan + 209 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execMain.c:1726
    frame #27: 0x0000000100bbc7f1 postgres`standard_ExecutorRun + 657 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execMain.c:357
    frame #28: 0x000000010556a116 pg_stat_statements.so`pgss_ExecutorRun + 230 at /Users/mtakahara/code/yugabyte-db/src/postgres/contrib/pg_stat_statements/pg_stat_statements.c:1311
    frame #29: 0x000000010311b533 yb_pg_metrics.so`ybpgm_ExecutorRun + 179 at /Users/mtakahara/code/yugabyte-db/src/postgres/contrib/yb_pg_metrics/yb_pg_metrics.c:586
    frame #30: 0x0000000100bbc535 postgres`ExecutorRun + 69 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/executor/execMain.c:295
    frame #31: 0x0000000100e6db85 postgres`PortalRunSelect + 309 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/pquery.c:956
    frame #32: 0x0000000100e6d4a4 postgres`PortalRun + 804 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/pquery.c:785
    frame #33: 0x0000000100e6a8ea postgres`exec_simple_query + 1546 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/postgres.c:1220
    frame #34: 0x0000000100e682a5 postgres`yb_exec_simple_query_impl + 21 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/postgres.c:4759
    frame #35: 0x0000000100e6840a postgres`yb_exec_query_wrapper_one_attempt + 346 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/postgres.c:4727
    frame #36: 0x0000000100e6827a postgres`yb_exec_query_wrapper + 74 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/postgres.c:4751
    frame #37: 0x0000000100e634f5 postgres`yb_exec_simple_query + 69 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/postgres.c:4774
    frame #38: 0x0000000100e61dbe postgres`PostgresMain + 2910 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/tcop/postgres.c:5399
    frame #39: 0x0000000100d7dc45 postgres`BackendRun + 933 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/postmaster/postmaster.c:4736
    frame #40: 0x0000000100d7ccef postgres`BackendStartup + 703 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/postmaster/postmaster.c:4400
    frame #41: 0x0000000100d7b6a0 postgres`ServerLoop + 992 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/postmaster/postmaster.c:1778
    frame #42: 0x0000000100d77c2e postgres`PostmasterMain + 7566 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/postmaster/postmaster.c:1434
    frame #43: 0x0000000100c66e8b postgres`PostgresServerProcessMain + 779 at /Users/mtakahara/code/yugabyte-db/src/postgres/src/backend/main/main.c:234
    frame #44: 0x0000000100c67442 postgres`main + 34
    frame #45: 0x00007ff81338941f dyld`start + 1903
(lldb)

Test Case

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1 (    pk integer NOT NULL,    col_int_key integer,    col_datetime_key timestamp without time zone,    col_varchar_key character varying(1),    col_varchar_nokey character varying(1),    CONSTRAINT t1_pkey PRIMARY KEY(pk ASC));

CREATE TABLE t2 (    pk integer NOT NULL,    col_int_key integer,    col_datetime_key timestamp without time zone,    col_varchar_key character varying(1),    col_varchar_nokey character varying(1),    CONSTRAINT t2_pkey PRIMARY KEY(pk ASC));

COPY t1 (pk, col_int_key, col_datetime_key, col_varchar_key, col_varchar_nokey) FROM stdin;
1   4   1900-01-01 00:00:00 v   v
2   62  1979-01-03 10:33:32.027981  v   v
3   7   2027-11-28 00:50:27.051028  c   c
4   1   2003-10-09 19:53:04.008332  \N  \N
5   0   2027-11-08 21:02:12.009395  x   x
6   7   1974-04-01 00:00:00 i   i
7   7   1900-01-01 00:00:00 e   e
8   1   1973-07-12 00:00:00 p   p
9   7   2006-04-04 01:21:01.040391  s   s
10  1   1900-01-01 00:00:00 j   j
11  5   1984-03-05 03:41:18.061978  z   z
12  2   1989-08-03 11:33:04.049998  c   c
13  0   2010-04-28 21:44:45.050791  a   a
14  1   1972-04-06 00:00:00 q   q
15  8   2017-04-18 00:00:00 y   y
16  1   2009-12-18 19:39:55.005399  \N  \N
17  1   2011-08-01 12:19:39.028493  r   r
18  9   1995-09-25 21:29:06.004058  v   v
19  1   2010-09-20 09:11:48.065041  \N  \N
20  5   2020-03-27 09:32:04.056959  r   r
\.

COPY t2 (pk, col_int_key, col_datetime_key, col_varchar_key, col_varchar_nokey) FROM stdin;
10  8   1981-07-20 00:00:00 x   x
11  7   2013-10-06 17:56:40.056051  d   d
12  1   \N  r   r
13  7   1978-12-23 05:17:49.029955  f   f
14  9   \N  y   y
15  \N  1991-08-16 00:00:00 u   u
16  1   \N  m   m
17  9   1984-02-07 09:50:39.001189  \N  \N
18  2   1979-08-04 23:00:25.012582  o   o
19  9   2005-08-07 12:57:18.026416  w   w
20  2   1900-01-01 00:00:00 m   m
21  4   1987-06-17 01:34:03.065074  q   q
22  0   \N  \N  \N
23  4   1900-01-01 00:00:00 d   d
24  8   \N  g   g
25  \N  2013-08-22 14:10:22.054447  x   x
26  \N  1981-01-07 12:15:05.055865  f   f
27  0   2004-07-18 00:00:00 p   p
28  \N  1986-03-24 07:33:11.043368  j   j
29  8   2014-06-13 23:19:49.0183    c   c
\.

CREATE INDEX t1_col_datetime_key_idx ON t1 (col_datetime_key ASC);
CREATE INDEX t1_col_int_key_col_varchar_key_idx ON t1 (col_int_key ASC, col_varchar_key ASC);
CREATE INDEX t1_col_varchar_key_col_int_key_col_datetime_key_idx ON t1 (col_varchar_key ASC, col_int_key ASC, col_datetime_key ASC);
CREATE INDEX t2_col_datetime_key_idx ON t2 (col_datetime_key ASC);
CREATE INDEX t2_col_int_key_col_varchar_key_idx ON t2 (col_int_key ASC, col_varchar_key ASC);

CREATE INDEX t2_col_varchar_key_col_int_key_col_datetime_key_idx ON t2 (col_varchar_key ASC, col_int_key ASC, col_datetime_key ASC);

/*+ IndexScanRegexp(.*) */ SELECT table1.pk FROM t1  AS table1  WHERE col_varchar_nokey  IN (  SELECT SUBQUERY3_t1 .col_varchar_key  FROM t1  AS SUBQUERY3_t1  JOIN t1  AS SUBQUERY3_t2  ON ( SUBQUERY3_t2 .col_varchar_key  = SUBQUERY3_t2 .col_varchar_key  )  JOIN t2  AS SUBQUERY3_t3  ON SUBQUERY3_t3 .pk  = SUBQUERY3_t1 .col_int_key  OR SUBQUERY3_t2 .pk  <  ANY (  SELECT  20  UNION  ALL  SELECT  5  )  WHERE SUBQUERY3_t2 .pk  >= table1 .pk  AND SUBQUERY3_t1 .col_int_key  =  5  LIMIT  1  )   ;

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

andrei-mart commented 3 months ago

The same root cause as in #21733, pg_hint_plan messes up ParallelAppend. Won't happen while ParallelAppend is disabled, likely one solution will close all the issues.