roachtest: tlp failed on complex join condition involving geometry #84957

cockroach-teamcity · 2022-07-23T11:57:10Z

roachtest.tlp failed with artifacts on release-22.1 @ 3fbf4a9d769e6db6aafd8a241a2a09b7c5e33738:

The test failed on branch=release-22.1, cloud=gce:
test artifacts and logs in: /artifacts/tlp/run_1
	tlp.go:179,tlp.go:76,test_runner.go:883: expected unpartitioned and partitioned results to be equal
		(1) attached stack trace
		  -- stack trace:
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runTLPQuery.func2
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/tlp.go:266
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runWithTimeout.func1
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/tlp.go:289
		  | runtime.goexit
		  | 	GOROOT/src/runtime/asm_amd64.s:1581
		Wraps: (2) expected unpartitioned and partitioned results to be equal
		  |   []string{
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  |   	... // 11 identical elements
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  |   	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  | + 	"105.62.204.94/25,1973-06-22 00:00:00 +0000 +0000,BOX(0.014666717427117393 -0.17670277548785074,1.3762302200845573 1.9520902929095347),0106000000090000000103000000010000000B000000062BEC748F1DFDC140EB1CC33E3FA7C143D20AA19059F8C100CC1E9BEF7777C179CD21AB5AFDF2"...,
		  |   	... // 656 identical and 656 inserted elements
		  |   }
		  | sql: SELECT * FROM defaultdb.public.table1 AS tab_13521 JOIN defaultdb.public.table1 AS tab_13522 ON true
		  | (SELECT * FROM defaultdb.public.table1 AS tab_13521 JOIN defaultdb.public.table1 AS tab_13522 ON tab_13521.col1_3::GEOMETRY NOT IN (WITH with_341 (col_893) AS (SELECT tab_13523.col1_4 AS col_893 FROM defaultdb.public.table1@table1_col1_0_key AS tab_13523 WHERE true) SELECT st_makeline('01030000C0010000000A000000E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2A33FBDE96D28FAC129F4CF457C3CF3C1F0140B9AFE96CB41FECB17EE639BF5C1FC247EB9A4A1F14109C4D1CC823CF3C10C0DFBF987C1F0C1702A1D6536E6EDC1EAD22042D4950142EFEBD6B09485FBC13C047ED673D8E14180C023B327CDB6416090BD005C36FC4100CB95253527994110AD54A4EFF5DB4138A60149E149FD41367E609059ADF741A05C761FB9ACB74117BB44BCE60AFBC18E7212E96300EBC182F302118343F0417883F37F03D2E54120F5A6A828C1EB411812EB8CC687DB416047BC2FA05BE9C1D8280DA81393FB410044FB875BBAEE41264046DE3D4D00C29BCEDD83824C01C270909E5D4896FF4144A4A6C7236FEE4130F610770050DCC1E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2'::GEOMETRY)::GEOMETRY::GEOMETRY AS col_894 FROM with_341 AS cte_ref_115 WHERE true GROUP BY cte_ref_115.col_893 LIMIT 23)) UNION ALL (SELECT * FROM defaultdb.public.table1 AS tab_13521 JOIN defaultdb.public.table1 AS tab_13522 ON NOT (tab_13521.col1_3::GEOMETRY NOT IN (WITH with_341 (col_893) AS (SELECT tab_13523.col1_4 AS col_893 FROM defaultdb.public.table1@table1_col1_0_key AS tab_13523 WHERE true) SELECT st_makeline('01030000C0010000000A000000E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2A33FBDE96D28FAC129F4CF457C3CF3C1F0140B9AFE96CB41FECB17EE639BF5C1FC247EB9A4A1F14109C4D1CC823CF3C10C0DFBF987C1F0C1702A1D6536E6EDC1EAD22042D4950142EFEBD6B09485FBC13C047ED673D8E14180C023B327CDB6416090BD005C36FC4100CB95253527994110AD54A4EFF5DB4138A60149E149FD41367E609059ADF741A05C761FB9ACB74117BB44BCE60AFBC18E7212E96300EBC182F302118343F0417883F37F03D2E54120F5A6A828C1EB411812EB8CC687DB416047BC2FA05BE9C1D8280DA81393FB410044FB875BBAEE41264046DE3D4D00C29BCEDD83824C01C270909E5D4896FF4144A4A6C7236FEE4130F610770050DCC1E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2'::GEOMETRY)::GEOMETRY::GEOMETRY AS col_894 FROM with_341 AS cte_ref_115 WHERE true GROUP BY cte_ref_115.col_893 LIMIT 23))) UNION ALL (SELECT * FROM defaultdb.public.table1 AS tab_13521 JOIN defaultdb.public.table1 AS tab_13522 ON (tab_13521.col1_3::GEOMETRY NOT IN (WITH with_341 (col_893) AS (SELECT tab_13523.col1_4 AS col_893 FROM defaultdb.public.table1@table1_col1_0_key AS tab_13523 WHERE true) SELECT st_makeline('01030000C0010000000A000000E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2A33FBDE96D28FAC129F4CF457C3CF3C1F0140B9AFE96CB41FECB17EE639BF5C1FC247EB9A4A1F14109C4D1CC823CF3C10C0DFBF987C1F0C1702A1D6536E6EDC1EAD22042D4950142EFEBD6B09485FBC13C047ED673D8E14180C023B327CDB6416090BD005C36FC4100CB95253527994110AD54A4EFF5DB4138A60149E149FD41367E609059ADF741A05C761FB9ACB74117BB44BCE60AFBC18E7212E96300EBC182F302118343F0417883F37F03D2E54120F5A6A828C1EB411812EB8CC687DB416047BC2FA05BE9C1D8280DA81393FB410044FB875BBAEE41264046DE3D4D00C29BCEDD83824C01C270909E5D4896FF4144A4A6C7236FEE4130F610770050DCC1E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2'::GEOMETRY)::GEOMETRY::GEOMETRY AS col_894 FROM with_341 AS cte_ref_115 WHERE true GROUP BY cte_ref_115.col_893 LIMIT 23)) IS NULL)
		  | with args: []
		Error types: (1) *withstack.withStack (2) *errutil.leafError

