zclllyybb commented on code in PR #63923: URL: https://github.com/apache/doris/pull/63923#discussion_r3346238180
########## regression-test/suites/query_p0/sql_functions/datetime_functions/test_human_readable_seconds.groovy: ########## @@ -0,0 +1,186 @@ +// 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. + +// human_readable_seconds follows Presto/Trino semantics: +// - rounds to whole seconds with round(abs(x)) (no fractional / millisecond output) +// - the sign is dropped (absolute value) +// - emits only weeks / days / hours / minutes / seconds +// - NaN / Infinity are rejected +// This suite checks three things: +// 1. vectorized BE execution on nullable / not-nullable / const columns (qt_ + .out), +// 2. FE constant folding produces exactly the same string as BE execution, +// 3. NaN / Infinity raise an error on both the FE-fold and BE-exec paths. +suite("test_human_readable_seconds") { + // ==================================================================== + // Part 1: vectorized BE execution + nullable / const column handling + // ==================================================================== + sql "drop table if exists test_human_readable_seconds" + sql """ + create table test_human_readable_seconds ( + k0 int, + a double not null, + b double null + ) + distributed by hash(k0) + properties + ( + "replication_num" = "1" + ); + """ + + qt_empty_nullable "select human_readable_seconds(b) from test_human_readable_seconds order by k0" + qt_empty_not_nullable "select human_readable_seconds(a) from test_human_readable_seconds order by k0" + + sql "insert into test_human_readable_seconds values (1, 1, null), (2, 1, null), (3, 1, null)" + qt_all_null "select human_readable_seconds(b) from test_human_readable_seconds order by k0" + + sql "truncate table test_human_readable_seconds" + sql """ + insert into test_human_readable_seconds values + (1, 96, 96), + (2, 3762, 3762), + (3, 56363463, 56363463), + (4, 0, 0), + (5, -96, -96), + (6, 0.9, 0.9), + (7, 1.2, 1.2), + (8, 61, 61), + (9, 604800, 604800), + (10, 3600, null); + """ + + qt_nullable "select human_readable_seconds(b) from test_human_readable_seconds order by k0" + qt_not_nullable "select human_readable_seconds(a) from test_human_readable_seconds order by k0" + qt_nullable_no_null "select human_readable_seconds(nullable(a)) from test_human_readable_seconds order by k0" + qt_const_nullable "select human_readable_seconds(NULL) from test_human_readable_seconds order by k0" + qt_const_not_nullable "select human_readable_seconds(3762) from test_human_readable_seconds order by k0" + qt_const_nullable_no_null "select human_readable_seconds(nullable(3762))" + + // ==================================================================== + // Part 2: golden correctness (BE execution) + FE/BE folding consistency. + // + // The list mixes integer and fractional literals on purpose: integer + // literals (e.g. 96, 3600, 604800) are coerced to DOUBLE by the function + // signature, so they still fold through humanReadableSeconds(DoubleLiteral). + // ==================================================================== + def goldenCases = [ + // zero / sub-second rounding + [0, "0 seconds"], + [0.4, "0 seconds"], + [0.5, "1 second"], + [0.9, "1 second"], + [1, "1 second"], + [1.5, "2 seconds"], + // minute boundary: round-half-up lands exactly on 60 + [59, "59 seconds"], + [59.4, "59 seconds"], + [59.5, "1 minute"], + [60, "1 minute"], + [60.4, "1 minute"], + [60.5, "1 minute, 1 second"], + [61, "1 minute, 1 second"], + [119, "1 minute, 59 seconds"], + [120, "2 minutes"], + // hour boundary + [3599, "59 minutes, 59 seconds"], + [3599.5, "1 hour"], + [3600, "1 hour"], + [3601, "1 hour, 1 second"], + [3660, "1 hour, 1 minute"], + [7199, "1 hour, 59 minutes, 59 seconds"], + [7200, "2 hours"], + // day boundary + [86399, "23 hours, 59 minutes, 59 seconds"], + [86400, "1 day"], + [90061, "1 day, 1 hour, 1 minute, 1 second"], + [172800, "2 days"], + // week boundary + [604799, "6 days, 23 hours, 59 minutes, 59 seconds"], + [604800, "1 week"], + [691200, "1 week, 1 day"], + [1209600, "2 weeks"], + // Presto/Trino documentation examples + [96, "1 minute, 36 seconds"], + [3762, "1 hour, 2 minutes, 42 seconds"], + [56363463, "93 weeks, 1 day, 8 hours, 31 minutes, 3 seconds"], + // negatives use absolute value (no leading '-') + [-0.5, "1 second"], + [-59.5, "1 minute"], + [-96, "1 minute, 36 seconds"], + [-3762, "1 hour, 2 minutes, 42 seconds"], + // large values + [1000000, "1 week, 4 days, 13 hours, 46 minutes, 40 seconds"], + [535333.9513888889, "6 days, 4 hours, 42 minutes, 14 seconds"], + [535333.2513888889, "6 days, 4 hours, 42 minutes, 13 seconds"], + [1234567890, "2041 weeks, 1 day, 23 hours, 31 minutes, 30 seconds"], + // out-of-range finite DOUBLE values saturate at int64_t max on both + // FE (Java Math.round -> Long.MAX_VALUE) and BE (explicit clamp). + // Passed as strings so they reach SQL as 1e20 / -1e20, not BigDecimal "1E+20". + ["1e20", "15250284452471 weeks, 3 days, 15 hours, 30 minutes, 7 seconds"], + ["-1e20", "15250284452471 weeks, 3 days, 15 hours, 30 minutes, 7 seconds"] + ] + + // Correctness: force BE to evaluate each constant and compare against the + // hand-verified Presto/Trino result. enable_sql_cache is disabled so a + // repeated query text cannot return a stale cached row. + sql "set enable_sql_cache=false" + sql "set debug_skip_fold_constant=true" + goldenCases.each { c -> Review Comment: why not use `qt_sql` to test them? -- 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
