[YSQL][SQLsmith] Delete statement involving triggers fails assertion in debug build for invalid tupleid

[def-]

Jira Link: DB-1028

Description

SQLsmith found this, the null pointer looks like it will cause problems later on. Can be reproduced:

CREATE TABLE single_row (k int primary key, v1 int, v2 int);
CREATE TABLE client (
	name		text,
	phonenum	int8,
	deadline	date
) WITH (oids=false);
CREATE TABLE table_create_org(
			 col_smallint			SMALLINT,
			 col_integer			INTEGER,
			 col_bigint				BIGINT,
			 col_real					REAL,
			 col_double				DOUBLE PRECISION,
			 col_char					CHARACTER(7),
			 col_varchar			VARCHAR(7),
			 col_text					TEXT,
			 col_bytea				BYTEA,
			 col_timestamp		TIMESTAMP(2),
			 col_timestamp_tz TIMESTAMP WITH TIME ZONE,
			 col_bool					BOOLEAN,
			 col_array_int		INTEGER[],
			 col_array_text		TEXT[],
			 PRIMARY KEY(col_smallint));
CREATE TABLE table_create_ctas_nodata AS SELECT * FROM table_create_org WITH NO DATA;
COPY public.single_row (k, v1, v2) FROM stdin;
1	2	1
\.
ALTER TABLE ONLY public.single_row
    ADD CONSTRAINT single_row_pkey PRIMARY KEY (k);
CREATE TRIGGER single_row_delete_trigger BEFORE DELETE ON public.single_row FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
CREATE TRIGGER single_row_update_trigger BEFORE UPDATE ON public.single_row FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
COPY public.table_create_org (col_smallint, col_integer, col_bigint, col_real, col_double, col_char, col_varchar, col_text, col_bytea, col_timestamp, col_timestamp_tz, col_bool, col_array_int, col_array_text) FROM stdin;
4	4	4	4.4000001	4.40000000000000036	four   	four	four	\\x44f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-04-04 04:04:04.44	2019-04-04 14:04:04.4444+02	t	{4,4,4}	{four,four,four}
14	4	4	4.4000001	4.40000000000000036	four   	four	four	\\x44f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-04-04 04:04:04.44	2019-04-04 14:04:04.4444+02	t	{4,4,4}	{four,four,four}
1	1	1	1.10000002	1.10000000000000009	one    	one	one	\\x11f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-01-01 01:01:01.11	2019-01-01 10:01:01.1111+01	t	{1,1,1}	{one,one,one}
13	3	3	3.29999995	3.29999999999999982	three  	three	three	\\x33f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-03-03 03:03:03.33	2019-03-03 12:03:03.3333+01	t	{3,3,3}	{three,three,three}
2	2	2	2.20000005	2.20000000000000018	two    	two	two	\\x22f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-02-02 02:02:02.22	2019-02-02 11:02:02.2222+01	t	{2,2,2}	{two,two,two}
7	7	7	7.69999981	7.70000000000000018	seven  	seven	seven	\\x77f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-07-07 07:07:07.78	2019-07-07 17:07:07.7777+02	t	{7,7,7}	{seven,seven,seven}
11	1	1	1.10000002	1.10000000000000009	one    	one	one	\\x11f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-01-01 01:01:01.11	2019-01-01 10:01:01.1111+01	t	{1,1,1}	{one,one,one}
15	5	5	5.5	5.5	five   	five	five	\\x55f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-05-05 05:05:05.56	2019-05-05 15:05:05.5555+02	t	{5,5,5}	{five,five,five}
3	3	3	3.29999995	3.29999999999999982	three  	three	three	\\x33f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-03-03 03:03:03.33	2019-03-03 12:03:03.3333+01	t	{3,3,3}	{three,three,three}
8	8	8	8.80000019	8.80000000000000071	eight  	eight	eight	\\x88f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-08-08 08:08:08.89	2019-08-08 18:08:08.8888+02	t	{8,8,8}	{eight,eight,eight}
5	5	5	5.5	5.5	five   	five	five	\\x55f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-05-05 05:05:05.56	2019-05-05 15:05:05.5555+02	t	{5,5,5}	{five,five,five}
16	6	6	6.5999999	6.59999999999999964	six    	six	six	\\x66f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-06-06 06:06:06.67	2019-06-06 16:06:06.6666+02	t	{6,6,6}	{six,six,six}
6	6	6	6.5999999	6.59999999999999964	six    	six	six	\\x66f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-06-06 06:06:06.67	2019-06-06 16:06:06.6666+02	t	{6,6,6}	{six,six,six}
19	9	9	9.89999962	9.90000000000000036	nine   	nine	nine	\\x99f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-09-09 09:09:10	2019-09-09 19:09:09.9999+02	t	{9,9,9}	{nine,nine,nine}
9	9	9	9.89999962	9.90000000000000036	nine   	nine	nine	\\x99f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-09-09 09:09:10	2019-09-09 19:09:09.9999+02	t	{9,9,9}	{nine,nine,nine}
12	2	2	2.20000005	2.20000000000000018	two    	two	two	\\x22f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-02-02 02:02:02.22	2019-02-02 11:02:02.2222+01	t	{2,2,2}	{two,two,two}
18	8	8	8.80000019	8.80000000000000071	eight  	eight	eight	\\x88f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-08-08 08:08:08.89	2019-08-08 18:08:08.8888+02	t	{8,8,8}	{eight,eight,eight}
17	7	7	7.69999981	7.70000000000000018	seven  	seven	seven	\\x77f1e2d3c4b5a6079889706a5b4c3d2e1f	2019-07-07 07:07:07.78	2019-07-07 17:07:07.7777+02	t	{7,7,7}	{seven,seven,seven}
\.