Help

See: roachtest README

See: How To Investigate (internal)

/cc @cockroachdb/sql-queries _{This test on roachdash | Improve this report!

Jira issue: CRDB-17971}

The text was updated successfully, but these errors were encountered:

michae2 · 2022-07-24T17:52:19Z

Reduced to:

CREATE TABLE table1 (
  col1_0 INET,
  col1_1 DATE NOT NULL,
  col1_2 BOX2D,
  col1_3 GEOMETRY NOT NULL,
  col1_4 STRING
         AS (CASE WHEN col1_1 IS NULL THEN '' ELSE '%"':::STRING END) VIRTUAL
         NOT NULL,
  UNIQUE (col1_0 DESC) STORING(col1_2),
  FAMILY (col1_2, col1_1, col1_3)
);

INSERT
  INTO defaultdb.public.table1 (col1_1, col1_2, col1_3)
VALUES (
        '1986-09-20':::DATE,
        'BOX(-0.9553617556250505 0.373642371510408,-0.5504995924074046 0.882407929762621)':::BOX2D,
        '01060000000200000001030000000100000004000000000000000000444000000000000044400000000000003440000000000080464000000000008046400000000000003E4000000000000044400000000000004440010300000002000000060000000000000000003440000000000080414000000000000024400000000000003E40000000000000244000000000000024400000000000003E4000000000000014400000000000804640000000000000344000000000000034400000000000804140040000000000000000003E40000000000000344000000000000034400000000000002E40000000000000344000000000000039400000000000003E400000000000003440'
       );

SELECT
  *
FROM
  defaultdb.public.table1 AS tab_13521
  JOIN defaultdb.public.table1 AS tab_13522 ON true;

