matrixorigin / matrixorigin/matrixone

[Bug]: LOAD DATA writes NULL to a stored generated foreign-key column

Open
#28,054 1 comment 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## Description

`LOAD DATA INLINE` with an explicit list of writable base columns stores `NULL` in a stored generated column when that generated column is a child foreign key. With a JSON document as the source expression, the document is stored correctly but its materialized foreign-key value is not recomputed. The resulting `NULL` bypasses the foreign-key check and can leave a dangling logical reference after the parent row is deleted.

## Environment

- Branch: `main`
- Commit: `95d04586eb80864499d0fde27aeb912b463ae4cd`
- Deployment: local single-CN MatrixOne

## Steps to reproduce

```sql
CREATE DATABASE load_generated_fk_lifecycle;
USE load_generated_fk_lifecycle;

CREATE TABLE parent(id INT PRIMARY KEY);
INSERT INTO parent VALUES (1);

CREATE TABLE child(
id INT PRIMARY KEY,
doc JSON,
parent_id INT GENERATED ALWAYS AS
(CAST(doc ->> '$.parent_id' AS SIGNED)) STORED,
FOREIGN KEY(parent_id) REFERENCES parent(id)
);

LOAD DATA INLINE FORMAT='jsonline',
DATA='{"id":10,"doc":{"parent_id":1,"label":"loaded"}}',
jsontype='object'
INTO TABLE child (id, doc);

SELECT id, doc ->> '$.parent_id', parent_id FROM child;
DELETE FROM parent WHERE id = 1;
SELECT id, doc ->> '$.parent_id', parent_id FROM child;

UPDATE child
SET doc = JSON_SET(doc, '$.label', 'updated')
WHERE id = 10;
```

## Actual behavior

The load succeeds, but the first read is:

```text
10 | 1 | NULL
```

`DELETE FROM parent WHERE id = 1` succeeds, leaving the child document logically referring to the deleted parent while its stored FK key remains `NULL`. The later unrelated JSON update returns `ERROR 1452` and leaves the row unchanged:

```text
10 | 1 | NULL | loaded
```

Without an explicit column list, the same JSONLine load fails before writing any row with `the table column is larger than input data column`.

## Expected behavior

The generated value must be computed as `1` during the load, so the foreign-key check is enforced and deletion of parent `1` is rejected while the child exists. The no-column-list form should also load the writable columns without treating generated/internal columns as input fields.

## Stability and controls

- Reproducer: 3/3 runs produced `doc.parent_id = 1` and stored `parent_id = NULL`; each allowed the parent delete; each later update returned 1452 and preserved the original child row.
- Direct INSERT control: `INSERT INTO child(id, doc) VALUES (9, '{"parent_id":1}')` stores `parent_id = 1`.
- Generated-column-only control: the same explicit JSONLine load into the same table shape without the FK stores `parent_id = 1`.
- Plain-FK control: JSONLine load into a table with only ordinary `parent_id` FK column succeeds.
- Atomicity: the post-delete JSON update is rejected and does not change the document or stored generated value.

## Evidence

The issue is reproducible using only the SQL above on the recorded `main` commit.

## Code analysis

`pkg/sql/plan/bind_load.go:91-127` excludes explicitly named generated columns from load input, as expected. The failure occurs only when that omitted generated column is also present in `TableDef.Fkeys`; `pkg/sql/plan/build_load.go:668-673` takes a dedicated FK planning path. The exact projection/recompute omission in that path is not yet confirmed.

## Regression coverage

Add a deterministic LOAD DATA regression for stored generated child-FK columns covering JSONLine with and without an explicit column list, valid and invalid parent references, and post-load parent deletion.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.