sql: non-nullable column with null value

[yuzefovich]

CREATE TABLE table4 (
	col4_0
		INTERVAL,
	col4_5
		DECIMAL,
	col4_6
		JSONB NOT NULL,
	col4_7
		REGPROC,
	col4_9
		"char",
	col4_12
		STRING AS (lower(CAST(col4_0 AS STRING))) STORED,
	col4_13
		STRING AS (lower(col4_9)) VIRTUAL,
	UNIQUE (
		col4_12 DESC,
		col4_5,
		col4_9 ASC,
		col4_13 ASC
	)
		STORING(col4_0),
	FAMILY (col4_9, col4_5),
	FAMILY (col4_7),
	FAMILY (col4_6)
);

INSERT
INTO
	table4
		(
			col4_5,
			col4_9,
			col4_0,
			col4_6
		)
VALUES
	(
		0,
		'',
		'-49 years -9 mons -45 days -09:22:21.302525',
		e'[0.4320261707216839, {"+,+ic&L%<ue": [], "l&\\"8": [], "sy%bmo": {"*!rBt@Sat,": false}}, [[]]]'
	);

UPDATE table4 AS tab_653 SET col4_9 = tab_653.col4_12 WHERE true;

DELETE FROM table4 WHERE true;

UPDATE table4 SET col4_5 = 1 WHERE true;

SELECT tab_2236.col4_6 FROM table4 AS tab_2236;

results in

ERROR: internal error: unexpected error from the vectorized engine: null_value_error: non-nullable column "table4:col4_6" with no value! Index scanned was "primary" with the index key columns (rowid) and the values (?)
(1) attached stack trace
  -- stack trace:
  | github.com/cockroachdb/cockroach/pkg/sql/colfetcher.(*cFetcher).fillNulls
  | 	/Users/yuzefovich/go/src/github.com/cockroachdb/cockroach/pkg/sql/colfetcher/cfetcher.go:1495
  | github.com/cockroachdb/cockroach/pkg/sql/colfetcher.(*cFetcher).NextBatch
  | 	/Users/yuzefovich/go/src/github.com/cockroachdb/cockroach/pkg/sql/colfetcher/cfetcher.go:1107
  | github.com/cockroachdb/cockroach/pkg/sql/colfetcher.(*ColBatchScan).Next
  | 	/Users/yuzefovich/go/src/github.com/cockroachdb/cockroach/pkg/sql/colfetcher/colbatch_scan.go:109
  | github.com/cockroachdb/cockroach/pkg/sql/colexec/colexecutils.(*CancelChecker).Next
  | 	/Users/yuzefovich/go/src/github.com/cockroachdb/cockroach/pkg/sql/colexec/colexecutils/cancel_checker.go:59
  | github.com/cockroachdb/cockroach/pkg/sql/colexec/colexecbase.(*simpleProjectOp).Next
  | 	/Users/yuzefovich/go/src/github.com/cockroachdb/cockroach/pkg/sql/colexec/colexecbase/simple_project.go:125
...

[jordanlewis]

Note this issue as well, which may or may not be related: #69867

[yuzefovich]

I'm guessing it is not related because the repro here doesn't use CTAS (unless the queries used here use the same infrastructure as CTAS).

[yuzefovich]

I've simplified the reproduction a bit, and it looks like it relies on several things: multiple column families, virtual/stored columns, char"char" type, so I'm wondering whether it is related to #69673.

[rafiss]

Just to clarify, does your repro rely on char or "char" (or either)? Annoyingly, they are different. see #69665 (comment)

[yuzefovich]

I didn't investigate that, but in the schema above it is "char". Thanks for the callout though!

[yuzefovich]

Adding a GA blocker label since it is a regression from 21.1.9.

[yuzefovich]

cc @rytaft I think we should treat this as a GA-blocker for now.
cc @mgartner this could be related to #70811.

[mgartner]

There's a good chance it is related to #69327, which is basically the same as the issue you've linked (there's a handful of issues for the same underlying problem with assignment casts). If it is, #70722 should fix the issue. I'll assign(ment cast) myself to the issue for now.

[yuzefovich]

IIRC I did try running this on top of #70722 last week, and the problem was still present.

[mgartner]

Ahhh. That might be because #70722 only fixes assignment casts for INSERTs, not UPDATEs or UPSERTs. The repro above has some UPDATEs that could trigger the bug. I hope to have UPDATEs and UPSERTs fixed soon in another PR.

[mgartner]

This is reproducible in 21.1.7 by switching the "char" column to a CHAR column and inserting blank-character strings like ' ':

CREATE TABLE table4 (
	col4_0 STRING,
	col4_5 DECIMAL,
	col4_6 JSONB NOT NULL,
	col4_7 REGPROC,
	col4_9 CHAR,
	col4_12 STRING AS (lower(CAST(col4_0 AS STRING))) STORED,
	col4_13 STRING AS (lower(col4_9)) VIRTUAL,
	UNIQUE (
		col4_12 DESC,
		col4_5,
		col4_9 ASC,
		col4_13 ASC
	) STORING(col4_0),
	FAMILY (col4_9, col4_5),
	FAMILY (col4_7),
	FAMILY (col4_6)
);

INSERT
INTO
	table4
		(
			col4_5,
			col4_9,
			col4_0,
			col4_6
		)
VALUES
	(
		0,
		' ',
		' ',
		e'[0.4320261707216839, {"+,+ic&L%<ue": [], "l&\\"8": [], "sy%bmo": {"*!rBt@Sat,": false}}, [[]]]'
	);

UPDATE table4 AS tab_653 SET col4_9 = tab_653.col4_12 WHERE true;

DELETE FROM table4 WHERE true;

UPDATE table4 SET col4_5 = 1 WHERE true;

SELECT tab_2236.col4_6 FROM table4 AS tab_2236;

[rytaft]

We've determined this is not a GA blocker