(
  SELECT
    *
  FROM
    defaultdb.public.table1 AS tab_13521
    JOIN defaultdb.public.table1 AS tab_13522 ON
        tab_13521.col1_3::GEOMETRY
        NOT IN (
            WITH
              with_341 (col_893)
                AS (
                  SELECT
                    tab_13523.col1_4 AS col_893
                  FROM
                    defaultdb.public.table1@table1_col1_0_key
                      AS tab_13523
                  WHERE
                    true
                )
            SELECT
              st_makeline(
                '01030000C0010000000A000000E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2A33FBDE96D28FAC129F4CF457C3CF3C1F0140B9AFE96CB41FECB17EE639BF5C1FC247EB9A4A1F14109C4D1CC823CF3C10C0DFBF987C1F0C1702A1D6536E6EDC1EAD22042D4950142EFEBD6B09485FBC13C047ED673D8E14180C023B327CDB6416090BD005C36FC4100CB95253527994110AD54A4EFF5DB4138A60149E149FD41367E609059ADF741A05C761FB9ACB74117BB44BCE60AFBC18E7212E96300EBC182F302118343F0417883F37F03D2E54120F5A6A828C1EB411812EB8CC687DB416047BC2FA05BE9C1D8280DA81393FB410044FB875BBAEE41264046DE3D4D00C29BCEDD83824C01C270909E5D4896FF4144A4A6C7236FEE4130F610770050DCC1E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2'::GEOMETRY
              )::GEOMETRY::GEOMETRY
                AS col_894
            FROM
              with_341 AS cte_ref_115
            WHERE
              true
            GROUP BY
              cte_ref_115.col_893
            LIMIT
              23
          )
)
UNION ALL
  (
    SELECT
      *
    FROM
      defaultdb.public.table1 AS tab_13521
      JOIN defaultdb.public.table1 AS tab_13522 ON
          NOT
            (
              tab_13521.col1_3::GEOMETRY
              NOT IN (
                  WITH
                    with_341 (col_893)
                      AS (
                        SELECT
                          tab_13523.col1_4 AS col_893
                        FROM
                          defaultdb.public.table1@table1_col1_0_key
                            AS tab_13523
                        WHERE
                          true
                      )
                  SELECT
                    st_makeline(
                      '01030000C0010000000A000000E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2A33FBDE96D28FAC129F4CF457C3CF3C1F0140B9AFE96CB41FECB17EE639BF5C1FC247EB9A4A1F14109C4D1CC823CF3C10C0DFBF987C1F0C1702A1D6536E6EDC1EAD22042D4950142EFEBD6B09485FBC13C047ED673D8E14180C023B327CDB6416090BD005C36FC4100CB95253527994110AD54A4EFF5DB4138A60149E149FD41367E609059ADF741A05C761FB9ACB74117BB44BCE60AFBC18E7212E96300EBC182F302118343F0417883F37F03D2E54120F5A6A828C1EB411812EB8CC687DB416047BC2FA05BE9C1D8280DA81393FB410044FB875BBAEE41264046DE3D4D00C29BCEDD83824C01C270909E5D4896FF4144A4A6C7236FEE4130F610770050DCC1E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2'::GEOMETRY
                    )::GEOMETRY::GEOMETRY
                      AS col_894
                  FROM
                    with_341 AS cte_ref_115
                  WHERE
                    true
                  GROUP BY
                    cte_ref_115.col_893
                  LIMIT
                    23
                )
            )
  )
