roachtest: schemachange/random-load failed [using NaN values does not work for unique constraints]

[cockroach-teamcity]

roachtest.schemachange/random-load failed with artifacts on master @ f0116ea373a2b87155e7f0264df4f783ce177360:

(schemachange_random_load.go:123).runSchemaChangeRandomLoad: full command output in run_180843.638172978_n1_workload-run-schemac.log: COMMAND_PROBLEM: exit status 1
test artifacts and logs in: /artifacts/schemachange/random-load/run_1

Parameters:

• ROACHTEST_arch=amd64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_fs=ext4
• ROACHTEST_localSSD=true
• ROACHTEST_metamorphicBuild=false
• ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

/cc @cockroachdb/sql-foundations

This test on roachdash | Improve this report!

Jira issue: CRDB-36866

[rafiss]

  {
   "sql": "INSERT INTO schema_w19_25.table_w19_83 (col83_w19_84,col83_w19_85,col83_w19_86,col83_w19_87,col83_w19_88,col83_w19_89) VALUES ('01050000C000000000':::GEOMETRY,'0cb0acb1-85aa-4153-a89d-50e0aa784493':::UUID,'0103000020E610000000000000':::GEOGRAPHY,2.038927883002937359E+35:::DECIMAL,NULL,'01050000E0E61000000800000001020000C0050000004615AF757D206640FFF4CC85EE3341C00008A656BD7451C18ECDD8CB38100042F6C864F636166340FC2353B6602F4E40A2DC7F734397F9C1861D7F632CE8EDC1E3EE772A692650C008FAE9F4CE5C4F408094EF5A58E9B8411422E4EC77730242B34DA1027EFB62C0B6A771BDE24D50403C0E432414C20042B8DDA6916F90D3C1157CABD1169F51C0E0611B203C01444040AA74020517BA4174C098D213A8E54101020000C002000000F4BD0BD8686454C09AED20563F0E51405D1F91AC5E94F7C1E0494750CCDDC8C1B6EF942BAA2566C0DA9E43895CD5524068582A84640EE3C1C009CE22C5EFFFC101020000C005000000F1415B2FE1DD54C000ACDF99014131C064D49FD11D5102C27C45A6BCA57CE6C11CEE42CD17E641C0701D81A10607104094B719A9537CF241C03EA26CC4B2FE41800B7FA0D5E15B40CE7CB15F70643CC07E300BCDDD7DF741FAA25DA58A27FFC180E4BB95DF5B4840087DA7C8AEB83C4090F8FD4E05F1CCC1DA512423F80702C254C565B517544E40BE999953D2244040D0884311FEAECA4100D4E9CD3BF16E4101020000C009000000B7A93DCFC2B855C090FD91E6278224C0D4CB21EE7081FE41B5A9280B9750F3C11BB20B1F906B65C09EB8E8A9D3C054C0CC7EF0D1F445E04169880C94F7F500C26691E13F42CA60C07C556DB10E4855C024C26B9229F2D9C1201DE273ABC4DB4148C3749A25F03FC0CB272039458C51C03CF6293D6D9CEC418814D66732BE00C2AC611786FE5C4340B800AB326E012C401C418F485D84E541143161C8C388F041B802C42607BC5940E88733C142D84440BA210EF41F80F5C1D90D1F64985DF0C1CFE6CF4CAEA651C0B4D44777B4F23240C411EA5E22BAFB41965C4FBFF71CFA417F3775E5609364C0B83409921E06534006D4DF6AA2270242E46C02067661FC4122BB78E6250B66C0684FAD27834628403A8EEAD33D4FF7C1BCCC57436212FE4101020000C00800000020188112835020C06050F66AAF132DC0DCC00CC18F4CE8412875D2500525D541F03721626C6F20C09EC9CFDB76D14CC0BA6A2B993B8CF6C1808705A0BC45FE4148521670327F60404C6A3F1994F052C0FC74453F4FF5E0C154E1E666A380FBC1BCB2FB1FD9566140F81FC31E5FF94D40E46CCF95D017E2C17C908E023863F3C1A82F63AD947A55408C0102B6B3065640405A7C60633BC9C18F7B60D9D5FC01C2D8594C2C6DFD55C00098F1E037E9524000148E9C31BFC3C1D03F6186EC1BE741EF4D6E3D3C3953C0C8302EBA53F449406466702D3C10E74140CD79A58B77F1418ED6577C6A7F64C040F702C396D63A4084A659CFB161ED4138F57ABDB518EB4101020000C002000000F002349F4D8125C0A6E09541BC6155C0D4352C727E0FE94195B17F21E9AEF5C1F0939088DF40504069DA011A9B074AC0E44E079286FEDAC168C770ADC01FFD4101020000C005000000DEFA54106FF54CC030536EB3341A36C0B0F527A2825CF6C18727B3713C02F2C18E7E0FCE4CBD6040B1FFC08A144D41C074A87AE7EF6DF94194D7540E1C7CE641B8D9B03AB8125E407C7B8A555D7F334000E38C50241AE941C080BA1C530BBC4138F5D8273FF64340A41965FC396B334042531BFFA548FFC1FEA18A097992F8C15C33A90883C742C0A46C2CB4F2CD3440A2A56514348FF1C1462E5BD7297E024201020000C009000000A0B26865D0C24BC070DD98763D9B3EC070B449D7F17B0142E0A205CB1050CDC126F87EC6E5854FC0B41BB1AC30EE54C0B456BD61D770FD417A914D25E5ADF641AC85503820054A40569D28FEBA8341C09EDF51CF0770F3C1822CE1D5DA3DEDC1BC65CFFCE2836040F820C5A01ED23140C00133110B1EA24179EF991E806CF0C1C858E179B54258409EF5163BC21A5140F462E592533BE2C13C4F2B74FA2BFC4110F45F1390E320C00ECDB16FB1645140A69A2852766F0042C0FFD39A585DB3C1E8624B15AA314FC0BC5CACE1749B5140BC23F562D897FD41585751EBFB17E7C1DD259AE189185BC03E9F678F801A5340507A21935875D3C151398A4A83B2F7C19C30F1D0AF3360C048E037035AF1434040687931AB80CFC1A09DE004EBAAB241':::GEOGRAPHY)"
  }
 ],
 "expectedExecErrors": "",
 "expectedCommitErrors": "",
 "message": "***UNEXPECTED ERROR; Received an unexpected execution error.: ERROR: duplicate key value violates unique constraint \"table_w19_83_expr_key\" (SQLSTATE 23505)",

