sqlsmith: use constant or positive number for the function `OVERLAY` #10695

st1page · 2023-07-03T06:23:48Z

https://buildkite.com/risingwavelabs/pull-request/builds/26586#01890d80-44dd-43ca-8ec7-b22fc2c502a1

It is because that the start_pos and string_size of the expressionOVERLAY is too large. I think it should be a constant or bounded number @kwannoel
🤔 it might be a performance issue of the overlay function #10699. But no matter how, we should ban the nonpositive offset here.

The text was updated successfully, but these errors were encountered:

st1page · 2023-07-03T06:26:07Z

DDLs and data

CREATE TABLE alltypes1 (c1 BOOLEAN, c2 SMALLINT, c3 INT, c4 BIGINT, c5 REAL, c6 DOUBLE, c7 NUMERIC, c8 DATE, c9 CHARACTER VARYING, c10 TIME, c11 TIMESTAMP, c13 INTERVAL, c14 STRUCT<a INT>, c15 INT[], c16 CHARACTER VARYING[]);

CREATE TABLE person (id BIGINT, name CHARACTER VARYING, email_address CHARACTER VARYING, credit_card CHARACTER VARYING, city CHARACTER VARYING, state CHARACTER VARYING, date_time TIMESTAMP, extra CHARACTER VARYING, PRIMARY KEY (id));