delete from public.single_row
where 
case when EXISTS (
      select  
          95 as c0
        from 
          public.client as ref_0
        where cast(null as point) <@ cast(null as line)) then case when (cast(null as tsquery) = case when false then cast(null as tsquery) else cast(null as tsquery) end
            ) 
        and (cast(null as timetz) < cast(null as timetz)) then (select col_double from public.table_create_org limit 1 offset 3)
         else (select col_double from public.table_create_org limit 1 offset 3)
         end
       else case when (cast(null as tsquery) = case when false then cast(null as tsquery) else cast(null as tsquery) end
            ) 
        and (cast(null as timetz) < cast(null as timetz)) then (select col_double from public.table_create_org limit 1 offset 3)
         else (select col_double from public.table_create_org limit 1 offset 3)
         end
       end
     >= cast(coalesce(pg_catalog.pg_stat_get_checkpoint_write_time(),
    (select col_double from public.table_create_ctas_nodata limit 1 offset 2)
      ) as float8)
returning 
  public.single_row.v2 as c0, 
  public.single_row.k as c1, 
  public.single_row.v2 as c2, 
  public.single_row.v2 as c3;

Failure in tserver logs:

TRAP: FailedAssertion("!(((const void*)(fdw_trigtuple) != ((void*)0)) ^ ((_Bool) (((const void*)(tupleid) != ((void*)0)) && ((tupleid)->ip_posid != 0))))", File: "../../../../../../src/postgres/src/backend/commands/trigger.c", Line: 2752)
2022-02-01 16:06:31.415 CET [52434] LOG:  server process (PID 86766) was terminated by signal 6: Abort trap
2022-02-01 16:06:31.415 CET [52434] DETAIL:  Failed process was running: delete from public.single_row
[...]

[tedyu]

Which build did you use ?
As of commit 042ad2c, I don't see assertion in debug build:

