sql: generic query plans result in (possibly?) unexpected index out of range errors

[stevendanna]

Describe the problem

When plan_cache_mode is set to force_generic_plan some prepared inserts behave differently when processing a computed column that technically overflows but which can also be evaluated to NULL since one of the inputs are NULL.

At least, that is the nature of the following reproduction:

Setup:

> CREATE TABLE repro1 (col1 INT8 PRIMARY KEY, col2 INT4 NULL, col3 INT4 NULL, col4 INT8 NULL AS ((col1 + col2) + col3) STORED);                                                    
CREATE TABLE

> PREPARE insert_repro1 AS INSERT INTO repro1 (col1, col2, col3) VALUES ($1, $2, $3);                                                                                              
PREPARE

Working insert:

> set plan_cache_mode=auto;                                                                                                                                                        
SET

> EXECUTE insert_repro1(9223372036854775807, 507217562, NULL);                                                                                                                     
INSERT 0 1

Not working:

> truncate repro1;                                                                                                                                                                 
TRUNCATE

> set plan_cache_mode=force_generic_plan;                                                                                                                                          
SET

> EXECUTE insert_repro1(9223372036854775807, 507217562, NULL);                                                                                                                     
ERROR: integer out of range
SQLSTATE: 22003

Jira issue: CRDB-40376

[blathers-crl[bot]]

Hi @stevendanna, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[yuzefovich]

cc @mgartner

[mgartner]

I don't think this is a bug. When a custom query plan is used, the query plan is optimized with known values for the placeholders. The FoldNullBinaryRight normalization rules (see here) performs the following transformation for the computed column expression (col1 + col2) + col3):

(Plus
  (Plus (Const 9223372036854775807) (Const 507217562))
  (Null)
)
=>
NULL

This prevents the execution engine from ever performing the overflowing addition operation.

With a generic query plan, no optimization occurs after the placeholder values are known. Therefore, this same transformation never occurs, and the addition operation overflows.

The optimizer is free to either perform this transformation or not, so both outcomes are valid.

FWIW, this always results in an overflow error in Postgres:

DROP TABLE IF EXISTS t;

CREATE TABLE t (
  col1 INT8 PRIMARY KEY,
  col2 INT4 NULL,
  col3 INT4 NULL,
  col4 INT8 NULL GENERATED ALWAYS AS ((col1 + col2) + col3) STORED
);

PREPARE p AS INSERT INTO t (col1, col2, col3) VALUES ($1, $2, $3);

EXECUTE p(9223372036854775807, 507217562, NULL);
-- psql:tmp.sql:15: ERROR:  22003: bigint out of range
-- LOCATION:  int84pl, int8.c:915

[stevendanna]

👍 Thanks for taking a look and for the detailed explanation.