INSERT INTO person VALUES ((BIGINT '938'), '5LHBM98qnJ', 'eyrpHYmmpL', 'vZh3h18MPS', 'TaVuJqa1wt', 'h9ebANlWjK', TIMESTAMP '2023-06-30 18:25:55', '0vafhJQArE'), ((BIGINT '396'), 'Je02o8xfFD', 'IVDQ1jEgK7', 'beQzFakzn2', 'sw2EPcdldy', 'wzBqajp6Yg', TIMESTAMP '2023-06-30 18:26:22', '9E7jBmDlNK'), ((BIGINT '120'), 'PHJzNUer7M', 'iMI3lsGsoS', 'MzSlBIXlIt', 'I4SQmc5fEl', 'g1cbn5pZNR', TIMESTAMP '2023-06-30 17:27:24', 'C207hcBvnz'), ((BIGINT '8421600910210919261'), 'k5S2xhxS3y', 'vLSHQaCFXx', 'FLWT8foxON', 'EKjceiT3fu', 'rlPjofwxTk', TIMESTAMP '2023-06-30 18:25:46', '6S261kk0hP'), ((BIGINT '9223372036854775807'), 'FGIT5H5JkY', '4Qiu9qhbjY', 'CgttSns37T', 'qDG5PSdJKr', 'Nug8msdOxD', TIMESTAMP '2023-06-29 18:27:24', 'ACB0vUrjPR'), ((BIGINT '-9223372036854775808'), 'AaYfAg2mHg', '78z7v6hdfX', 'oLolCYmjaw', 'TmO7Sm8hn0', 'fRRTM1xW0g', TIMESTAMP '2023-06-30 18:25:47', 'CvsgK9rKZa'), ((BIGINT '580'), 'MIkb8kHQBu', 'wdww9MubY1', 'b6p7oqPz25', 'yurnr8rnq7', 'XxUdu1rIFv', TIMESTAMP '2023-06-30 18:26:12', 'GqNh9bxzw5'), ((BIGINT '452'), 'S6sMxmXFyX', 'e72XhQW2Ke', 'GJmqURkgWH', '3cN47U1EBY', '8mvwscwX2X', TIMESTAMP '2023-06-30 18:27:23', 'LtNHtcaMBi'), ((BIGINT '173'), 'Rcue1au9nP', 'oC7i6ynjAQ', 'egA4upnCGm', '0udHyORjSy', 'fgIMqS0V5u', TIMESTAMP '2023-06-30 17:27:24', 'DParTRvQX7'), ((BIGINT '9223372036854775807'), 'lsUbYj7c1S', 'C283Bc8wjw', 'ZSaI6UkWif', 'fq5iIR6azF', 'PUQYtlEp67', TIMESTAMP '2023-06-30 18:27:01', 'xwfN5cqYPX'), ((BIGINT '642'), 'IKAj1r0UEj', 'ySW7c425h9', '3HMMM3Ba4X', '6fRXnl1qQ8', 'VOQsEtfuJw', TIMESTAMP '2023-06-30 18:26:24', 'Pv4QNAw0wK'), ((BIGINT '535'), 'y8wDrWD8Az', 'po90Cuh5C6', '2v1wzV5pzZ', 'y9ebkZ1K43', '84WZuc4e4w', TIMESTAMP '2023-06-30 18:27:10', 'iPLkaxti3K'), ((BIGINT '0'), 'pnIgwKvJEy', 'MXjKmdWx6e', 'Xc1JYnkQG9', 'pJCdzQnYJc', 'HvHrqj96IX', TIMESTAMP '2023-06-30 18:27:23', '171OejrBbd'), ((BIGINT '777'), 'weiNCD0jQc', '1FJDwIdOcV', 'V6mxLtC5O2', 'legribCJ3r', 'GfLfmPxRnM', TIMESTAMP '2023-06-30 18:27:24', 'NgQl8yI4fD'), ((BIGINT '-1710408287912697177'), 'dTUNztmKqC', 'KdP2SaTUzY', 'o2ikEqfS5x', 'FCIsKC5qFq', 'kNmJ4DMTwZ', TIMESTAMP '2023-06-30 18:27:14', 'rAtzGdQZdN'), ((BIGINT '374'), 'UxgZUmtpSH', 'F4Y9pWeBGI', 'N162oCZ2He', 'B6Z8R1XG2h', 'cDIqe2Yfrl', TIMESTAMP '2023-06-30 18:27:02', 'f4VYRyeTas'), ((BIGINT '728'), 'Oke0p3Dhzd', 'fDDGoDCc5E', 'pxS709ImNp', '5Cb9uIWtqe', 'C683SYmD3g', TIMESTAMP '2023-06-30 18:26:25', 'zGetdcW4jr'), ((BIGINT '0'), '8iCDGrHlYY', 'HiPf4Wspp6', 'T3unzWMqZM', 'XAeSbPv15x', '0tbFBNhCfP', TIMESTAMP '2023-06-30 18:26:11', 'QEE0XEC4KW'), ((BIGINT '118'), 'NjmBibT99r', 'J5lgQJc2HV', '9icYeKMVSR', '5yphHyxI0n', 'vWrwjSqk5p', TIMESTAMP '2023-06-30 18:25:49', '4H9TMP6fDJ'), ((BIGINT '123'), 'qpEDd5SXs1', 'COWYsalZvk', '1Ydu2S44cu', 'CVu3xdUu4f', 'kcG4yaiiBA', TIMESTAMP '2023-06-30 18:27:18', '3oHnvh195C'), ((BIGINT '27'), 'aGcui3qD5z', 'LvsxPHw72a', '07qX1o3Bno', 'j5HGwapEGW', '5WFQVpiI0R', TIMESTAMP '2023-06-30 18:25:46', 'HPKtE43MMa'), ((BIGINT '406'), 'XvrcxsTrBp', 'uYJdfeLGoY', 'pOcsuyKaeV', 'u5IIStXdlC', 'Q06j7LVuKL', TIMESTAMP '2023-06-30 17:27:24', 'Bm0nejAE6O'), ((BIGINT '-235903608041799906'), 'OEK26rFfyS', 'VAYMWq8OSE', 'xdKrC2ExSn', 'ayl9mi5g4K', 'y9BGDma31A', TIMESTAMP '2023-06-30 18:27:24', '2yRYxcOVPi'), ((BIGINT '903'), 'OLUosB5tKX', 'Gq2r3giCag', 'Fmvn6iMw34', 'ehYkV9N8aq', 'zIX4AhuC0N', TIMESTAMP '2023-06-30 17:27:24', '1Zl6K5ISoD'), ((BIGINT '1'), 'pFtfoIMdgU', 'XChzSWsSBu', 'MhTCiLOggB', 'oiw2rQsErz', 'kVyNJkV7SB', TIMESTAMP '2023-06-30 18:27:03', 'laLkI7fBcH'), ((BIGINT '641'), '3JNxpBVvuL', 'VkM5Lb7bj4', 'ezzOX6kyDS', 'FVzQ19m3lt', 'dNC6ZgwhmZ', TIMESTAMP '2023-06-29 18:27:24', 'TQ63h9jESY'), ((BIGINT '597'), 'SudPyCb4W9', 'iN5u2PRp5f', '4f6oUPT9eV', 'nyKqa4Bpx5', 'ShYDTjDhCo', TIMESTAMP '2023-06-30 18:26:31', 'mDuHkyWkWB'), ((BIGINT '-9223372036854775808'), 'Zm1r5jesGz', 'JoHZ9UCw90', 'MxEm9Q7qWn', '2uyEfGPfn8', '7AXrVbCf4c', TIMESTAMP '2023-06-29 18:27:24', 'AP9glBT9OX'), ((BIGINT '580'), 'nvYXnJQrWP', 'zBOb0w5kte', 'tpyLIM94kv', 'RpAGY9Z9jB', 'Vceb7RvKkI', TIMESTAMP '2023-06-30 18:26:38', 'RhjEQlgaGT'), ((BIGINT '719'), 'goo5yQ2zHu', 'Poiqsnhies', '3eyEotfyxG', 'EP1LbZtXu7', 'fQU5LHlamL', TIMESTAMP '2023-06-30 18:27:19', '0q6LSd5VOX'), ((BIGINT '894'), 'gt7UgJgaJm', 'GPiJgyTYI4', 'ThrCWz57v5', 'HmyAmTSM8q', '7WhVz4qZDi', TIMESTAMP '2023-06-30 18:26:42', 'nEEktC0mPT'), ((BIGINT '1'), 'm5LFueo4rI', 'Pm0SK9wZkO', 'Xc4ocJ7sKW', 'leaci9GrRb', '07PtL61wUO', TIMESTAMP '2023-06-30 18:27:08', 'rWI9isy15s'), ((BIGINT '55'), 'Sp3T38oRtm', 'slpxrKXtWv', 'KxlMSn99O0', 'CsGAQgkVhi', 'L3VehRXysF', TIMESTAMP '2023-06-29 18:27:24', 'pNUqgFKJZi'), ((BIGINT '142'), 'd4BQCwxTKY', 'OcvptPBi82', 'gXrXV3Oq6N', 'D0tqrgjskN', 'g0gzM7wNPk', TIMESTAMP '2023-06-30 18:26:17', 'F7HIico7q2'), ((BIGINT '9223372036854775807'), 'nX7z6psrA6', 'WxyzAXbnTw', '9ca65V05Cn', 'WSo3Hi41XX', '9OhsZzf9KS', TIMESTAMP '2023-06-30 18:26:03', 'AjH5tGagJ1'), ((BIGINT '-9223372036854775808'), 'um4dCzaZCi', 'QZw40g8hhO', '2MYYxiDFVQ', 'd5BBb2KJ1K', 'WMDbytXetI', TIMESTAMP '2023-06-30 18:26:27', '4CV0HA2g06'), ((BIGINT '-9223372036854775808'), '6dm4uZLkkg', 'TCtAJKpDBz', '39AphqCwsS', 'cCELVCjb4C', 'aYhF49hDrs', TIMESTAMP '2023-06-30 18:26:33', '7EX7Vc2AWF'), ((BIGINT '289'), 'Z2f6skNpAk', 'Qlxj4oQpMi', 'G10gBIvKtF', 'Eq64PXaAqM', 'a9s379sndT', TIMESTAMP '2023-06-23 18:27:24', 'AJUiTOhrh2'), ((BIGINT '9223372036854775807'), 'xP5X4yOK3x', 'PqrOtMfM2H', 'oq8IcDtv8Z', 'J9SpHENNca', '8keuU9uRx9', TIMESTAMP '2023-06-30 18:27:23', '3pB1XjexoX'), ((BIGINT '484'), 'WShpA9xz66', 'WfuTKrUHV3', '6eqVhVJazH', 'fCVLvWvEeK', 'zd2YCsVFvJ', TIMESTAMP '2023-06-30 18:26:21', 'uAHdyhNG6u'), ((BIGINT '389'), '1D24NUr8Hv', 'c2USQNI9tF', 'nETmYmGCHX', 'AVb0QFQWi7', 'MYQZUAKhRS', TIMESTAMP '2023-06-30 18:26:46', '4K7BOIP0zP'), ((BIGINT '583'), 'dauGHCox9S', 'uMuXnxoW0e', 'xdnjr9txg5', 'gCRFDayMkj', '2HTHc8QpME', TIMESTAMP '2023-06-30 18:26:54', 'jM7GPgo0u9'), ((BIGINT '472'), '6k47bKLJul', '0CYmKPIcrM', 'GzLH5AiXiN', 'HtdhJGRTYX', 'GiQMfzA9nk', TIMESTAMP '2023-06-30 18:26:59', '5RCpHQKufU'), ((BIGINT '9223372036854775807'), 'N6rOz3ILFs', 'rMkJHdUm3Y', 'zVceG2jddE', 'y7bAJ3lX3C', 'pDjhCedZyi', TIMESTAMP '2023-06-30 18:26:24', 'u9i6WS1tIM'), ((BIGINT '711'), 'Va0swJ2AqV', 'w2V8jtT4aD', 'lm7zM3qpog', 'iotKKV6BiO', 'WuVFH0mp0T', TIMESTAMP '2023-06-23 18:27:24', 'vczXm0Hi6o'), ((BIGINT '994'), 'usM2EpEcsA', 'mjqHgmKMSe', 'lwpoqe2K4F', 'tVSqdXM3VT', 'gx81rtr4cQ', TIMESTAMP '2023-06-30 18:27:09', 'B0cnoXBe6U'), ((BIGINT '891'), 'GZmGOckDWs', 'JKv3bwTYzR', 'BNkvEegXdP', 'sGVj5rh5Cj', 'swdvYPdFyr', TIMESTAMP '2023-06-30 18:27:23', 'zS8p8C5RCF'), ((BIGINT '571'), 'VJJqqptJpz', '2Hwb70Ec1w', 'OgZqDj0I9m', 'tpt3wzkLyN', 'dux5yYhFEN', TIMESTAMP '2023-06-30 18:26:25', '4S2lwRfSYw'), ((BIGINT '380'), 'dXYZZ1XpfU', 'oSWtgW5z36', 'p59bAsMkYs', 'eCfrg50riO', '4aX2kDjAPV', TIMESTAMP '2023-06-30 18:27:21', 'H23Lt4WwD7'), ((BIGINT '835'), 'hWTSVN2ZjY', 'RDLDu6Wqxp', 'mFG6IyK7Ir', 'B2yoMJRGG3', 'fntWJ7jFay', TIMESTAMP '2023-06-30 18:25:57', 'OhQaFWXm1F');
INSERT INTO alltypes1 VALUES (true, (SMALLINT '-15773'), (INT '855'), (BIGINT '177'), (REAL '1'), (FLOAT '719'), (640), DATE '2023-06-30', 'uZUl4f0IYo', TIME '17:27:24', TIMESTAMP '2023-06-30 18:26:24', (INTERVAL '-19'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '259'), (INT '1092066354'), (INT '776'), (INT '667')], ARRAY['BMjqGVFirf', 'OkRY7xVrZo']), (false, (SMALLINT '-31796'), (INT '2147483647'), (BIGINT '963'), (REAL '-2147483648'), (FLOAT '352'), (618), DATE '2023-06-30', 'PJ8qTm0bwJ', TIME '18:26:07', TIMESTAMP '2023-06-30 18:27:24', (INTERVAL '13'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '-1244187973')], ARRAY['SBg8uuVBQJ', '1AcghAsN9k', 'qyBkd9vcoN']), (true, (SMALLINT '451'), (INT '970'), (BIGINT '964'), (REAL '565'), (FLOAT '-821645464'), (305), DATE '2023-06-30', 'tcxaCzDgUO', TIME '18:26:26', TIMESTAMP '2023-06-29 18:27:24', (INTERVAL '76'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '464'), (INT '666')], ARRAY['AsuMlC6Ij2', 'SCHVmtLP5B', 'r2wQEWDrdJ']), (false, (SMALLINT '32767'), (INT '945'), (BIGINT '827'), (REAL '1'), (FLOAT '2147483647'), (881), DATE '2023-06-30', 'pXekppor7U', TIME '18:26:45', TIMESTAMP '2023-06-30 18:27:12', (INTERVAL '-604800'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '2147483647')], ARRAY['rDgOmvEVaT']), (false, (SMALLINT '768'), (INT '319'), (BIGINT '884'), (REAL '774'), (FLOAT '572'), (667), DATE '2023-06-30', 'yn9vauHv0G', TIME '18:26:58', TIMESTAMP '2023-06-30 18:25:50', (INTERVAL '-50'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '978'), (INT '63')], ARRAY['d5Ko3nRToq', 'n5HHCc7Mxr', 'gdwQliAhx3', 'RHYkh38hHF']), (true, (SMALLINT '-32768'), (INT '94'), (BIGINT '975'), (REAL '551'), (FLOAT '-1299004452'), (317), DATE '2023-06-30', 'bdgdN1RCnX', TIME '18:25:57', TIMESTAMP '2023-06-30 18:26:51', (INTERVAL '-68'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '234'), (INT '1187507939')], ARRAY['hRp8aa45R7', 'sUduldzyRN', 'hFoMwePIUM']), (true, (SMALLINT '342'), (INT '473'), (BIGINT '9223372036854775807'), (REAL '442970462'), (FLOAT '678'), (27), DATE '2023-06-30', 'qAexwLf97O', TIME '18:25:59', TIMESTAMP '2023-06-30 18:26:53', (INTERVAL '-77'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '369'), (INT '106')], ARRAY['uIwdELmb1S', '7JLAQS8lVs']), (false, (SMALLINT '32767'), (INT '137'), (BIGINT '415'), (REAL '133'), (FLOAT '377'), (-2147483648), DATE '2023-06-29', 'EPQifQ00X5', TIME '18:27:10', TIMESTAMP '2023-06-30 18:27:02', (INTERVAL '7'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '0'), (INT '394'), (INT '264')], ARRAY['IEY1gingd4']), (true, (SMALLINT '236'), (INT '535'), (BIGINT '84'), (REAL '688'), (FLOAT '710'), (367), DATE '2023-06-30', 'YQBMQ2fDfr', TIME '18:27:24', TIMESTAMP '2023-06-30 18:26:05', (INTERVAL '-60'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '-614033911'), (INT '2147483647'), (INT '257')], ARRAY['iv4xRS8rDA', 'JmXDEIuN9T']), (true, (SMALLINT '283'), (INT '776'), (BIGINT '461'), (REAL '310'), (FLOAT '521'), (200), DATE '2023-06-30', 'jyOHl8P9iy', TIME '18:26:44', TIMESTAMP '2023-06-30 18:27:24', (INTERVAL '-60'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '425'), (INT '440'), (INT '702'), (INT '876')], ARRAY['1tky3fEHru', 'H4fiE9nT0t', 'KM8izaZV2n', 'M5PxALhmnQ']), (false, (SMALLINT '504'), (INT '986'), (BIGINT '108'), (REAL '507'), (FLOAT '190'), (883), DATE '2023-06-29', 'VF8pvnUIco', TIME '18:26:24', TIMESTAMP '2023-06-30 18:27:12', (INTERVAL '-77'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '-2147483648')], ARRAY['fczD232fr0', 'pziPKLqq1V']), (true, (SMALLINT '32767'), (INT '600'), (BIGINT '996'), (REAL '1'), (FLOAT '446'), (2147483647), DATE '2023-06-30', 'xKSqwPvxqJ', TIME '18:26:24', TIMESTAMP '2023-06-23 18:27:24', (INTERVAL '-1'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '31'), (INT '-1960265936')], ARRAY['ADAUksGyke', 'kmkxl9kybP']), (false, (SMALLINT '-29887'), (INT '-12333936'), (BIGINT '649'), (REAL '199'), (FLOAT '123'), (20), DATE '2023-06-30', 'KnJxIPpFSD', TIME '18:27:22', TIMESTAMP '2023-06-29 18:27:24', (INTERVAL '82'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '640'), (INT '790'), (INT '742')], ARRAY['NLHubDudBI']), (false, (SMALLINT '234'), (INT '930'), (BIGINT '299'), (REAL '976'), (FLOAT '409'), (0), DATE '2023-06-30', 'WAIHDlr3d1', TIME '18:26:34', TIMESTAMP '2023-06-30 18:27:23', (INTERVAL '-85'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '826'), (INT '391')], ARRAY['FjSHoI64YJ', '0BRAmYi0E7', '0iM6Kmk9ZP']), (true, (SMALLINT '116'), (INT '464'), (BIGINT '121'), (REAL '978'), (FLOAT '707'), (606), DATE '2023-06-30', 'BdgCRRGUeh', TIME '18:26:21', TIMESTAMP '2023-06-30 18:27:01', (INTERVAL '-76'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '292'), (INT '141')], ARRAY['v83Whoca2j']), (false, (SMALLINT '436'), (INT '562'), (BIGINT '78'), (REAL '904'), (FLOAT '685'), (2147483647), DATE '2023-06-30', 'huWRsNqNWJ', TIME '17:27:24', TIMESTAMP '2023-06-30 18:26:38', (INTERVAL '78'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '1'), (INT '162'), (INT '-2147483648'), (INT '842')], ARRAY['yE8skVWEy8', '7k9XvjTPk7', 'XyfL7PhkaR']), (true, (SMALLINT '-9847'), (INT '2147483647'), (BIGINT '3990375456644291249'), (REAL '736'), (FLOAT '502'), (0), DATE '2023-06-30', 'xLeI8vp2h2', TIME '18:26:11', TIMESTAMP '2023-06-23 18:27:24', (INTERVAL '-1'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '447'), (INT '837'), (INT '268'), (INT '524')], ARRAY['ZpvDUkkcl3']), (true, (SMALLINT '0'), (INT '349'), (BIGINT '5'), (REAL '549'), (FLOAT '708'), (1870764959), DATE '2023-06-30', 'ScTWPW4Yzo', TIME '18:25:59', TIMESTAMP '2023-06-30 18:26:01', (INTERVAL '-604800'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '560')], ARRAY['6R0R8HaNS4', 'bzr1Vd5EPQ']), (false, (SMALLINT '-15777'), (INT '780'), (BIGINT '513'), (REAL '2147483647'), (FLOAT '178'), (963), DATE '2023-06-30', 'oJStrpMFLN', TIME '18:27:24', TIMESTAMP '2023-06-30 18:27:11', (INTERVAL '-87'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '878')], ARRAY['haqwidOtNH', '5YJiPTHT4y', 'C0kiToWowk']), (true, (SMALLINT '467'), (INT '-2147483648'), (BIGINT '9223372036854775807'), (REAL '0'), (FLOAT '570789560'), (773), DATE '2023-06-30', 'cMatCEAvOA', TIME '18:26:24', TIMESTAMP '2023-06-23 18:27:24', (INTERVAL '-1'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '-2147483648'), (INT '687')], ARRAY['iDR0GzkvFP', 'uANcZKML1g']), (false, (SMALLINT '104'), (INT '443'), (BIGINT '1'), (REAL '2147483647'), (FLOAT '298'), (124), DATE '2023-06-30', 'ShTLCmwasw', TIME '18:27:21', TIMESTAMP '2023-06-30 18:27:24', (INTERVAL '-1'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '620'), (INT '808')], ARRAY['MGhoaStGDw']), (true, (SMALLINT '315'), (INT '154'), (BIGINT '272'), (REAL '38'), (FLOAT '431'), (123), DATE '2023-06-30', 'aJksJDaxmq', TIME '18:26:24', TIMESTAMP '2023-06-30 18:27:17', (INTERVAL '52'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '269')], ARRAY['yaPU3Tw9P9', 'kfl4FF2olf', 'H0D3st0Grj', 'gqAT5U5PFu']), (false, (SMALLINT '672'), (INT '0'), (BIGINT '109'), (REAL '331'), (FLOAT '605'), (145), DATE '2023-06-30', 'ezpoAjs7aU', TIME '18:27:04', TIMESTAMP '2023-06-30 18:26:35', (INTERVAL '-604800'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '653082037')], ARRAY['wNoDQkim1e', 'aequrqh8aK']), (true, (SMALLINT '535'), (INT '1'), (BIGINT '0'), (REAL '526'), (FLOAT '156'), (-1896759156), DATE '2023-06-30', 'B6VTXT7UkF', TIME '18:25:48', TIMESTAMP '2023-06-30 18:27:10', (INTERVAL '-3600'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '812')], ARRAY['5oI6tHLjS4']), (true, (SMALLINT '430'), (INT '240810994'), (BIGINT '320'), (REAL '2147483647'), (FLOAT '569'), (598), DATE '2023-06-30', 'seezwO39fr', TIME '18:25:51', TIMESTAMP '2023-06-30 18:26:47', (INTERVAL '71'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '712'), (INT '190'), (INT '884')], ARRAY['N9YxTILhto', 'qSsQlFsrTV', 'mGjMAmGPJn']), (true, (SMALLINT '748'), (INT '961'), (BIGINT '761'), (REAL '461'), (FLOAT '592'), (395), DATE '2023-06-30', 'x0VjyIWwJM', TIME '17:27:24', TIMESTAMP '2023-06-30 18:27:01', (INTERVAL '58'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '749'), (INT '745301396')], ARRAY['tjZT7jTUVS', 'xX29czt3dR', 'pnteJXpUAB', 'FSiV4TP9rf']), (true, (SMALLINT '873'), (INT '890211118'), (BIGINT '-9223372036854775808'), (REAL '277'), (FLOAT '445'), (905), DATE '2023-06-30', 'vxvl39OOox', TIME '17:27:24', TIMESTAMP '2023-06-30 18:27:23', (INTERVAL '86400'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '753')], ARRAY['kc7XqeSPi1']), (true, (SMALLINT '814'), (INT '543'), (BIGINT '-9223372036854775808'), (REAL '703'), (FLOAT '2147483647'), (446), DATE '2023-06-30', 'GzgIUAAa4o', TIME '18:27:00', TIMESTAMP '2023-06-30 18:27:08', (INTERVAL '53'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '530')], ARRAY['oPG09CpCIU', '8PnUAVvmC9', 'AXwr49EmCo']), (true, (SMALLINT '-29608'), (INT '107'), (BIGINT '873'), (REAL '243'), (FLOAT '1'), (65), DATE '2023-06-30', '4zLojbJ7MO', TIME '18:26:01', TIMESTAMP '2023-06-30 18:26:22', (INTERVAL '65'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '108')], ARRAY['N0W5SzQjSf', 'Mp9TKLfOri', 'ZU2noSbL4J']), (true, (SMALLINT '371'), (INT '245'), (BIGINT '-9223372036854775808'), (REAL '671'), (FLOAT '1'), (483), DATE '2023-06-30', 'Bf4kmwYRPO', TIME '18:25:45', TIMESTAMP '2023-06-30 18:26:27', (INTERVAL '29'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '636')], ARRAY['xL3zNN3Kix', '6ol6tIuGPT', '51La3hNYfu']), (false, (SMALLINT '383'), (INT '49'), (BIGINT '447'), (REAL '296'), (FLOAT '102'), (902), DATE '2023-06-30', 'jibu5LJATP', TIME '18:26:55', TIMESTAMP '2023-06-30 18:27:23', (INTERVAL '83'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '1'), (INT '2147483647')], ARRAY['uw5PzcUtbf', '5tRXtO39q8', '8pKeUFpGui', 'XgdbfTuPOX']), (false, (SMALLINT '-20373'), (INT '319'), (BIGINT '1'), (REAL '293'), (FLOAT '0'), (0), DATE '2023-06-30', 'cOGfLQXqSF', TIME '18:26:39', TIMESTAMP '2023-06-29 18:27:24', (INTERVAL '86400'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '2147483647'), (INT '-2147483648')], ARRAY['5PwbPvG0wn', 'rYPwxq3Hs5']), (true, (SMALLINT '682'), (INT '697'), (BIGINT '312'), (REAL '1718140162'), (FLOAT '247'), (753), DATE '2023-06-30', 'YDTmLYg1k4', TIME '18:25:56', TIMESTAMP '2023-06-30 18:26:53', (INTERVAL '-45'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '104')], ARRAY['5XIK8oyYhf']), (true, (SMALLINT '26'), (INT '770'), (BIGINT '217'), (REAL '-1845838661'), (FLOAT '-2147483648'), (724), DATE '2023-06-30', 'iYaELDFhOF', TIME '18:26:09', TIMESTAMP '2023-06-30 18:27:23', (INTERVAL '79'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '335'), (INT '609631704'), (INT '509'), (INT '200')], ARRAY['RVAmC5KcYU', 'HJCxn780Ns', 'RPYZjWx63H']), (false, (SMALLINT '0'), (INT '1'), (BIGINT '681'), (REAL '714'), (FLOAT '-2147483648'), (4), DATE '2023-06-30', 'JUVgqFP7qG', TIME '18:27:24', TIMESTAMP '2023-06-30 18:26:24', (INTERVAL '-86400'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '490'), (INT '190'), (INT '-2147483648')], ARRAY['ighC5TSaBZ']), (false, (SMALLINT '452'), (INT '41'), (BIGINT '590'), (REAL '4'), (FLOAT '847'), (709), DATE '2023-06-30', 'SYNQB318a9', TIME '18:25:56', TIMESTAMP '2023-06-30 18:27:24', (INTERVAL '62'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '-762690160')], ARRAY['5rCaz5DT46', 'rv75f0wOLc', 'DcxIxsjgIW', 'bUuFeE8aDu']), (true, (SMALLINT '373'), (INT '-1008420944'), (BIGINT '1406992022028402440'), (REAL '263927437'), (FLOAT '2147483647'), (0), DATE '2023-06-29', 'VRKIxUkfF0', TIME '18:25:47', TIMESTAMP '2023-06-30 17:27:24', (INTERVAL '-11'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '656'), (INT '568'), (INT '583'), (INT '751')], ARRAY['bN5P4ps0yu', 'z8C1oAzMZ5', 'rFbLFiTNtk', 'G56YttxuxU']), (false, (SMALLINT '534'), (INT '310'), (BIGINT '9223372036854775807'), (REAL '926'), (FLOAT '-1781433032'), (616), DATE '2023-06-30', '07OkF2yEhv', TIME '18:26:19', TIMESTAMP '2023-06-30 18:26:37', (INTERVAL '81'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '1831555434'), (INT '98'), (INT '757'), (INT '678')], ARRAY['zVIvDCLVs9', 'a0lSqud69d', 'QSzJyRf6aF']), (true, (SMALLINT '32767'), (INT '-2147483648'), (BIGINT '355'), (REAL '33'), (FLOAT '1'), (996), DATE '2023-06-30', '3bLr46o9Zu', TIME '18:27:07', TIMESTAMP '2023-06-30 18:26:05', (INTERVAL '-9'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '-2147483648')], ARRAY['3p3avC5XcP', 'xyzOZSwVmF', 'Z2lbriufPj']), (false, (SMALLINT '460'), (INT '957'), (BIGINT '828'), (REAL '704'), (FLOAT '327'), (719), DATE '2023-06-30', 'ZvmQL6Q03e', TIME '18:27:24', TIMESTAMP '2023-06-30 18:26:05', (INTERVAL '99'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '30'), (INT '70')], ARRAY['Fgj76EyVZu']), (true, (SMALLINT '678'), (INT '759'), (BIGINT '1041903236574308501'), (REAL '461'), (FLOAT '533'), (396), DATE '2023-06-30', 'YjtrTB09Gw', TIME '18:26:23', TIMESTAMP '2023-06-29 18:27:24', (INTERVAL '44'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '364')], ARRAY['qLZmAVFnL8', '5hM80lTDb7', 'ESztOjOR5x']), (false, (SMALLINT '501'), (INT '382'), (BIGINT '109'), (REAL '766'), (FLOAT '2147483647'), (1), DATE '2023-06-30', 'igJIdtDDpd', TIME '18:26:24', TIMESTAMP '2023-06-30 18:27:14', (INTERVAL '0'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '387'), (INT '2147483647'), (INT '861828192')], ARRAY['RzAG7sJGvY', 'Zds90BajLU', '9re12adLy4']), (false, (SMALLINT '0'), (INT '2147483647'), (BIGINT '6778002978904354901'), (REAL '690'), (FLOAT '-572620792'), (598), DATE '2023-06-30', '9v2gvdDEPk', TIME '18:26:13', TIMESTAMP '2023-06-30 18:25:57', (INTERVAL '62'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '467317270')], ARRAY['4hudISF4EN']), (true, (SMALLINT '27024'), (INT '775'), (BIGINT '550'), (REAL '729'), (FLOAT '-1608165552'), (164), DATE '2023-06-30', 'nhFqKvMnfk', TIME '18:27:17', TIMESTAMP '2023-06-30 18:25:56', (INTERVAL '-604800'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '0')], ARRAY['94U39hC6wM', '18vFQTfyD3']), (true, (SMALLINT '417'), (INT '28'), (BIGINT '740'), (REAL '567'), (FLOAT '197'), (275), DATE '2023-06-30', 'g4SCtkOTuT', TIME '18:27:24', TIMESTAMP '2023-06-30 17:27:24', (INTERVAL '-86400'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '597'), (INT '70'), (INT '633'), (INT '370')], ARRAY['ZWjfcgOr8y']), (true, (SMALLINT '12553'), (INT '344'), (BIGINT '459'), (REAL '76'), (FLOAT '127'), (-1544333886), DATE '2023-06-30', 'i7tZmnNQAP', TIME '18:27:02', TIMESTAMP '2023-06-30 18:25:49', (INTERVAL '11'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '168'), (INT '24'), (INT '892'), (INT '44')], ARRAY['Jc35wEIola', 'rxSZqmF7k1', 'VbZu1gZbwO']), (false, (SMALLINT '736'), (INT '-503990107'), (BIGINT '9223372036854775807'), (REAL '183'), (FLOAT '628'), (843), DATE '2023-06-30', 'aiVQmxlCHV', TIME '17:27:24', TIMESTAMP '2023-06-30 18:27:24', (INTERVAL '-76'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '1'), (INT '900')], ARRAY['80cfv14nKx']), (true, (SMALLINT '475'), (INT '-2147483648'), (BIGINT '311'), (REAL '728'), (FLOAT '-1458082822'), (485), DATE '2023-06-30', 'GwPOAB8eMo', TIME '18:25:50', TIMESTAMP '2023-06-30 18:26:50', (INTERVAL '93'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '141'), (INT '44563312'), (INT '-1802495654'), (INT '0')], ARRAY['VCtqG59XSJ', 'SHRiR1HGPA']), (true, (SMALLINT '299'), (INT '944'), (BIGINT '588'), (REAL '164'), (FLOAT '944'), (891), DATE '2023-06-30', 'PxJl11XFdW', TIME '18:27:10', TIMESTAMP '2023-06-30 18:27:11', (INTERVAL '-15'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '524')], ARRAY['0n6Ha0tRBb', 'ubusg7GDk3']), (true, (SMALLINT '85'), (INT '923'), (BIGINT '810'), (REAL '666'), (FLOAT '150'), (151013503), DATE '2023-06-30', 'kHRLZ0oAUz', TIME '18:27:23', TIMESTAMP '2023-06-30 18:25:50', (INTERVAL '60'), CAST(NULL AS STRUCT<a INT>), ARRAY[(INT '2147483647')], ARRAY['OZwZgIGRta', 'pGaphRdk4M']);

