roachtest: unoptimized-query-oracle/disable-rules=all/seed-multi-region failed

[cockroach-teamcity]

Note: This build has runtime assertions enabled. If the same failure was hit in a run without assertions enabled, there should be a similar failure without this message. If there isn't one, then this failure is likely due to an assertion violation or (assertion) timeout.

roachtest.unoptimized-query-oracle/disable-rules=all/seed-multi-region failed with artifacts on release-25.4 @ 692cf64099c7f1b2e40b20b642595cbd43d750b0:

(query_comparison_util.go:419).runOneRoundQueryComparison: . 559 statements run: expected unoptimized and optimized results to be equal
  []string{
  	... // 2 identical elements
  	``{"a": 5, "c": [1, 2]}``,
  	``{"a": 5, "c": [1, 2]}``,
- 	``{"a": 5, "c": [1, 2]}``,
  }
sql: SELECT
	'{"a": 5, "c": [1, 2]}':::JSONB AS col_1766
FROM
	defaultdb.public.seed AS tab_559
WHERE
	jsonb_path_exists(tab_559._jsonb::JSONB, '$."b"[*]':::JSONPATH::JSONPATH)::BOOL
ORDER BY
	col_1766 ASC NULLS LAST,
	tab_559.tableoid ASC,
	tab_559.crdb_internal_mvcc_timestamp ASC NULLS FIRST,
	tab_559._bool ASC,
	tab_559._float8,
	tab_559._int2 DESC NULLS LAST,
	tab_559._decimal,
	tab_559._timestamptz DESC,
	tab_559.crdb_internal_origin_timestamp DESC,
	tab_559._inet ASC,
	tab_559._timestamp NULLS LAST,
	tab_559._string DESC NULLS FIRST,
	tab_559._float4 ASC NULLS FIRST,
	tab_559._enum ASC NULLS LAST,
	tab_559._bytes ASC,
	tab_559._jsonb ASC NULLS LAST,
	tab_559._int8 NULLS LAST,
	tab_559._uuid DESC NULLS FIRST,
	tab_559._date ASC,
	tab_559.crdb_internal_origin_id ASC,
	tab_559._int4 ASC NULLS LAST,
	tab_559._interval NULLS LAST
LIMIT
	55:::INT8
test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=all/seed-multi-region/run_1

Parameters:

• arch=amd64
• cloud=gce
• coverageBuild=false
• cpu=4
• encrypted=false
• metamorphicBufferedSender=true
• runtimeAssertionsBuild=true
• ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

Jira issue: CRDB-56161

[ZhouXing19]

I cannot reproduce this diff. Per the log, we prepare the schema with:

SET statement_timeout='1m0s';
SET unconstrained_non_covering_index_scan_enabled = true;
SET sql_safe_updates = false;

BEGIN; CREATE TYPE greeting AS ENUM ('hello', 'howdy', 'hi', 'good day', 'morning'); COMMIT;
BEGIN;
CREATE TABLE IF NOT EXISTS seed AS
	SELECT
		g::INT2 AS _int2,
		g::INT4 AS _int4,
		g::INT8 AS _int8,
		g::FLOAT4 AS _float4,
		g::FLOAT8 AS _float8,
		'2001-01-01'::DATE + g AS _date,
		'2001-01-01'::TIMESTAMP + g * '1 day'::INTERVAL AS _timestamp,
		'2001-01-01'::TIMESTAMPTZ + g * '1 day'::INTERVAL AS _timestamptz,
		g * '1 day'::INTERVAL AS _interval,
		g % 2 = 1 AS _bool,
		g::DECIMAL AS _decimal,
		g::STRING AS _string,
		g::STRING::BYTES AS _bytes,
		substring('00000000-0000-0000-0000-' || g::STRING || '00000000000', 1, 36)::UUID AS _uuid,
		'0.0.0.0'::INET + g AS _inet,
		g::STRING::JSONB AS _jsonb,
		enum_range('hello'::greeting)[g] as _enum
	FROM
		generate_series(1, 5) AS g;
COMMIT;

INSERT INTO seed DEFAULT VALUES;
CREATE INDEX on seed (_int8, _float8, _date);
CREATE INVERTED INDEX on seed (_jsonb);

