search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
29.85k stars 3.77k forks source link

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

Closed cockroach-teamcity closed 1 year ago

cockroach-teamcity commented 1 year ago

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](https://github.com/cockroachdb/cockroach/blob/master/pkg/cmd/roachtest/README.md) See: [How To Investigate \(internal\)](https://cockroachlabs.atlassian.net/l/c/SSSBr8c7)

Same failure on other branches

- #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] - #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

cockroach-teamcity commented 1 year ago

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

test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=half/run_1
    query_comparison_util.go:235,query_comparison_util.go:66,unoptimized_query_oracle.go:51,test_runner.go:917: . 9708 statements run: expected unoptimized and optimized results to be equal
        (1) attached stack trace
          -- stack trace:
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.(*queryComparisonHelper).makeError
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:343
          | [...repeated from below...]
        Wraps: (2) . 9708 statements run
        Wraps: (3) attached stack trace
          -- stack trace:
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runUnoptimizedQueryOracleImpl
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:165
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1.1
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:54
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runOneRoundQueryComparison
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:234
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runQueryComparison
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:66
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:51
          | main.(*testRunner).runTest.func2
          |     main/pkg/cmd/roachtest/test_runner.go:917
          | runtime.goexit
          |     GOROOT/src/runtime/asm_amd64.s:1594
        Wraps: (4) expected unoptimized and optimized results to be equal
          |   []string{
          |     ",aa,1970-01-11 00:08:20 +0000 +0000,-42 years -1 mons -209 days "...,
          |     ",aa,1970-01-11 00:08:20 +0000 +0000,76 years 11 mons 809 days 04"...,
          | -   ",aa,1970-01-11 00:08:20 +0000 +0000,NULL",
          |   }
          | sql: SELECT
          |     '':::STRING AS col_34028,
          |     'aa':::STRING AS col_34029,
          |     '1970-01-11 00:08:20':::TIMESTAMP AS col_34030,
          |     tab_11211.col3_3 AS col_34031
          | FROM
          |     defaultdb.public.table3@[0] AS tab_11211
          | WHERE
          |     (NOT (tab_11211.col3_3::INTERVAL IN (SELECT '-2 years -4 mons -280 days -11:52:06.292315':::INTERVAL::INTERVAL AS col_34027 FROM defaultdb.public.table3@[0] AS tab_11212 JOIN defaultdb.public.table3@[0] AS tab_11213 ON ((tab_11212.col3_6) = (tab_11213.col3_6)) AND ((tab_11212.col3_5) = (tab_11213.col3_1)) WHERE _st_covers('0103000000020000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000050000009A9999999999C93F9A9999999999C93F9A9999999999C93F9A9999999999D93F9A9999999999D93F9A9999999999D93F9A9999999999D93F9A9999999999C93F9A9999999999C93F9A9999999999C93F':::GEOMETRY::GEOMETRY, '010400000004000000010100000000000000000024400000000000004440010100000000000000000044400000000000003E4001010000000000000000003440000000000000344001010000000000000000003E400000000000002440':::GEOMETRY::GEOMETRY)::BOOL)))
        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](https://github.com/cockroachdb/cockroach/blob/master/pkg/cmd/roachtest/README.md) See: [How To Investigate \(internal\)](https://cockroachlabs.atlassian.net/l/c/SSSBr8c7)

Same failure on other branches

- #86790 roachtest.unoptimized-query-oracle/disable-rules=half failed due to extra precision decimals [C-test-failure T-sql-queries]

This test on roachdash | Improve this report!

mgartner commented 1 year ago

The first failure in the issue description is #86790.

cockroach-teamcity commented 1 year ago

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

          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1.1
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:54
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runOneRoundQueryComparison
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:234
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runQueryComparison
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:66
          | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1
          |     github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:51
          | main.(*testRunner).runTest.func2
          |     main/pkg/cmd/roachtest/test_runner.go:917
          | runtime.goexit
          |     GOROOT/src/runtime/asm_amd64.s:1594
        Wraps: (4) expected unoptimized and optimized results to be equal
          |   []string(
          | -   {
          | -       "\x01,\x01,0,0,0,00101000010100010110110000010101101111000110,99.8,,!7\x12,aaaaaa",
          | -       "\x01,\x01,0,0,0,10010111001001001010111010101100000101011111,9.8,,!7\x12,aaaaaa",
          | -       "\x01,\x01,0,0,NULL,00000000000000000000000000000000000000000000,-678856255251888.0742,,!7\x12,aaaaaa",
          | -       "\x01,\x01,0,0,NULL,10001101010100001110111111000100011111010011,-3822008.342994803374,,!7\x12,aaaaaa",
          | -       "\x01,\x01,105,105,0,11101010010111000010011111010101000100101111,1663415343443917501.0000000000,,!7\x12,aaaaaa",
          | -       "\x05,\x05,0,0,0,11101010010111000010011111010101000100101111,-50380406.47285401833,,!7\x12,aaaaaa",
          | -       "\x05,\x05,0,0,NULL,00000000000000000000000000000000000000000000,7963326292684867.154,\x10|, a",
          | -       "\x05,\x05,105,105,NULL,00000000000000000000000000000000000000000000,1663415343656215443.0000000000,,!7\x12,aaaaaa",
          | -       "\t,\t,0,0,0,00010110001000001000110100101010110110111101,-67269114308810.19725,\x10|,\x16y",
          | -       "\t,\t,0,0,NULL,00000000000000000000000000000000000000000000,2415633042621588109,\x10|,-\x10?!",
          | -       "\t,\t,105,105,0,00000000000000000000000000000000000000000000,1663415344816691668.0000000000,,!7\x12,aaaaaa",
          | -       "\n,\n,0,0,0,00000000000000000000000000000000000000000000,1.201E+11,,!7\x12,\x17\x11",
          | -       "\r,\r,0,0,0,00000000000000000000000000000000000000000000,1,\x10|,``",
          | -       "\x0e,\x0e,105,105,0,00110010000001011000000111000010100110111000,1663415343594153444.0000000000,,!7\x12,aaaaaa",
          | -       " , ,0,0,NULL,00000000000000000000000000000000000000000000,0.111,\x10|,\ta",
          | -       "!,!,0,0,NULL,00000000000000000000000000000000000000000000,99.8,,!7\x12,(\x06^",
          | -       ...,
          | -   },
          | +   nil,
          |   )
          | sql: SELECT
          |     tab_374.col2_8 AS col_1222,
          |     tab_374.col2_8 AS col_1223,
          |     tab_374.col2_4 AS col_1224,
          |     tab_374.col2_4 AS col_1225,
          |     tab_374.col2_1 AS col_1226,
          |     tab_374.col2_6 AS col_1227,
          |     tab_374.col2_10 AS col_1228,
          |     tab_374.col2_13 AS col_1229,
          |     tab_374.col2_2 AS col_1230
          | FROM
          |     defaultdb.public.table2@[0] AS tab_374
          | WHERE
          |     (('17:44:46.308403':::TIME < (('13:53:20':::TIME::TIME - '-65 years -3 mons -957 days -02:34:45.145087':::INTERVAL::INTERVAL)::TIME::TIME - '11 years 892 days 10:38:16.35096':::INTERVAL::INTERVAL)::TIME) OR ('ab2b:aeaf:e72b:592:d7a8:27f0:f483:f225/41':::INET::INET << 'b99a:c7da:4507:a13d:e764:4a91:274c:6cac/80':::INET::INET)::BOOL)
        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](https://github.com/cockroachdb/cockroach/blob/master/pkg/cmd/roachtest/README.md) See: [How To Investigate \(internal\)](https://cockroachlabs.atlassian.net/l/c/SSSBr8c7)

Same failure on other branches

- #86790 roachtest.unoptimized-query-oracle/disable-rules=half failed due to extra precision decimals [C-test-failure T-sql-queries]

This test on roachdash | Improve this report!

cockroach-teamcity commented 1 year ago

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

          |     }, ""),
          |     strings.Join({
          |         ... // 320 identical bytes
          |         "A76604051379A79639555C0DA267D2B125E0142B5C8086DCFDC61C0800ACAC05",
          |         "68F06C0E8E50A5748C9F141,2031-11-21 21:12:20.000596 +0000 +0000,{",
          | +       "11:05:07.15971,24:00:00,24:00:00,",
          |         "07:35:42.171296},1991-05-25 09:55:02.000912 +0000 UTC,0103000020",
          |         "E610000001000000050000000000000000000000000000000000000000000000",
          |         ... // 666 identical bytes
          |     }, ""),
          |   }
          | sql: SELECT
          |     NULL AS col_75933,
          |     NULL AS col_75934,
          |     tab_24808.col3_6 AS col_75935,
          |     tab_24808.col3_6 AS col_75936,
          |     ('[1, 2]':::JSONB::JSONB->>e'\xef\xbf\xbd':::STRING::STRING)::STRING AS col_75937,
          |     tab_24807.col2_10 AS col_75938,
          |     tab_24808.col3_9 AS col_75939,
          |     '\xa7af0fecae84':::BYTES AS col_75940,
          |     tab_24808.col3_0 AS col_75941,
          |     (-55):::INT8 AS col_75942,
          |     tab_24808.col3_9 AS col_75943,
          |     tab_24807.col2_3 AS col_75944,
          |     tab_24807.col2_0 AS col_75945,
          |     true AS col_75946,
          |     '1967-04-08 03:46:40+00:00':::TIMESTAMPTZ AS col_75947,
          |     0:::OID AS col_75948,
          |     e'\'B':::STRING AS col_75949,
          |     tab_24808.crdb_internal_mvcc_timestamp AS col_75950,
          |     e'{"?AV": [], "a": {"6{{A\\\\cR;nGS": [true, {}], "a": [{}]}, "bar": {"K-V": [{}], "YK,?c=e>O": "$\'!W"}, "foo": {}, "foobar": {}}':::JSONB
          |         AS col_75951,
          |     tab_24807.col2_8 AS col_75952,
          |     tab_24807.col2_15 AS col_75953,
          |     '2031-11-21 21:12:20.000596':::TIMESTAMP AS col_75954,
          |     (tab_24808.col3_2::TIME[] || ARRAY['07:35:42.171296':::TIME]::TIME[])::TIME[] AS col_75955,
          |     '1991-05-25 09:55:02.000912+00:00':::TIMESTAMPTZ AS col_75956,
          |     '0103000020E6100000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000':::GEOGRAPHY
          |         AS col_75957,
          |     tab_24807.col2_12 AS col_75958,
          |     tab_24808.col3_10 AS col_75959,
          |     tab_24807.tableoid AS col_75960,
          |     1.201E+11:::DECIMAL AS col_75961,
          |     e'\'':::STRING AS col_75962,
          |     '{"cars": [{"make": "Volkswagen", "model": "Rabbit", "trim": "S", "year": "2009"}, {"make": "Toyota", "model": "Camry", "trim": "LE", "year": "2002"}, {"make": "Ford", "model": "Focus", "trim": "SE", "year": "2011"}, {"make": "Buick", "model": "Grand National", "trim": "T-Type", "year": "1987"}, {"make": "Buick", "model": "Skylark", "trim": "Gran Sport", "year": "1966"}, {"make": "Porsche", "model": "911", "trim": "Turbo S", "year": "2022"}, {"make": "Chevrolet", "model": "Corvette", "trim": "C8", "year": "2022"}]}':::JSONB
          |         AS col_75963
          | FROM
          |     defaultdb.public.table2@[0] AS tab_24807
          |     FULL JOIN defaultdb.public.table3@[0] AS tab_24808 ON (tab_24807.col2_16) = (tab_24808.col3_6)
        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](https://github.com/cockroachdb/cockroach/blob/master/pkg/cmd/roachtest/README.md) See: [How To Investigate \(internal\)](https://cockroachlabs.atlassian.net/l/c/SSSBr8c7)

Same failure on other branches

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

This test on roachdash | Improve this report!

mgartner commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

Here's a reduction of this failure:

That one looks like a dup of #87919

michae2 commented 1 year ago

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 commented 1 year ago

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