[annrpom]

full txn:

"BEGIN",
  {
   "sql": "CREATE TABLE IF NOT EXISTS schema_w19_25.table_w19_83 (col83_w19_84 GEOMETRY NOT NULL, col83_w19_85 UUID NULL, col83_w19_86 GEOGRAPHY NOT NULL, col83_w19_87 DECIMAL NOT NULL, col83_w19_88 TSQUERY NULL, col83_w19_89 GEOGRAPHY NOT NULL, \"col\\\\uD97F83_w19_90\" STRING NOT NULL AS (lower(CAST(col83_w19_86 AS STRING))) STORED, INDEX (col83_w19_87) PARTITION BY LIST (col83_w19_87) (PARTITION \"t ablE_83_part_0\" VALUES IN (((-7925.986910022135752):::DECIMAL,), (325.1005861012552977:::DECIMAL,), ((-3.593443130059552152E+35):::DECIMAL,), ((-1.0895304462599777E+35):::DECIMAL,), (6539126950201.523828:::DECIMAL,), ((-58.01188687050376443):::DECIMAL,), (5.671561289832152420E+37:::DECIMAL,), (5.544653779718502635E+23:::DECIMAL,), ((-675108661.3651386132):::DECIMAL,), (7713406138525.630580:::DECIMAL,)), PARTITION table_83_part_1 VALUES IN (((-0.523799698873371894):::DECIMAL,), (6.363945477054649847E+36:::DECIMAL,), (591447569385585.2376:::DECIMAL,), (119050637837.0954975:::DECIMAL,), (3.871734178662057626E+30:::DECIMAL,), ((-6.226031469961921280E+19):::DECIMAL,), (7.053055715439608492E+36:::DECIMAL,), (1:::DECIMAL,), ((-591851567.868547498):::DECIMAL,), (3.457001752980497024E+29:::DECIMAL,)), PARTITION \"DEFAULT\" VALUES IN ((DEFAULT,))), UNIQUE ((col83_w19_87 + 'NaN':::DECIMAL) ASC) STORING (col83_w19_84, col83_w19_87, \"col\\\\uD97F83_w19_90\"), INDEX (\"col\\\\uD97F83_w19_90\") STORING (col83_w19_86, col83_w19_87, col83_w19_88), INVERTED INDEX (col83_w19_84 ASC))"
  },
  {
   "sql": "CREATE SEQUENCE IF NOT EXISTS schema_w19_25.seq_w19_91"
  },
  {
   "sql": "INSERT INTO schema_w19_25.table_w19_83 (col83_w19_84,col83_w19_85,col83_w19_86,col83_w19_87,col83_w19_88,col83_w19_89) VALUES ('010200000000000000':::GEOMETRY,'7ecd8460-28c6-465b-bbc8-18c3c6cf8f37':::UUID,'01050000E0E61000000900000001020000C005000000E820338855B95DC02E159AECB97C4DC0F40794F9A52CF2410DDFE504F195F2C174A11C6F61296140A8CBCE71C0CE51C0DCF3B93D14C3EC41A78B8D0D3421F3C1200500C4C8A157402C83B367FE3635C005F9576AA3F8FEC18635FFF128EBF4410E0545F5916E6340A0B1BFD1F9FC4640C0AC608229D5C6C10E9EA567756B02C22C0A29DD7B865940D0E84334E2E647409A68E6D8F62AFCC1387E61150834D2C101020000C00400000000B7E21F42C65C40124D11FE7AD64EC03A7233B7AF6AE8C1E87D3BC7A94ED9415C1057FF73CC5A40A0996559E7FD21C06849498AF154FBC1A8F7939B2FFEF241983D8C3D37895A401C3112465A674140C66046099C8EE4C1D0BB4DFAF2F7FB41D44819F8FDEF5840882C76B5A63A5340702247AFEC6CCAC1FA2EB9A337A2004201020000C0030000009E2D16E12C0F6440A3219F3B118C52C064C7A25EB324FC41449984FA0367E241F090268021BA5B40E2B52310FBAD3FC06135A77B1C8CFBC180F057C65222BCC1848563EEA2AB64405C7E1072AF07384080C384CFE606FE412221637335F601C201020000C00200000048185AB13AF0474025E313B31CB850C08024CBAFBA25DB41BCFD999A6DBA00427880EE5ADE745840A6BBE8159DEC55C0089AD6174D6902C2206628E8425EF04101020000C003000000AC41265F96C84EC0624C52C4427650408E56DF1F4D180042C679C27ABAD5FEC160EE588454072AC0C0A0E8C8C42329401C908582A083F341E8D1C31053A9EA4165861B4430B760C0205DCB2114931440D0E65FA117B1DF413DF79BF052D000C201020000C002000000C0B4559BF9446440085858DC584452406034182E6337BB418B53AFD19DA6F4C150D60E532ED65A4048DB4885EF854D40D0D6E44A9A35CA41A033CA73769AB9C101020000C002000000F8A54F9986F944C03E7150BDC9AB52C040F51AB309D7E941501D0D3C0B55FEC1AA8193F8B3274AC0996BE5A4886E56C04030B6A0BA1BA04108E142E359C3FD4101020000C0020000000898D903760C5F409CFDE9215835484068DB9936C546DD413446A79442F0FDC168E70F671A1E3E40F8CA9747657F5640B8AB2CBCCCB2D2C1801423B4E7DBA44101020000C0030000006BA22C29D4AA61C0F26FA8F7FB514EC0104F3C3F8189EC41AB98B51B784FF1C1B41F44BDE6444240A0B1EE26A96A56C03C80ACB33646FD41605989FD7332D3C1EC0F65F9F4A64840896BF359FDF240C0F405516CEA9DF9C170D7F844B83AC6C1':::GEOGRAPHY,(-2.905158470357292346E+23):::DECIMAL,'''cBjJl'' | !''aM''':::TSQUERY,'01030000E0E6100000010000000800000084E7D04D3D1856C0284C818BB91A2BC016285E0A46C5F34150FF693D7319FAC19C4C4386468D57C07451B8E0BCD943C0CCF59673BFBBEA411214161CCCD2F8C1A817BCA15A363DC0202B223F3AA61C4000748E2296346BC1D2E4080F022AFDC134242DD331B45A40C0A21F5E26F8164050D0ABA59168F1417065D2673077F84178E7811A3BBB3E40D4DC72BF0B4556401ED934273C0C0042803CA75ADCD5E6C1C8DAF986E3AC38C0F4D14A3D05A14D40FC9D362ACB940142F27ABDBB5CC1FCC18A45C98DCA7B5AC0D4BC482B254C544060886407E342B9C1C4FC929FB35EEE4184E7D04D3D1856C0284C818BB91A2BC016285E0A46C5F34150FF693D7319FAC1':::GEOGRAPHY)"
  },
  {
   "sql": "DROP FUNCTION IF EXISTS \"NoSuchFunction\""
  },
  {
   "sql": "INSERT INTO schema_w19_25.table_w19_83 (col83_w19_84,col83_w19_85,col83_w19_86,col83_w19_87,col83_w19_88,col83_w19_89) VALUES ('01050000C000000000':::GEOMETRY,'0cb0acb1-85aa-4153-a89d-50e0aa784493':::UUID,'0103000020E610000000000000':::GEOGRAPHY,2.038927883002937359E+35:::DECIMAL,NULL,'01050000E0E61000000800000001020000C0050000004615AF757D206640FFF4CC85EE3341C00008A656BD7451C18ECDD8CB38100042F6C864F636166340FC2353B6602F4E40A2DC7F734397F9C1861D7F632CE8EDC1E3EE772A692650C008FAE9F4CE5C4F408094EF5A58E9B8411422E4EC77730242B34DA1027EFB62C0B6A771BDE24D50403C0E432414C20042B8DDA6916F90D3C1157CABD1169F51C0E0611B203C01444040AA74020517BA4174C098D213A8E54101020000C002000000F4BD0BD8686454C09AED20563F0E51405D1F91AC5E94F7C1E0494750CCDDC8C1B6EF942BAA2566C0DA9E43895CD5524068582A84640EE3C1C009CE22C5EFFFC101020000C005000000F1415B2FE1DD54C000ACDF99014131C064D49FD11D5102C27C45A6BCA57CE6C11CEE42CD17E641C0701D81A10607104094B719A9537CF241C03EA26CC4B2FE41800B7FA0D5E15B40CE7CB15F70643CC07E300BCDDD7DF741FAA25DA58A27FFC180E4BB95DF5B4840087DA7C8AEB83C4090F8FD4E05F1CCC1DA512423F80702C254C565B517544E40BE999953D2244040D0884311FEAECA4100D4E9CD3BF16E4101020000C009000000B7A93DCFC2B855C090FD91E6278224C0D4CB21EE7081FE41B5A9280B9750F3C11BB20B1F906B65C09EB8E8A9D3C054C0CC7EF0D1F445E04169880C94F7F500C26691E13F42CA60C07C556DB10E4855C024C26B9229F2D9C1201DE273ABC4DB4148C3749A25F03FC0CB272039458C51C03CF6293D6D9CEC418814D66732BE00C2AC611786FE5C4340B800AB326E012C401C418F485D84E541143161C8C388F041B802C42607BC5940E88733C142D84440BA210EF41F80F5C1D90D1F64985DF0C1CFE6CF4CAEA651C0B4D44777B4F23240C411EA5E22BAFB41965C4FBFF71CFA417F3775E5609364C0B83409921E06534006D4DF6AA2270242E46C02067661FC4122BB78E6250B66C0684FAD27834628403A8EEAD33D4FF7C1BCCC57436212FE4101020000C00800000020188112835020C06050F66AAF132DC0DCC00CC18F4CE8412875D2500525D541F03721626C6F20C09EC9CFDB76D14CC0BA6A2B993B8CF6C1808705A0BC45FE4148521670327F60404C6A3F1994F052C0FC74453F4FF5E0C154E1E666A380FBC1BCB2FB1FD9566140F81FC31E5FF94D40E46CCF95D017E2C17C908E023863F3C1A82F63AD947A55408C0102B6B3065640405A7C60633BC9C18F7B60D9D5FC01C2D8594C2C6DFD55C00098F1E037E9524000148E9C31BFC3C1D03F6186EC1BE741EF4D6E3D3C3953C0C8302EBA53F449406466702D3C10E74140CD79A58B77F1418ED6577C6A7F64C040F702C396D63A4084A659CFB161ED4138F57ABDB518EB4101020000C002000000F002349F4D8125C0A6E09541BC6155C0D4352C727E0FE94195B17F21E9AEF5C1F0939088DF40504069DA011A9B074AC0E44E079286FEDAC168C770ADC01FFD4101020000C005000000DEFA54106FF54CC030536EB3341A36C0B0F527A2825CF6C18727B3713C02F2C18E7E0FCE4CBD6040B1FFC08A144D41C074A87AE7EF6DF94194D7540E1C7CE641B8D9B03AB8125E407C7B8A555D7F334000E38C50241AE941C080BA1C530BBC4138F5D8273FF64340A41965FC396B334042531BFFA548FFC1FEA18A097992F8C15C33A90883C742C0A46C2CB4F2CD3440A2A56514348FF1C1462E5BD7297E024201020000C009000000A0B26865D0C24BC070DD98763D9B3EC070B449D7F17B0142E0A205CB1050CDC126F87EC6E5854FC0B41BB1AC30EE54C0B456BD61D770FD417A914D25E5ADF641AC85503820054A40569D28FEBA8341C09EDF51CF0770F3C1822CE1D5DA3DEDC1BC65CFFCE2836040F820C5A01ED23140C00133110B1EA24179EF991E806CF0C1C858E179B54258409EF5163BC21A5140F462E592533BE2C13C4F2B74FA2BFC4110F45F1390E320C00ECDB16FB1645140A69A2852766F0042C0FFD39A585DB3C1E8624B15AA314FC0BC5CACE1749B5140BC23F562D897FD41585751EBFB17E7C1DD259AE189185BC03E9F678F801A5340507A21935875D3C151398A4A83B2F7C19C30F1D0AF3360C048E037035AF1434040687931AB80CFC1A09DE004EBAAB241':::GEOGRAPHY)"
  }
 ],

