changefeedccl: inconsistent behavior between SELECT queries and changefeed expressions

[jayshrivastava]

This issue tracks some queries which seem to work when performing a SELECT ... WHERE but do not work when used in a changefeed CREATE CHANGEFEED ... AS SELECT. So far, it looks like builtins are the only things causing problems.

[ ] #107382 similar_to_escape() builtin

    changefeed_test.go:934: SELECT array_to_string(IFNULL(array_agg(distinct rowid),'{}'),'|') FROM seed WHERE (seed._bool AND (seed._bool OR similar_to_escape(seed._string::STRING, seed._string::STRING, seed._string::STRING)::BOOL))
    changefeed_test.go:950: Expecting statement CREATE CHANGEFEED WITH schema_change_policy='stop' AS SELECT rowid FROM seed WHERE (seed._bool AND (seed._bool OR similar_to_escape(seed._string::STRING, seed._string::STRING, seed._string::STRING)::BOOL)) to emit 43 events
    changefeed_test.go:967: similar_to_escape(): invalid escape string

[ ] #96532 st_addmeasure() builtin

   changefeed_test.go:926: SELECT array_to_string(IFNULL(array_agg(distinct rowid),'{}'),'|') FROM seed WHERE (seed._bool AND (seed._bool OR _st_containsproperly('010500000006000000010200000006000000B857524DE504E24150D08567BB34EFC11030A619F338FC414154D6369276FCC1C7946C7EA4AFFD412CBE7B5586CBF3C1A81ECEE71A31FB4105145B1B280EF0C14C5F0E7A241102425214EA05DAE5A2418736A1CFFEA5FE41920932F51DAEF541010200000002000000AD609053C14CD6410CD87D04A516C9C175939A21CCB9F3C16B24B9E5CA17D4C1010200000002000000FE8B356AD279D9C11972A9BE0E1CFB41823BB0FD8F4702C2BF97E513C5340142010200000008000000DCDFF3A52ABACCC1012093B1F1E900C2440EF771C275F6417E808BD1E098F9C11EAB41121EF4F2418F293556DC07C8C1C72CB86B2ED8F44196418442EA92BD41D2FE95715940FA41C12665FB65BBF141B05359D2118BD7C138228931E27FE5410380ED152613E5C1E69A53026F5AFC41564A07E1325CFAC1C29F2DED28500042010200000002000000237D5BD8EB7AE141C8CA96B5C850F5C187184FF687C5F5411DF006AFE113D8C10102000000050000007C12DDCA3BEEEB419554E57B4760EDC14AD3625025DDFB41C044F53A0DADB8411D1CD43F2DFBF54178DC9DAD563ECE4122243910C4E4FEC1ED5DA810DC4A0242D4702D18E1A3F2C1720CCD4FFB71F341':::GEOMETRY::GEOMETRY, st_addmeasure('01030000C001000000090000009919FE18829AF9C1DBDB72058EADE3C14F30D1A618DBC44123566DB717E5E0C1A492434886CC00C2631F0E42C278FAC1566333DF80A4CB418AB4A42D172ED9C1D0A51BA7915DE341A6A42AEBDD8402C238C6B8CB1080EEC144BAFCAFAAC0F0417A25FE8FD92DC34124E4D9792459EBC1FF308F0FFC1EE8C1861ED91816B9C2C1BACC5127387D0142136A5AE50FDFF7C12CFFE119A97DFCC13EB8840F2BDDD3C12E302B72AC400242B72627C0E7C0D141E15441E0E8E1EB419C71C757C958FBC1B8D2BAF1E429F4C1F5AB11EA3C4F0242A66049F67C7F02C26BA741639710EFC1D6A4481EA937F1C13CA5AAE11F43FD41E1F7459E8406E1411E627B3B500EE1419919FE18829AF9C1DBDB72058EADE3C14F30D1A618DBC44123566DB717E5E0C1':::GEOMETRY::GEOMETRY, (-2.1640923848244995):::FLOAT8::FLOAT8, 0.10539122302522724:::FLOAT8::FLOAT8)::GEOMETRY::GEOMETRY)::BOOL))
   changefeed_test.go:942: Expecting statement CREATE CHANGEFEED WITH schema_change_policy='stop' AS SELECT rowid FROM seed WHERE (seed._bool AND (seed._bool OR _st_containsproperly('010500000006000000010200000006000000B857524DE504E24150D08567BB34EFC11030A619F338FC414154D6369276FCC1C7946C7EA4AFFD412CBE7B5586CBF3C1A81ECEE71A31FB4105145B1B280EF0C14C5F0E7A241102425214EA05DAE5A2418736A1CFFEA5FE41920932F51DAEF541010200000002000000AD609053C14CD6410CD87D04A516C9C175939A21CCB9F3C16B24B9E5CA17D4C1010200000002000000FE8B356AD279D9C11972A9BE0E1CFB41823BB0FD8F4702C2BF97E513C5340142010200000008000000DCDFF3A52ABACCC1012093B1F1E900C2440EF771C275F6417E808BD1E098F9C11EAB41121EF4F2418F293556DC07C8C1C72CB86B2ED8F44196418442EA92BD41D2FE95715940FA41C12665FB65BBF141B05359D2118BD7C138228931E27FE5410380ED152613E5C1E69A53026F5AFC41564A07E1325CFAC1C29F2DED28500042010200000002000000237D5BD8EB7AE141C8CA96B5C850F5C187184FF687C5F5411DF006AFE113D8C10102000000050000007C12DDCA3BEEEB419554E57B4760EDC14AD3625025DDFB41C044F53A0DADB8411D1CD43F2DFBF54178DC9DAD563ECE4122243910C4E4FEC1ED5DA810DC4A0242D4702D18E1A3F2C1720CCD4FFB71F341':::GEOMETRY::GEOMETRY, st_addmeasure('01030000C001000000090000009919FE18829AF9C1DBDB72058EADE3C14F30D1A618DBC44123566DB717E5E0C1A492434886CC00C2631F0E42C278FAC1566333DF80A4CB418AB4A42D172ED9C1D0A51BA7915DE341A6A42AEBDD8402C238C6B8CB1080EEC144BAFCAFAAC0F0417A25FE8FD92DC34124E4D9792459EBC1FF308F0FFC1EE8C1861ED91816B9C2C1BACC5127387D0142136A5AE50FDFF7C12CFFE119A97DFCC13EB8840F2BDDD3C12E302B72AC400242B72627C0E7C0D141E15441E0E8E1EB419C71C757C958FBC1B8D2BAF1E429F4C1F5AB11EA3C4F0242A66049F67C7F02C26BA741639710EFC1D6A4481EA937F1C13CA5AAE11F43FD41E1F7459E8406E1411E627B3B500EE1419919FE18829AF9C1DBDB72058EADE3C14F30D1A618DBC44123566DB717E5E0C1':::GEOMETRY::GEOMETRY, (-2.1640923848244995):::FLOAT8::FLOAT8, 0.10539122302522724:::FLOAT8::FLOAT8)::GEOMETRY::GEOMETRY)::BOOL)) to emit 50 events
   changefeed_test.go:959: st_addmeasure(): input geometry must be LINESTRING or MULTILINESTRING

Jira issue: CRDB-30106

Epic CRDB-21713

[blathers-crl]

cc @cockroachdb/cdc

[amruss]

Root of the problem might be in how strings are parsed in the two scenarios