roachtest/costfuzz: incorrect results with st_simplifypreservetopology in some cases

[cockroach-teamcity]

roachtest.costfuzz failed with artifacts on release-22.2 @ ca300add723ec3cc4404cacd56ee85e74d0b29ed:

test artifacts and logs in: /artifacts/costfuzz/run_1
(query_comparison_util.go:246).runOneRoundQueryComparison: . 1041 statements run: expected unperturbed and perturbed results to be equal
  []string{
  	... // 151 identical elements
  	"0a4d0a4d-0a4d-0a4d-0a4d-0a4d0a4d0a4d,1.012335259315146,NULL,0,,1"...,
  	"0a4d0a4d-0a4d-0a4d-0a4d-0a4d0a4d0a4d,1.017419931374874,010100000"...,
  	strings.Join({
  		... // 833 identical bytes
  		"241010200008002000000FF6F0F8D665CFFC170A206EDFA9BC5C1CE8D7FF30D3",
  		"3E5C131E2FDE8D142F0C1B08470677248DB41584D53201C17DA4101020000800",
- 		"6",
+ 		"3",
  		"0000005340F8A60CF3F1C15A4C524B7D78F7C160204B7E0C40E9",
- 		"C1708370C6B8FCE0C1525B6AE4C611F8C14BDA1C4DDE6C00C2302816DF048CC0",
- 		"41174CED924F17FEC1D04A1B840CBFC3C1A854280FC2E2E6419430F0852A4601",
- 		"C20C997069EB06F6",
  		"C124456A081F7BFF411F2FF86C981B02C258D235A29CF8D841306D4A41D0F0FE",
  		"419C4DC871D698F341988D7638FEB2FC41010200008002000000C0539F37773B",
  		... // 2360 identical bytes
  	}, ""),
  	"0a4d0a4d-0a4d-0a4d-0a4d-0a4d0a4d0a4d,1.0793854811858994,01020000"...,
  	"0a4d0a4d-0a4d-0a4d-0a4d-0a4d0a4d0a4d,1.105750379921529,NULL,0,,1"...,
  	... // 137 identical elements
  }
sql: SELECT
	'0a4d0a4d-0a4d-0a4d-0a4d-0a4d0a4d0a4d':::UUID AS col_178,
	tab_60.col2_8 AS col_179,
	st_simplifypreservetopology(tab_60.col2_0::GEOMETRY, 1.2345678901234556e+39:::FLOAT8::FLOAT8)::GEOMETRY AS col_180,
	tab_60.col2_6 AS col_181,
	'':::STRING AS col_182,
	tab_60.tableoid AS col_183,
	tab_60.col2_10 AS col_184,
	NULL AS col_185,
	'00:00:10':::INTERVAL AS col_186,
	tab_60.col2_10 AS col_187,
	tab_60.col2_13 AS col_188,
	tab_60.col2_7 AS col_189
FROM
	defaultdb.public.table2@[0] AS tab_60

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

Help

See: roachtest README

See: How To Investigate (internal)

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-27968

[msirek]

Here's a simplified test case. About half the time the EXCEPT ALL queries return a row (they should return no rows):

drop table if exists t1;
create table t1 (a geometry, b geometry);
drop table if exists t2;
create table t2 (a geometry, b geometry);
drop table if exists t3;
create table t3 (a geometry, b geometry);

insert into t3 values ('010500008009000000010200008003000000D411813FE9CBF541808B34F7D037E1415C19EA122CA5DAC1D45997253297EA419815B64E7E9CD541ADEB39B8C9FEFAC110C2E5F70092CBC128EA70F7093AFC417A26B147A4A6EDC10102000080030000000A9A66F5A5BEF941F0631670ED4CD141D8B9D49A2B9E00C20CB156A9E684FB41005DBFF8ECD2EB41390610A56F9201C28C50FFFB1255F14108915989794CF741BC756B268DF4FA410102000080030000008CB0A8E10C74EE41D4AB024D2C75EB418424C965C8B20042B829D28F2392D441DC2E7D5CE0C50142C0895AD67D75CD4188D3717F1BF2D341127D87A4FB92F5414435E5BE6546FE4101020000800400000074B50E9539A1FD419806A11977F4DC41502A64F25F7CCB41C02EC800374FC941AEB87C989158F141A46D00794334E241404522418D0AA9C14EF47FF39050F341804DC6966BEF0042A8A902B4AD30E6C1F0398192B6E8F8417ECF5149131901C2010200008002000000C20C46B4F9DCF24110D72937BE33D2C1BC14ED0135EBF8C16675C691F3AE0042746A09D8A2D3F941681818D79D7ED24101020000800A000000FF6F0F8D665CFFC170A206EDFA9BC5C1CE8D7FF30D33E5C126245B234567F0C1EE5A05051229F3C11AEAE225DA7EEFC18992FCC9E23DFBC135B93D701DE301C2D0C731EF658AE34184D5A463BEA5EAC132075620A2AAFDC1B01DFFB38401FC4138713DA25AC1F641D2D7924D2FF7FEC19D9573D42534F9C130B606DB9E32F94162E0C2B3BD9AF641C085917A089CE5C110C2E0E5F44A02428A6199C298DE0142D294C79550EDF241FE2F0D868768F541808780A9823002425D11F10A674BF3C1ADCBAE7D2E05F2C168ED45E5D041FA41444A5C9CB1F6E34131E2FDE8D142F0C1B08470677248DB41584D53201C17DA410102000080070000005340F8A60CF3F1C15A4C524B7D78F7C160204B7E0C40E9C1708370C6B8FCE0C1525B6AE4C611F8C14BDA1C4DDE6C00C2604BEA8E70FDBFC1B9AEBBBC38E1FBC12A503A14ADBAF141302816DF048CC041174CED924F17FEC1D04A1B840CBFC3C1A854280FC2E2E6419430F0852A4601C20C997069EB06F6C124456A081F7BFF411F2FF86C981B02C258D235A29CF8D841306D4A41D0F0FE419C4DC871D698F341988D7638FEB2FC41010200008002000000C0539F37773B014258CF8DBC418FF9417455A74E8643EA41C8DA72278C0EE6C126E32F2B9CEDF441905AC61396F0D3C101020000800400000020C8BBE83D3DCF41F8089A428257D7C1E8434C0A184ED3C1E26DE2B21F8BF34140364D32454DC3413A0DA399B16CF941625BF5968C89F34170DE7F681304FC41D75CDBD54D6BF7C1FA1BD535F47DF441D0D0A85F249F0042581BDF269E31FAC1');

