search

cockroachdb / cockroach

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

sql: invalid datum type given: RECORD, expected RECORD #117101

Open cockroach-teamcity opened 10 months ago

cockroach-teamcity commented 10 months ago

roachtest.sqlsmith/setup=empty/setting=default failed with artifacts on master @ 4f31fbae529e459c00ef2490e193c0c246e8d0f9:

                            '01050000C00800000001020000C00300000034B76439C0BEF64100AAF285F945CD4126101CEF48CA01428EC082D0130BFBC17893E9DDB819FE41488CDCBE3B67DF41E0E83A87FB23B8C1581E6E69E2FFD0C1229B86C1C1FDF341022BA367D8CFF0418472AE6A8A2DFAC19CABF74A6023E2C101020000C00200000098DC2F29FF36FF41EE1218589DD1F541685C567B874BF941F0B8D1089B5602C260880E82F306E0C140AECB7CE4CFC1418CD34CADC15FFC419E6F0B2CDE4F014201020000C006000000906C24AEF119DEC1A42401F39A82F6C1AC0DF142109100C26C4E5F310277FE417897950B1EEBD54173D1EC44E348FDC18842851E4BC5F541802BC7261DC4EB41D899734EE8F2EB41D8DD615C9863FDC1BC0421B63E9501C2D46526C05A56FCC150AF5217C782EB41C0CDB1AC6E6AE6410829D33CA526F041C2D9B33525C3F5C1DE0FC74EBB67F941C861F3BB4806FA4142B4E73971B3FDC1B011F681C905D74194B482CA0F41E941A2696E7C3028F2417C187083BC14F0C1D4AC720DF2F9EC4101020000C0030000001C08B11065D7FF418E84FF07A6C7F6C1424542A9D561FDC118590E9E277BD1417C195E5D3E9E0042179552D4F663F1C1DE5F1C9720E8F4C1805D2AA71919C641A0C180C7A8EDFB4194D01FE002ACED4114C5ADCDFFFEE941F49355E6FC67014201020000C0040000000CF9FA3E5130EE4110E7A0484CDFE241385A360FDB94FDC1FBA0F15DB283F0C10007EB58B388D94120FABF89578FB641BF7762AB4FB8F2C1D8B6AB241B3BFAC15DC8BDF22D9AF2C13E13E9FB9F3BF94172787940919DFEC174F8B890D2D6F34178A6EFE33CBAE2C160CC68D2C493E841E8AACF0CCAF0E54133F12C8C1E50F3C101020000C0020000004AFF947B6DC7E9C1E8E88D4FEC8D02C22E71CC1FA7F8F0C19C2B3680D37EF3C1D8B5369EA3DCDAC1B6AC77EF996EF6C1987E8814F67FD041D8848A44960BFB4101020000C006000000EE35589C0DDFF2415B9D42B2E83CF4C1A4A529C8AB9EFC4148FA8874CC58D241A40F0CAD53A4FB41FA6310FB8EE3FEC115B939908E13F3C1C082BD476E3AEF413425CAA5030F0142922EDDF3A5F5EEC180FF415F2B2DE4417E2EDC2272E1E4C17271F52CA8D101423C2899D496F5F541C4381B8C03E8E641F797FE539FE300C2E00BCD828C61D341FCF2F70AC45BE9410A86984BDEF1E6C1604760AB5061C9C16E77DA7D51E3FDC1500894DCA1330142B42A5E4864D501424C6673A03CA9E04101020000C00300000000B6929F80A189C1BC8D2E803027E34140396C224EEECB413C5EC43E414900C29A756E16EFAF0142A837215E48F7F641A022D079CD01FBC1158B9A3E259FF6C10230AFCC2F02F1414C3FAC81E7B700427ED627940970F74134D6229B9EC8FE41':::GEOMETRY,
                            '+Inf':::FLOAT8
                        ),
                        (
                            CASE
                            WHEN true
                            THEN '010500008005000000010200008009000000E3352F0FF57102C2B2C90DECC035EAC19EBF9824C94AFBC1B080667265EFE6C12075B68B6FA3BAC17C033CEC65A4E3413D6DE65C988C01C2535E06CCB45FF2C1B61145608B1801C2A48C341793A5F9C19123B8921317F7C1301651651931CF41C27D486A57F601427BE2857A450C00C210766600EBD2F1C15052CC076F43E741C08CB4019A31A1C1EA50A466A9E40142B4F49387A533E84146B564EB64ABF041407A09A7E3ECF0C1405000824BC7F341F84C69D947BCFA41DB653B9B5E1700C2909E17B94CB8DC41943BD377D64702423090B0DDD8A0FA41010200008002000000CCBB8E04D14EFB411ADFFF71575BF241DC120836F339E4C118E2F2DE7E890142308F442C7A89F941C0F399CA5D67C7C101020000800400000006DF16AC63C1F441887B3CADE4B4F4414882053D681FDAC1BE4264BA7A2EFCC1A68052BC18C4F341181EF8A3D806E44108CBF847D1A1F7C1602838902963D441421C8678E6210042250C83AFCC04F0C1802BD7D3A994A0411436B678368EF54101020000800500000024986B90859BE141FABA3E750A9DF5C160C65824C813B5C150E789812581F1410531762E1D9AF0C124B5D043831800428084FE55BB9201429C64F066AEB3D5C188F5B9567B64DD41AAB9E794F8F3F94138C28380B0A30042D4F6ED5B4563FC413498BF9E8E34DDC138C9DE5CC49BFB41FEE984414D5BF4C10102000080030000000CD6910BA579E741C20D2B2A193E01C2242AE38FE14BFA4110ABA2C5B218C841E45B4272EDA0FE41F487017B2A7BE8C1F0A80413E362CD418A679BAF9717F2412058AF45A753F1C1':::GEOMETRY
                            ELSE '01070000000700000001030000000100000008000000641E3C46D8D9ECC18064E528275CAD41F4A5A842E09FFD41BC4F931247B7E3C11C48D0DE6823FF41567630EC4F38F141F0CCB487D610D0417A486320031DF141C00E59A9E8CFB941B6772EA1012AF5418C8FBE8A785FE4C13A470B57F53AF3410140E274E30F01C266EF8E5800A5F741641E3C46D8D9ECC18064E528275CAD41010200000003000000849098DE3E54FE419DA60D6E8FAF00C294F2CA76DE2F00426EC46616A279F0415CED8AF61E59F0C1C88B2F9D650BEB410106000000070000000103000000010000000400000071FD68466E8401C2FFF33D61E33101C200470E6EF875E1C1CE49B7DFFC54FBC14AC47BC43F7CF7417C2FBEF5B862F1C171FD68466E8401C2FFF33D61E33101C20103000000010000000B00000008FCA572FB2AD6418D178162FB4602C2404CEE37959EF14140BF795F4A6BF7C1B07ED9D812D7F54159372406A48AF4C1547F05033539F7410C312521412DF5C14E15D187C037F84124E5A7482C4DE0C10C9F04C3FB84FC4118E87F314442D4413CC0BFA5271C0142C82A3BEF90B4FC41D058D822BB5FDA41A4B1B3805F1DF84111B10E6B73C4F1C15C8006A5A659FD41C838D7E51E89D1C100255101E6EEBB4108FCA572FB2AD6418D178162FB4602C201030000000100000007000000BE748AEF3A1FFBC142590ABDFA2A02C2E4EE135A32BFEA41AAF29FDFBC83EEC1F89699A18806EBC1002AC09AF80689C1362551B5AD39F9C1822E5B008C6DF74122E144726318F8C11CB038E88E73F141D6A122D01FB3FAC11CBE6C0FC680E541BE748AEF3A1FFBC142590ABDFA2A02C20103000000010000000A00000058CC74F91F70FCC1855871B1B66BFBC128BCB8D8A5B3F4412B94E5A814A201C2F6498A809B57F641BE378F1A9FA5EDC17818B694DDCAFB4100EB108A7A3A86C1147469B01A3EEB411085EAF84D6EC0C1105EFEB7281D0042189F738775F7D141ECD968F8B824E3C114639A909D55ED41CC5CFDFE8F3BE7C13014ADC98D4EC1419479713C4FD2FFC11CA953CFE673E44158CC74F91F70FCC1855871B1B66BFBC10103000000010000000800000033DD4FD02FB7F0C1B84636D15EA8F5C1F85DAE976E12D5C18A618D8CB3DDF6C150143F3873E1E0414AC2F1EC1115F9C120D6A33E5CF6DA41E47943B11D60EBC15C6900657F86E441E4D07CEFCB27E9418CACBF0E5146ECC14A9CED544C73F2410ECC5009C2F3EAC100F6F4945F24EC4133DD4FD02FB7F0C1B84636D15EA8F5C101030000000100000005000000DC7496FFD1840142A3906004800000C2307F67322158DC41C07842332A27C3C1F544BB08F40AFAC118E915437713D0415EB7B4EB5E87EFC1D49566B4DC37DEC1DC7496FFD1840142A3906004800000C20103000000010000000A0000005A2829CCD409EDC1281F032A8F1EE14158875E8D42F70142ADE9A3525C45FEC16439FC9601F7FC41E053E4C50D5DD741849F4D9395180042F80CFBBB5345D941C08A73C58345EB41E0646E9CF005EB41A4707A4C0CB0F54164723012473501424C932DC1BC5DE041685D6289C080FC41EAFCB8023CD0FBC1F83874596030FF4168BC50FA4EEDD0C1503E9CB654DBEB415A2829CCD409EDC1281F032A8F1EE1410104000000040000000101000000E6EDC68D865EE4C18CE00D7B34B8E341010100000020417FADB0D3FB417698916DFA0DF641010100000010BE2593953AF3C1D0FBEEA8445ACEC1010100000034A42ABEED59E74160FC2342FC14B2C10106000000050000000103000000010000000C0000009C744A64754202C21876D223620EE0C1F86D14F8135EFEC13AA10BB5FF1EE1C1F377B7A0FE3DFCC19233824D891CEEC100401C9DFD9789C12A529FB4EAE3FFC13448143D09A1EB41408571C26F14FEC1407CCFE9719FE241588E3D66EB31EAC1EA02AB7761BBF2416C316B092DAFE3C1A89BA3D35EEBFA418097E744051C914100CE0FD81D588BC1C88C524BF80EE641FCA6998A1441D7C138EC28F5AC260142542DD753A046DEC190088B8E63B6D6C19C744A64754202C21876D223620EE0C1010300000001000000080000002692A89BA17CFDC19725DD853A1DF4C144B5189095FDF8C1D32BF7C34AADFEC15617F61FD600EAC1924A8D7A30FBE2C17823A45FEDB2E641F03B76109E40C7C104F619455555F54176E654C1B2F0F9416CFB2FA6B997D5C1B441E5D00065F7417A453BE52DD8FCC100A53B159AF1B7412692A89BA17CFDC19725DD853A1DF4C10103000000010000000600000024AB93A4776D01C2EE4734E1CE40F9C170F9B9EEDBC6CBC138920AFC06A600C28C2F2CFE6AC5E24120FDB14284FCCAC1D8666B6A51B6FF419C322B85DB94F941EC6E4F6882A2DEC1F0A58DCD895DD54124AB93A4776D01C2EE4734E1CE40F9C1010300000001000000050000001DBCCB0F1B5BFDC140E32E15340CB741D00D77709D5DC5C114A32FE3439FE5C1FCE55BB58457E441B087BC70FBACD941D031C14B5083F7C19885B9B23671EF411DBCCB0F1B5BFDC140E32E15340CB7410103000000010000000600000080ACA04242C8B1C1AAFFB8D396C5FCC1B23D5245BA8C0042364CA6E63918FAC1D41A2C3C7CAEFC419832B5C4DCF7EAC134A67060F40DF241507D170D9CC7CCC110772753FA0BDDC108A31ED33EF7DB4180ACA04242C8B1C1AAFFB8D396C5FCC10101000000B7425FD39D49F0C1F61819CD812BF0410105000000070000000102000000060000006072F11B412FF9C1E6AB0D1A5189F9C104A33F054924E9C1D260676F339AFBC10816DA6FE520FA41E32D973D282E00C25A5841C014E3F4413FC38F3F4C62F0C1402B6D799718FC4190DF04BC79BEC0C1A8AE01F193C2FD4144A3963B5D0AEC4101020000000200000080890A7C90EEFA41A3A63DEA731702C2325B64659A2100426085B9D45458BDC1010200000006000000816303A74BA0F5C100C012E9976725C1D2B6787DB0BEEAC190CCCF68583AC7C10A7B82B21150E0C144084FE39753E3C14087245CE9CDACC1844D62662ADDD6C19624B75419C3F141BA7E44895F3F0242E0E4EFE81ED5DDC17C051C11B2BDE441010200000004000000309530117F9EDB414452CECD4CA7DCC1302936D18FA1FE412625C36D93D4FEC18421AD57CADDF54100D42C91ED16BE410C8F6D6A4B68FF41409C003C244AB74101020000000300000098D8ED0E8B25D841906193494CA6F7C100C886A16607AEC1F08BA65BDDB8C8C16412C3DB2B70F241DC1CBC14B988E1410102000000020000000806B909D80BE541A491FFFE6E37FBC1F8995B19DE9DF1C1782D652140FAFF41010200000003000000ACF4A57AB219E34126BF91F779E8F041B03B7517F262C8C1343C4FD9E237FC418C902CC46809D8C1B04EA793C094F641':::GEOMETRY
                            END,
                            (
                                WITH
                                    with😨470 (col3690)
                                        AS (
                                            SELECT
                                                *
                                            FROM
                                                (VALUES ('BOX(-10 -10,10 10)':::BOX2D), (NULL)) AS tab̶1882 (col3690)
                                        )
                                SELECT
                                    0.046206402833265714:::FLOAT8 AS "c ol3698"
                                FROM
                                    (
                                        VALUES
                                            ('\x48':::BYTES),
                                            ('\xb686177a3c9d1b80':::BYTES),
                                            ('\x22':::BYTES),
                                            (
                                                st_astwkb(st_setpoint('0101000040BE779815E62EFEC17012C296E465CFC1F185657099F3F8C1':::GEOMETRY::GEOMETRY, (-8983494160054706617):::INT8::INT8, NULL::GEOMETRY)::GEOMETRY::GEOMETRY, (-3539179023040272177):::INT8::INT8, (-8008386847117313163):::INT8::INT8)::BYTES
                                            )
                                    )
                                        AS tab1887 (coͭl3697)
                                WHERE
                                    true
                                LIMIT
                                    1:::INT8
                            )
                        ),
                        (
                            '010100008000C0547A7FFEAC4120EF774BBDB9CBC10913F974C58AF0C1':::GEOMETRY,
                            0.0816271516406426:::FLOAT8
                        ),
                        (NULL, (-0.910997474516815):::FLOAT8)
                )
                    AS tab1888 (col3699, col3700)
        )
