roachtest: unoptimized-query-oracle/disable-rules=half failed

[cockroach-teamcity]

roachtest.unoptimized-query-oracle/disable-rules=half failed with artifacts on master @ 773568fbda06ba9be9fb1bc34a331f21c8891ffa:

		  | + 		".0",
		  |   		",-6.839896181776363352E+23",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"Infinity,0,01ef01ef-01ef-01ef-01ef-01ef01ef01ef,0,-5603543133744",
		  |   		"919124",
		  | + 		".0",
		  |   		",-6.839896181776363352E+23",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"Infinity,0,01ef01ef-01ef-01ef-01ef-01ef01ef01ef,NULL,-5603543133",
		  |   		"744919124",
		  | + 		".0",
		  |   		",-6.839896181776363352E+23",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"NaN,0,01ef01ef-01ef-01ef-01ef-01ef01ef01ef,0,-560354313374491912",
		  |   		"4",
		  | + 		".0",
		  |   		",-6.839896181776363352E+23",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"NaN,0,01ef01ef-01ef-01ef-01ef-01ef01ef01ef,0,-560354313374491912",
		  |   		"4",
		  | + 		".0",
		  |   		",-6.839896181776363352E+23",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"NaN,0,01ef01ef-01ef-01ef-01ef-01ef01ef01ef,0,-560354313374491912",
		  |   		"4",
		  | + 		".0",
		  |   		",-6.839896181776363352E+23",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"NaN,0,01ef01ef-01ef-01ef-01ef-01ef01ef01ef,0,-560354313374491912",
		  |   		"4",
		  | + 		".0",
		  |   		",-6.839896181776363352E+23",
		  |   	}, ""),
		  |   }
		  | sql: SELECT
		  | 	tab_2767.col2_3 AS col_7639,
		  | 	0:::OID AS col_7640,
		  | 	'01ef01ef-01ef-01ef-01ef-01ef01ef01ef':::UUID AS col_7641,
		  | 	tab_2767.col2_2 AS col_7642,
		  | 	((-5603543133744919124):::INT8::INT8 / 1:::DECIMAL::DECIMAL)::DECIMAL AS col_7643,
		  | 	(-6.839896181776363352E+23):::DECIMAL AS col_7644
		  | FROM
		  | 	defaultdb.public.table2@[0] AS tab_2767
		Error types: (1) *withstack.withStack (2) *errutil.withPrefix (3) *withstack.withStack (4) *errutil.leafError

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

Same failure on other branches

• roachtest: unoptimized-query-oracle/disable-rules=half failed #87779 roachtest: unoptimized-query-oracle/disable-rules=half failed [C-test-failure O-roachtest O-robot T-sql-queries branch-release-22.2 release-blocker]
• roachtest.unoptimized-query-oracle/disable-rules=half failed due to extra precision decimals #86790 roachtest.unoptimized-query-oracle/disable-rules=half failed due to extra precision decimals [C-test-failure T-sql-queries]

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-19556

[mgartner]

The first failure in the issue description is #86790.

[mgartner]

I've reduce this failure (the first comment after the description) to:

CREATE TABLE t (
  i INTERVAL
);

INSERT INTO t (i) VALUES (NULL);

SET testing_optimizer_random_seed = 6320964980407535657;

SET testing_optimizer_disable_rule_probability = 0.500000;

SET vectorize = off;

-- Returns 1 row.
SELECT i
FROM t
WHERE NOT (i IN (
    SELECT '1 day'::INTERVAL
    FROM t t1 JOIN t t2 ON true
    WHERE false
));

RESET vectorize;

-- Returns 0 rows.
SELECT i
FROM t
WHERE NOT (i IN (
    SELECT '1 day'::INTERVAL
    FROM t t1 JOIN t t2 ON true
    WHERE false
));

It appears to be a bug in the vectorized engine. I've created #88141 to track this.

[msirek]

Here's a reduction of this failure:

drop table if exists table2;
drop table if exists table3;
CREATE TABLE table2 (
      col2_3 INT8 NOT NULL,
      col2_11 INT4 NOT NULL,
      col2_12 INT4 NOT NULL,
      col2_16 INT8 NOT NULL AS ((col2_11 + col2_3) + col2_12) VIRTUAL
  );

CREATE TABLE table3 (
      col3_2 TIME[] NOT NULL,
      col3_6 INT8 NOT NULL
  );

insert into table2 (col2_3, col2_11, col2_12) values
  (-3111591259888446974 ,           1 ,         126),
                   (-76 ,         -35 ,          28);

insert into table3 values (ARRAY['11:05:07.15971'::TIME, '24:00:00'::TIME, '24:00:00'::TIME], -100);

SET testing_optimizer_random_seed = 4672630055387349891;

SET testing_optimizer_disable_rule_probability = 0.500000;

SET vectorize = off;

SELECT
        (tab_24808.col3_2::TIME[] || ARRAY['07:35:42.171296':::TIME]::TIME[])::TIME[] AS col_75955
FROM
        table2 AS tab_24807
        FULL JOIN table3 AS tab_24808 ON (tab_24807.col2_16) = (tab_24808.col3_6);

RESET vectorize;

SET optimizer_use_not_visible_indexes = true;

SELECT
        (tab_24808.col3_2::TIME[] || ARRAY['07:35:42.171296':::TIME]::TIME[])::TIME[] AS col_75955
FROM
        table2 AS tab_24807
        FULL JOIN table3 AS tab_24808 ON (tab_24807.col2_16) = (tab_24808.col3_6);

I don't know if the problem is in the vectorized engine or row engine.

[DrewKimball]

Here's a reduction of this failure:

That one looks like a dup of #87919

[michae2]

The one I was looking at reduces to this:

I.e. runtime interval handling seems to diverge from optimizer constant interval handling.

I propose we call that one a dupe of #88128 to remove it from this issue.

[mgartner]

All of the failures are tracked elsewhere. Removing the release blocker labels and closing.