ALTER TABLE seed SPLIT AT VALUES (6751474004831495444);
ALTER TABLE seed SPLIT AT VALUES (2469531690781714981);
ALTER TABLE seed SPLIT AT VALUES (3481373140815455548);
ALTER TABLE seed SPLIT AT VALUES (5505280298446355977);
ALTER TABLE seed SPLIT AT VALUES (4528434292317653252);
ALTER TABLE seed SPLIT AT VALUES (2062454318058547612);
ALTER TABLE seed SPLIT AT VALUES (1047970300784889691);
ALTER TABLE seed SCATTER;

The "optimization" in the test:

RESET testing_optimizer_random_seed;
RESET testing_optimizer_disable_rule_probability;
SET distsql = auto;

And we compare the result of the following stmt with and without the optimization:

SELECT
	'{"a": 5, "c": [1, 2]}':::JSONB AS col_1766
FROM
	defaultdb.public.seed AS tab_559
WHERE
	jsonb_path_exists(tab_559._jsonb::JSONB, '$."b"[*]':::JSONPATH::JSONPATH)::BOOL
ORDER BY
	col_1766 ASC NULLS LAST,
	tab_559.tableoid ASC,
	tab_559.crdb_internal_mvcc_timestamp ASC NULLS FIRST,
	tab_559._bool ASC,
	tab_559._float8,
	tab_559._int2 DESC NULLS LAST,
	tab_559._decimal,
	tab_559._timestamptz DESC,
	tab_559.crdb_internal_origin_timestamp DESC,
	tab_559._inet ASC,
	tab_559._timestamp NULLS LAST,
	tab_559._string DESC NULLS FIRST,
	tab_559._float4 ASC NULLS FIRST,
	tab_559._enum ASC NULLS LAST,
	tab_559._bytes ASC,
	tab_559._jsonb ASC NULLS LAST,
	tab_559._int8 NULLS LAST,
	tab_559._uuid DESC NULLS FIRST,
	tab_559._date ASC,
	tab_559.crdb_internal_origin_id ASC,
	tab_559._int4 ASC NULLS LAST,
	tab_559._interval NULLS LAST
LIMIT
	55:::INT8

But both gives empty result, which matches PG's behavior too.

[mgartner]

@ZhouXing19 and I reduced to the SQL below. Looks like a correctness bug. Probably could use some more manual reduction to simplify and better understand what is going on.

CREATE TYPE greeting AS ENUM ('hello', 'howdy', 'hi', 'good day', 'morning');

CREATE TABLE IF NOT EXISTS seed AS SELECT g::INT2 AS _int2,
                                          g::INT4 AS _int4,
                                          g AS _int8,
                                          g AS _float4,
                                          g AS _float8,
                                          '2001-01-01'::DATE + g AS _date,
                                          '2001-01-01'::TIMESTAMP
                                          + g * '1 day'::INTERVAL AS _timestamp,
                                          '2001-01-01'::TIMESTAMPTZ
                                          + g * '1 day'::INTERVAL AS _timestamptz,
                                          g * '1 day'::INTERVAL AS _interval,
                                          g % 2 = 1 AS _bool,
                                          g AS _decimal,
                                          g::STRING AS _string,
                                          g::STRING::BYTES AS _bytes,
                                          substring(
                                            '00000000-0000-0000-0000-'
                                            || g::STRING
                                            || '00000000000',
                                            1,
                                            36
                                          ) AS _uuid,
                                          '0.0.0.0'::INET + g AS _inet,
                                          g::STRING::JSONB AS _jsonb,
                                          enum_range('hello'::greeting)[g] AS _enum
                                     FROM generate_series(1, 0) AS g;

CREATE INVERTED INDEX ON seed (_jsonb);

INSERT
  INTO defaultdb.public.seed AS tab_58 (
                                        _int4,
                                        _int8,
                                        _float4,
                                        _float8,
                                        _date,
                                        _timestamptz,
                                        _interval,
                                        _string,
                                        _bytes,
                                        _inet,
                                        _jsonb,
                                        _enum
                                       )