SELECT
    name('E4B39F8E/C18565AB':::PG_LSN::PG_LSN)::NAME AS col3701
LIMIT
    30:::INT8;
test artifacts and logs in: /artifacts/sqlsmith/setup=empty/setting=default/run_1

Parameters:

See: roachtest README

See: How To Investigate (internal)

See: Grafana

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-34978

rharding6373 commented 10 months ago

error: pq: internal error: invalid datum type given: RECORD, expected RECORD

Repro (edit: simplified a bit more):

CREATE FUNCTION func()
    RETURNS RECORD
    CALLED ON NULL INPUT
    LANGUAGE SQL
    AS $funcbody$SELECT ((42)::INT8, (43)::INT8)$funcbody$;

SELECT
  *
FROM
(
  VALUES
    (
     (('aloha'::TEXT,
     (44)::INT8), NULL)
    ),
    (COALESCE(func()::RECORD, NULL))
);

Stack trace:

github.com/cockroachdb/cockroach/pkg/sql/rowenc/encoded_datum.go:195: DatumToEncDatum()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:228: Next()
github.com/cockroachdb/cockroach/pkg/sql/colexec/columnarizer.go:239: Next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:118: next()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:126: Next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:250: nextAdapter()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:254: next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:286: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:320: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:910: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1957: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1672: func3()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1675: PlanAndRunAll()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2393: execWithDistSQLEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1931: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1108: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:146: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3374: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2227: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2232: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2149: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:953: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:247: processCommands()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:1001: func3()
src/runtime/asm_amd64.s:1650: goexit()
yuzefovich commented 6 months ago