st1page · 2023-07-03T06:27:50Z

dev=> CREATE MATERIALIZED VIEW stream_query AS 
SELECT tumble_0.c13 AS col_0 
FROM alltypes1 AS tumble_0 
FULL JOIN person AS hop_1 
ON tumble_0.c11 = hop_1.date_time AND (hop_1.state) 
NOT IN (
    ('mSGO2PVYTJ'),
    'HREzSY2VhU',
    tumble_0.c9,
    hop_1.email_address,
    hop_1.email_address, 
    (TRIM(BOTH FROM hop_1.state)), 
    hop_1.city,
    hop_1.extra, 
    hop_1.state) ;
CREATE_MATERIALIZED_VIEW
Time: 2140.758 ms (00:02.141)
dev=> CREATE MATERIALIZED VIEW stream_query AS 
SELECT tumble_0.c13 AS col_0 
FROM alltypes1 AS tumble_0 
FULL JOIN person AS hop_1 
ON tumble_0.c11 = hop_1.date_time AND (hop_1.state) 
NOT IN (
    ('mSGO2PVYTJ'),
    'HREzSY2VhU',
    tumble_0.c9,
    hop_1.email_address,
    hop_1.email_address, 
    (TRIM(BOTH FROM hop_1.state)), 
    (OVERLAY(hop_1.city PLACING tumble_0.c9 FROM tumble_0.c3 FOR tumble_0.c3)), 
    hop_1.city,
    hop_1.extra, 
    hop_1.state) ;

