andygrove commented on code in PR #7114: URL: https://github.com/apache/datafusion-comet/pull/7114#discussion_r4542045161
########## docs/source/contributor-guide/floating_point.md: ########## @@ -201,11 +201,13 @@ and only for a type that contains a `FLOAT` or `DOUBLE`. `MaxSparkVersion`, and add the `run-all-spark-profiles` label to the pull request. CI pins one patch release per Spark line, so when a patch release changed the rule, also test the routing in Scala. -- Add the expression to `CometFloatSemanticsSuite`, which crosses the edge values with operator - contexts and with every expression and aggregate that accepts floating-point input. New - expressions go in its `expressions` or `aggregates` list. If one does not match Spark yet, add a - `KnownGap` that links the issue. Known gaps are strict: a case that starts matching Spark fails, - so the fix also removes the entry. +- Add the expression to the sweep in `float_semantics/`, which crosses the edge values with + operator contexts and with every expression and aggregate that accepts floating-point input. + A new expression goes in `expressions.sql`, or in `aggregates.sql` for an aggregate, with one + query for `DOUBLE` and one for `FLOAT`. If one does not match Spark yet, mark its queries + `ignore(<issue>)`. A SQL file test cannot expect a difference, so a gap that should fail once it + is fixed goes in `CometFloatSemanticsSuite`, whose test for `collect_set` before Spark 4.2 fails + when the answers start to match. Review Comment: Applied your suggestion. The page now sends a known mismatch to a strict `CometFloatSemanticsSuite` test that links the issue and fails once the answers match. ########## spark/src/test/resources/sql-tests/float_semantics/expressions.sql: ########## @@ -0,0 +1,417 @@ +-- 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. + +-- Expressions with float input, over the edge values (one column) or every ordered pair of +-- them (two columns). Each follows one of Spark's float equality rules, which the contributor +-- guide lists (contributor-guide/floating_point.md). The sweep tracks #6385. +-- +-- The e and p CTEs negate the flagged stored values to make the sign-bit NaN at query time +-- (see comparison.sql). +-- +-- hash and xxhash64 of a float, an array and a struct are in expressions/hash/hash.sql. Review Comment: Added `hash(array(-f))`, `xxhash64(array(-f))` and two more array hashes over the edge values to `hash.sql`, next to the `array(-d)` query. It passes. ########## spark/src/test/resources/sql-tests/float_semantics/expressions.sql: ########## @@ -0,0 +1,417 @@ +-- 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. + +-- Expressions with float input, over the edge values (one column) or every ordered pair of +-- them (two columns). Each follows one of Spark's float equality rules, which the contributor +-- guide lists (contributor-guide/floating_point.md). The sweep tracks #6385. +-- +-- The e and p CTEs negate the flagged stored values to make the sign-bit NaN at query time +-- (see comparison.sql). +-- +-- hash and xxhash64 of a float, an array and a struct are in expressions/hash/hash.sql. +-- collect_set is in aggregates_collect_set_4_2.sql. +-- +-- ConstantFolding is on so that the IN query reaches Comet with a folded literal, as Spark +-- plans it. + +-- ConstantFolding: enabled + +statement +CREATE TABLE fs_edge_double(id INT, c DOUBLE, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_double VALUES + (1, 0.0D, false), + (2, double('-0.0'), false), + (3, double('NaN'), false), + (4, double('NaN'), true), + (5, 1.0D, false), + (6, -1.0D, false), + (7, double('Infinity'), false), + (8, double('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_double USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_double a CROSS JOIN fs_edge_double b + +statement +CREATE TABLE fs_edge_float(id INT, c FLOAT, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_float VALUES + (1, float(0.0), false), + (2, float('-0.0'), false), + (3, float('NaN'), false), + (4, float('NaN'), true), + (5, float(1.0), false), + (6, float(-1.0), false), + (7, float('Infinity'), false), + (8, float('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_float USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_float a CROSS JOIN fs_edge_float b + +-- abs, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, abs(c) FROM e + +-- abs, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, abs(c) FROM e + +-- negative, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, -c FROM e + +-- negative, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, -c FROM e + +-- signum, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, signum(c) FROM e + +-- signum, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, signum(c) FROM e + +-- ceil, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, ceil(c) FROM e + +-- ceil, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, ceil(c) FROM e + +-- floor, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, floor(c) FROM e + +-- floor, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, floor(c) FROM e + +-- round, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, round(c, 1), bround(c, 1) FROM e + +-- round, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, round(c, 1), bround(c, 1) FROM e + +-- sqrt, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, sqrt(c) FROM e + +-- sqrt, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, sqrt(c) FROM e + +-- isnan, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, isnan(c) FROM e + +-- isnan, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, isnan(c) FROM e + +-- cast to string, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS STRING) FROM e + +-- cast to string, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS STRING) FROM e + +-- cast to integral, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, try_cast(c AS INT), try_cast(c AS BIGINT) FROM e + +-- cast to integral, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, try_cast(c AS INT), try_cast(c AS BIGINT) FROM e + +-- cast to decimal, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS DECIMAL(10, 2)) FROM e + +-- cast to decimal, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS DECIMAL(10, 2)) FROM e + +-- cast to boolean, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS BOOLEAN) FROM e + +-- cast to boolean, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS BOOLEAN) FROM e + +-- cast between float types, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS DOUBLE), CAST(c AS FLOAT) FROM e + +-- cast between float types, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS DOUBLE), CAST(c AS FLOAT) FROM e + +-- cast array to string, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(array(c) AS STRING) FROM e + +-- cast array to string, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(array(c) AS STRING) FROM e + +-- greatest, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, greatest(l, r) FROM p + +-- greatest, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, greatest(l, r) FROM p + +-- least, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, least(l, r) FROM p + +-- least, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, least(l, r) FROM p + +-- nanvl, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, nanvl(l, r) FROM p + +-- nanvl, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, nanvl(l, r) FROM p + +-- IN with column list, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, l IN (r, CAST('2.0' AS DOUBLE)) FROM p + +-- IN with column list, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, l IN (r, CAST('2.0' AS FLOAT)) FROM p + +-- CASE value WHEN, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, CASE l WHEN r THEN 1 ELSE 0 END FROM p + +-- CASE value WHEN, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, CASE l WHEN r THEN 1 ELSE 0 END FROM p + +-- array_contains, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_contains(array(l, 2), r) FROM p + +-- array_contains, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_contains(array(l, 2), r) FROM p + +-- array_position, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_position(array(2, l), r) FROM p + +-- array_position, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_position(array(2, l), r) FROM p + +-- array_remove, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_remove(array(l, 2), r) FROM p + +-- array_remove, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_remove(array(l, 2), r) FROM p + +-- arrays_overlap, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, arrays_overlap(array(l), array(r)) FROM p + +-- arrays_overlap, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, arrays_overlap(array(l), array(r)) FROM p + +-- array_distinct, DOUBLE +-- Whether float elements run natively depends on the Spark release (array_distinct.sql), so only +-- the answer is checked. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_distinct(array(l, r)) FROM p + +-- array_distinct, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_distinct(array(l, r)) FROM p + +-- array_union, DOUBLE +-- Whether float elements run natively depends on the Spark release (array_union.sql), so only the +-- answer is checked. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_union(array(l), array(r)) FROM p + +-- array_union, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_union(array(l), array(r)) FROM p + +-- array_intersect, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_intersect(array(l), array(r)) FROM p + +-- array_intersect, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_intersect(array(l), array(r)) FROM p + +-- array_except, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_except(array(l), array(r)) FROM p + +-- array_except, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_except(array(l), array(r)) FROM p Review Comment: Moved to `float_semantics/array_set_operations_4_2.sql`, with both `allowIncompatible` keys and `MinSparkVersion: 4.2`, so a plain `query` pins native execution. It has `array_intersect` and `array_except`, and `array_distinct` and `array_union` as well (see the thread on the `spark_answer_only` queries). I ran it on Spark 4.2 and all eight queries run natively and agree with Spark, so none needed `ignore`. `array_sort` and `sort_array` stay in `expressions.sql`. You are right that `array_sort` goes through the dispatcher, so it does not exercise a native kernel. I left it for now, as it still compares Comet's dispatcher result with Spark. ########## spark/src/test/resources/sql-tests/float_semantics/expressions.sql: ########## @@ -0,0 +1,417 @@ +-- 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. + +-- Expressions with float input, over the edge values (one column) or every ordered pair of +-- them (two columns). Each follows one of Spark's float equality rules, which the contributor +-- guide lists (contributor-guide/floating_point.md). The sweep tracks #6385. +-- +-- The e and p CTEs negate the flagged stored values to make the sign-bit NaN at query time +-- (see comparison.sql). +-- +-- hash and xxhash64 of a float, an array and a struct are in expressions/hash/hash.sql. +-- collect_set is in aggregates_collect_set_4_2.sql. +-- +-- ConstantFolding is on so that the IN query reaches Comet with a folded literal, as Spark +-- plans it. + +-- ConstantFolding: enabled + +statement +CREATE TABLE fs_edge_double(id INT, c DOUBLE, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_double VALUES + (1, 0.0D, false), + (2, double('-0.0'), false), + (3, double('NaN'), false), + (4, double('NaN'), true), + (5, 1.0D, false), + (6, -1.0D, false), + (7, double('Infinity'), false), + (8, double('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_double USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_double a CROSS JOIN fs_edge_double b + +statement +CREATE TABLE fs_edge_float(id INT, c FLOAT, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_float VALUES + (1, float(0.0), false), + (2, float('-0.0'), false), + (3, float('NaN'), false), + (4, float('NaN'), true), + (5, float(1.0), false), + (6, float(-1.0), false), + (7, float('Infinity'), false), + (8, float('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_float USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_float a CROSS JOIN fs_edge_float b + +-- abs, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, abs(c) FROM e + +-- abs, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, abs(c) FROM e + +-- negative, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, -c FROM e + +-- negative, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, -c FROM e + +-- signum, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, signum(c) FROM e + +-- signum, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, signum(c) FROM e + +-- ceil, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, ceil(c) FROM e + +-- ceil, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, ceil(c) FROM e + +-- floor, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, floor(c) FROM e + +-- floor, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, floor(c) FROM e + +-- round, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, round(c, 1), bround(c, 1) FROM e + +-- round, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, round(c, 1), bround(c, 1) FROM e + +-- sqrt, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, sqrt(c) FROM e + +-- sqrt, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, sqrt(c) FROM e + +-- isnan, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, isnan(c) FROM e + +-- isnan, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, isnan(c) FROM e + +-- cast to string, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS STRING) FROM e + +-- cast to string, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS STRING) FROM e + +-- cast to integral, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, try_cast(c AS INT), try_cast(c AS BIGINT) FROM e + +-- cast to integral, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, try_cast(c AS INT), try_cast(c AS BIGINT) FROM e + +-- cast to decimal, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS DECIMAL(10, 2)) FROM e + +-- cast to decimal, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS DECIMAL(10, 2)) FROM e + +-- cast to boolean, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS BOOLEAN) FROM e + +-- cast to boolean, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS BOOLEAN) FROM e + +-- cast between float types, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS DOUBLE), CAST(c AS FLOAT) FROM e + +-- cast between float types, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS DOUBLE), CAST(c AS FLOAT) FROM e + +-- cast array to string, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(array(c) AS STRING) FROM e + +-- cast array to string, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(array(c) AS STRING) FROM e + +-- greatest, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, greatest(l, r) FROM p + +-- greatest, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, greatest(l, r) FROM p + +-- least, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, least(l, r) FROM p + +-- least, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, least(l, r) FROM p + +-- nanvl, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, nanvl(l, r) FROM p + +-- nanvl, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, nanvl(l, r) FROM p + +-- IN with column list, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, l IN (r, CAST('2.0' AS DOUBLE)) FROM p + +-- IN with column list, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, l IN (r, CAST('2.0' AS FLOAT)) FROM p + +-- CASE value WHEN, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, CASE l WHEN r THEN 1 ELSE 0 END FROM p + +-- CASE value WHEN, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, CASE l WHEN r THEN 1 ELSE 0 END FROM p + +-- array_contains, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_contains(array(l, 2), r) FROM p + +-- array_contains, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_contains(array(l, 2), r) FROM p + +-- array_position, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_position(array(2, l), r) FROM p + +-- array_position, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_position(array(2, l), r) FROM p + +-- array_remove, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_remove(array(l, 2), r) FROM p + +-- array_remove, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_remove(array(l, 2), r) FROM p + +-- arrays_overlap, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, arrays_overlap(array(l), array(r)) FROM p + +-- arrays_overlap, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, arrays_overlap(array(l), array(r)) FROM p + +-- array_distinct, DOUBLE +-- Whether float elements run natively depends on the Spark release (array_distinct.sql), so only +-- the answer is checked. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_distinct(array(l, r)) FROM p + +-- array_distinct, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_distinct(array(l, r)) FROM p + +-- array_union, DOUBLE +-- Whether float elements run natively depends on the Spark release (array_union.sql), so only the +-- answer is checked. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_union(array(l), array(r)) FROM p + +-- array_union, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_union(array(l), array(r)) FROM p + +-- array_intersect, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_intersect(array(l), array(r)) FROM p + +-- array_intersect, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_intersect(array(l), array(r)) FROM p + +-- array_except, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_except(array(l), array(r)) FROM p + +-- array_except, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_except(array(l), array(r)) FROM p + +-- sort_array, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, sort_array(array(l, r, 2)) FROM p + +-- sort_array, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, sort_array(array(l, r, 2)) FROM p + +-- sort_array descending, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, sort_array(array(l, r, 2), false) FROM p + +-- sort_array descending, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, sort_array(array(l, r, 2), false) FROM p + +-- array_sort, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_sort(array(l, r, 2)) FROM p + +-- array_sort, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_sort(array(l, r, 2)) FROM p + +-- array_max, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_max(array(l, r)) FROM p + +-- array_max, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_max(array(l, r)) FROM p + +-- array_min, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_min(array(l, r)) FROM p + +-- array_min, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_min(array(l, r)) FROM p + +-- map lookup, DOUBLE +-- A map with a float key falls back to Spark (map_builders_float_key_gate.sql), so only the answer +-- is checked. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, map(l, 1)[r], element_at(map(l, 1), r) FROM p WHERE l IS NOT NULL + +-- map lookup, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, map(l, 1)[r], element_at(map(l, 1), r) FROM p WHERE l IS NOT NULL Review Comment: Agreed. `map(l, 1)[r]` is gone, since Spark rewrites it to a `CASE` over the keys, which `comparison.sql` covers. `element_at(map(l, 1), r)` is `expect_fallback(Spark looks a key up in its SQL ordering)`, as in `element_at_map.sql`, and the comment cites that file and the key gate fixture for what each covers. ########## spark/src/test/resources/sql-tests/float_semantics/expressions.sql: ########## @@ -0,0 +1,417 @@ +-- 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. + +-- Expressions with float input, over the edge values (one column) or every ordered pair of +-- them (two columns). Each follows one of Spark's float equality rules, which the contributor +-- guide lists (contributor-guide/floating_point.md). The sweep tracks #6385. +-- +-- The e and p CTEs negate the flagged stored values to make the sign-bit NaN at query time +-- (see comparison.sql). +-- +-- hash and xxhash64 of a float, an array and a struct are in expressions/hash/hash.sql. +-- collect_set is in aggregates_collect_set_4_2.sql. +-- +-- ConstantFolding is on so that the IN query reaches Comet with a folded literal, as Spark +-- plans it. + +-- ConstantFolding: enabled + +statement +CREATE TABLE fs_edge_double(id INT, c DOUBLE, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_double VALUES + (1, 0.0D, false), + (2, double('-0.0'), false), + (3, double('NaN'), false), + (4, double('NaN'), true), + (5, 1.0D, false), + (6, -1.0D, false), + (7, double('Infinity'), false), + (8, double('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_double USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_double a CROSS JOIN fs_edge_double b + +statement +CREATE TABLE fs_edge_float(id INT, c FLOAT, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_float VALUES + (1, float(0.0), false), + (2, float('-0.0'), false), + (3, float('NaN'), false), + (4, float('NaN'), true), + (5, float(1.0), false), + (6, float(-1.0), false), + (7, float('Infinity'), false), + (8, float('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_float USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_float a CROSS JOIN fs_edge_float b + +-- abs, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, abs(c) FROM e + +-- abs, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, abs(c) FROM e + +-- negative, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, -c FROM e + +-- negative, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, -c FROM e + +-- signum, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, signum(c) FROM e + +-- signum, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, signum(c) FROM e + +-- ceil, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, ceil(c) FROM e + +-- ceil, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, ceil(c) FROM e + +-- floor, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, floor(c) FROM e + +-- floor, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, floor(c) FROM e + +-- round, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, round(c, 1), bround(c, 1) FROM e + +-- round, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, round(c, 1), bround(c, 1) FROM e + +-- sqrt, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, sqrt(c) FROM e + +-- sqrt, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, sqrt(c) FROM e + +-- isnan, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, isnan(c) FROM e + +-- isnan, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, isnan(c) FROM e + +-- cast to string, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS STRING) FROM e + +-- cast to string, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS STRING) FROM e + +-- cast to integral, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, try_cast(c AS INT), try_cast(c AS BIGINT) FROM e + +-- cast to integral, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, try_cast(c AS INT), try_cast(c AS BIGINT) FROM e + +-- cast to decimal, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS DECIMAL(10, 2)) FROM e + +-- cast to decimal, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS DECIMAL(10, 2)) FROM e + +-- cast to boolean, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS BOOLEAN) FROM e + +-- cast to boolean, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS BOOLEAN) FROM e + +-- cast between float types, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(c AS DOUBLE), CAST(c AS FLOAT) FROM e + +-- cast between float types, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(c AS DOUBLE), CAST(c AS FLOAT) FROM e + +-- cast array to string, DOUBLE +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_double) +SELECT id, CAST(array(c) AS STRING) FROM e + +-- cast array to string, FLOAT +query +WITH e AS (SELECT id, IF(neg, -c, c) AS c FROM fs_edge_float) +SELECT id, CAST(array(c) AS STRING) FROM e + +-- greatest, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, greatest(l, r) FROM p + +-- greatest, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, greatest(l, r) FROM p + +-- least, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, least(l, r) FROM p + +-- least, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, least(l, r) FROM p + +-- nanvl, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, nanvl(l, r) FROM p + +-- nanvl, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, nanvl(l, r) FROM p + +-- IN with column list, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, l IN (r, CAST('2.0' AS DOUBLE)) FROM p + +-- IN with column list, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, l IN (r, CAST('2.0' AS FLOAT)) FROM p + +-- CASE value WHEN, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, CASE l WHEN r THEN 1 ELSE 0 END FROM p + +-- CASE value WHEN, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, CASE l WHEN r THEN 1 ELSE 0 END FROM p + +-- array_contains, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_contains(array(l, 2), r) FROM p + +-- array_contains, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_contains(array(l, 2), r) FROM p + +-- array_position, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_position(array(2, l), r) FROM p + +-- array_position, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_position(array(2, l), r) FROM p + +-- array_remove, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_remove(array(l, 2), r) FROM p + +-- array_remove, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_remove(array(l, 2), r) FROM p + +-- arrays_overlap, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, arrays_overlap(array(l), array(r)) FROM p + +-- arrays_overlap, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, arrays_overlap(array(l), array(r)) FROM p + +-- array_distinct, DOUBLE +-- Whether float elements run natively depends on the Spark release (array_distinct.sql), so only +-- the answer is checked. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_distinct(array(l, r)) FROM p + +-- array_distinct, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_distinct(array(l, r)) FROM p + +-- array_union, DOUBLE +-- Whether float elements run natively depends on the Spark release (array_union.sql), so only the +-- answer is checked. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, array_union(array(l), array(r)) FROM p + +-- array_union, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, array_union(array(l), array(r)) FROM p Review Comment: Moved with the others to `array_set_operations_4_2.sql`, whose header points to `array_set_signed_zero*.sql` for the release routing and says the sweep does not cover the releases before 4.2. ########## spark/src/test/resources/sql-tests/float_semantics/aggregates.sql: ########## @@ -0,0 +1,193 @@ +-- 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. + +-- Aggregate functions over float input. Each pair of edge values is expanded from one +-- input row, so its two values reach the same partial aggregate in array order. Window +-- tests also order by pos to preserve signed-zero ties after a shuffle. mode gets each pair +-- as (l, r, r), so the mode is unique under every Spark equality rule. +-- +-- The e and p CTEs negate the flagged stored values to make the sign-bit NaN at query time +-- (see comparison.sql). The sweep tracks #6385. +-- +-- collect_set is in aggregates_collect_set_4_2.sql. + + +statement +CREATE TABLE fs_edge_double(id INT, c DOUBLE, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_double VALUES + (1, 0.0D, false), + (2, double('-0.0'), false), + (3, double('NaN'), false), + (4, double('NaN'), true), + (5, 1.0D, false), + (6, -1.0D, false), + (7, double('Infinity'), false), + (8, double('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_double USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_double a CROSS JOIN fs_edge_double b + +statement +CREATE TABLE fs_edge_float(id INT, c FLOAT, neg BOOLEAN) USING parquet + +statement +INSERT INTO fs_edge_float VALUES + (1, float(0.0), false), + (2, float('-0.0'), false), + (3, float('NaN'), false), + (4, float('NaN'), true), + (5, float(1.0), false), + (6, float(-1.0), false), + (7, float('Infinity'), false), + (8, float('-Infinity'), false), + (9, NULL, false) + +statement +CREATE TABLE fs_pair_float USING parquet AS +SELECT a.id * 10 + b.id AS id, a.c AS l, b.c AS r, a.neg AS lneg, b.neg AS rneg +FROM fs_edge_float a CROSS JOIN fs_edge_float b + +-- max, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, max(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- max, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, max(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- min, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, min(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- min, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, min(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- max_by, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, max_by(pos, x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- max_by, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, max_by(pos, x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- min_by, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, min_by(pos, x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- min_by, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, min_by(pos, x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- sum, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, sum(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- sum, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, sum(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- avg, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, avg(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- avg, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, avg(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- count(DISTINCT), DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, count(DISTINCT x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- count(DISTINCT), FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, count(DISTINCT x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- approx_count_distinct, DOUBLE +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, approx_count_distinct(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- approx_count_distinct, FLOAT +query +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, approx_count_distinct(x) FROM (SELECT id, pos, x FROM p LATERAL VIEW posexplode(array(l, r)) t AS pos, x) GROUP BY id + +-- mode, DOUBLE +-- mode falls back to Spark unless Mode.allowIncompatible is set, so only the answer is checked +-- here. expressions/aggregate/mode.sql runs it natively. +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_double) +SELECT id, mode(x) FROM (SELECT id, x FROM p LATERAL VIEW explode(array(l, r, r)) t AS x) GROUP BY id + +-- mode, FLOAT +query spark_answer_only +WITH p AS (SELECT id, IF(lneg, -l, l) AS l, IF(rneg, -r, r) AS r FROM fs_pair_float) +SELECT id, mode(x) FROM (SELECT id, x FROM p LATERAL VIEW explode(array(l, r, r)) t AS x) GROUP BY id Review Comment: Moved to `float_semantics/aggregates_mode.sql` with `Mode.allowIncompatible=true` and `MinSparkVersion: 3.5`, as you suggested, and the plain `query` now pins native execution. It passes on Spark 4.1 and 3.5. The header says that #7115 is why it starts at 3.5. -- 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]
