Unexpected result when using a record in WHERE filter

[suyZhong]

Describe the problem

Consider the test case below. It is unexpected that the third query returns a row 1 NULL, since the second query shows that the expression should be evaluated to false.

To Reproduce

What did you do? Describe in your own words.

Start the CockroachDB instance by using Docker.

docker run --name cockroachdb-test -p 10011:26257 -p 10012:8080 cockroachdb/cockroach:latest start-single-node --insecure

Execute the below statements through psql:

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE  TABLE  t0(c0 INT, c1 INT);
INSERT INTO t0(c0) VALUES (1);

SELECT * FROM t0; -- 1 NULL
SELECT ((t0.c0, t0.c1) IS NOT NULL) FROM t0; -- f
SELECT * FROM t0 WHERE ((t0.c0, t0.c1) IS NOT NULL);
-- Expected: empty
-- Actual: 1 NULL

Expected behavior
The third query should return an empty result

Additional data / screenshots
If the problem is SQL-related, include a copy of the SQL query and the schema
of the supporting tables.

If a node in your cluster encountered a fatal error, supply the contents of the
log directories (at minimum of the affected node(s), but preferably all nodes).

Note that log files can contain confidential information. Please continue
creating this issue, but contact support@cockroachlabs.com to submit the log
files in private.

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version 24.2.1
• Server OS: Docker image
• Client app: psql and JDBC

Additional context
What was the impact?

Add any other context about the problem here.

Jira issue: CRDB-42161

[blathers-crl]

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

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

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: DROP DATABASE)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

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

[mgartner]

This might be a duplicate of #127814.

[mgartner]

More evidence that it is a duplicate: we get a correct result with the row-by-row engine:

CREATE  TABLE  t0(c0 INT, c1 INT);

INSERT INTO t0(c0) VALUES (1);

-- This should return no rows.
SELECT * FROM t0 WHERE ((t0.c0, t0.c1) IS NOT NULL);
--   c0 |  c1
-- -----+-------
--    1 | NULL
-- (1 row)

SET vectorize=off;

SELECT * FROM t0 WHERE ((t0.c0, t0.c1) IS NOT NULL);
--   c0 | c1
-- -----+-----
-- (0 rows)

[mgartner]

This is NOT a duplicate of #127814. It was fixed by #126901, but for some reason wasn't backported to 24.2. I've created a backport PR: #130947.