CREATE_MATERIALIZED_VIEW
Time: 98836.193 ms (01:38.836)

st1page · 2023-07-03T06:31:57Z

shorter reproduce

dev=> create materialized view tmp as select * from alltypes1 AS tumble_0 
FULL JOIN person AS hop_1 
ON tumble_0.c11 = hop_1.date_time;
CREATE_MATERIALIZED_VIEW
Time: 2161.373 ms (00:02.161)
dev=> select count(*) from tmp;   
 count 
-------
    92
(1 row)
dev=> create materialized view mvv as select OVERLAY(city PLACING c9 FROM c3 FOR c3) as a from tmp;
CREATE_MATERIALIZED_VIEW
Time: 121302.499 ms (02:01.302)

the data in tmp

    city    |     c9     |     c3      
------------+------------+-------------
 legribCJ3r | ShTLCmwasw |         443
 sGVj5rh5Cj | vxvl39OOox |   890211118
 sGVj5rh5Cj | iYaELDFhOF |         770
 legribCJ3r | PJ8qTm0bwJ |  2147483647
 I4SQmc5fEl | g4SCtkOTuT |          28
            | PxJl11XFdW |         944
 iotKKV6BiO | xLeI8vp2h2 |  2147483647
 B2yoMJRGG3 | 9v2gvdDEPk |  2147483647
            | BdgCRRGUeh |         464
 ayl9mi5g4K | aiVQmxlCHV |  -503990107
 iotKKV6BiO | xKSqwPvxqJ |         600
 XAeSbPv15x |            |            
 gCRFDayMkj |            |            
            | ZvmQL6Q03e |         957
 y7bAJ3lX3C | uZUl4f0IYo |         855
 ehYkV9N8aq | VRKIxUkfF0 | -1008420944
            | GwPOAB8eMo | -2147483648
 j5HGwapEGW |            |            
 fCVLvWvEeK |            |            
 y9ebkZ1K43 | B6VTXT7UkF |           1
 AVb0QFQWi7 |            |            
            | ScTWPW4Yzo |         349
            | nhFqKvMnfk |         775
 ayl9mi5g4K | jyOHl8P9iy |         776
 3cN47U1EBY | WAIHDlr3d1 |         930
 tpt3wzkLyN |            |            
 5yphHyxI0n | i7tZmnNQAP |         344
 iotKKV6BiO | cMatCEAvOA | -2147483648
 FVzQ19m3lt | cOGfLQXqSF |         319
 RpAGY9Z9jB | huWRsNqNWJ |         562
 ayl9mi5g4K | SYNQB318a9 |          41
 u5IIStXdlC | VRKIxUkfF0 | -1008420944
            | bdgdN1RCnX |          94
            | seezwO39fr |   240810994
            | qAexwLf97O |         473
 0udHyORjSy | g4SCtkOTuT |          28
 cCELVCjb4C |            |            
            | yn9vauHv0G |         319
 CsGAQgkVhi | cOGfLQXqSF |         319
            | kHRLZ0oAUz |         923
            | 07OkF2yEhv |         310
 TaVuJqa1wt |            |            
 sGVj5rh5Cj | jibu5LJATP |          49
 6fRXnl1qQ8 | uZUl4f0IYo |         855
 CVu3xdUu4f |            |            
            | VF8pvnUIco |         986
            | oJStrpMFLN |         780
            | YDTmLYg1k4 |         697
 nyKqa4Bpx5 |            |            
            | aJksJDaxmq |         154
 legribCJ3r | SYNQB318a9 |          41
 sw2EPcdldy | 4zLojbJ7MO |         107
 CsGAQgkVhi | tcxaCzDgUO |         970
 sGVj5rh5Cj | WAIHDlr3d1 |         930
 EP1LbZtXu7 |            |            
 tVSqdXM3VT |            |            
 HtdhJGRTYX |            |            
 u5IIStXdlC | g4SCtkOTuT |          28
            | x0VjyIWwJM |         961
 0udHyORjSy | VRKIxUkfF0 | -1008420944
 CsGAQgkVhi | KnJxIPpFSD |   -12333936
 FVzQ19m3lt | KnJxIPpFSD |   -12333936
 Eq64PXaAqM | cMatCEAvOA | -2147483648
 eCfrg50riO |            |            
 HmyAmTSM8q |            |            
 leaci9GrRb | GzgIUAAa4o |         543
 FVzQ19m3lt | tcxaCzDgUO |         970
 3cN47U1EBY | jibu5LJATP |          49
            | pXekppor7U |         945
 y7bAJ3lX3C | JUVgqFP7qG |           1
 FCIsKC5qFq | igJIdtDDpd |         382
 legribCJ3r | aiVQmxlCHV |  -503990107
 3cN47U1EBY | iYaELDFhOF |         770
            | Bf4kmwYRPO |         245
 5Cb9uIWtqe |            |            
 3cN47U1EBY | vxvl39OOox |   890211118
 B6Z8R1XG2h | EPQifQ00X5 |         137
 ayl9mi5g4K | PJ8qTm0bwJ |  2147483647
 FVzQ19m3lt | YjtrTB09Gw |         759
            | ezpoAjs7aU |           0
 ehYkV9N8aq | g4SCtkOTuT |          28
 ayl9mi5g4K | ShTLCmwasw |         443
 I4SQmc5fEl | VRKIxUkfF0 | -1008420944
 legribCJ3r | jyOHl8P9iy |         776
            | 3bLr46o9Zu | -2147483648
 CsGAQgkVhi | YjtrTB09Gw |         759
 6fRXnl1qQ8 | JUVgqFP7qG |           1
 Eq64PXaAqM | xKSqwPvxqJ |         600
 EKjceiT3fu |            |            
            | YQBMQ2fDfr |         535
 Eq64PXaAqM | xLeI8vp2h2 |  2147483647
 D0tqrgjskN |            |            