UNION ALL
  (
    SELECT
      *
    FROM
      defaultdb.public.table1 AS tab_13521
      JOIN defaultdb.public.table1 AS tab_13522 ON
          (
            tab_13521.col1_3::GEOMETRY
            NOT IN (
                WITH
                  with_341 (col_893)
                    AS (
                      SELECT
                        tab_13523.col1_4 AS col_893
                      FROM
                        defaultdb.public.table1@table1_col1_0_key
                          AS tab_13523
                      WHERE
                        true
                    )
                SELECT
                  st_makeline(
                    '01030000C0010000000A000000E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2A33FBDE96D28FAC129F4CF457C3CF3C1F0140B9AFE96CB41FECB17EE639BF5C1FC247EB9A4A1F14109C4D1CC823CF3C10C0DFBF987C1F0C1702A1D6536E6EDC1EAD22042D4950142EFEBD6B09485FBC13C047ED673D8E14180C023B327CDB6416090BD005C36FC4100CB95253527994110AD54A4EFF5DB4138A60149E149FD41367E609059ADF741A05C761FB9ACB74117BB44BCE60AFBC18E7212E96300EBC182F302118343F0417883F37F03D2E54120F5A6A828C1EB411812EB8CC687DB416047BC2FA05BE9C1D8280DA81393FB410044FB875BBAEE41264046DE3D4D00C29BCEDD83824C01C270909E5D4896FF4144A4A6C7236FEE4130F610770050DCC1E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2'::GEOMETRY
                  )::GEOMETRY::GEOMETRY
                    AS col_894
                FROM
                  with_341 AS cte_ref_115
                WHERE
                  true
                GROUP BY
                  cte_ref_115.col_893
                LIMIT
                  23
              )
          ) IS NULL
  );

michae2 · 2022-07-24T17:53:32Z

Results of the two queries:

  col1_0 |   col1_1   |                                      col1_2                                      |                                                                                                                                                                                                                                                                     col1_3                                                                                                                                                                                                                                                                     | col1_4 | col1_0 |   col1_1   |                                      col1_2                                      |                                                                                                                                                                                                                                                                     col1_3                                                                                                                                                                                                                                                                     | col1_4
---------+------------+----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+------------+----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------
  NULL   | 1986-09-20 | BOX(-0.9553617556250505 0.373642371510408,-0.5504995924074046 0.882407929762621) | 01060000000200000001030000000100000004000000000000000000444000000000000044400000000000003440000000000080464000000000008046400000000000003E4000000000000044400000000000004440010300000002000000060000000000000000003440000000000080414000000000000024400000000000003E40000000000000244000000000000024400000000000003E4000000000000014400000000000804640000000000000344000000000000034400000000000804140040000000000000000003E40000000000000344000000000000034400000000000002E40000000000000344000000000000039400000000000003E400000000000003440 | %"     | NULL   | 1986-09-20 | BOX(-0.9553617556250505 0.373642371510408,-0.5504995924074046 0.882407929762621) | 01060000000200000001030000000100000004000000000000000000444000000000000044400000000000003440000000000080464000000000008046400000000000003E4000000000000044400000000000004440010300000002000000060000000000000000003440000000000080414000000000000024400000000000003E40000000000000244000000000000024400000000000003E4000000000000014400000000000804640000000000000344000000000000034400000000000804140040000000000000000003E40000000000000344000000000000034400000000000002E40000000000000344000000000000039400000000000003E400000000000003440 | %"
(1 row)


Time: 2ms total (execution 1ms / network 0ms)

  col1_0 |   col1_1   |                                      col1_2                                      |                                                                                                                                                                                                                                                                     col1_3                                                                                                                                                                                                                                                                     | col1_4 | col1_0 |   col1_1   |                                      col1_2                                      |                                                                                                                                                                                                                                                                     col1_3                                                                                                                                                                                                                                                                     | col1_4