VALUES (
        0:::INT8,
        (0):::INT8,
        NULL,
        0,
        '1981-06-19',
        NULL,
        '200 years 00:00:10':::INTERVAL,
        e'\x0b\x14\x1c{MfDG8':::STRING,
        e'\\xa5d277b30cd459':::BYTES,
        '94.60/0',
        '[{"b": {"IAZ63jM": "J9Ms"}, "foo": "sx1zWWh"}, null]',
        'good day'
       );

SET testing_optimizer_random_seed = 7891169484701056850;

SET testing_optimizer_disable_rule_probability = 1.000000;

SET vectorize = off;

SET distsql = off;

  SELECT '{"a": 5, "c": [1, 2]}':::JSONB AS col_1766
    FROM defaultdb.public.seed AS tab_559
   WHERE jsonb_path_exists(tab_559._jsonb::JSONB, '$."b"[*]':::JSONPATH::JSONPATH)::BOOL
ORDER BY col_1766 ASC NULLS LAST,
         tab_559.tableoid ASC,
         tab_559.crdb_internal_mvcc_timestamp ASC NULLS FIRST,
         tab_559._bool ASC,
         tab_559._float8,
         tab_559._int2 DESC NULLS LAST,
         tab_559._decimal,
         tab_559._timestamptz DESC,
         tab_559.crdb_internal_origin_timestamp DESC,
         tab_559._inet ASC,
         tab_559._timestamp NULLS LAST,
         tab_559._string DESC NULLS FIRST,
         tab_559._float4 ASC NULLS FIRST,
         tab_559._enum ASC NULLS LAST,
         tab_559._bytes ASC,
         tab_559._jsonb ASC NULLS LAST,
         tab_559._int8 NULLS LAST,
         tab_559._uuid DESC NULLS FIRST,
         tab_559._date ASC,
         tab_559.crdb_internal_origin_id ASC,
         tab_559._int4 ASC NULLS LAST,
         tab_559._interval NULLS LAST
   LIMIT 55:::INT8;
--           col_1766
-- -------------------------
--   {"a": 5, "c": [1, 2]}
-- (1 row)

RESET testing_optimizer_random_seed;

RESET testing_optimizer_disable_rule_probability;

SET distsql = auto;

  SELECT '{"a": 5, "c": [1, 2]}':::JSONB AS col_1766
    FROM defaultdb.public.seed AS tab_559
   WHERE jsonb_path_exists(tab_559._jsonb::JSONB, '$."b"[*]':::JSONPATH::JSONPATH)::BOOL
ORDER BY col_1766 ASC NULLS LAST,
         tab_559.tableoid ASC,
         tab_559.crdb_internal_mvcc_timestamp ASC NULLS FIRST,
         tab_559._bool ASC,
         tab_559._float8,
         tab_559._int2 DESC NULLS LAST,
         tab_559._decimal,
         tab_559._timestamptz DESC,
         tab_559.crdb_internal_origin_timestamp DESC,
         tab_559._inet ASC,
         tab_559._timestamp NULLS LAST,
         tab_559._string DESC NULLS FIRST,
         tab_559._float4 ASC NULLS FIRST,
         tab_559._enum ASC NULLS LAST,
         tab_559._bytes ASC,
         tab_559._jsonb ASC NULLS LAST,
         tab_559._int8 NULLS LAST,
         tab_559._uuid DESC NULLS FIRST,
         tab_559._date ASC,
         tab_559.crdb_internal_origin_id ASC,
         tab_559._int4 ASC NULLS LAST,
         tab_559._interval NULLS LAST
   LIMIT 55:::INT8;
--   col_1766
-- ------------
-- (0 rows)

[ZhouXing19]

This is related to index acceleration for jsonb_path_exist filter:

CREATE TABLE json_tab (a INT PRIMARY KEY,  b JSONB);                                                                        
INSERT INTO json_tab VALUES (1,'[{"b": {"x": "y"}, "a": "x"}, null]');
CREATE INVERTED INDEX foo_inv ON json_tab(b);

Run without index acceleration:

> SELECT a FROM json_tab@primary WHERE jsonb_path_exists(b, '$."b"[*]');

  a
-----
  1
(1 rows)

Run with index acceleration:

> SELECT a FROM json_tab@foo_inv WHERE jsonb_path_exists(b, '$."b"[*]'); 

  a
-----
(0 rows)