[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 042ad2c4c155f97356f80f8e14637195d207a91e, 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 bd9950ebe8fb8e6bb551149c61520446f6d8ccd0. 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