---------+------------+----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+------------+----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------
  NULL   | 1986-09-20 | BOX(-0.9553617556250505 0.373642371510408,-0.5504995924074046 0.882407929762621) | 01060000000200000001030000000100000004000000000000000000444000000000000044400000000000003440000000000080464000000000008046400000000000003E4000000000000044400000000000004440010300000002000000060000000000000000003440000000000080414000000000000024400000000000003E40000000000000244000000000000024400000000000003E4000000000000014400000000000804640000000000000344000000000000034400000000000804140040000000000000000003E40000000000000344000000000000034400000000000002E40000000000000344000000000000039400000000000003E400000000000003440 | %"     | NULL   | 1986-09-20 | BOX(-0.9553617556250505 0.373642371510408,-0.5504995924074046 0.882407929762621) | 01060000000200000001030000000100000004000000000000000000444000000000000044400000000000003440000000000080464000000000008046400000000000003E4000000000000044400000000000004440010300000002000000060000000000000000003440000000000080414000000000000024400000000000003E40000000000000244000000000000024400000000000003E4000000000000014400000000000804640000000000000344000000000000034400000000000804140040000000000000000003E40000000000000344000000000000034400000000000002E40000000000000344000000000000039400000000000003E400000000000003440 | %"
  NULL   | 1986-09-20 | BOX(-0.9553617556250505 0.373642371510408,-0.5504995924074046 0.882407929762621) | 01060000000200000001030000000100000004000000000000000000444000000000000044400000000000003440000000000080464000000000008046400000000000003E4000000000000044400000000000004440010300000002000000060000000000000000003440000000000080414000000000000024400000000000003E40000000000000244000000000000024400000000000003E4000000000000014400000000000804640000000000000344000000000000034400000000000804140040000000000000000003E40000000000000344000000000000034400000000000002E40000000000000344000000000000039400000000000003E400000000000003440 | %"     | NULL   | 1986-09-20 | BOX(-0.9553617556250505 0.373642371510408,-0.5504995924074046 0.882407929762621) | 01060000000200000001030000000100000004000000000000000000444000000000000044400000000000003440000000000080464000000000008046400000000000003E4000000000000044400000000000004440010300000002000000060000000000000000003440000000000080414000000000000024400000000000003E40000000000000244000000000000024400000000000003E4000000000000014400000000000804640000000000000344000000000000034400000000000804140040000000000000000003E40000000000000344000000000000034400000000000002E40000000000000344000000000000039400000000000003E400000000000003440 | %"
(2 rows)


Time: 9ms total (execution 8ms / network 0ms)

DrewKimball · 2022-08-23T22:23:12Z

This one looks like a bug in our logical properties calculation for st_makeline; we incorrectly mark it as non-null because its input is non-null. However, st_makeline can return null when its input is a non-null geometry of the wrong type (should we be erroring instead?)

I think there may be a similar problem with st_extent (returns null when the input is empty).

mgartner · 2022-08-23T22:31:02Z

Postgres also returns NULL in that case, but does have a notice:

marcus=# SELECT st_makeline(
                    '01030000C0010000000A000000E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2A33FBDE96D28FAC129F4CF457C3CF3C1F0140B9AFE96CB41FECB17EE639BF5C1FC247EB9A4A1F14109C4D1CC823CF3C10C0DFBF987C1F0C1702A1D6536E6EDC1EAD22042D4950142EFEBD6B09485FBC13C047ED673D8E14180C023B327CDB6416090BD005C36FC4100CB95253527994110AD54A4EFF5DB4138A60149E149FD41367E609059ADF741A05C761FB9ACB74117BB44BCE60AFBC18E7212E96300EBC182F302118343F0417883F37F03D2E54120F5A6A828C1EB411812EB8CC687DB416047BC2FA05BE9C1D8280DA81393FB410044FB875BBAEE41264046DE3D4D00C29BCEDD83824C01C270909E5D4896FF4144A4A6C7236FEE4130F610770050DCC1E3541998A11EF4C1C67BC6F18E0CECC16004C47A6465BEC16261323B0A0B02C2'::GEOMETRY
                  );
NOTICE:  00000: No points or linestrings in input array
LOCATION:  LWGEOM_makeline_garray, lwgeom_functions_basic.c:1529
 st_makeline
-------------
 NULL
(1 row)

I guess we assume all operators return non-null values if their input is non-null?

DrewKimball · 2022-08-24T00:34:25Z

I guess we assume all operators return non-null values if their input is non-null?

I think this is still correct in most cases - for example I don't see how st_collect could return null on non-null input. This function should be the culprit:

cockroach/pkg/sql/opt/operator.go

