[YSQL] unrecognized node type: 8388615

[FranckPachot]

Jira Link: DB-12381

Description

Error encountered in orthanc reported on forum:

CREATE TABLE IF NOT EXISTS GlobalIntegers(
       key INTEGER PRIMARY KEY,
       value BIGINT);

CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
    key INTEGER,
    value BIGINT);

WITH deleted_rows AS (
      DELETE FROM GlobalIntegersChanges
      WHERE GlobalIntegersChanges.key = 0
      RETURNING value
  )
  UPDATE GlobalIntegers
  SET value = value + (
      SELECT COALESCE(SUM(value), 0)
      FROM deleted_rows
  )
  WHERE GlobalIntegers.key = 0
;

fails with:

ERROR:  XX000: unrecognized node type: 8388615
LOCATION:  exprType, nodeFuncs.c:267

The plan is:

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Update on public.globalintegers  (cost=125.01..129.13 rows=1 width=44)
   CTE deleted_rows
     ->  Delete on public.globalintegerschanges  (cost=0.00..102.50 rows=1000 width=44)
           Output: globalintegerschanges.value
           ->  Seq Scan on public.globalintegerschanges  (cost=0.00..102.50 rows=1000 width=44)
                 Output: globalintegerschanges.key, globalintegerschanges.value, globalintegerschanges.ybctid
                 Storage Filter: (globalintegerschanges.key = 0)
   InitPlan 2 (returns $2)
     ->  Aggregate  (cost=22.50..22.51 rows=1 width=32)
           Output: COALESCE(sum(deleted_rows.value), '0'::numeric)
           ->  CTE Scan on deleted_rows  (cost=0.00..20.00 rows=1000 width=8)
                 Output: deleted_rows.value
   ->  Result  (cost=0.00..4.12 rows=1 width=44)
         Output: 0, 0
(14 rows)

Two ways to workaround:

• cast from numeric (result of sum()) to bigint The plan is:

                                                QUERY PLAN
  --------------------------------------------------------------------------------------------------------------
  Update on public.globalintegers  (cost=125.02..129.13 rows=1 width=44)
  CTE deleted_rows
   ->  Delete on public.globalintegerschanges  (cost=0.00..102.50 rows=1000 width=44)
         Output: globalintegerschanges.value
         ->  Seq Scan on public.globalintegerschanges  (cost=0.00..102.50 rows=1000 width=44)
               Output: globalintegerschanges.key, globalintegerschanges.value, globalintegerschanges.ybctid
               Storage Filter: (globalintegerschanges.key = 0)
  InitPlan 2 (returns $2)
   ->  Aggregate  (cost=22.50..22.52 rows=1 width=8)
         Output: COALESCE((sum(deleted_rows.value))::bigint, '0'::bigint)
         ->  CTE Scan on deleted_rows  (cost=0.00..20.00 rows=1000 width=8)
               Output: deleted_rows.value
  ->  Result  (cost=0.00..4.12 rows=1 width=44)
       Output: 0, 0
  (14 rows)

• disable pushdowns with set yb_enable_expression_pushdown to off; The plan is:

                                                QUERY PLAN
  --------------------------------------------------------------------------------------------------------------
  Update on public.globalintegers  (cost=125.01..129.13 rows=1 width=44)
  CTE deleted_rows
   ->  Delete on public.globalintegerschanges  (cost=0.00..102.50 rows=1000 width=44)
         Output: globalintegerschanges.value
         ->  Seq Scan on public.globalintegerschanges  (cost=0.00..102.50 rows=1000 width=44)
               Output: globalintegerschanges.key, globalintegerschanges.value, globalintegerschanges.ybctid
               Filter: (globalintegerschanges.key = 0)
  InitPlan 2 (returns $2)
   ->  Aggregate  (cost=22.50..22.51 rows=1 width=32)
         Output: COALESCE(sum(deleted_rows.value), '0'::numeric)
         ->  CTE Scan on deleted_rows  (cost=0.00..20.00 rows=1000 width=8)
               Output: deleted_rows.value
  ->  Index Scan using globalintegers_pkey on public.globalintegers  (cost=0.00..4.12 rows=1 width=44)
       Output: globalintegers.key, ((globalintegers.value)::numeric + $2), globalintegers.ybctid
       Index Cond: (globalintegers.key = 0)
  (15 rows)

Issue Type

kind/bug

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

• [X] I confirm this issue does not contain any sensitive information.

[FranckPachot]

Adding link to the orthanc forum: https://discourse.orthanc-server.org/t/deployment-of-orthanc-in-k8s-with-yugabyte/4946/2