insert into t1
SELECT
        st_simplifypreservetopology(a, 1.2345678901234556e+39) AS col_180, a
FROM
        t3;

insert into t2
SELECT
        st_simplifypreservetopology(a, 1.2345678901234556e+39) AS col_180, a
FROM
        t3;

select * from t1 EXCEPT ALL select * from t2;
select * from t2 EXCEPT ALL select * from t1;

[rharding6373]

I'm actually starting to think that this is expected behavior, possibly due to float precision differences during execution. For testing I made the following slightly simplified test case:

CREATE TABLE t3(a GEOMETRY);

INSERT INTO t3 VALUES ('010500008009000000010200008003000000D411813FE9CBF541808B34F7D037E1415C19EA122CA5DAC1D45997253297EA419815B64E7E9CD541ADEB39B8C9FEFAC110C2E5F70092CBC128EA70F7093AFC417A26B147A4A6EDC10102000080030000000A9A66F5A5BEF941F0631670ED4CD141D8B9D49A2B9E00C20CB156A9E684FB41005DBFF8ECD2EB41390610A56F9201C28C50FFFB1255F14108915989794CF741BC756B268DF4FA410102000080030000008CB0A8E10C74EE41D4AB024D2C75EB418424C965C8B20042B829D28F2392D441DC2E7D5CE0C50142C0895AD67D75CD4188D3717F1BF2D341127D87A4FB92F5414435E5BE6546FE4101020000800400000074B50E9539A1FD419806A11977F4DC41502A64F25F7CCB41C02EC800374FC941AEB87C989158F141A46D00794334E241404522418D0AA9C14EF47FF39050F341804DC6966BEF0042A8A902B4AD30E6C1F0398192B6E8F8417ECF5149131901C2010200008002000000C20C46B4F9DCF24110D72937BE33D2C1BC14ED0135EBF8C16675C691F3AE0042746A09D8A2D3F941681818D79D7ED24101020000800A000000FF6F0F8D665CFFC170A206EDFA9BC5C1CE8D7FF30D33E5C126245B234567F0C1EE5A05051229F3C11AEAE225DA7EEFC18992FCC9E23DFBC135B93D701DE301C2D0C731EF658AE34184D5A463BEA5EAC132075620A2AAFDC1B01DFFB38401FC4138713DA25AC1F641D2D7924D2FF7FEC19D9573D42534F9C130B606DB9E32F94162E0C2B3BD9AF641C085917A089CE5C110C2E0E5F44A02428A6199C298DE0142D294C79550EDF241FE2F0D868768F541808780A9823002425D11F10A674BF3C1ADCBAE7D2E05F2C168ED45E5D041FA41444A5C9CB1F6E34131E2FDE8D142F0C1B08470677248DB41584D53201C17DA410102000080070000005340F8A60CF3F1C15A4C524B7D78F7C160204B7E0C40E9C1708370C6B8FCE0C1525B6AE4C611F8C14BDA1C4DDE6C00C2604BEA8E70FDBFC1B9AEBBBC38E1FBC12A503A14ADBAF141302816DF048CC041174CED924F17FEC1D04A1B840CBFC3C1A854280FC2E2E6419430F0852A4601C20C997069EB06F6C124456A081F7BFF411F2FF86C981B02C258D235A29CF8D841306D4A41D0F0FE419C4DC871D698F341988D7638FEB2FC41010200008002000000C0539F37773B014258CF8DBC418FF9417455A74E8643EA41C8DA72278C0EE6C126E32F2B9CEDF441905AC61396F0D3C101020000800400000020C8BBE83D3DCF41F8089A428257D7C1E8434C0A184ED3C1E26DE2B21F8BF34140364D32454DC3413A0DA399B16CF941625BF5968C89F34170DE7F681304FC41D75CDBD54D6BF7C1FA1BD535F47DF441D0D0A85F249F0042581BDF269E31FAC1');

SELECT count(*)
  FROM (
    SELECT st_simplifypreservetopology(a, 1.2345678901234556e+39)
      FROM t3
    EXCEPT ALL
    SELECT st_simplifypreservetopology(a, 1.2345678901234556e+39)
      FROM t3
  ) AS foo;

If we run the last query repeatedly in postgres, we get 0 most of the time (equivalent to no rows in the original repro), but 1 some of the time (2/10 runs). In CRDB, we may get 1 slightly more frequently (3/10 runs), but that seems within expectations.

I think that equality is not the right thing to use to evaluable correctness of st_simplifypreservetopology. We should probably disable it in costfuzz and other generated tests. I can't think of a way that we can evaluate the results of the function using approximation if it's used in the middle of a query.

[rharding6373]

There's more info about the determinism of st_simplifypreservetopology in #91800. The summary there is that it was fixed somewhat recently in a third party library and a patch needs to be applied.

Dupe of #91800.