yugabyte=# delete from public.single_row
yugabyte-# where
yugabyte-# case when EXISTS (
yugabyte(#       select
yugabyte(#           95 as c0
yugabyte(#         from
yugabyte(#           public.client as ref_0
yugabyte(#         where cast(null as point) <@ cast(null as line)) then case when (cast(null as tsquery) = case when false then cast(null as tsquery) else cast(null as tsquery) end
yugabyte(#             )
yugabyte-#         and (cast(null as timetz) < cast(null as timetz)) then (select col_double from public.table_create_org limit 1 offset 3)
yugabyte-#          else (select col_double from public.table_create_org limit 1 offset 3)
yugabyte-#          end
yugabyte-#        else case when (cast(null as tsquery) = case when false then cast(null as tsquery) else cast(null as tsquery) end
yugabyte(#             )
yugabyte-#         and (cast(null as timetz) < cast(null as timetz)) then (select col_double from public.table_create_org limit 1 offset 3)
yugabyte-#          else (select col_double from public.table_create_org limit 1 offset 3)
yugabyte-#          end
yugabyte-#        end
yugabyte-#      >= cast(coalesce(pg_catalog.pg_stat_get_checkpoint_write_time(),
yugabyte(#     (select col_double from public.table_create_ctas_nodata limit 1 offset 2)
yugabyte(#       ) as float8)
yugabyte-# returning
yugabyte-#   public.single_row.v2 as c0,
yugabyte-#   public.single_row.k as c1,
yugabyte-#   public.single_row.v2 as c2,
yugabyte-#   public.single_row.v2 as c3;
 c0 | c1 | c2 | c3
----+----+----+----
(0 rows)

DELETE 0

[def-]

Build is bd9950e. I guess these triggers are also important, especially since it fails in trigger.c:

COPY public.single_row (k, v1, v2) FROM stdin;
1	2	1
\.
ALTER TABLE ONLY public.single_row
    ADD CONSTRAINT single_row_pkey PRIMARY KEY (k);
CREATE TRIGGER single_row_delete_trigger BEFORE DELETE ON public.single_row FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
CREATE TRIGGER single_row_update_trigger BEFORE UPDATE ON public.single_row FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();

[def-]

The inserted data also mattered, added it now, can reproduce locally on a clean DB.

[tedyu]

With one row in the table:

yugabyte=# select * from single_row;
 k | v1 | v2
---+----+----
 1 |  2 |  1
(1 row)

I added two triggers and populated table_create_org table.
There is crash in debug build for the DELETE statement.

[def-]

Just to make sure, but with the full command including the COPY public.table_create_org you can reproduce it?

[tedyu]

pg_catalog.pg_stat_get_checkpoint_write_time()

The above is not relevant to YB.
Can you trim the query a little bit by dropping non-YB constructs ?

[def-]

If these constructs cause crashes, then we should disable them. I'd have to change SQLsmith to exclude pg_* functions, but this seems like we'd just hide the existing problems. (Haven't checked if it's actually related to this functino)

[tedyu]

Without accessing catalog, the following query crashes:

delete from public.single_row
where
case when EXISTS (
      select
          95 as c0
        from
          public.client as ref_0
        where cast(null as point) <@ cast(null as line)) then case when (cast(null as tsquery) = case when false then cast(null as tsquery) else cast(null as tsquery) end
            )
        and (cast(null as timetz) < cast(null as timetz)) then (select col_double from public.table_create_org limit 1 offset 3)
         else (select col_double from public.table_create_org limit 1 offset 3)
         end
       else case when (cast(null as tsquery) = case when false then cast(null as tsquery) else cast(null as tsquery) end
            )
        and (cast(null as timetz) < cast(null as timetz)) then (select col_double from public.table_create_org limit 1 offset 3)
         else (select col_double from public.table_create_org limit 1 offset 3)
         end
       end
     >= cast(coalesce(0,
    (select col_double from public.table_create_ctas_nodata limit 1 offset 2)
      ) as float8)
returning
  public.single_row.v2 as c0,
  public.single_row.k as c1,
  public.single_row.v2 as c2,
  public.single_row.v2 as c3;

I suggest changing the title of this issue to:

Delete statement involving triggers fails assertion in debug build for invalid tupleid