(92 rows)

st1page · 2023-07-03T06:40:11Z

dev=> select OVERLAY(city PLACING c9 FROM c3 FOR c3) as a from tmp;
ERROR:  QueryError: Internal(Expr error: Invalid parameter start: -503990107 is not positive

kwannoel · 2023-07-03T06:59:02Z

Related: #10496.

Taking a look.

github-actions bot added this to the release-0.20 milestone Jul 3, 2023

st1page assigned kwannoel Jul 3, 2023

st1page changed the title ~~sqlsmith: use constant or bounded number for the function OVERLAY~~ sqlsmith: use constant or positive number for the function OVERLAY Jul 3, 2023

kwannoel mentioned this issue Jul 3, 2023

fix(sqlsmith): constrain overlay arguments #10703

Merged

8 tasks

kwannoel closed this as completed in #10703 Jul 3, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sqlsmith: use constant or positive number for the function `OVERLAY` #10695

sqlsmith: use constant or positive number for the function `OVERLAY` #10695

st1page commented Jul 3, 2023 •

edited

Loading

st1page commented Jul 3, 2023

st1page commented Jul 3, 2023

st1page commented Jul 3, 2023

st1page commented Jul 3, 2023

kwannoel commented Jul 3, 2023

sqlsmith: use constant or positive number for the function OVERLAY #10695

sqlsmith: use constant or positive number for the function OVERLAY #10695

Comments

st1page commented Jul 3, 2023 • edited Loading

st1page commented Jul 3, 2023

st1page commented Jul 3, 2023

st1page commented Jul 3, 2023

st1page commented Jul 3, 2023

kwannoel commented Jul 3, 2023

sqlsmith: use constant or positive number for the function `OVERLAY` #10695

sqlsmith: use constant or positive number for the function `OVERLAY` #10695

st1page commented Jul 3, 2023 •

edited

Loading