Open cockroach-teamcity opened 3 weeks ago
Reduced:
CREATE TABLE t (
i INT
);
INSERT INTO t VALUES (0);
-- Full scan.
SELECT 1 FROM t;
-- ?column?
-- ------------
-- 1
-- (1 row)
-- Unioned, partitioned scans with "NOT predicate" and "predicate IS NULL",
-- which should produce the single row in the table.
SELECT 1
FROM t
WHERE NOT (
-- predicate:
('127.0.0.1'::INET - i) IN (
(SELECT NULL FROM t WHERE false),
)
)
UNION ALL
SELECT 1
FROM t
WHERE (
-- predicate:
('127.0.0.1'::INET - i) IN (
(SELECT NULL FROM t WHERE false),
)
) IS NULL;
-- ?column?
-- ------------
-- 1
-- 1
-- (2 rows)
Looks like the problem is in the vectorized engine - when it is disabled we get the correct result:
CREATE TABLE t (
i INT
);
INSERT INTO t VALUES (0);
-- Full scan.
SELECT 1 FROM t;
-- ?column?
-- ------------
-- 1
-- (1 row)
-- Unioned, partitioned scans with "NOT predicate" and "predicate IS NULL",
-- which should produce the single row in the table.
SELECT 1
FROM t
WHERE NOT (
-- predicate:
('127.0.0.1'::INET - i) IN (
(SELECT NULL FROM t WHERE false),
)
)
UNION ALL
SELECT 1
FROM t
WHERE (
-- predicate:
('127.0.0.1'::INET - i) IN (
(SELECT NULL FROM t WHERE false),
)
) IS NULL;
-- ?column?
-- ------------
-- 1
-- 1
-- (2 rows)
SET vectorize=off;
-- Same query with vectorize=off returns the correct result.
SELECT 1
FROM t
WHERE NOT (
-- predicate:
('127.0.0.1'::INET - i) IN (
(SELECT NULL FROM t WHERE false),
)
)
UNION ALL
SELECT 1
FROM t
WHERE (
-- predicate:
('127.0.0.1'::INET - i) IN (
(SELECT NULL FROM t WHERE false),
)
) IS NULL;
-- ?column?
-- ------------
-- 1
-- (1 row)
This is a recent regression. I bisected to https://github.com/cockroachdb/cockroach/commit/07e2f9a694b8d4909427767af49df74eda9bb88d.
On 24.1, this reduces to:
CREATE TABLE t (a INT4);
ALTER TABLE t INJECT STATISTICS e'[{"avg_size": 20, "columns": ["a"], "created_at": "2000-01-01 00:00:00+00:00", "distinct_count": 67, "name": "__auto__", "null_count": 98, "row_count": 100}]';
INSERT INTO t (a) VALUES (0);
SELECT
('1.1.1.1'::INET - a::INT8) IN ((SELECT NULL FROM (VALUES (0)) v(i)),),
NOT (('1.1.1.1'::INET - a::INT8) IN ((SELECT NULL FROM (VALUES (0)) v(i)),)),
(('1.1.1.1'::INET - a::INT8) IN ((SELECT NULL FROM (VALUES (0)) v(i)),)) IS NULL
FROM t;
-- ?column? | ?column? | ?column?
-- -----------+----------+-----------
-- NULL | NULL | t
-- (1 row)
SELECT
('1.1.1.1'::INET - a::INT8) IN ((SELECT NULL FROM (VALUES (0)) v(i)),),
NOT (('1.1.1.1'::INET - a::INT8) IN ((SELECT NULL FROM (VALUES (0)) v(i)),)),
(('1.1.1.1'::INET - a::INT8) IN ((SELECT NULL FROM (VALUES (0)) v(i)),)) IS NULL
FROM t
WHERE NOT (('1.1.1.1'::INET - a::INT8) IN (( SELECT NULL FROM (VALUES (0)) v(i)),));
-- ?column? | ?column? | ?column?
-- -----------+----------+-----------
-- f | t | f
-- (1 row)
roachtest.tlp failed with artifacts on release-24.1 @ ab04f621a4c834c9f0cc7fd65187c531553ce384:
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-42210