DETAIL: Key (col83_w19_87 + 'NaN':::DECIMAL)=(NaN) already exists.
CONSTRAINT: table_w19_83_expr_key

[annrpom]

the simple repro is the following:

CREATE TABLE t (
    id SERIAL PRIMARY KEY,
    col83_w19_87 DECIMAL
);

CREATE UNIQUE INDEX t_expr_key 
ON t ((col83_w19_87 + 'NaN'::DECIMAL));

insert into t values(1, 2.10);

insert into t values(2, 3.10);

> ERROR: duplicate key value violates unique constraint "t_expr_key"
SQLSTATE: 23505
DETAIL: Key (col83_w19_87 + 'NaN':::DECIMAL)=(NaN) already exists.
CONSTRAINT: t_expr_key

perhaps avoiding exprs that would generate NaN in unique constraints is what we should do

we could also make NaNs not equal, but that seems like a hot take and also deviates from postgres compat

[rafiss]

nice find! yes, i think it's best to avoid NaNs for unique constraint definitions. maybe we could avoid NaN for any generated expression in DDL?

[annrpom]

i haven't been able to reproduce this in the schemachange workload easily (i was hoping to get a debugger in there)

i think i should cc: @cockroachdb/sql-queries here before i get lost in the sauce -

q for queries: is

