search

cockroachdb / cockroach

CockroachDB - the open source, cloud-native distributed SQL database.
https://www.cockroachlabs.com
Other
29.52k stars 3.7k forks source link

pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed [duplicate key value violates unique constraint] #125751

Open cockroach-teamcity opened 3 weeks ago

cockroach-teamcity commented 3 weeks ago

pkg/ccl/testccl/workload/schemachange/schemachange_test.TestWorkload failed on release-24.1.2-rc @ 35c3e0c133c56c91399e730f49bc488cf6dd4c11:

   },
   {
    "query": "SELECT ARRAY[parent.table_schema, parent.table_name, quote_ident(parent.column_name), quote_ident(child.column_name)] FROM (SELECT conname, conkey, confkey, conrelid, confrelid FROM pg_constraint WHERE (contype = 'f') AND (conrelid = 'schema_w0_26.table_w1_16'::REGCLASS::INT8)) AS con JOIN (SELECT column_name, ordinal_position, column_default FROM information_schema.columns WHERE (table_schema = 'schema_w0_26') AND (table_name = 'table_w1_16')) AS child ON conkey[1] = child.ordinal_position JOIN (SELECT pc.oid, cols.table_schema, cols.table_name, cols.column_name, cols.ordinal_position FROM pg_class AS pc JOIN pg_namespace AS pn ON pc.relnamespace = pn.oid JOIN information_schema.columns AS cols ON ((pc.relname = cols.table_name) AND (pn.nspname = cols.table_schema))) AS parent ON ((con.confkey[1] = parent.ordinal_position) AND (con.confrelid = parent.oid)) WHERE child.column_name != 'rowid'",
    "result": null
   }
  ],
  "previousStatements": [
   "CREATE SEQUENCE IF NOT EXISTS schema_w1_13.seq_w1_15",
   "CREATE TABLE schema_w0_26.table_w1_16 (col16_w1_17 BYTES NOT NULL, col16_w1_18 BOOL, \"col1'6_w1_19\" STRING COLLATE de_DE NOT NULL, \"co!l16_w1_20\" FLOAT8 NOT NULL, col16_w1_21 OID NULL, \"co l16_w1_22\" \"char\" NULL, col16_w1_23 OID, col16_w1_24 NAME NOT NULL, \"c!ol16_w1_25\" JSONB NULL, \"/co\"\"l16_w1_26\" INT4 NULL, \"col16'_w1_27\" FLOAT8 NOT NULL, col16_w1_28 GEOMETRY NOT NULL, col16_w1_29 BYTES, col16_w1_30 INTERVAL, \"col16\\\\xa4_w1_31\" JSONB NOT NULL, \"c̍ol\r16_w1_32\" INT8 NULL, col16_w1_33 FLOAT4 NULL, UNIQUE ((\"c̍ol\r16_w1_32\" + \"/co\"\"l16_w1_26\") ASC), INDEX (col16_w1_21 ASC) STORING (col16_w1_18, \"co l16_w1_22\", col16_w1_24, col16_w1_28, col16_w1_29, col16_w1_30, \"col16\\\\xa4_w1_31\", col16_w1_33) VISIBILITY 0.35, UNIQUE (col16_w1_21, col16_w1_33 ASC, \"co l16_w1_22\", \"co!l16_w1_20\", col16_w1_24, \"/co\"\"l16_w1_26\" ASC, col16_w1_23 DESC, \"c!ol16_w1_25\", \"col1'6_w1_19\" ASC, col16_w1_29 ASC, \"col16'_w1_27\" DESC, col16_w1_30 DESC) STORING (\"c̍ol\r16_w1_32\"), INDEX (\"/co\"\"l16_w1_26\" DESC) VISIBILITY 0.78, UNIQUE (\"co!l16_w1_20\" DESC) STORING (\"col1'6_w1_19\", \"co l16_w1_22\", col16_w1_23, \"c!ol16_w1_25\", col16_w1_29, \"c̍ol\r16_w1_32\", col16_w1_33), UNIQUE (\"c̍ol\r16_w1_32\", \"c!ol16_w1_25\" ASC) STORING (col16_w1_18, col16_w1_21, \"co l16_w1_22\", col16_w1_23, \"col16'_w1_27\", col16_w1_29), UNIQUE (col16_w1_17 ASC, col16_w1_23 DESC, \"/co\"\"l16_w1_26\", (\"co!l16_w1_20\" + col16_w1_33) ASC, \"c̍ol\r16_w1_32\" DESC, col16_w1_30 ASC, \"col1'6_w1_19\" DESC, col16_w1_18 ASC, col16_w1_33, \"co l16_w1_22\" DESC, col16_w1_21 DESC, col16_w1_24, \"col16\\\\xa4_w1_31\"), INDEX (((col16_w1_33 + \"co!l16_w1_20\") + \"col16'_w1_27\") DESC, col16_w1_17 DESC) STORING (\"co!l16_w1_20\", \"co l16_w1_22\", \"c!ol16_w1_25\", \"/co\"\"l16_w1_26\", col16_w1_28, col16_w1_30, \"col16\\\\xa4_w1_31\", \"c̍ol\r16_w1_32\", col16_w1_33), FAMILY (col16_w1_28), FAMILY (\"c!ol16_w1_25\", \"/co\"\"l16_w1_26\", \"col1'6_w1_19\"), FAMILY (col16_w1_17, \"co!l16_w1_20\", \"c̍ol\r16_w1_32\"), FAMILY (col16_w1_23), FAMILY (\"col16'_w1_27\", col16_w1_33, col16_w1_18), FAMILY (col16_w1_29, col16_w1_21, col16_w1_30, \"co l16_w1_22\"), FAMILY (\"col16\\\\xa4_w1_31\", col16_w1_24))",
   "INSERT INTO schema_w0_26.table_w1_16 (col16_w1_17,col16_w1_18,\"col1'6_w1_19\",\"co!l16_w1_20\",col16_w1_21,\"co l16_w1_22\",col16_w1_23,col16_w1_24,\"c!ol16_w1_25\",\"/co\"\"l16_w1_26\",\"col16'_w1_27\",col16_w1_28,col16_w1_29,col16_w1_30,\"col16\\\\xa4_w1_31\",\"c̍ol\r16_w1_32\",col16_w1_33) VALUES ('\\x023ced01':::BYTES,false,e'\\U000EB4C9\\U000736C7\\U000099C0\\U000921E5\\U00045705' COLLATE de_DE,0.0:::FLOAT8,3615:::OID,'r'::\"char\",90000:::OID,e'\\x1b\u003eb\\'\\x1a'::NAME:::NAME,'[{\"1hv\": 0.38380800823336125, \"7WxqdP4AB\": null, \"baz\": [null, null, \"foobar\"], \"foo\": 0.907397094710884, \"ugRAfVWU\": {\"JQJcy\": {\"a\": null, \"bar\": 3.002055136213273, \"baz\": null, \"c\": true, \"nGgF\": 2.17228485738689}, \"b\": 4.230072051680517, \"c\": null}}, [{\"SUakUeu3\": true, \"foo\": null}, true, true, \"40Trg\"], [true, \"Q7\"], 1.5442406476676414, false]':::JSONB,37:::INT8,0.03362331446017158:::FLOAT8,'01030000400100000009000000304B18C62C5BFEC14264212D9682E5C1ACE2193BAAC0E9C17890B0335096F3C1945BFC082192DCC1CC133593BEC3FB41D29EC1A5C5F5EAC1F0B6A0765CEDFAC1BC637B436FCCFCC1806F3BC1D4D8CAC170CE868D214DDEC1B26E314677B7E8C1181495FB3FB3FC41E029FDF0E578F1419E82ECF1A49702C2845C6BED763CE2C1386212840ADDD841E44EA06D06CCFCC1F204676B0AF7F7C1E67DDCA38677FA413CC0E76D70F9DBC1F777FC922135FEC14C56870AB8E2F64150F683983E32F041304B18C62C5BFEC14264212D9682E5C1ACE2193BAAC0E9C1':::GEOMETRY,'\\x':::BYTES,'-46 years -5 mons -223 days -05:40:23.327262':::INTERVAL,'{\"M67m\": [\"uO\"], \"QlAJ8\": 0.2984358503836573, \"bar\": false, \"baz\": [false, true]}':::JSONB,35:::INT8,NULL),('\\xfbd8549fcb4aaefc':::BYTES,false,e'\\U0009C42B\\U00085B2A\\U000C744F\\U000E4FDF\\U0002BB56\\U000A35F5\\U000426AC\\U000A9710\\U00090456' COLLATE de_DE,(-0.0):::FLOAT8,25:::OID,e'\\t'::\"char\",25:::OID,'xv'::NAME:::NAME,'{\"Vbd7hGv\": null, \"b\": false, \"foo\": null, \"foobar\": true, \"xGb\": [true, \"F7S31Rn\", null]}':::JSONB,(-14):::INT8,(-0.0):::FLOAT8,'010300000001000000040000005E8185305D1FFBC16EB241EE4693F9C10C7AD3BEE23EFC410275525CE3AE004250C11B155577DB41C88EE1720F1EF9415E8185305D1FFBC16EB241EE4693F9C1':::GEOMETRY,'\\x8fa9d3':::BYTES,'22 years 7 mons 766 days 21:10:49.653636':::INTERVAL,'{\"baz\": null, \"ekOJV\": \"baz\", \"foobar\": [{\"WT5\": \"ivo\", \"c\": \"ZjK\", \"pvrkZrlV4\": null}, 0.1520572319319641]}':::JSONB,80:::INT8,(-0.5707768797874451):::FLOAT8),('\\x4a865ab00cdb26':::BYTES,true,e'\\U000544BC\\U00034862\\U00002365\\U000D1477\\U00014352\\U0007CE3F\\U00012D93\\U000CED2B\\U000B2E39' COLLATE de_DE,0.565243544913826:::FLOAT8,23:::OID,NULL,1042:::OID,e'W,a\\x14oDzEs'::NAME:::NAME,'{\"TaspG7Xh\": null, \"Wp4ACJz\": [true, false], \"foo\": false}':::JSONB,(-92):::INT8,(-0.6868296594875365):::FLOAT8,'0104000040070000000101000040ACE6E1B8B953EB41F5745416CC32F2C180A1B2C83E87BE410101000040E81D08607E98DEC1FA67C4BC84C4FFC14C71DCEC952EEA4101010000400003AB4BC52EAEC1C28E8A14C92901C2A6F239AC41B7ECC10101000040F0B8E7A76A4FCB4146674AC6E246FFC1429E28587AA6FBC101010000401ED2ED63E2C200C214B7ABD0CC19E4415844B6500516EA410101000040F69ED0814A16E2C190DB9023B8D2EE41ECEDCD117894F4C101010000405076B681EA99F0C1F85A8036D74AF1416E4E1EDAED980242':::GEOMETRY,'\\x99de039695':::BYTES,'-60 years -6 mons -67 days -22:19:34.49658':::INTERVAL,'{\"CNUC\": [\"foobar\", false], \"J6kVDa5sR\": 1.1159178169162471, \"PpU1S\": [0.8765462788257372, null, 2.5343532601068546], \"c\": {\"1gnkJe\": true, \"a\": 0.09615373174932818, \"bar\": [[{\"bB\": false}, true], true, {\"c\": 2.0295542174376866, \"foobar\": 0.2833705668899665}, 2.8665298085920394, true], \"prrOQpZ\": false}}':::JSONB,125:::INT8,(-0.6805527806282043):::FLOAT8)"
  ]
 }
}
    schema_change_external_test.go:132: 
            Error Trace:    pkg/ccl/testccl/workload/schemachange/schemachange_test_test/pkg/ccl/testccl/workload/schemachange/schema_change_external_test.go:132
            Error:          Received unexpected error:
                            ***UNEXPECTED ERROR; Received an unexpected execution error.: ERROR: duplicate key value violates unique constraint "table_w1_16_co!l16_w1_20_key" (SQLSTATE 23505)
                            (1) forced error mark
                              | "fatal error when running txn"
                              | github.com/cockroachdb/errors/withstack/*withstack.withStack::
                            Wraps: (2)
                            Wraps: (3) attached stack trace
                              -- stack trace:
                              | github.com/cockroachdb/cockroach/pkg/workload/schemachange.(*opStmt).executeStmt
                              |     github.com/cockroachdb/cockroach/pkg/workload/schemachange/operation_generator.go:3120
                              | github.com/cockroachdb/cockroach/pkg/workload/schemachange.(*schemaChangeWorker).runInTxn
                              |     github.com/cockroachdb/cockroach/pkg/workload/schemachange/schemachange.go:511
                              | github.com/cockroachdb/cockroach/pkg/workload/schemachange.(*schemaChangeWorker).run
                              |     github.com/cockroachdb/cockroach/pkg/workload/schemachange/schemachange.go:605
                              | pkg/ccl/testccl/workload/schemachange/schemachange_test_test.TestWorkload.TestWorkload.func4.func6
                              |     pkg/ccl/testccl/workload/schemachange/schemachange_test_test/pkg/ccl/testccl/workload/schemachange/schema_change_external_test.go:121
                              | golang.org/x/sync/errgroup.(*Group).Go.func1
                              |     golang.org/x/sync/errgroup/external/org_golang_x_sync/errgroup/errgroup.go:78
                              | runtime.goexit
                              |     src/runtime/asm_amd64.s:1695
                            Wraps: (4) ***UNEXPECTED ERROR; Received an unexpected execution error.
                            Wraps: (5) ERROR: duplicate key value violates unique constraint "table_w1_16_co!l16_w1_20_key" (SQLSTATE 23505)
                            Error types: (1) *markers.withMark (2) *schemachange.ErrorState (3) *withstack.withStack (4) *errutil.withPrefix (5) *pgconn.PgError
            Test:           TestWorkload
Schema Workload Stats
Total Schema Statements Executed = 36
Total Schema Statements Succeeded = 36
Total Schema Statement Expected Failures = 0
Total Transactions Committed = 21
Total Transactions Rolled Back = 43
Total Transactions Executed = 64
    schema_change_external_test.go:98: backup, tracing data, and system table dumps in /var/lib/engflow/worker/work/1/exec/bazel-out/k8-fastbuild/testlogs/pkg/ccl/testccl/workload/schemachange/schemachange_test/run_21_of_25/test.outputs/logTestWorkload2109485748
    panic.go:626: -- test log scope end --
test logs left over in: outputs.zip/logTestWorkload2109485748
--- FAIL: TestWorkload (11.98s)

Parameters:

See also: How To Investigate a Go Test Failure (internal)

/cc @cockroachdb/sql-foundations

This test on roachdash | Improve this report!

Jira issue: CRDB-39611

cockroach-teamcity commented 3 weeks ago

pkg/ccl/testccl/workload/schemachange/schemachange_test.TestWorkload failed on release-24.1.2-rc @ 4037be1494ff8f3873edfa2510a7fa79545b413e:

   },
   {
    "query": "SELECT ARRAY[parent.table_schema, parent.table_name, quote_ident(parent.column_name), quote_ident(child.column_name)] FROM (SELECT conname, conkey, confkey, conrelid, confrelid FROM pg_constraint WHERE (contype = 'f') AND (conrelid = 'schema_w0_16.table_w0_55'::REGCLASS::INT8)) AS con JOIN (SELECT column_name, ordinal_position, column_default FROM information_schema.columns WHERE (table_schema = 'schema_w0_16') AND (table_name = 'table_w0_55')) AS child ON conkey[1] = child.ordinal_position JOIN (SELECT pc.oid, cols.table_schema, cols.table_name, cols.column_name, cols.ordinal_position FROM pg_class AS pc JOIN pg_namespace AS pn ON pc.relnamespace = pn.oid JOIN information_schema.columns AS cols ON ((pc.relname = cols.table_name) AND (pn.nspname = cols.table_schema))) AS parent ON ((con.confkey[1] = parent.ordinal_position) AND (con.confrelid = parent.oid)) WHERE child.column_name != 'rowid'",
    "result": null
   }
  ],
  "previousStatements": [
   "CREATE TABLE schema_w0_16.table_w0_55 (\"col55 _w0_56\" FLOAT4 NOT NULL, col5̏5_w0_57 BOX2D NULL, \"!col55_w0_58\" GEOGRAPHY NOT NULL, col55_w0_59 VARCHAR NOT NULL, UNIQUE (col55_w0_59 ASC) STORING (\"col55 _w0_56\", \"!col55_w0_58\"), INDEX (\"col55 _w0_56\"), UNIQUE ((\"col55 _w0_56\" + 0.2537466883659363:::FLOAT8)), INDEX (col5̏5_w0_57 DESC) STORING (col55_w0_59), UNIQUE (\"col55 _w0_56\", col5̏5_w0_57 ASC), INDEX (\"col55 _w0_56\" DESC) STORING (\"!col55_w0_58\") PARTITION BY LIST (\"col55 _w0_56\") (PARTITION \"tabl\\\\u4596e_55_part_0\" VALUES IN ((0.0:::FLOAT8,)), PARTITION \"t abl\\\\u4596e_55_part_1\" VALUES IN (((-1.199499487876892):::FLOAT8,)), PARTITION \"tabl\\\\u4596e_55_part_2\" VALUES IN ((1.2670646905899048:::FLOAT8,)), PARTITION \"tabl\\\\u4596e_55_part_3\" VALUES IN (((-0.20344634354114532):::FLOAT8,))), INDEX (\"col55 _w0_56\" ASC, (\"col55 _w0_56\" + 0.37552177906036377:::FLOAT8) DESC))",
   "INSERT INTO schema_w0_16.table_w0_55 (\"col55 _w0_56\",col5̏5_w0_57,\"!col55_w0_58\",col55_w0_59) VALUES ((-1.526540994644165):::FLOAT8,'BOX(-1.1303581340633653 -0.512563710369231,1.5282298679786184 -0.2914727525549499)':::BOX2D,'01040000A0E61000000100000001010000802031ED1A01CD4440C04FEB9C531E4EC0D0B1C37E6D4AD341':::GEOGRAPHY,''::VARCHAR),(1.401298464324817e-45:::FLOAT8,NULL,'0102000020E610000002000000CC9DF003984E5D403DB15B7023A752C088911BF7F22E5540882DCA8B9B952EC0':::GEOGRAPHY,'X'::VARCHAR),(0.9389936327934265:::FLOAT8,'BOX(0.18654400235352142 0.45372436372591257,0.649556289075322 1.466738206995367)':::BOX2D,'0104000020E610000000000000':::GEOGRAPHY,e'R``[\\f(lX'::VARCHAR)",
   "INSERT INTO schema_w0_16.table_w0_55 (\"col55 _w0_56\",col5̏5_w0_57,\"!col55_w0_58\",col55_w0_59) VALUES (0.0:::FLOAT8,'BOX(-0.851823137270188 -1.0970450541832317,0.42869979121301593 0.0778694338778807)':::BOX2D,'0106000020E61000000900000001030000000100000005000000C0C4BC3CD1F65FC0E8E262DDDA054AC0C81894E4B07F5BC00970D735843756C08E88967964965BC05C9D393BF7964E40B512811D224A63C052BF600DCF055540C0C4BC3CD1F65FC0E8E262DDDA054AC001030000000100000008000000E8A0B27D65A858C04F7B51E0408551C099174886009155C0A1DB53B0392B54C0445541589EAB5C40E59DCABA6B5848C0C47CB5634AB95840ECB805E5CA903C40F8813E54261A4440FECB62EB2F90544080C4EF0857DC45C09835127EC03C5440C6310A2AAD985FC000B6B4CC24925540E8A0B27D65A858C04F7B51E0408551C0010300000001000000040000007CAA8D7C0B6360C0E7C4D10525B354C054CCA985BE4A474008C64FEDBA2150401D0890D673D95BC0882952E92A5656407CAA8D7C0B6360C0E7C4D10525B354C001030000000100000006000000C031734E7A9B2B40C505B5F2DA2546C0D40177131C455E40DC01275B3AB43DC07CF4D219F51E6440A0F0F88A056417404651EFA2A7684FC0F0B6BF36E5565640B0D6B5748D4466C0802C2A06C52E3A40C031734E7A9B2B40C505B5F2DA2546C00103000000010000000D000000B45737C4E92866C010E76AA1FF8230C0D04BF453301562C019487C0FA61342C031DCB104962366C08ED17448FEA44DC07017345B1D4464C05AE1C2A1288250C0A8700DBE02FE4FC00C08D600044356C0B0211EC704C44E40167D366A1BB647C080007CF9F6BC2C40EEE3E348B4C23EC0E6B18E0D53B6624080FDA8D9E431EFBF006258B53497E3BF50828ED0E77910C089BF397FFD8D5CC05816573FAE84484064453A2A846766C006E26C443B4856409357BC7E845E65C03C51564522AE3540B45737C4E92866C010E76AA1FF8230C00103000000010000000A000000F6D6EACF1E6D64C03A550EABCE8655C08194601319B851C0103717AE3FE04DC0A0D5812B7C9734C0AEAF0FA3B29A52C0584A69958A2C4040343A371ADF0452C054B31393283D64403E46A4AB32674BC060B20A9BC76724C0DCC1632C245D54402E35EA20942852C04480AF8DAE654940BE874612510B58C06079E5BDFD8F0E402B518DB2396461C0E40A92BED6C933C0F6D6EACF1E6D64C03A550EABCE8655C00103000000010000000A00000046EB55492D4258C099C0FC0A375A55C020B2F7BFA6751BC0A49BC44F957445C0FEE78317DD02654008E9137CA33F54C0D41FB0B1F8E151408054476381F834C0D0A0FEEAAA8A58407013983D5C7C15C0E495687B743656408014AC7B824F15C0687E35D7CF7D5F4010E36FE9D6FC1A40F05AF5FB6C5743C004BD6CAB0CCE4040478C72C3C63F66C0589EA28FCC75514046EB55492D4258C099C0FC0A375A55C001030000000100000006000000E8A7C3930DFE3CC0DC29C545CAC94BC040DE27A1A0D802C01C27EC54D2C444C0E8B3EB260EBA3B4060A8895DAE724340607654A9598A2340CE64295790B65040A41167D800D85BC0683052B163A73340E8A7C3930DFE3CC0DC29C545CAC94BC001030000000100000009000000046B8E14D69265C0B0DB9456E86D4BC02E9148C1F8CF61C0078E880A25254AC0F067D9BC3065314030713B877D5D55C0F8A667306AD554403A66A86D813748C0C0098C59809F41C028C9BCBA8AEF3BC0E3266C81B83D52C0EC73F493106B4F401F62ED76DC8856C0B05441A460C61140B99EE00EBE3464C0A8B0C63E987A2340046B8E14D69265C0B0DB9456E86D4BC0':::GEOGRAPHY,e'E\\x04'::VARCHAR)"
  ]
 }
}
    schema_change_external_test.go:132: 
            Error Trace:    pkg/ccl/testccl/workload/schemachange/schemachange_test_test/pkg/ccl/testccl/workload/schemachange/schema_change_external_test.go:132
            Error:          Received unexpected error:
                            ***UNEXPECTED ERROR; Received an unexpected execution error.: ERROR: duplicate key value violates unique constraint "table_w0_55_expr_key" (SQLSTATE 23505)
                            (1) forced error mark
                              | "fatal error when running txn"
                              | github.com/cockroachdb/errors/withstack/*withstack.withStack::
                            Wraps: (2)
                            Wraps: (3) attached stack trace
                              -- stack trace:
                              | github.com/cockroachdb/cockroach/pkg/workload/schemachange.(*opStmt).executeStmt
                              |     github.com/cockroachdb/cockroach/pkg/workload/schemachange/operation_generator.go:3120
                              | github.com/cockroachdb/cockroach/pkg/workload/schemachange.(*schemaChangeWorker).runInTxn
                              |     github.com/cockroachdb/cockroach/pkg/workload/schemachange/schemachange.go:511
                              | github.com/cockroachdb/cockroach/pkg/workload/schemachange.(*schemaChangeWorker).run
                              |     github.com/cockroachdb/cockroach/pkg/workload/schemachange/schemachange.go:605
                              | pkg/ccl/testccl/workload/schemachange/schemachange_test_test.TestWorkload.TestWorkload.func4.func6
                              |     pkg/ccl/testccl/workload/schemachange/schemachange_test_test/pkg/ccl/testccl/workload/schemachange/schema_change_external_test.go:121
                              | golang.org/x/sync/errgroup.(*Group).Go.func1
                              |     golang.org/x/sync/errgroup/external/org_golang_x_sync/errgroup/errgroup.go:78
                              | runtime.goexit
                              |     src/runtime/asm_amd64.s:1695
                            Wraps: (4) ***UNEXPECTED ERROR; Received an unexpected execution error.
                            Wraps: (5) ERROR: duplicate key value violates unique constraint "table_w0_55_expr_key" (SQLSTATE 23505)
                            Error types: (1) *markers.withMark (2) *schemachange.ErrorState (3) *withstack.withStack (4) *errutil.withPrefix (5) *pgconn.PgError
            Test:           TestWorkload
Schema Workload Stats
Total Schema Statements Executed = 103
Total Schema Statements Succeeded = 103
Total Schema Statement Expected Failures = 0
Total Transactions Committed = 64
Total Transactions Rolled Back = 87
Total Transactions Executed = 151
    schema_change_external_test.go:98: backup, tracing data, and system table dumps in /var/lib/engflow/worker/work/0/exec/bazel-out/k8-fastbuild/testlogs/pkg/ccl/testccl/workload/schemachange/schemachange_test/run_8_of_25/test.outputs/logTestWorkload1471406582
    panic.go:626: -- test log scope end --
test logs left over in: outputs.zip/logTestWorkload1471406582
--- FAIL: TestWorkload (24.32s)

Parameters:

See also: How To Investigate a Go Test Failure (internal)

This test on roachdash | Improve this report!

annrpom commented 2 weeks ago

the inserts are essentially doing:

demo@127.0.0.1:26257/movr> CREATE TEMP VIEW temp_view AS                       
                        -> SELECT                                              
                        ->     original_value,                                 
                        ->     (original_value + 0.2537466883659363) AS        
                        -> modified_value                                      
                        -> FROM (                                              
                        ->     VALUES                                          
                        ->         (-1.526540994644165:::FLOAT8),              
                        ->         (1.401298464324817e-45:::FLOAT8),           
                        ->         (0.9389936327934265:::FLOAT8),              
                        ->         (0.0:::FLOAT8)                              
                        -> ) AS v(original_value);                             
CREATE VIEW

Time: 6ms total (execution 6ms / network 0ms)

demo@127.0.0.1:26257/movr> select * from temp_view;                            
     original_value     |   modified_value
------------------------+----------------------
     -1.526540994644165 | -1.2727943062782288
  1.401298464324817e-45 |  0.2537466883659363
     0.9389936327934265 |  1.1927403211593628
                      0 |  0.2537466883659363
(4 rows)

Time: 5ms total (execution 4ms / network 0ms)

(original_value + 0.2537466883659363) is the unique constraint

i thought we had something to prevent this from happening... let me take a quick peek

annrpom commented 2 weeks ago

i investigated by looking for queries like:

SELECT count(*) \u003e 0 FROM (SELECT * FROM schema_w0_16.table_w0_55 WHERE (\"col55 _w0_56\" + 0.2537466883659363:::FLOAT8) = (SELECT (\"col55 _w0_56\" + 0.2537466883659363:::FLOAT8) FROM (VALUES (0.9389936327934265:::FLOAT8)) AS t (\"col55 _w0_56\")))

these queries come from this part of our schemachange code: https://github.com/cockroachdb/cockroach/blob/0823fda427341de8bfb6823332cc4ea7508575b8/pkg/workload/schemachange/error_screening.go#L351 in valuesViolateUniqueConstraints

there are several of them comparing our unique constraint (\"col55 _w0_56\" + 0.2537466883659363:::FLOAT8), but they are "batched out" (i.e. we check one value at a time againt the table) since i believe we only check if values that we are inserting violate the unqiue constraint with values that already exist in the table -- not in the same insert

annrpom commented 2 weeks ago

gpt4 gave me something like -- leaving this here in case this is helpful for coming up with said query above:

SELECT 
    EXISTS (
        SELECT 
            result_value
        FROM 
            (
                SELECT 
                    ("col55_w0_56" + 0.2537466883659363:::FLOAT8) AS result_value
                FROM 
                    (
                        VALUES 
                            (-1.526540994644165:::FLOAT8),
                            (1.401298464324817e-45:::FLOAT8),
                            (0.9389936327934265:::FLOAT8),
                            (0.0:::FLOAT8)
                    ) AS t ("col55_w0_56")
            ) AS results
        GROUP BY 
            result_value
        HAVING 
            COUNT(*) > 1
    ) AS has_duplicates;
rafiss commented 6 days ago

Including the table definition here too:

{
 "workerId": 0,
 "clientTimestamp": "06:32:42.703608",
 "ops": [
  "BEGIN",
  {
   "sql": "CREATE TABLE schema_w0_16.table_w0_55 (\"col55 _w0_56\" FLOAT4 NOT NULL, col5̏5_w0_57 BOX2D NULL, \"!col55_w0_58\" GEOGRAPHY NOT NULL, col55_w0_59 VARCHAR NOT NULL, UNIQUE (col55_w0_59 ASC) STORING (\"col55 _w0_56\", \"!col55_w0_58\"), INDEX (\"col55 _w0_56\"), UNIQUE ((\"col55 _w0_56\" + 0.2537466883659363:::FLOAT8)), INDEX (col5̏5_w0_57 DESC) STORING (col55_w0_59), UNIQUE (\"col55 _w0_56\", col5̏5_w0_57 ASC), INDEX (\"col55 _w0_56\" DESC) STORING (\"!col55_w0_58\") PARTITION BY LIST (\"col55 _w0_56\") (PARTITION \"tabl\\\\u4596e_55_part_0\" VALUES IN ((0.0:::FLOAT8,)), PARTITION \"t abl\\\\u4596e_55_part_1\" VALUES IN (((-1.199499487876892):::FLOAT8,)), PARTITION \"tabl\\\\u4596e_55_part_2\" VALUES IN ((1.2670646905899048:::FLOAT8,)), PARTITION \"tabl\\\\u4596e_55_part_3\" VALUES IN (((-0.20344634354114532):::FLOAT8,))), INDEX (\"col55 _w0_56\" ASC, (\"col55 _w0_56\" + 0.37552177906036377:::FLOAT8) DESC))"
  },
  {
   "sql": "INSERT INTO schema_w0_16.table_w0_55 (\"col55 _w0_56\",col5̏5_w0_57,\"!col55_w0_58\",col55_w0_59) VALUES ((-1.526540994644165):::FLOAT8,'BOX(-1.1303581340633653 -0.512563710369231,1.5282298679786184 -0.2914727525549499)':::BOX2D,'01040000A0E61000000100000001010000802031ED1A01CD4440C04FEB9C531E4EC0D0B1C37E6D4AD341':::GEOGRAPHY,''::VARCHAR),(1.401298464324817e-45:::FLOAT8,NULL,'0102000020E610000002000000CC9DF003984E5D403DB15B7023A752C088911BF7F22E5540882DCA8B9B952EC0':::GEOGRAPHY,'X'::VARCHAR),(0.9389936327934265:::FLOAT8,'BOX(0.18654400235352142 0.45372436372591257,0.649556289075322 1.466738206995367)':::BOX2D,'0104000020E610000000000000':::GEOGRAPHY,e'R`[\\f(lX'::VARCHAR)"
  },
  {
   "sql": "INSERT INTO schema_w0_16.table_w0_55 (\"col55 _w0_56\",col5̏5_w0_57,\"!col55_w0_58\",col55_w0_59) VALUES (0.0:::FLOAT8,'BOX(-0.851823137270188 -1.0970450541832317,0.42869979121301593 0.0778694338778807)':::BOX2D,'0106000020E61000000900000001030000000100000005000000C0C4BC3CD1F65FC0E8E262DDDA054AC0C81894E4B07F5BC00970D735843756C08E88967964965BC05C9D393BF7964E40B512811D224A63C052BF600DCF055540C0C4BC3CD1F65FC0E8E262DDDA054AC001030000000100000008000000E8A0B27D65A858C04F7B51E0408551C099174886009155C0A1DB53B0392B54C0445541589EAB5C40E59DCABA6B5848C0C47CB5634AB95840ECB805E5CA903C40F8813E54261A4440FECB62EB2F90544080C4EF0857DC45C09835127EC03C5440C6310A2AAD985FC000B6B4CC24925540E8A0B27D65A858C04F7B51E0408551C0010300000001000000040000007CAA8D7C0B6360C0E7C4D10525B354C054CCA985BE4A474008C64FEDBA2150401D0890D673D95BC0882952E92A5656407CAA8D7C0B6360C0E7C4D10525B354C001030000000100000006000000C031734E7A9B2B40C505B5F2DA2546C0D40177131C455E40DC01275B3AB43DC07CF4D219F51E6440A0F0F88A056417404651EFA2A7684FC0F0B6BF36E5565640B0D6B5748D4466C0802C2A06C52E3A40C031734E7A9B2B40C505B5F2DA2546C00103000000010000000D000000B45737C4E92866C010E76AA1FF8230C0D04BF453301562C019487C0FA61342C031DCB104962366C08ED17448FEA44DC07017345B1D4464C05AE1C2A1288250C0A8700DBE02FE4FC00C08D600044356C0B0211EC704C44E40167D366A1BB647C080007CF9F6BC2C40EEE3E348B4C23EC0E6B18E0D53B6624080FDA8D9E431EFBF006258B53497E3BF50828ED0E77910C089BF397FFD8D5CC05816573FAE84484064453A2A846766C006E26C443B4856409357BC7E845E65C03C51564522AE3540B45737C4E92866C010E76AA1FF8230C00103000000010000000A000000F6D6EACF1E6D64C03A550EABCE8655C08194601319B851C0103717AE3FE04DC0A0D5812B7C9734C0AEAF0FA3B29A52C0584A69958A2C4040343A371ADF0452C054B31393283D64403E46A4AB32674BC060B20A9BC76724C0DCC1632C245D54402E35EA20942852C04480AF8DAE654940BE874612510B58C06079E5BDFD8F0E402B518DB2396461C0E40A92BED6C933C0F6D6EACF1E6D64C03A550EABCE8655C00103000000010000000A00000046EB55492D4258C099C0FC0A375A55C020B2F7BFA6751BC0A49BC44F957445C0FEE78317DD02654008E9137CA33F54C0D41FB0B1F8E151408054476381F834C0D0A0FEEAAA8A58407013983D5C7C15C0E495687B743656408014AC7B824F15C0687E35D7CF7D5F4010E36FE9D6FC1A40F05AF5FB6C5743C004BD6CAB0CCE4040478C72C3C63F66C0589EA28FCC75514046EB55492D4258C099C0FC0A375A55C001030000000100000006000000E8A7C3930DFE3CC0DC29C545CAC94BC040DE27A1A0D802C01C27EC54D2C444C0E8B3EB260EBA3B4060A8895DAE724340607654A9598A2340CE64295790B65040A41167D800D85BC0683052B163A73340E8A7C3930DFE3CC0DC29C545CAC94BC001030000000100000009000000046B8E14D69265C0B0DB9456E86D4BC02E9148C1F8CF61C0078E880A25254AC0F067D9BC3065314030713B877D5D55C0F8A667306AD554403A66A86D813748C0C0098C59809F41C028C9BCBA8AEF3BC0E3266C81B83D52C0EC73F493106B4F401F62ED76DC8856C0B05441A460C61140B99EE00EBE3464C0A8B0C63E987A2340046B8E14D69265C0B0DB9456E86D4BC0':::GEOGRAPHY,e'E\\x04'::VARCHAR)"
  }
 ],
 "expectedExecErrors": "",
 "expectedCommitErrors": "",
 "message": "***UNEXPECTED ERROR; Received an unexpected execution error.: ERROR: duplicate key value violates unique constraint \"table_w0_55_expr_key\" (SQLSTATE 23505)",

I wonder if we need to do something to not define a unique constraint on a computed column that is based on a nullable value.

fqazi commented 5 days ago

Simplest possible repro of his problem is:

 CREATE TABLE schema_w0_16.table_w0_55 ("col55 _w0_56" FLOAT4 NOT NULL, col5̏5_w0_57 BOX2D NULL, "!col55_w0_58" GEOGRAPHY NOT NULL, col55_w0_59 VARCHAR NOT NULL, UNIQUE (col55_w0_59 ASC) STORING ("col55 _w0_56", "!col55_w0_58"), INDEX ("col55 _w0_56"), UNIQUE (("col55 _w0_56" + 0.2537466883659363:::FLOAT8)), INDEX (col5̏5_w0_57 DESC) STORING (col55_w0_59), UNIQUE ("col55 _w0_56", col5̏5_w0_57 ASC), INDEX ("col55 _w0_56" DESC) STORING ("!col55_w0_58") PARTITION BY LIST ("col55 _w0_56") (PARTITION "tabl\\\\u4596e_55_part_0" VALUES IN ((0.0:::FLOAT8,)), PARTITION "t abl\\\\u4596e_55_part_1" VALUES IN (((-1.199499487876892):::FLOAT8,)), PARTITION "tabl\\\\u4596e_55_part_2" VALUES IN ((1.2670646905899048:::FLOAT8,)), PARTITION "tabl\\\\u4596e_55_part_3" VALUES IN (((-0.20344634354114532):::FLOAT8,))), INDEX ("col55 _w0_56" ASC, ("col55 _w0_56" + 0.37552177906036377:::FLOAT8) DESC));
   INSERT INTO schema_w0_16.table_w0_55 ("col55 _w0_56",col5̏5_w0_57,"!col55_w0_58",col55_w0_59) VALUES ((-1.526540994644165):::FLOAT8,'BOX(-1.1303581340633653 -0.512563710369231,1.5282298679786184 -0.2914727525549499)':::BOX2D,'01040000A0E61000000100000001010000802031ED1A01CD4440C04FEB9C531E4EC0D0B1C37E6D4AD341':::GEOGRAPHY,''::VARCHAR),(1.401298464324817e-45:::FLOAT8,NULL,'0102000020E610000002000000CC9DF003984E5D403DB15B7023A752C088911BF7F22E5540882DCA8B9B952EC0':::GEOGRAPHY,'X'::VARCHAR),(0.9389936327934265:::FLOAT8,'BOX(0.18654400235352142 0.45372436372591257,0.649556289075322 1.466738206995367)':::BOX2D,'0104000020E610000000000000':::GEOGRAPHY,e'R``[\\f(lX'::VARCHAR);
   INSERT INTO schema_w0_16.table_w0_55 ("col55 _w0_56",col5̏5_w0_57,"!col55_w0_58",col55_w0_59) VALUES (0.0:::FLOAT8,'BOX(-0.851823137270188 -1.0970450541832317,0.42869979121301593 0.0778694338778807)':::BOX2D,'0106000020E61000000900000001030000000100000005000000C0C4BC3CD1F65FC0E8E262DDDA054AC0C81894E4B07F5BC00970D735843756C08E88967964965BC05C9D393BF7964E40B512811D224A63C052BF600DCF055540C0C4BC3CD1F65FC0E8E262DDDA054AC001030000000100000008000000E8A0B27D65A858C04F7B51E0408551C099174886009155C0A1DB53B0392B54C0445541589EAB5C40E59DCABA6B5848C0C47CB5634AB95840ECB805E5CA903C40F8813E54261A4440FECB62EB2F90544080C4EF0857DC45C09835127EC03C5440C6310A2AAD985FC000B6B4CC24925540E8A0B27D65A858C04F7B51E0408551C0010300000001000000040000007CAA8D7C0B6360C0E7C4D10525B354C054CCA985BE4A474008C64FEDBA2150401D0890D673D95BC0882952E92A5656407CAA8D7C0B6360C0E7C4D10525B354C001030000000100000006000000C031734E7A9B2B40C505B5F2DA2546C0D40177131C455E40DC01275B3AB43DC07CF4D219F51E6440A0F0F88A056417404651EFA2A7684FC0F0B6BF36E5565640B0D6B5748D4466C0802C2A06C52E3A40C031734E7A9B2B40C505B5F2DA2546C00103000000010000000D000000B45737C4E92866C010E76AA1FF8230C0D04BF453301562C019487C0FA61342C031DCB104962366C08ED17448FEA44DC07017345B1D4464C05AE1C2A1288250C0A8700DBE02FE4FC00C08D600044356C0B0211EC704C44E40167D366A1BB647C080007CF9F6BC2C40EEE3E348B4C23EC0E6B18E0D53B6624080FDA8D9E431EFBF006258B53497E3BF50828ED0E77910C089BF397FFD8D5CC05816573FAE84484064453A2A846766C006E26C443B4856409357BC7E845E65C03C51564522AE3540B45737C4E92866C010E76AA1FF8230C00103000000010000000A000000F6D6EACF1E6D64C03A550EABCE8655C08194601319B851C0103717AE3FE04DC0A0D5812B7C9734C0AEAF0FA3B29A52C0584A69958A2C4040343A371ADF0452C054B31393283D64403E46A4AB32674BC060B20A9BC76724C0DCC1632C245D54402E35EA20942852C04480AF8DAE654940BE874612510B58C06079E5BDFD8F0E402B518DB2396461C0E40A92BED6C933C0F6D6EACF1E6D64C03A550EABCE8655C00103000000010000000A00000046EB55492D4258C099C0FC0A375A55C020B2F7BFA6751BC0A49BC44F957445C0FEE78317DD02654008E9137CA33F54C0D41FB0B1F8E151408054476381F834C0D0A0FEEAAA8A58407013983D5C7C15C0E495687B743656408014AC7B824F15C0687E35D7CF7D5F4010E36FE9D6FC1A40F05AF5FB6C5743C004BD6CAB0CCE4040478C72C3C63F66C0589EA28FCC75514046EB55492D4258C099C0FC0A375A55C001030000000100000006000000E8A7C3930DFE3CC0DC29C545CAC94BC040DE27A1A0D802C01C27EC54D2C444C0E8B3EB260EBA3B4060A8895DAE724340607654A9598A2340CE64295790B65040A41167D800D85BC0683052B163A73340E8A7C3930DFE3CC0DC29C545CAC94BC001030000000100000009000000046B8E14D69265C0B0DB9456E86D4BC02E9148C1F8CF61C0078E880A25254AC0F067D9BC3065314030713B877D5D55C0F8A667306AD554403A66A86D813748C0C0098C59809F41C028C9BCBA8AEF3BC0E3266C81B83D52C0EC73F493106B4F401F62ED76DC8856C0B05441A460C61140B99EE00EBE3464C0A8B0C63E987A2340046B8E14D69265C0B0DB9456E86D4BC0':::GEOGRAPHY,e'E\\x04'::VARCHAR);

Running the uniqueness query indicates the values are not equal:

SELECT count(*) FROM (SELECT * FROM schema_w0_16.table_w0_55 WHERE ("col55 _w0_56" + 0.2537466883659363:::FLOAT8) = (SELECT ("col55 _w0_56" + 0.2537466883659363:::FLOAT8) FROM (VALUES (0.0:::FLOAT8)) AS t ("col55 _w0_56")))
  count
---------
      0

This is linked to floating point precision within the insert versus the comparison. The transaction does see the rows fine, which I initially thought was the problem. Additionally, a comparison query shows similar properties:

 SELECT c>d FROM (SELECT ("col55 _w0_56" + 0.2537466883659363:::FLOAT8) as c FROM schema_w0_16.table_w0_55) as A, (SELECT (("col55 _w0_56" + 0.2537466883659363:::FLOAT8)) as d FROM (VALUES (0.0:::FLOAT8)) AS t ("col55 _w0_56")) AS B;
  ?column?
------------
     f
     t
     t
fqazi commented 5 days ago

I was going crazy trying to understand why this doesn't work, but its clear from the plans. When we run with equality we see:

 EXPLAIN SELECT count(*) FROM (SELECT * FROM schema_w0_16.table_w0_55 WHERE ("col55 _w0_56" + 0.2537466883659363:::FLOAT8):::FLOAT4 = (SELECT ("col55 _w0_56" + 0.2537466883659363:::FLOAT8) FROM (VALUES
                                -> (0.0:::FLOAT8)) AS t ("col55 _w0_56"))::FLOAT4)
                                ->
                                -> ;
                                       info
-----------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • group (scalar)
  │ estimated row count: 1
  │
  └── • scan
        estimated row count: 0 (<0.01% of the table; stats collected 8 hours ago)
        table: table_w0_55@table_w0_55_col55 _w0_56_idx
        spans: [/0.0 - /0.0]

The optimizer is able to pull 0.2537466883659363 from both sides of the expression above and only looks for col55 _w0_56 containing zero. For integer arithmetic this would be right, but for floating point its possible that because of rounding errors that this isn't true (i.e. col55 _w0_56 + constant, could equal the target value if w0_56 is small enough because of the limits of floating point precision).

If we instead opt for not equal the plan changes and we see:

EXPLAIN SELECT count(*) FROM (SELECT * FROM schema_w0_16.table_w0_55 WHERE ("col55 _w0_56" + 0.2537466883659363:::FLOAT8):::FLOAT4 <> (SELECT ("col55 _w0_56" + 0.2537466883659363:::FLOAT8) FROM (VALUES
                                -> (0.0:::FLOAT8)) AS t ("col55 _w0_56"))::FLOAT4)
                                ->
                                -> ;
                                      info
--------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • group (scalar)
  │ estimated row count: 1
  │
  └── • scan
        estimated row count: 2 (67% of the table; stats collected 8 hours ago)
        table: table_w0_55@table_w0_55_expr_key
        spans: (/NULL - /0.2537466883659362] [/0.25374668836593633 - ]

This produces the correct result because the index on the expression is used, and we scan for values not matching it directly. Let me bug the queries team to see if this is desired behaviour, since selecting the index expression index would also produce the correct result in this case.