roachtest: tlp failure: false vs NULL

[cockroach-teamcity]

roachtest.tlp failed with artifacts on release-24.2 @ 742a2251a05088a238fc6eb4c2466dace3cb1bb2:

  	strings.Join({
  		... // 97 identical bytes
  		"001110101111001110010001111,01010110010101001100100101001111100,",
  		"11010111010001101011111001111011001},b\x1a/,\x00,true,NULL,false,true,",
- 		"false",
+ 		"NULL",
  		",false",
  	}, ""),
  	strings.Join({
  		"504801479,N_\x1e/,{11100011100001010010110000001000010,000000100001",
  		"01011010111001110100111,10011000010011010101101100100010100,0000",
  		"0100010100110001110100111010001},b\x1a/,\x00,true,NULL,false,true,",
- 		"false",
+ 		"NULL",
  		",false",
  	}, ""),
  	strings.Join({
  		"582321906,\x06 QK\x03,{01001010010110100011100001101011001},b\x1a/,\x00,true",
  		",NULL,false,true,",
- 		"false",
+ 		"NULL",
  		",false",
  	}, ""),
  	strings.Join({
  		... // 101 identical bytes
  		"000000000000000000000000000,01111111111111111111111111111111111,",
  		"01011100000010110001000000011010111},b\x1a/,\x00,true,NULL,false,true,",
- 		"false",
+ 		"NULL",
  		",false",
  	}, ""),
  }
sql: SELECT *, tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),), NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)), (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL, true, false, false FROM defaultdb.public.table_3 AS tab_22480
(SELECT *,
tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),), NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)), (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL,
tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),), NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)), (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL
FROM defaultdb.public.table_3 AS tab_22480
WHERE tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),))
UNION ALL (SELECT *,
tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),), NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)), (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL,
NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)), tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),), (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL
FROM defaultdb.public.table_3 AS tab_22480
WHERE NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)))
UNION ALL (SELECT *,
tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),), NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)), (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL,
(tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL, (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NOT NULL, (NOT (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),))) IS NOT NULL
FROM defaultdb.public.table_3 AS tab_22480
WHERE (tab_22480."col%q3 _0"::OID NOT IN ((SELECT NULL AS col_1540 FROM defaultdb.public.table_2@"table_2_c ol2_2_idx" AS tab_22484 INNER JOIN defaultdb.public.table_3 AS tab_22485 ON NULL GROUP BY tab_22485.tableoid, tab_22485."col?""3_2", tab_22484.crdb_internal_mvcc_timestamp, tab_22485.col3_3, tab_22485."col%q3 _0", tab_22484.col2_1 ORDER BY tab_22484.col2_1 ASC NULLS LAST, tab_22484.crdb_internal_mvcc_timestamp ASC NULLS FIRST LIMIT 1:::INT8),)) IS NULL)
with args: []
test artifacts and logs in: /artifacts/tlp/run_1

Parameters:

• ROACHTEST_arch=amd64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_metamorphicBuild=false
• ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

Jira issue: CRDB-40618

[michae2]

Initial reduction: repro.txt

running this with:

./cockroach demo --multitenant=false --set=errexit=false --no-example-database -f ~/Downloads/127814/repro.txt

[michae2]

Using that repro, this reproduces on v24.1.2 and v23.2.5, but not v23.1.21. So seems like it was a regression in v23.2. I'll remove release-blocker since this wasn't a recent regression.

[mgartner]

I believe this boils down to this interesting query result:

CREATE TABLE t1 (
  i INT
);

CREATE TABLE t2 (
  o OID PRIMARY KEY
);

INSERT INTO t2 VALUES (0);

-- p: o NOT IN ((SELECT NULL FROM t1),)
SELECT
  -- p
  o NOT IN (
    (SELECT NULL FROM t1),
  ),
  -- NOT p
  NOT (
    o NOT IN (
      (SELECT NULL FROM t1),
    )
  )
FROM t2;
--   ?column? | ?column?
-- -----------+-----------
--      t     |   NULL
-- (1 row)

For some predicate p that evaluates to true, is it valid for NOT p to be NULL? Or must NOT p always be false?

If NOT p can be NULL, then there might not be a bug here—but TLP must be updated to account for this, e.g., the changes in #73701 might not be valid.

If NOT p must be false, then a bug exists.

[mgartner]

Note that the trailing commas have semantic signficance:

-- Same as above, without trailing commas.
SELECT
  -- p
  o NOT IN (
    (SELECT NULL FROM t1)
  ),
  -- NOT p
  NOT (
    o NOT IN (
      (SELECT NULL FROM t1)
    )
  )
FROM t2;
--   ?column? | ?column?
-- -----------+-----------
--      t     |    f
-- (1 row)

---

The commas make the single-row result of the subquery an item in the IN (<list>). The query will error if the subquery results more than one row. In this case, the subquery returns zero rows, so the single-row result becomes NULL, which is expected:

CREATE TABLE t (i INT);

SELECT (SELECT i FROM t);
--    i
-- --------
--   NULL
-- (1 row)

So the predicate p is equivalent to o NOT IN (NULL). This should always evaluate to NULL, regardless of the value of o. Therefore, I believe there is a bug here and a true result for the first column is an incorrect result.

[mgartner]

It looks like there's a bug in the vectorized execution engine that is causing this. We get a correct result when forcing the row-by-row engine:

CREATE TABLE t1 (
  i INT
);

CREATE TABLE t2 (
  o OID PRIMARY KEY
);

INSERT INTO t2 VALUES (0);

-- p: o NOT IN ((SELECT NULL FROM t1),)
SELECT
  -- p
  o NOT IN (
    (SELECT NULL FROM t1),
  ),
  -- NOT p
  NOT (
    o NOT IN (
      (SELECT NULL FROM t1),
    )
  )
FROM t2;
--   ?column? | ?column?
-- -----------+-----------
--      t     |   NULL
-- (1 row)

SET vectorize=off;

-- p: o NOT IN ((SELECT NULL FROM t1),)
SELECT
  -- p
  o NOT IN (
    (SELECT NULL FROM t1),
  ),
  -- NOT p
  NOT (
    o NOT IN (
      (SELECT NULL FROM t1),
    )
  )
FROM t2;
--   ?column? | ?column?
-- -----------+-----------
--     NULL   |   NULL
-- (1 row)

[rytaft]

@mgartner are you still working on this issue?

[mgartner]

Ya, I need to make some changes to #128123. I'll try to get to that this week.