cockroach/pkg/sql/randgen/schema.go

Line 594 in 66517cd

expr, semType, _, referencedCols = randExpr(rng, eligibleExprIndexRefs, false /* nullOk */)

the culprit? do we want to ensure that any RandDatum we generate in our expression

cockroach/pkg/sql/randgen/expr.go

Lines 219 to 222 in 6b662e3

	expr = &tree.BinaryExpr{
	Operator: treebin.MakeBinaryOperator(treebin.Plus),
	Left: tree.NewUnresolvedName(string(x.Name)),
	Right: RandDatum(rng, xTyp, nullOk),

isn't going to be some expression that produces a constant result (like NaN or infinity) whenever a unique constraint exists on the index/column we are making an expression for?

[yuzefovich]

The idea to adjust randExpr so that it doesn't accept NaNs (and probably infinities) from RandDatum sounds good to me. In other words, I'd modify randExpr to call RandDatum in a loop until an acceptable datum is returned.

[cockroach-teamcity]

roachtest.schemachange/random-load failed with artifacts on master @ 04e1faeb6b674729e989e001d37429786b584c6e:

(schemachange_random_load.go:123).runSchemaChangeRandomLoad: full command output in run_173400.849254453_n1_workload-run-schemac.log: COMMAND_PROBLEM: exit status 1
test artifacts and logs in: /artifacts/schemachange/random-load/run_1

Parameters:

• ROACHTEST_arch=amd64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_fs=ext4
• ROACHTEST_localSSD=true
• ROACHTEST_metamorphicBuild=false
• ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

[github-actions]

We have marked this test failure issue as stale because it has been
inactive for 1 month. If this failure is still relevant, removing the
stale label or adding a comment will keep it active. Otherwise,
we'll close it in 5 days to keep the test failure queue tidy.

[rafiss]

Given that this NaN issue hasn't reoccurred in a while, I propose we don't spend time working on it unless it occurs more.