Lines 366 to 373 in 003c036

    
           // AggregateIsNeverNullOnNonNullInput returns true if the given aggregate 
        
           // operator never returns NULL when the input set contains at least one non-NULL 
        
           // value. This is true of most aggregates. 
        
           // 
        
           // For multi-input aggregations, returns true if the aggregate is never NULL 
        
           // when all inputs have at least a non-NULL value (though not necessarily on the 
        
           // same input row). 
        
           func AggregateIsNeverNullOnNonNullInput(op Operator) bool {

86604: scbuildstmt: added cluster version gating for declarative schema changer r=Xiang-Gu a=Xiang-Gu For each implemented DDL stmt in the new schema changer, we added a minimal supported cluster version so that we can return an unimplemented error when the cluster version is lower than the minimal supported cluster version of that DDL statement. This will be useful in mixed-version cluster where we make sure we don't use new schema changer for statements that are only supported in v22.2, but not in v22.1. Partially fix #79840 Release justification: improve safety and prevent issues in mixed-version cluster. Release note: None 86605: builtins: don't panic on placeholders in bounded staleness function r=rafiss a=rafiss fixes #86243 Release note (bug fix): Fixed a crash/panic that could occur if placeholder arguments were used with the with_min_timestamp or with_max_staleness functions. Release justification: Fix a crash caused by a panic. 86607: sql: add nil guard when formatting placeholders r=rafiss a=rafiss Fixes #85363 Release note (bug fix): Fixed a crash that could happen when formatting queries that have placeholder BitArray arguments. Release justification: Fix a panic. 86634: sql: remove sql.defaults.optimizer_use_not_visible_indexes.enabled r=mgartner,wenyihu6,rafiss a=michae2 **sql: remove sql.defaults.optimizer_use_not_visible_indexes.enabled** In 22.2 the use of cluster settings for session setting defaults is deprecated, but I forgot this when reviewing #86033. Remove the unreleased default cluster setting we added. Release note: None **sql: add more session variables to statement bundles** Add some missing session variables to statement diagnostic bundles. This isn't quite everything, but it's better than what we had before. Release note: None ---- Release justification: low-risk bug fix. 86673: insights: enable the anomaly detector r=matthewtodd a=matthewtodd This change turns on the insights "anomaly detector" by default, catching statement executions in the >p99 latency for their fingerprint that are also far enough away from median latency and above a (default 50ms) threshold. Release justification: Category 2: Bug fixes and low-risk updates to new functionality. Release note (ops change): The `sql.insights.anomaly_detection.enabled` cluster setting now defaults to true, and the `sql.insights.anomaly_detection.latency_threshold` cluster setting now defaults to 50ms, down from 100ms to complement the fixed-threshold detector's default of 100ms. 86722: opt: don't mark st_makeline and st_extend as non-null r=DrewKimball a=DrewKimball `st_makeline` can take arguments of type `POINT`, `MULTIPOINT`, and `LINESTRING`. Other types cause it to return null (even if the input is non-null). Similarly, `st_extent` returns null when the input geometry is non-null, but empty. This commit prevents these two aggregate functions from being marked as non-null, since doing so can lead to incorrect results in the cases mentioned above. Fixes #84957 Release justification: low-risk fix to bug that can lead to incorrect results Release note (bug fix): Fixed a longstanding bug that could cause the optimizer to produce an incorrect plan when aggregate functions `st_makeline` or `st_extent` were called with invalid-type and empty inputs respectively. 86747: ui: fix typo r=maryliag a=maryliag Fix typo on Execution Index Recommendation text, using singular or plural accordingly. Release justification: low risk change Release note: None 86791: sql: slight refactor to audit logging code r=rafiss a=RichardJCai Release justification: Small refactor, no functionality change Release note: None Fixes #84760 Co-authored-by: Xiang Gu <[email protected]> Co-authored-by: Rafi Shamim <[email protected]> Co-authored-by: Michael Erickson <[email protected]> Co-authored-by: Matthew Todd <[email protected]> Co-authored-by: DrewKimball <[email protected]> Co-authored-by: Marylia Gutierrez <[email protected]> Co-authored-by: richardjcai <[email protected]>

`st_makeline` can take arguments of type `POINT`, `MULTIPOINT`, and `LINESTRING`. Other types cause it to return null (even if the input is non-null). Similarly, `st_extent` returns null when the input geometry is non-null, but empty. This commit prevents these two aggregate functions from being marked as non-null, since doing so can lead to incorrect results in the cases mentioned above. Fixes #84957 Release justification: low-risk fix to bug that can lead to incorrect results Release note (bug fix): Fixed a longstanding bug that could cause the optimizer to produce an incorrect plan when aggregate functions `st_makeline` or `st_extent` were called with invalid-type and empty inputs respectively.

`st_makeline` can take arguments of type `POINT`, `MULTIPOINT`, and `LINESTRING`. Other types cause it to return null (even if the input is non-null). Similarly, `st_extent` returns null when the input geometry is non-null, but empty. This commit prevents these two aggregate functions from being marked as non-null, since doing so can lead to incorrect results in the cases mentioned above. Fixes cockroachdb#84957 Release justification: low-risk fix to bug that can lead to incorrect results Release note (bug fix): Fixed a longstanding bug that could cause the optimizer to produce an incorrect plan when aggregate functions `st_makeline` or `st_extent` were called with invalid-type and empty inputs respectively.

`st_makeline` can take arguments of type `POINT`, `MULTIPOINT`, and `LINESTRING`. Other types cause it to return null (even if the input is non-null). Similarly, `st_extent` returns null when the input geometry is non-null, but empty. This commit prevents these two aggregate functions from being marked as non-null, since doing so can lead to incorrect results in the cases mentioned above. Fixes #84957 Release justification: low-risk fix to bug that can lead to incorrect results Release note (bug fix): Fixed a longstanding bug that could cause the optimizer to produce an incorrect plan when aggregate functions `st_makeline` or `st_extent` were called with invalid-type and empty inputs respectively.

cockroach-teamcity added this to the 22.1 milestone Jul 23, 2022

blathers-crl bot added the T-sql-queries SQL Queries Team label Jul 23, 2022

michae2 changed the title ~~roachtest: tlp failed~~ roachtest: tlp failed on complex join condition involving geometry Jul 24, 2022

michae2 removed the release-blocker Indicates a release-blocker. Use with branch-release-2x.x label to denote which branch is blocked. label Jul 25, 2022

DrewKimball self-assigned this Aug 16, 2022

DrewKimball mentioned this issue Aug 24, 2022

opt: don't mark st_makeline and st_extend as non-null #86722

Merged

craig bot closed this as completed in d000954 Aug 25, 2022

blathers-crl bot mentioned this issue Sep 28, 2022

release-22.1: opt: don't mark st_makeline and st_extend as non-null #88952

Merged

blathers-crl bot mentioned this issue Sep 28, 2022

release-21.2: opt: don't mark st_makeline and st_extend as non-null #88954

Merged

mgartner added this to SQL Queries Jul 24, 2023

mgartner moved this to Done in SQL Queries Jul 24, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

roachtest: tlp failed on complex join condition involving geometry #84957

roachtest: tlp failed on complex join condition involving geometry #84957

cockroach-teamcity commented Jul 23, 2022 •

edited by cockroach-jira-scripts

Loading

michae2 commented Jul 24, 2022

michae2 commented Jul 24, 2022

DrewKimball commented Aug 23, 2022

mgartner commented Aug 23, 2022

DrewKimball commented Aug 24, 2022

roachtest: tlp failed on complex join condition involving geometry #84957

roachtest: tlp failed on complex join condition involving geometry #84957

Comments

cockroach-teamcity commented Jul 23, 2022 • edited by cockroach-jira-scripts Loading

michae2 commented Jul 24, 2022

michae2 commented Jul 24, 2022

DrewKimball commented Aug 23, 2022

mgartner commented Aug 23, 2022

DrewKimball commented Aug 24, 2022

cockroach-teamcity commented Jul 23, 2022 •

edited by cockroach-jira-scripts

Loading