cc @DrewKimball this is another example of typing issue which seems related to what #119616 tried to solve.

DrewKimball commented 6 months ago

This seems sort of orthogonal to that: the issue is that we don't know the concrete type of a RECORD-returning routine until the call to buildScalar, which doesn't happen until after the initial type-checking. So the coalesce ends up being typed as AnyTuple, which doesn't conflict with the Tuple(Tuple(Text, Int), Null) in the first row of the Values operator. In the end, we think the second row of the Values is compatible with the first without needing a cast.

We attempted to fix this by calling buildScalar before retrieving the resolved type when building a Values operator, but this is defeated by the coalesce, since the AnyTuple type that was originally resolved with doesn't change.

DrewKimball commented 3 months ago

I'm now thinking that we should do something like this: discard a RECORD cast when the input expression already has a composite type, and return a user-facing error in other cases. We don't have the same concept of a dynamic tuple like postgres does, so pure RECORD casting just doesn't make sense.

DrewKimball commented 2 months ago

I looked at this again - the cast to RECORD isn't the problem, and isn't necessary for the reproduction. The issue is that we don't resolve the concrete return type of a RECORD-returning function until after it is built in the optbuilder, and we type-check before that. After the UDF is built, its resolved type is correct, but the COALESCE that wraps it still has the incorrect resolved AnyTuple type.