alamb commented on code in PR #8773: URL: https://github.com/apache/arrow-datafusion/pull/8773#discussion_r1445280843
########## datafusion/sqllogictest/test_files/expr.slt: ########## @@ -0,0 +1,1249 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at + +# http://www.apache.org/licenses/LICENSE-2.0 + +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# test_boolean_expressions +query BBBB +SELECT true, false, false = false, true = false +---- +true false true false + +# test_mathematical_expressions_with_null +query RRRRRRRRRRRRRRRRRR?RRRRRRRIRRRRRRBB +SELECT + sqrt(NULL), + cbrt(NULL), + sin(NULL), + cos(NULL), + tan(NULL), + asin(NULL), + acos(NULL), + atan(NULL), + sinh(NULL), + cosh(NULL), + tanh(NULL), + asinh(NULL), + acosh(NULL), + atanh(NULL), + floor(NULL), + ceil(NULL), + round(NULL), + trunc(NULL), + abs(NULL), + signum(NULL), + exp(NULL), + ln(NULL), + log2(NULL), + log10(NULL), + power(NULL, 2), + power(NULL, NULL), + power(2, NULL), + atan2(NULL, NULL), + atan2(1, NULL), + atan2(NULL, 1), + nanvl(NULL, NULL), + nanvl(1, NULL), + nanvl(NULL, 1), + isnan(NULL), + iszero(NULL) +---- +NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL + +# test_array_cast_invalid_timezone_will_panic +statement error Parser error: Invalid timezone "Foo": 'Foo' is not a valid timezone +SELECT arrow_cast('2021-01-02T03:04:00', 'Timestamp(Nanosecond, Some("Foo"))') + +# test_array_index +query III??IIIIII +SELECT + ([5,4,3,2,1])[1], + ([5,4,3,2,1])[2], + ([5,4,3,2,1])[5], + ([[1, 2], [2, 3], [3,4]])[1], + ([[1, 2], [2, 3], [3,4]])[3], + ([[1, 2], [2, 3], [3,4]])[1][1], + ([[1, 2], [2, 3], [3,4]])[2][2], + ([[1, 2], [2, 3], [3,4]])[3][2], + -- out of bounds + ([5,4,3,2,1])[0], + ([5,4,3,2,1])[6], + -- ([5,4,3,2,1])[-1], -- TODO: wrong answer + -- ([5,4,3,2,1])[null], -- TODO: not supported + ([5,4,3,2,1])[100] +---- +5 4 1 [1, 2] [3, 4] 1 3 4 NULL NULL NULL + +# test_array_literals +query ????? +SELECT + [1,2,3,4,5], + [true, false], + ['str1', 'str2'], + [[1,2], [3,4]], + [] Review Comment: 💯 for adding the test that was commented out in the rs test ########## datafusion/sqllogictest/test_files/expr.slt: ########## @@ -0,0 +1,1249 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at + +# http://www.apache.org/licenses/LICENSE-2.0 + +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# test_boolean_expressions +query BBBB +SELECT true, false, false = false, true = false +---- +true false true false + +# test_mathematical_expressions_with_null +query RRRRRRRRRRRRRRRRRR?RRRRRRRIRRRRRRBB +SELECT + sqrt(NULL), + cbrt(NULL), + sin(NULL), + cos(NULL), + tan(NULL), + asin(NULL), + acos(NULL), + atan(NULL), + sinh(NULL), + cosh(NULL), + tanh(NULL), + asinh(NULL), + acosh(NULL), + atanh(NULL), + floor(NULL), + ceil(NULL), + round(NULL), + trunc(NULL), + abs(NULL), + signum(NULL), + exp(NULL), + ln(NULL), + log2(NULL), + log10(NULL), + power(NULL, 2), + power(NULL, NULL), + power(2, NULL), + atan2(NULL, NULL), + atan2(1, NULL), + atan2(NULL, 1), + nanvl(NULL, NULL), + nanvl(1, NULL), + nanvl(NULL, 1), + isnan(NULL), + iszero(NULL) +---- +NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL + +# test_array_cast_invalid_timezone_will_panic +statement error Parser error: Invalid timezone "Foo": 'Foo' is not a valid timezone +SELECT arrow_cast('2021-01-02T03:04:00', 'Timestamp(Nanosecond, Some("Foo"))') + +# test_array_index +query III??IIIIII +SELECT + ([5,4,3,2,1])[1], + ([5,4,3,2,1])[2], + ([5,4,3,2,1])[5], + ([[1, 2], [2, 3], [3,4]])[1], + ([[1, 2], [2, 3], [3,4]])[3], + ([[1, 2], [2, 3], [3,4]])[1][1], + ([[1, 2], [2, 3], [3,4]])[2][2], + ([[1, 2], [2, 3], [3,4]])[3][2], + -- out of bounds + ([5,4,3,2,1])[0], + ([5,4,3,2,1])[6], + -- ([5,4,3,2,1])[-1], -- TODO: wrong answer + -- ([5,4,3,2,1])[null], -- TODO: not supported + ([5,4,3,2,1])[100] +---- +5 4 1 [1, 2] [3, 4] 1 3 4 NULL NULL NULL + +# test_array_literals +query ????? +SELECT + [1,2,3,4,5], + [true, false], + ['str1', 'str2'], + [[1,2], [3,4]], + [] +---- +[1, 2, 3, 4, 5] [true, false] [str1, str2] [[1, 2], [3, 4]] [] + +# test_struct_literals +query ?????? +SELECT + STRUCT(1,2,3,4,5), + STRUCT(Null), + STRUCT(2), + STRUCT('1',Null), + STRUCT(true, false), + STRUCT('str1', 'str2') +---- +{c0: 1, c1: 2, c2: 3, c3: 4, c4: 5} {c0: } {c0: 2} {c0: 1, c1: } {c0: true, c1: false} {c0: str1, c1: str2} + +# test binary_bitwise_shift +query IIII +SELECT + 2 << 10, + 2048 >> 10, + 2048 << NULL, + 2048 >> NULL +---- +2048 2 NULL NULL + +query ? +SELECT interval '1' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '1 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '500 milliseconds' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '5 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 5.000000000 secs + +query ? +SELECT interval '0.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '5 minute' +---- +0 years 0 mons 0 days 0 hours 5 mins 0.000000000 secs + +query ? +SELECT interval '5 minute 1 second' +---- +0 years 0 mons 0 days 0 hours 5 mins 1.000000000 secs + +query ? +SELECT interval '1 hour' +---- +0 years 0 mons 0 days 1 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 hour' +---- +0 years 0 mons 0 days 5 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day' +---- +0 years 0 mons 1 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 week' +---- +0 years 0 mons 7 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 weeks' +---- +0 years 0 mons 14 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day 1' +---- +0 years 0 mons 1 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.5' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '0.5 day 1' +---- +0 years 0 mons 0 days 12 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.49 day' +---- +0 years 0 mons 0 days 11 hours 45 mins 36.000000000 secs + +query ? +SELECT interval '0.499 day' +---- +0 years 0 mons 0 days 11 hours 58 mins 33.600000000 secs + +query ? +SELECT interval '0.4999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 51.360000000 secs + +query ? +SELECT interval '0.49999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.136000000 secs + +query ? +SELECT interval '0.49999999999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.999999136 secs + +query ? +SELECT interval '5 day' +---- +0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs + +query ? Review Comment: The original test had a comment I think that was helpful: ```suggestion # Hour is ignored, this matches PostgreSQL query ? ``` ########## datafusion/sqllogictest/test_files/expr.slt: ########## @@ -0,0 +1,1249 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at + +# http://www.apache.org/licenses/LICENSE-2.0 + +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# test_boolean_expressions +query BBBB +SELECT true, false, false = false, true = false +---- +true false true false + +# test_mathematical_expressions_with_null +query RRRRRRRRRRRRRRRRRR?RRRRRRRIRRRRRRBB +SELECT + sqrt(NULL), + cbrt(NULL), + sin(NULL), + cos(NULL), + tan(NULL), + asin(NULL), + acos(NULL), + atan(NULL), + sinh(NULL), + cosh(NULL), + tanh(NULL), + asinh(NULL), + acosh(NULL), + atanh(NULL), + floor(NULL), + ceil(NULL), + round(NULL), + trunc(NULL), + abs(NULL), + signum(NULL), + exp(NULL), + ln(NULL), + log2(NULL), + log10(NULL), + power(NULL, 2), + power(NULL, NULL), + power(2, NULL), + atan2(NULL, NULL), + atan2(1, NULL), + atan2(NULL, 1), + nanvl(NULL, NULL), + nanvl(1, NULL), + nanvl(NULL, 1), + isnan(NULL), + iszero(NULL) +---- +NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL + +# test_array_cast_invalid_timezone_will_panic +statement error Parser error: Invalid timezone "Foo": 'Foo' is not a valid timezone +SELECT arrow_cast('2021-01-02T03:04:00', 'Timestamp(Nanosecond, Some("Foo"))') + +# test_array_index +query III??IIIIII +SELECT + ([5,4,3,2,1])[1], + ([5,4,3,2,1])[2], + ([5,4,3,2,1])[5], + ([[1, 2], [2, 3], [3,4]])[1], + ([[1, 2], [2, 3], [3,4]])[3], + ([[1, 2], [2, 3], [3,4]])[1][1], + ([[1, 2], [2, 3], [3,4]])[2][2], + ([[1, 2], [2, 3], [3,4]])[3][2], + -- out of bounds + ([5,4,3,2,1])[0], + ([5,4,3,2,1])[6], + -- ([5,4,3,2,1])[-1], -- TODO: wrong answer + -- ([5,4,3,2,1])[null], -- TODO: not supported + ([5,4,3,2,1])[100] +---- +5 4 1 [1, 2] [3, 4] 1 3 4 NULL NULL NULL + +# test_array_literals +query ????? +SELECT + [1,2,3,4,5], + [true, false], + ['str1', 'str2'], + [[1,2], [3,4]], + [] +---- +[1, 2, 3, 4, 5] [true, false] [str1, str2] [[1, 2], [3, 4]] [] + +# test_struct_literals +query ?????? +SELECT + STRUCT(1,2,3,4,5), + STRUCT(Null), + STRUCT(2), + STRUCT('1',Null), + STRUCT(true, false), + STRUCT('str1', 'str2') +---- +{c0: 1, c1: 2, c2: 3, c3: 4, c4: 5} {c0: } {c0: 2} {c0: 1, c1: } {c0: true, c1: false} {c0: str1, c1: str2} + +# test binary_bitwise_shift +query IIII +SELECT + 2 << 10, + 2048 >> 10, + 2048 << NULL, + 2048 >> NULL +---- +2048 2 NULL NULL + +query ? +SELECT interval '1' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '1 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '500 milliseconds' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '5 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 5.000000000 secs + +query ? +SELECT interval '0.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '5 minute' +---- +0 years 0 mons 0 days 0 hours 5 mins 0.000000000 secs + +query ? +SELECT interval '5 minute 1 second' +---- +0 years 0 mons 0 days 0 hours 5 mins 1.000000000 secs + +query ? +SELECT interval '1 hour' +---- +0 years 0 mons 0 days 1 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 hour' +---- +0 years 0 mons 0 days 5 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day' +---- +0 years 0 mons 1 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 week' +---- +0 years 0 mons 7 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 weeks' +---- +0 years 0 mons 14 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day 1' +---- +0 years 0 mons 1 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.5' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '0.5 day 1' +---- +0 years 0 mons 0 days 12 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.49 day' +---- +0 years 0 mons 0 days 11 hours 45 mins 36.000000000 secs + +query ? +SELECT interval '0.499 day' +---- +0 years 0 mons 0 days 11 hours 58 mins 33.600000000 secs + +query ? +SELECT interval '0.4999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 51.360000000 secs + +query ? +SELECT interval '0.49999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.136000000 secs + +query ? +SELECT interval '0.49999999999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.999999136 secs + +query ? +SELECT interval '5 day' +---- +0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 day' hour +---- +0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 day 4 hours 3 minutes 2 seconds 100 milliseconds' +---- +0 years 0 mons 5 days 4 hours 3 mins 2.100000000 secs + +query ? +SELECT interval '0.5 month' +---- +0 years 0 mons 15 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '0.5' month +---- +0 years 0 mons 15 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 month' +---- +0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1' MONTH +---- +0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 month' +---- +0 years 5 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '13 month' +---- +0 years 13 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '0.5 year' +---- +0 years 6 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year' +---- +0 years 12 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 decade' +---- +0 years 120 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 decades' +---- +0 years 240 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 century' +---- +0 years 1200 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 year' +---- +0 years 24 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day' +---- +0 years 12 mons 1 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour' +---- +0 years 12 mons 1 days 1 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour 1 minute' +---- +0 years 12 mons 1 days 1 hours 1 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour 1 minute 1 second' +---- +0 years 12 mons 1 days 1 hours 1 mins 1.000000000 secs + +query I +SELECT ascii('') +---- +0 + +query I +SELECT ascii('x') +---- +120 + +query I +SELECT ascii(NULL) +---- +NULL + +query I +SELECT bit_length('') +---- +0 + +query I +SELECT bit_length('chars') +---- +40 + +query I +SELECT bit_length('josé') +---- +40 + +query ? +SELECT bit_length(NULL) +---- +NULL + +query T +SELECT btrim(' xyxtrimyyx ', NULL) +---- +NULL + +query T +SELECT btrim(' xyxtrimyyx ') +---- +xyxtrimyyx + +query T +SELECT btrim('\n xyxtrimyyx \n') +---- +\n xyxtrimyyx \n + +query T +SELECT btrim('xyxtrimyyx', 'xyz') +---- +trim + +query T +SELECT btrim('\nxyxtrimyyx\n', 'xyz\n') +---- +trim + +query ? +SELECT btrim(NULL, 'xyz') +---- +NULL + +query T +SELECT chr(CAST(120 AS int)) +---- +x + +query T +SELECT chr(CAST(128175 AS int)) +---- +💯 + +query T +SELECT chr(CAST(NULL AS int)) +---- +NULL + +query T +SELECT concat('a','b','c') +---- +abc + +query T +SELECT concat('abcde', 2, NULL, 22) +---- +abcde222 + +query T +SELECT concat(NULL) +---- +(empty) + +query T +SELECT concat_ws(',', 'abcde', 2, NULL, 22) +---- +abcde,2,22 + +query T +SELECT concat_ws('|','a','b','c') +---- +a|b|c + +query T +SELECT concat_ws('|',NULL) +---- +(empty) + +query T +SELECT concat_ws(NULL,'a',NULL,'b','c') +---- +NULL + +query T +SELECT concat_ws('|','a',NULL) +---- +a + +query T +SELECT concat_ws('|','a',NULL,NULL) +---- +a + +query T +SELECT initcap('') +---- +(empty) + +query T +SELECT initcap('hi THOMAS') +---- +Hi Thomas + +query ? +SELECT initcap(NULL) +---- +NULL + +query T +SELECT lower('') +---- +(empty) + +query T +SELECT lower('TOM') +---- +tom + +query ? +SELECT lower(NULL) +---- +NULL + +query T +SELECT ltrim(' zzzytest ', NULL) +---- +NULL + +query T +SELECT ltrim(' zzzytest ') +---- +zzzytest + +query T +SELECT ltrim('zzzytest', 'xyz') +---- +test + +query ? +SELECT ltrim(NULL, 'xyz') +---- +NULL + +query I +SELECT octet_length('') +---- +0 + +query I +SELECT octet_length('chars') +---- +5 + +query I +SELECT octet_length('josé') +---- +5 + +query ? +SELECT octet_length(NULL) +---- +NULL + +query T +SELECT repeat('Pg', 4) +---- +PgPgPgPg + +query T +SELECT repeat('Pg', CAST(NULL AS INT)) +---- +NULL + +query ? +SELECT repeat(NULL, 4) +---- +NULL + +query T +SELECT replace('abcdefabcdef', 'cd', 'XX') +---- +abXXefabXXef + +query T +SELECT replace('abcdefabcdef', 'cd', NULL) +---- +NULL + +query T +SELECT replace('abcdefabcdef', 'notmatch', 'XX') +---- +abcdefabcdef + +query T +SELECT replace('abcdefabcdef', NULL, 'XX') +---- +NULL + +query ? +SELECT replace(NULL, 'cd', 'XX') +---- +NULL + +query T +SELECT rtrim(' testxxzx ') +---- + testxxzx + +query T +SELECT rtrim(' zzzytest ', NULL) +---- +NULL + +query T +SELECT rtrim('testxxzx', 'xyz') +---- +test + +query ? +SELECT rtrim(NULL, 'xyz') +---- +NULL + +query T +SELECT split_part('abc~@~def~@~ghi', '~@~', 2) +---- +def + +query T +SELECT split_part('abc~@~def~@~ghi', '~@~', 20) +---- +(empty) + +query ? +SELECT split_part(NULL, '~@~', 20) +---- +NULL + +query T +SELECT split_part('abc~@~def~@~ghi', NULL, 20) +---- +NULL + +query T +SELECT split_part('abc~@~def~@~ghi', '~@~', CAST(NULL AS INT)) +---- +NULL + +query B +SELECT starts_with('alphabet', 'alph') +---- +true + +query B +SELECT starts_with('alphabet', 'blph') +---- +false + +query B +SELECT starts_with(NULL, 'blph') +---- +NULL + +query B +SELECT starts_with('alphabet', NULL) +---- +NULL + +query T +SELECT to_hex(2147483647) +---- +7fffffff + +query T +SELECT to_hex(9223372036854775807) +---- +7fffffffffffffff + +query T +SELECT to_hex(CAST(NULL AS int)) +---- +NULL + +query T +SELECT trim(' tom ') +---- +tom + +query T +SELECT trim(LEADING ' tom ') +---- +tom + +query T +SELECT trim(TRAILING ' tom ') +---- + tom + +query T +SELECT trim(BOTH ' tom ') +---- +tom + +query T +SELECT trim(LEADING ' ' FROM ' tom ') +---- +tom + +query T +SELECT trim(TRAILING ' ' FROM ' tom ') +---- + tom + +query T +SELECT trim(BOTH ' ' FROM ' tom ') +---- +tom + +query T +SELECT trim(' ' FROM ' tom ') +---- +tom + +query T +SELECT trim(LEADING 'x' FROM 'xxxtomxxx') +---- +tomxxx + +query T +SELECT trim(TRAILING 'x' FROM 'xxxtomxxx') +---- +xxxtom + +query T +SELECT trim(BOTH 'x' FROM 'xxxtomxx') +---- +tom + +query T +SELECT trim('x' FROM 'xxxtomxx') +---- +tom + + +query T +SELECT trim(LEADING 'xy' FROM 'xyxabcxyzdefxyx') +---- +abcxyzdefxyx + +query T +SELECT trim(TRAILING 'xy' FROM 'xyxabcxyzdefxyx') +---- +xyxabcxyzdef + +query T +SELECT trim(BOTH 'xy' FROM 'xyxabcxyzdefxyx') +---- +abcxyzdef + +query T +SELECT trim('xy' FROM 'xyxabcxyzdefxyx') +---- +abcxyzdef + +query T +SELECT trim(' tom') +---- +tom + +query T +SELECT trim('') +---- +(empty) + +query T +SELECT trim('tom ') +---- +tom + +query T +SELECT upper('') +---- +(empty) + +query T +SELECT upper('tom') +---- +TOM + +query ? +SELECT upper(NULL) +---- +NULL + +# TODO issue: https://github.com/apache/arrow-datafusion/issues/6596 +# query ?? +#SELECT +# CAST([1,2,3,4] AS INT[]) as a, +# CAST([1,2,3,4] AS NUMERIC(10,4)[]) as b +#---- +#[1, 2, 3, 4] [1.0000, 2.0000, 3.0000, 4.0000] + +# test_random_expression +query BB +SELECT + random() BETWEEN 0.0 AND 1.0, + random() = random() +---- +true false + +# test_uuid_expression +query II +SELECT octet_length(uuid()), length(uuid()) +---- +36 36 + +# test_cast_expressions +query IIII +SELECT + CAST('0' AS INT) as a, + CAST(NULL AS INT) as b, + TRY_CAST('0' AS INT) as c, + TRY_CAST('x' AS INT) as d +---- +0 NULL 0 NULL + +# test_extract_date_part + +query R +SELECT date_part('YEAR', CAST('2000-01-01' AS DATE)) +---- +2000 + +query R +SELECT EXTRACT(year FROM timestamp '2020-09-08T12:00:00+00:00') +---- +2020 + +query R +SELECT date_part('QUARTER', CAST('2000-01-01' AS DATE)) +---- +1 + +query R +SELECT EXTRACT(quarter FROM to_timestamp('2020-09-08T12:00:00+00:00')) +---- +3 + +query R +SELECT date_part('MONTH', CAST('2000-01-01' AS DATE)) +---- +1 + +query R +SELECT EXTRACT(month FROM to_timestamp('2020-09-08T12:00:00+00:00')) +---- +9 + +query R +SELECT date_part('WEEK', CAST('2003-01-01' AS DATE)) +---- +1 + +query R +SELECT EXTRACT(WEEK FROM to_timestamp('2020-09-08T12:00:00+00:00')) +---- +37 + +query R +SELECT date_part('DAY', CAST('2000-01-01' AS DATE)) +---- +1 + +query R +SELECT EXTRACT(day FROM to_timestamp('2020-09-08T12:00:00+00:00')) +---- +8 + +query R +SELECT date_part('DOY', CAST('2000-01-01' AS DATE)) +---- +1 + +query R +SELECT EXTRACT(doy FROM to_timestamp('2020-09-08T12:00:00+00:00')) +---- +252 + +query R +SELECT date_part('DOW', CAST('2000-01-01' AS DATE)) +---- +6 + +query R +SELECT EXTRACT(dow FROM to_timestamp('2020-09-08T12:00:00+00:00')) +---- +2 + +query R +SELECT date_part('HOUR', CAST('2000-01-01' AS DATE)) +---- +0 + +query R +SELECT EXTRACT(hour FROM to_timestamp('2020-09-08T12:03:03+00:00')) +---- +12 + +query R +SELECT EXTRACT(minute FROM to_timestamp('2020-09-08T12:12:00+00:00')) +---- +12 + +query R +SELECT date_part('minute', to_timestamp('2020-09-08T12:12:00+00:00')) +---- +12 + +query R +SELECT EXTRACT(second FROM timestamp '2020-09-08T12:00:12.12345678+00:00') +---- +12.12345678 + +query R +SELECT EXTRACT(millisecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00') +---- +12123.45678 + +query R +SELECT EXTRACT(microsecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00') +---- +12123456.78 + +query R +SELECT EXTRACT(nanosecond FROM timestamp '2020-09-08T12:00:12.12345678+00:00') +---- +12123456780 + +query R Review Comment: ```suggestion # Keep precision when coercing Utf8 to Timestamp query R ``` ########## datafusion/sqllogictest/test_files/expr.slt: ########## @@ -0,0 +1,1249 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at + +# http://www.apache.org/licenses/LICENSE-2.0 + +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# test_boolean_expressions +query BBBB +SELECT true, false, false = false, true = false +---- +true false true false + +# test_mathematical_expressions_with_null +query RRRRRRRRRRRRRRRRRR?RRRRRRRIRRRRRRBB +SELECT + sqrt(NULL), + cbrt(NULL), + sin(NULL), + cos(NULL), + tan(NULL), + asin(NULL), + acos(NULL), + atan(NULL), + sinh(NULL), + cosh(NULL), + tanh(NULL), + asinh(NULL), + acosh(NULL), + atanh(NULL), + floor(NULL), + ceil(NULL), + round(NULL), + trunc(NULL), + abs(NULL), + signum(NULL), + exp(NULL), + ln(NULL), + log2(NULL), + log10(NULL), + power(NULL, 2), + power(NULL, NULL), + power(2, NULL), + atan2(NULL, NULL), + atan2(1, NULL), + atan2(NULL, 1), + nanvl(NULL, NULL), + nanvl(1, NULL), + nanvl(NULL, 1), + isnan(NULL), + iszero(NULL) +---- +NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL + +# test_array_cast_invalid_timezone_will_panic +statement error Parser error: Invalid timezone "Foo": 'Foo' is not a valid timezone +SELECT arrow_cast('2021-01-02T03:04:00', 'Timestamp(Nanosecond, Some("Foo"))') + +# test_array_index +query III??IIIIII +SELECT + ([5,4,3,2,1])[1], + ([5,4,3,2,1])[2], + ([5,4,3,2,1])[5], + ([[1, 2], [2, 3], [3,4]])[1], + ([[1, 2], [2, 3], [3,4]])[3], + ([[1, 2], [2, 3], [3,4]])[1][1], + ([[1, 2], [2, 3], [3,4]])[2][2], + ([[1, 2], [2, 3], [3,4]])[3][2], + -- out of bounds + ([5,4,3,2,1])[0], + ([5,4,3,2,1])[6], + -- ([5,4,3,2,1])[-1], -- TODO: wrong answer + -- ([5,4,3,2,1])[null], -- TODO: not supported + ([5,4,3,2,1])[100] +---- +5 4 1 [1, 2] [3, 4] 1 3 4 NULL NULL NULL + +# test_array_literals +query ????? +SELECT + [1,2,3,4,5], + [true, false], + ['str1', 'str2'], + [[1,2], [3,4]], + [] +---- +[1, 2, 3, 4, 5] [true, false] [str1, str2] [[1, 2], [3, 4]] [] + +# test_struct_literals +query ?????? +SELECT + STRUCT(1,2,3,4,5), + STRUCT(Null), + STRUCT(2), + STRUCT('1',Null), + STRUCT(true, false), + STRUCT('str1', 'str2') +---- +{c0: 1, c1: 2, c2: 3, c3: 4, c4: 5} {c0: } {c0: 2} {c0: 1, c1: } {c0: true, c1: false} {c0: str1, c1: str2} + +# test binary_bitwise_shift +query IIII +SELECT + 2 << 10, + 2048 >> 10, + 2048 << NULL, + 2048 >> NULL +---- +2048 2 NULL NULL + +query ? +SELECT interval '1' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '1 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '500 milliseconds' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '5 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 5.000000000 secs + +query ? +SELECT interval '0.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '5 minute' +---- +0 years 0 mons 0 days 0 hours 5 mins 0.000000000 secs + +query ? +SELECT interval '5 minute 1 second' +---- +0 years 0 mons 0 days 0 hours 5 mins 1.000000000 secs + +query ? +SELECT interval '1 hour' +---- +0 years 0 mons 0 days 1 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 hour' +---- +0 years 0 mons 0 days 5 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day' +---- +0 years 0 mons 1 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 week' +---- +0 years 0 mons 7 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 weeks' +---- +0 years 0 mons 14 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day 1' +---- +0 years 0 mons 1 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.5' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '0.5 day 1' +---- +0 years 0 mons 0 days 12 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.49 day' +---- +0 years 0 mons 0 days 11 hours 45 mins 36.000000000 secs + +query ? +SELECT interval '0.499 day' +---- +0 years 0 mons 0 days 11 hours 58 mins 33.600000000 secs + +query ? +SELECT interval '0.4999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 51.360000000 secs + +query ? +SELECT interval '0.49999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.136000000 secs + +query ? +SELECT interval '0.49999999999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.999999136 secs + +query ? +SELECT interval '5 day' +---- +0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 day' hour +---- +0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 day 4 hours 3 minutes 2 seconds 100 milliseconds' +---- +0 years 0 mons 5 days 4 hours 3 mins 2.100000000 secs + +query ? +SELECT interval '0.5 month' +---- +0 years 0 mons 15 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '0.5' month +---- +0 years 0 mons 15 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 month' +---- +0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1' MONTH +---- +0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 month' +---- +0 years 5 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '13 month' +---- +0 years 13 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '0.5 year' +---- +0 years 6 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year' +---- +0 years 12 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 decade' +---- +0 years 120 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 decades' +---- +0 years 240 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 century' +---- +0 years 1200 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 year' +---- +0 years 24 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day' +---- +0 years 12 mons 1 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour' +---- +0 years 12 mons 1 days 1 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour 1 minute' +---- +0 years 12 mons 1 days 1 hours 1 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour 1 minute 1 second' +---- +0 years 12 mons 1 days 1 hours 1 mins 1.000000000 secs + +query I +SELECT ascii('') +---- +0 + +query I +SELECT ascii('x') +---- +120 + +query I +SELECT ascii(NULL) +---- +NULL + +query I +SELECT bit_length('') +---- +0 + +query I +SELECT bit_length('chars') +---- +40 + +query I +SELECT bit_length('josé') +---- +40 + +query ? +SELECT bit_length(NULL) +---- +NULL + +query T +SELECT btrim(' xyxtrimyyx ', NULL) +---- +NULL + +query T +SELECT btrim(' xyxtrimyyx ') +---- +xyxtrimyyx + +query T +SELECT btrim('\n xyxtrimyyx \n') +---- +\n xyxtrimyyx \n + +query T +SELECT btrim('xyxtrimyyx', 'xyz') +---- +trim + +query T +SELECT btrim('\nxyxtrimyyx\n', 'xyz\n') +---- +trim + +query ? +SELECT btrim(NULL, 'xyz') +---- +NULL + +query T +SELECT chr(CAST(120 AS int)) +---- +x + +query T +SELECT chr(CAST(128175 AS int)) +---- +💯 Review Comment: nice! ########## datafusion/sqllogictest/test_files/expr.slt: ########## @@ -0,0 +1,1249 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at + +# http://www.apache.org/licenses/LICENSE-2.0 + +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# test_boolean_expressions +query BBBB +SELECT true, false, false = false, true = false +---- +true false true false + +# test_mathematical_expressions_with_null +query RRRRRRRRRRRRRRRRRR?RRRRRRRIRRRRRRBB +SELECT + sqrt(NULL), + cbrt(NULL), + sin(NULL), + cos(NULL), + tan(NULL), + asin(NULL), + acos(NULL), + atan(NULL), + sinh(NULL), + cosh(NULL), + tanh(NULL), + asinh(NULL), + acosh(NULL), + atanh(NULL), + floor(NULL), + ceil(NULL), + round(NULL), + trunc(NULL), + abs(NULL), + signum(NULL), + exp(NULL), + ln(NULL), + log2(NULL), + log10(NULL), + power(NULL, 2), + power(NULL, NULL), + power(2, NULL), + atan2(NULL, NULL), + atan2(1, NULL), + atan2(NULL, 1), + nanvl(NULL, NULL), + nanvl(1, NULL), + nanvl(NULL, 1), + isnan(NULL), + iszero(NULL) +---- +NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL + +# test_array_cast_invalid_timezone_will_panic +statement error Parser error: Invalid timezone "Foo": 'Foo' is not a valid timezone +SELECT arrow_cast('2021-01-02T03:04:00', 'Timestamp(Nanosecond, Some("Foo"))') + +# test_array_index +query III??IIIIII +SELECT + ([5,4,3,2,1])[1], + ([5,4,3,2,1])[2], + ([5,4,3,2,1])[5], + ([[1, 2], [2, 3], [3,4]])[1], + ([[1, 2], [2, 3], [3,4]])[3], + ([[1, 2], [2, 3], [3,4]])[1][1], + ([[1, 2], [2, 3], [3,4]])[2][2], + ([[1, 2], [2, 3], [3,4]])[3][2], + -- out of bounds + ([5,4,3,2,1])[0], + ([5,4,3,2,1])[6], + -- ([5,4,3,2,1])[-1], -- TODO: wrong answer + -- ([5,4,3,2,1])[null], -- TODO: not supported + ([5,4,3,2,1])[100] +---- +5 4 1 [1, 2] [3, 4] 1 3 4 NULL NULL NULL + +# test_array_literals +query ????? +SELECT + [1,2,3,4,5], + [true, false], + ['str1', 'str2'], + [[1,2], [3,4]], + [] +---- +[1, 2, 3, 4, 5] [true, false] [str1, str2] [[1, 2], [3, 4]] [] + +# test_struct_literals +query ?????? +SELECT + STRUCT(1,2,3,4,5), + STRUCT(Null), + STRUCT(2), + STRUCT('1',Null), + STRUCT(true, false), + STRUCT('str1', 'str2') +---- +{c0: 1, c1: 2, c2: 3, c3: 4, c4: 5} {c0: } {c0: 2} {c0: 1, c1: } {c0: true, c1: false} {c0: str1, c1: str2} + +# test binary_bitwise_shift +query IIII +SELECT + 2 << 10, + 2048 >> 10, + 2048 << NULL, + 2048 >> NULL +---- +2048 2 NULL NULL + +query ? +SELECT interval '1' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '1 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '500 milliseconds' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '5 second' +---- +0 years 0 mons 0 days 0 hours 0 mins 5.000000000 secs + +query ? +SELECT interval '0.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '.5 minute' +---- +0 years 0 mons 0 days 0 hours 0 mins 30.000000000 secs + +query ? +SELECT interval '5 minute' +---- +0 years 0 mons 0 days 0 hours 5 mins 0.000000000 secs + +query ? +SELECT interval '5 minute 1 second' +---- +0 years 0 mons 0 days 0 hours 5 mins 1.000000000 secs + +query ? +SELECT interval '1 hour' +---- +0 years 0 mons 0 days 1 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 hour' +---- +0 years 0 mons 0 days 5 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day' +---- +0 years 0 mons 1 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 week' +---- +0 years 0 mons 7 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 weeks' +---- +0 years 0 mons 14 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 day 1' +---- +0 years 0 mons 1 days 0 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.5' +---- +0 years 0 mons 0 days 0 hours 0 mins 0.500000000 secs + +query ? +SELECT interval '0.5 day 1' +---- +0 years 0 mons 0 days 12 hours 0 mins 1.000000000 secs + +query ? +SELECT interval '0.49 day' +---- +0 years 0 mons 0 days 11 hours 45 mins 36.000000000 secs + +query ? +SELECT interval '0.499 day' +---- +0 years 0 mons 0 days 11 hours 58 mins 33.600000000 secs + +query ? +SELECT interval '0.4999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 51.360000000 secs + +query ? +SELECT interval '0.49999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.136000000 secs + +query ? +SELECT interval '0.49999999999 day' +---- +0 years 0 mons 0 days 11 hours 59 mins 59.999999136 secs + +query ? +SELECT interval '5 day' +---- +0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 day' hour +---- +0 years 0 mons 5 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 day 4 hours 3 minutes 2 seconds 100 milliseconds' +---- +0 years 0 mons 5 days 4 hours 3 mins 2.100000000 secs + +query ? +SELECT interval '0.5 month' +---- +0 years 0 mons 15 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '0.5' month +---- +0 years 0 mons 15 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 month' +---- +0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1' MONTH +---- +0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '5 month' +---- +0 years 5 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '13 month' +---- +0 years 13 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '0.5 year' +---- +0 years 6 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year' +---- +0 years 12 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 decade' +---- +0 years 120 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 decades' +---- +0 years 240 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 century' +---- +0 years 1200 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '2 year' +---- +0 years 24 mons 0 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day' +---- +0 years 12 mons 1 days 0 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour' +---- +0 years 12 mons 1 days 1 hours 0 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour 1 minute' +---- +0 years 12 mons 1 days 1 hours 1 mins 0.000000000 secs + +query ? +SELECT interval '1 year 1 day 1 hour 1 minute 1 second' +---- +0 years 12 mons 1 days 1 hours 1 mins 1.000000000 secs + +query I +SELECT ascii('') Review Comment: I actually prefer this style (one query per expression) rather than the style earlier in the PR of one query with all the expressions as I find the output easier to understand ########## datafusion/core/tests/sql/expr.rs: ########## @@ -458,108 +165,6 @@ async fn test_substring_expr() -> Result<()> { Ok(()) } -/// Test string expressions test split into two batches -/// to prevent stack overflow error -#[tokio::test] -async fn test_string_expressions_batch1() -> Result<()> { - test_expression!("ascii('')", "0"); - test_expression!("ascii('x')", "120"); - test_expression!("ascii(NULL)", "NULL"); - test_expression!("bit_length('')", "0"); - test_expression!("bit_length('chars')", "40"); - test_expression!("bit_length('josé')", "40"); - test_expression!("bit_length(NULL)", "NULL"); - test_expression!("btrim(' xyxtrimyyx ', NULL)", "NULL"); - test_expression!("btrim(' xyxtrimyyx ')", "xyxtrimyyx"); - test_expression!("btrim('\n xyxtrimyyx \n')", "\n xyxtrimyyx \n"); - test_expression!("btrim('xyxtrimyyx', 'xyz')", "trim"); - test_expression!("btrim('\nxyxtrimyyx\n', 'xyz\n')", "trim"); - test_expression!("btrim(NULL, 'xyz')", "NULL"); - test_expression!("chr(CAST(120 AS int))", "x"); - test_expression!("chr(CAST(128175 AS int))", "💯"); - test_expression!("chr(CAST(NULL AS int))", "NULL"); - test_expression!("concat('a','b','c')", "abc"); - test_expression!("concat('abcde', 2, NULL, 22)", "abcde222"); - test_expression!("concat(NULL)", ""); - test_expression!("concat_ws(',', 'abcde', 2, NULL, 22)", "abcde,2,22"); - test_expression!("concat_ws('|','a','b','c')", "a|b|c"); - test_expression!("concat_ws('|',NULL)", ""); - test_expression!("concat_ws(NULL,'a',NULL,'b','c')", "NULL"); - test_expression!("concat_ws('|','a',NULL)", "a"); - test_expression!("concat_ws('|','a',NULL,NULL)", "a"); - test_expression!("initcap('')", ""); - test_expression!("initcap('hi THOMAS')", "Hi Thomas"); - test_expression!("initcap(NULL)", "NULL"); - test_expression!("lower('')", ""); - test_expression!("lower('TOM')", "tom"); - test_expression!("lower(NULL)", "NULL"); - test_expression!("ltrim(' zzzytest ', NULL)", "NULL"); - test_expression!("ltrim(' zzzytest ')", "zzzytest "); - test_expression!("ltrim('zzzytest', 'xyz')", "test"); - test_expression!("ltrim(NULL, 'xyz')", "NULL"); - test_expression!("octet_length('')", "0"); - test_expression!("octet_length('chars')", "5"); - test_expression!("octet_length('josé')", "5"); - test_expression!("octet_length(NULL)", "NULL"); - test_expression!("repeat('Pg', 4)", "PgPgPgPg"); - test_expression!("repeat('Pg', CAST(NULL AS INT))", "NULL"); - test_expression!("repeat(NULL, 4)", "NULL"); - test_expression!("replace('abcdefabcdef', 'cd', 'XX')", "abXXefabXXef"); - test_expression!("replace('abcdefabcdef', 'cd', NULL)", "NULL"); - test_expression!("replace('abcdefabcdef', 'notmatch', 'XX')", "abcdefabcdef"); - test_expression!("replace('abcdefabcdef', NULL, 'XX')", "NULL"); - test_expression!("replace(NULL, 'cd', 'XX')", "NULL"); - test_expression!("rtrim(' testxxzx ')", " testxxzx"); - test_expression!("rtrim(' zzzytest ', NULL)", "NULL"); - test_expression!("rtrim('testxxzx', 'xyz')", "test"); - test_expression!("rtrim(NULL, 'xyz')", "NULL"); - Ok(()) -} - -/// Test string expressions test split into two batches Review Comment: it is sweet that that this is no longer a problem with sqllogictest (though to be fair this code should probably have been using functions rather than macros) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
