You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@ignite.apache.org by "AMashenkov (via GitHub)" <gi...@apache.org> on 2025/09/23 08:47:05 UTC

[PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

AMashenkov opened a new pull request, #6637:
URL: https://github.com/apache/ignite-3/pull/6637

   Thank you for submitting the pull request.
   
   To streamline the review process of the patch and ensure better code quality
   we ask both an author and a reviewer to verify the following:
   
   ### The Review Checklist
   - [ ] **Formal criteria:** TC status, codestyle, mandatory documentation. Also make sure to complete the following:  
   \- There is a single JIRA ticket related to the pull request.  
   \- The web-link to the pull request is attached to the JIRA ticket.  
   \- The JIRA ticket has the Patch Available state.  
   \- The description of the JIRA ticket explains WHAT was made, WHY and HOW.  
   \- The pull request title is treated as the final commit message. The following pattern must be used: IGNITE-XXXX Change summary where XXXX - number of JIRA issue.
   - [ ] **Design:** new code conforms with the design principles of the components it is added to.
   - [ ] **Patch quality:** patch cannot be split into smaller pieces, its size must be reasonable.
   - [ ] **Code quality:** code is clean and readable, necessary developer documentation is added if needed.
   - [ ] **Tests code quality:** test set covers positive/negative scenarios, happy/edge cases. Tests are effective in terms of execution time and resources.
   
   ### Notes
   - [Apache Ignite Coding Guidelines](https://cwiki.apache.org/confluence/display/IGNITE/Java+Code+Style+Guide)


-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "AMashenkov (via GitHub)" <gi...@apache.org>.
AMashenkov commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2378458680


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,190 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+
+        sql("INSERT INTO test VALUES (?, ?, ?)", 1, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 2, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 3, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 4, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 5, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 6, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 7, +0.0f, +0.0d);
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-NaN'::FLOAT + 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT + '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT / '+Infinity'::FLOAT").get(0).get(0));
+
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-NaN'::DOUBLE + 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE + '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE / '+Infinity'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testLiterals() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT -'NaN'::FLOAT").get(0).get(0));
+
+        assertEquals(-0.0d, sql("SELECT -0.0::DOUBLE").get(0).get(0));
+        assertEquals(0.0d, sql("SELECT 0.0::DOUBLE").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT -'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(floats = {
+            -0.0f,
+            0.0f,
+            Float.NEGATIVE_INFINITY,
+            Float.POSITIVE_INFINITY,
+            Float.NaN
+    })
+    void testParameters(float f) {
+        assertEquals(f, (float) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(doubles = {
+            -0.0d,
+            0.0d,
+            Double.NEGATIVE_INFINITY,
+            Double.POSITIVE_INFINITY,
+            Double.NaN
+    })
+    void testParameters(double f) {
+        assertEquals(f, (double) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @Test
+    void testOrderBy() {
+        List<Float> floats = sql("SELECT f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));

Review Comment:
   Fixed



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "zstan (via GitHub)" <gi...@apache.org>.
zstan commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2378770934


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,200 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.hamcrest.Matchers.hasSize;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+
+        sql("INSERT INTO test VALUES (?, ?, ?)", 1, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 2, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 3, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 4, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 5, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 6, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 7, +0.0f, +0.0d);
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-NaN'::FLOAT + 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT + '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT / '+Infinity'::FLOAT").get(0).get(0));
+
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-NaN'::DOUBLE + 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE + '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE / '+Infinity'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testLiterals() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT -'NaN'::FLOAT").get(0).get(0));
+
+        assertEquals(-0.0d, sql("SELECT -0.0::DOUBLE").get(0).get(0));
+        assertEquals(0.0d, sql("SELECT 0.0::DOUBLE").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT -'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(floats = {
+            -0.0f,
+            0.0f,
+            Float.NEGATIVE_INFINITY,
+            Float.POSITIVE_INFINITY,
+            Float.NaN
+    })
+    void testParameters(float f) {
+        assertEquals(f, (float) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(doubles = {
+            -0.0d,
+            0.0d,
+            Double.NEGATIVE_INFINITY,
+            Double.POSITIVE_INFINITY,
+            Double.NaN
+    })
+    void testParameters(double f) {
+        assertEquals(f, (double) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @Test
+    void testOrderBy() {
+        List<Float> floats = sql("SELECT /*+ NO_INDEX */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT /*+ NO_INDEX */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+
+        floats = sql("SELECT /*+ FORCE_INDEX(test_f_idx) */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        doubles = sql("SELECT /*+ FORCE_INDEX(test_d_idx) */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+    }
+
+    @Test
+    void testComparisons() {
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::FLOAT").get(0).get(0)); // NaN never equals
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::DOUBLE").get(0).get(0)); // NaN never equals
+
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f > 0").get(0).get(0)); // 1.0, +Infinity
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f < 0").get(0).get(0)); // -Infinity, -1,0
+
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::FLOAT AND d IS DISTINCT FROM '-Infinity'::FLOAT").get(0).get(0)); // NaN, 1.0, -1,0
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::DOUBLE AND d IS DISTINCT FROM '-Infinity'::DOUBLE").get(0).get(0));
+
+        // NaN not equal to NaN
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE f IS DISTINCT FROM 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE d IS DISTINCT FROM 'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testAggregations() {
+        List<List<Object>> minRows = sql("SELECT MIN(f), MIN(d) FROM test");
+        List<List<Object>> maxRows = sql("SELECT MAX(f), MAX(d) FROM test");
+        List<List<Object>> avgRows = sql("SELECT AVG(f), AVG(d) FROM test");
+
+        assertThat(minRows.get(0), contains(Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY));
+        assertThat(maxRows.get(0), contains(Float.NaN, Double.NaN));
+        assertThat(avgRows.get(0), contains(Double.NaN, Double.NaN));
+    }
+
+    @Test
+    void testGrouping() {
+        // Insert more data.
+        sql("INSERT INTO test VALUES (?, ?, ?)", 8, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 9, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 10, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 11, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 12, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 13, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 14, +0.0f, +0.0d);
+
+        assertThat(sql("SELECT * FROM test"), hasSize(14));

Review Comment:
   what it lexographic tests running order will be changed on TC or IDE ? I suppose some above tests will fail too.



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "korlov42 (via GitHub)" <gi...@apache.org>.
korlov42 commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2379134322


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,207 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.hamcrest.Matchers.hasSize;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+    }
+
+    @BeforeEach
+    void resetTableState() {
+        sqlScript("DELETE * FROM test;"
+                + "INSERT INTO test VALUES (1, '-Infinity'::FLOAT, '-Infinity'::DOUBLE)"
+                + "INSERT INTO test VALUES (2, 'Infinity'::FLOAT, 'Infinity'::DOUBLE)"
+                + "INSERT INTO test VALUES (3, 'NaN'::FLOAT, 'NaN'::DOUBLE)"
+                + "INSERT INTO test VALUES (4, -0.0::FLOAT, -0.0::DOUBLE)"
+                + "INSERT INTO test VALUES (5, 0.0::FLOAT, 0.0::DOUBLE)"
+                + "INSERT INTO test VALUES (6, -1.0::FLOAT, -1.0::DOUBLE)"
+                + "INSERT INTO test VALUES (7, 1.0::FLOAT, 1.0::DOUBLE)"
+        );
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));

Review Comment:
   wondering why didn't you use `assertQuery()` here 



##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,190 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+
+        sql("INSERT INTO test VALUES (?, ?, ?)", 1, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 2, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 3, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 4, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 5, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 6, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 7, +0.0f, +0.0d);
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-NaN'::FLOAT + 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT + '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT / '+Infinity'::FLOAT").get(0).get(0));
+
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-NaN'::DOUBLE + 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE + '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE / '+Infinity'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testLiterals() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT -'NaN'::FLOAT").get(0).get(0));
+
+        assertEquals(-0.0d, sql("SELECT -0.0::DOUBLE").get(0).get(0));
+        assertEquals(0.0d, sql("SELECT 0.0::DOUBLE").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT -'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(floats = {
+            -0.0f,
+            0.0f,
+            Float.NEGATIVE_INFINITY,
+            Float.POSITIVE_INFINITY,
+            Float.NaN
+    })
+    void testParameters(float f) {
+        assertEquals(f, (float) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(doubles = {
+            -0.0d,
+            0.0d,
+            Double.NEGATIVE_INFINITY,
+            Double.POSITIVE_INFINITY,
+            Double.NaN
+    })
+    void testParameters(double f) {
+        assertEquals(f, (double) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @Test
+    void testOrderBy() {
+        List<Float> floats = sql("SELECT f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));

Review Comment:
   I would also add plan verification, that no explicit sorting is present. Otherwise it may obscure problem in index comparators



##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,207 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.hamcrest.Matchers.hasSize;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"

Review Comment:
   let's also add non-nullable variant of float and double. They may be treated differently in certain scenarios



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "AMashenkov (via GitHub)" <gi...@apache.org>.
AMashenkov commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2378906255


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,200 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.hamcrest.Matchers.hasSize;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+
+        sql("INSERT INTO test VALUES (?, ?, ?)", 1, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 2, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 3, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 4, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 5, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 6, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 7, +0.0f, +0.0d);
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-NaN'::FLOAT + 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT + '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT / '+Infinity'::FLOAT").get(0).get(0));
+
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-NaN'::DOUBLE + 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE + '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE / '+Infinity'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testLiterals() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT -'NaN'::FLOAT").get(0).get(0));
+
+        assertEquals(-0.0d, sql("SELECT -0.0::DOUBLE").get(0).get(0));
+        assertEquals(0.0d, sql("SELECT 0.0::DOUBLE").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT -'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(floats = {
+            -0.0f,
+            0.0f,
+            Float.NEGATIVE_INFINITY,
+            Float.POSITIVE_INFINITY,
+            Float.NaN
+    })
+    void testParameters(float f) {
+        assertEquals(f, (float) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(doubles = {
+            -0.0d,
+            0.0d,
+            Double.NEGATIVE_INFINITY,
+            Double.POSITIVE_INFINITY,
+            Double.NaN
+    })
+    void testParameters(double f) {
+        assertEquals(f, (double) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @Test
+    void testOrderBy() {
+        List<Float> floats = sql("SELECT /*+ NO_INDEX */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT /*+ NO_INDEX */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+
+        floats = sql("SELECT /*+ FORCE_INDEX(test_f_idx) */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        doubles = sql("SELECT /*+ FORCE_INDEX(test_d_idx) */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+    }
+
+    @Test
+    void testComparisons() {
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::FLOAT").get(0).get(0)); // NaN never equals
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::DOUBLE").get(0).get(0)); // NaN never equals
+
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f > 0").get(0).get(0)); // 1.0, +Infinity
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f < 0").get(0).get(0)); // -Infinity, -1,0
+
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::FLOAT AND d IS DISTINCT FROM '-Infinity'::FLOAT").get(0).get(0)); // NaN, 1.0, -1,0
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::DOUBLE AND d IS DISTINCT FROM '-Infinity'::DOUBLE").get(0).get(0));
+
+        // NaN not equal to NaN
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE f IS DISTINCT FROM 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE d IS DISTINCT FROM 'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testAggregations() {
+        List<List<Object>> minRows = sql("SELECT MIN(f), MIN(d) FROM test");
+        List<List<Object>> maxRows = sql("SELECT MAX(f), MAX(d) FROM test");
+        List<List<Object>> avgRows = sql("SELECT AVG(f), AVG(d) FROM test");
+
+        assertThat(minRows.get(0), contains(Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY));
+        assertThat(maxRows.get(0), contains(Float.NaN, Double.NaN));
+        assertThat(avgRows.get(0), contains(Double.NaN, Double.NaN));
+    }
+
+    @Test
+    void testGrouping() {
+        // Insert more data.
+        sql("INSERT INTO test VALUES (?, ?, ?)", 8, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 9, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 10, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 11, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 12, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 13, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 14, +0.0f, +0.0d);
+
+        assertThat(sql("SELECT * FROM test"), hasSize(14));
+
+        List<Float> floats = sql("SELECT f FROM test GROUP BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT d FROM test GROUP BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, containsInAnyOrder(Float.NEGATIVE_INFINITY, Float.POSITIVE_INFINITY, Float.NaN, -1.0f, 1.0f, 0.0f, -0.0f));

Review Comment:
   Why do expect any ordering here?
   I expect all duplicated values are merged into group. The ordering make no sense here.



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "AMashenkov (via GitHub)" <gi...@apache.org>.
AMashenkov commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2391127624


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,207 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.hamcrest.Matchers.hasSize;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"

Review Comment:
   I've added tests



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "zstan (via GitHub)" <gi...@apache.org>.
zstan commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2378770934


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,200 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.hamcrest.Matchers.hasSize;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+
+        sql("INSERT INTO test VALUES (?, ?, ?)", 1, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 2, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 3, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 4, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 5, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 6, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 7, +0.0f, +0.0d);
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-NaN'::FLOAT + 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT + '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT / '+Infinity'::FLOAT").get(0).get(0));
+
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-NaN'::DOUBLE + 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE + '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE / '+Infinity'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testLiterals() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT -'NaN'::FLOAT").get(0).get(0));
+
+        assertEquals(-0.0d, sql("SELECT -0.0::DOUBLE").get(0).get(0));
+        assertEquals(0.0d, sql("SELECT 0.0::DOUBLE").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT -'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(floats = {
+            -0.0f,
+            0.0f,
+            Float.NEGATIVE_INFINITY,
+            Float.POSITIVE_INFINITY,
+            Float.NaN
+    })
+    void testParameters(float f) {
+        assertEquals(f, (float) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(doubles = {
+            -0.0d,
+            0.0d,
+            Double.NEGATIVE_INFINITY,
+            Double.POSITIVE_INFINITY,
+            Double.NaN
+    })
+    void testParameters(double f) {
+        assertEquals(f, (double) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @Test
+    void testOrderBy() {
+        List<Float> floats = sql("SELECT /*+ NO_INDEX */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT /*+ NO_INDEX */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+
+        floats = sql("SELECT /*+ FORCE_INDEX(test_f_idx) */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        doubles = sql("SELECT /*+ FORCE_INDEX(test_d_idx) */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+    }
+
+    @Test
+    void testComparisons() {
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::FLOAT").get(0).get(0)); // NaN never equals
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::DOUBLE").get(0).get(0)); // NaN never equals
+
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f > 0").get(0).get(0)); // 1.0, +Infinity
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f < 0").get(0).get(0)); // -Infinity, -1,0
+
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::FLOAT AND d IS DISTINCT FROM '-Infinity'::FLOAT").get(0).get(0)); // NaN, 1.0, -1,0
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::DOUBLE AND d IS DISTINCT FROM '-Infinity'::DOUBLE").get(0).get(0));
+
+        // NaN not equal to NaN
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE f IS DISTINCT FROM 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE d IS DISTINCT FROM 'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testAggregations() {
+        List<List<Object>> minRows = sql("SELECT MIN(f), MIN(d) FROM test");
+        List<List<Object>> maxRows = sql("SELECT MAX(f), MAX(d) FROM test");
+        List<List<Object>> avgRows = sql("SELECT AVG(f), AVG(d) FROM test");
+
+        assertThat(minRows.get(0), contains(Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY));
+        assertThat(maxRows.get(0), contains(Float.NaN, Double.NaN));
+        assertThat(avgRows.get(0), contains(Double.NaN, Double.NaN));
+    }
+
+    @Test
+    void testGrouping() {
+        // Insert more data.
+        sql("INSERT INTO test VALUES (?, ?, ?)", 8, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 9, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 10, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 11, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 12, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 13, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 14, +0.0f, +0.0d);
+
+        assertThat(sql("SELECT * FROM test"), hasSize(14));

Review Comment:
   what it lexographic tests running order will be changed on TC or IDE ? I suppose some above tests will fail too.



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "AMashenkov (via GitHub)" <gi...@apache.org>.
AMashenkov merged PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637


-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "zstan (via GitHub)" <gi...@apache.org>.
zstan commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2376117032


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,190 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+
+        sql("INSERT INTO test VALUES (?, ?, ?)", 1, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 2, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 3, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 4, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 5, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 6, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 7, +0.0f, +0.0d);
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-NaN'::FLOAT + 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT + '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT / '+Infinity'::FLOAT").get(0).get(0));
+
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-NaN'::DOUBLE + 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE + '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE / '+Infinity'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testLiterals() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT -'NaN'::FLOAT").get(0).get(0));
+
+        assertEquals(-0.0d, sql("SELECT -0.0::DOUBLE").get(0).get(0));
+        assertEquals(0.0d, sql("SELECT 0.0::DOUBLE").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT -'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(floats = {
+            -0.0f,
+            0.0f,
+            Float.NEGATIVE_INFINITY,
+            Float.POSITIVE_INFINITY,
+            Float.NaN
+    })
+    void testParameters(float f) {
+        assertEquals(f, (float) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(doubles = {
+            -0.0d,
+            0.0d,
+            Double.NEGATIVE_INFINITY,
+            Double.POSITIVE_INFINITY,
+            Double.NaN
+    })
+    void testParameters(double f) {
+        assertEquals(f, (double) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @Test
+    void testOrderBy() {
+        List<Float> floats = sql("SELECT f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));

Review Comment:
   force_index ? if we want to check IndexScan too



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] IGNITE-26090 Sql. Validate Special Values for Approximate Numeric Types in SQL (NaN, ±Infinity, ±0) [ignite-3]

Posted by "zstan (via GitHub)" <gi...@apache.org>.
zstan commented on code in PR #6637:
URL: https://github.com/apache/ignite-3/pull/6637#discussion_r2378774794


##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFloatingPointTest.java:
##########
@@ -0,0 +1,200 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static java.util.stream.Collectors.toList;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.contains;
+import static org.hamcrest.Matchers.containsInAnyOrder;
+import static org.hamcrest.Matchers.hasSize;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.util.List;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+/** Test floating point special values test. */
+public class ItFloatingPointTest extends BaseSqlMultiStatementTest {
+    @BeforeAll
+    void createTestTable() {
+        sqlScript("CREATE TABLE test (id INT PRIMARY KEY, f FLOAT, d DOUBLE);"
+                + "CREATE INDEX test_f_idx on test (f);"
+                + "CREATE INDEX test_d_idx on test (d);"
+        );
+
+        sql("INSERT INTO test VALUES (?, ?, ?)", 1, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 2, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 3, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 4, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 5, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 6, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 7, +0.0f, +0.0d);
+    }
+
+    @Override
+    protected int initialNodes() {
+        return 2;
+    }
+
+    @Test
+    void testArithmetic() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT + 1").get(0).get(0));
+
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT - 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-NaN'::FLOAT + 1").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT + '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT '-Infinity'::FLOAT / '+Infinity'::FLOAT").get(0).get(0));
+
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE + 1").get(0).get(0));
+
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE - 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-NaN'::DOUBLE + 1").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE + '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT '-Infinity'::DOUBLE / '+Infinity'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testLiterals() {
+        assertEquals(-0.0f, sql("SELECT -0.0::FLOAT").get(0).get(0));
+        assertEquals(0.0f, sql("SELECT 0.0::FLOAT").get(0).get(0));
+        assertEquals(Float.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.POSITIVE_INFINITY, sql("SELECT '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(Float.NaN, sql("SELECT -'NaN'::FLOAT").get(0).get(0));
+
+        assertEquals(-0.0d, sql("SELECT -0.0::DOUBLE").get(0).get(0));
+        assertEquals(0.0d, sql("SELECT 0.0::DOUBLE").get(0).get(0));
+        assertEquals(Double.NEGATIVE_INFINITY, sql("SELECT '-Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.POSITIVE_INFINITY, sql("SELECT '+Infinity'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT 'NaN'::DOUBLE").get(0).get(0));
+        assertEquals(Double.NaN, sql("SELECT -'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(floats = {
+            -0.0f,
+            0.0f,
+            Float.NEGATIVE_INFINITY,
+            Float.POSITIVE_INFINITY,
+            Float.NaN
+    })
+    void testParameters(float f) {
+        assertEquals(f, (float) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @ParameterizedTest
+    @ValueSource(doubles = {
+            -0.0d,
+            0.0d,
+            Double.NEGATIVE_INFINITY,
+            Double.POSITIVE_INFINITY,
+            Double.NaN
+    })
+    void testParameters(double f) {
+        assertEquals(f, (double) sql("SELECT ?", f).get(0).get(0));
+    }
+
+    @Test
+    void testOrderBy() {
+        List<Float> floats = sql("SELECT /*+ NO_INDEX */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT /*+ NO_INDEX */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+
+        floats = sql("SELECT /*+ FORCE_INDEX(test_f_idx) */ f FROM test ORDER BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        doubles = sql("SELECT /*+ FORCE_INDEX(test_d_idx) */ d FROM test ORDER BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, contains(Float.NEGATIVE_INFINITY, -1.0f, -0.0f, 0.0f, 1.0f, Float.POSITIVE_INFINITY, Float.NaN));
+        assertThat(doubles, contains(Double.NEGATIVE_INFINITY, -1.0d, -0.0d, 0.0d, 1.0d, Double.POSITIVE_INFINITY, Double.NaN));
+    }
+
+    @Test
+    void testComparisons() {
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '-Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::FLOAT").get(0).get(0));
+        assertEquals(1L, sql("SELECT COUNT(*) FROM test WHERE f = '+Infinity'::DOUBLE").get(0).get(0));
+
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::FLOAT").get(0).get(0)); // NaN never equals
+        assertEquals(0L, sql("SELECT COUNT(*) FROM test WHERE f = 'NaN'::DOUBLE").get(0).get(0)); // NaN never equals
+
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f > 0").get(0).get(0)); // 1.0, +Infinity
+        assertEquals(2L, sql("SELECT COUNT(*) FROM test WHERE f < 0").get(0).get(0)); // -Infinity, -1,0
+
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::FLOAT AND d IS DISTINCT FROM '-Infinity'::FLOAT").get(0).get(0)); // NaN, 1.0, -1,0
+        assertEquals(5L, sql("SELECT COUNT(*) FROM test "
+                + "WHERE f IS DISTINCT FROM '+Infinity'::DOUBLE AND d IS DISTINCT FROM '-Infinity'::DOUBLE").get(0).get(0));
+
+        // NaN not equal to NaN
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE f IS DISTINCT FROM 'NaN'::FLOAT").get(0).get(0));
+        assertEquals(7L, sql("SELECT COUNT(*) FROM test WHERE d IS DISTINCT FROM 'NaN'::DOUBLE").get(0).get(0));
+    }
+
+    @Test
+    void testAggregations() {
+        List<List<Object>> minRows = sql("SELECT MIN(f), MIN(d) FROM test");
+        List<List<Object>> maxRows = sql("SELECT MAX(f), MAX(d) FROM test");
+        List<List<Object>> avgRows = sql("SELECT AVG(f), AVG(d) FROM test");
+
+        assertThat(minRows.get(0), contains(Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY));
+        assertThat(maxRows.get(0), contains(Float.NaN, Double.NaN));
+        assertThat(avgRows.get(0), contains(Double.NaN, Double.NaN));
+    }
+
+    @Test
+    void testGrouping() {
+        // Insert more data.
+        sql("INSERT INTO test VALUES (?, ?, ?)", 8, Float.POSITIVE_INFINITY, Double.POSITIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 9, Float.NEGATIVE_INFINITY, Double.NEGATIVE_INFINITY);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 10, Float.NaN, Double.NaN);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 11, -1.0f, -1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 12, 1.0f, 1.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 13, -0.0f, -0.0d);
+        sql("INSERT INTO test VALUES (?, ?, ?)", 14, +0.0f, +0.0d);
+
+        assertThat(sql("SELECT * FROM test"), hasSize(14));
+
+        List<Float> floats = sql("SELECT f FROM test GROUP BY f")
+                .stream().flatMap(List::stream).map(Float.class::cast).collect(toList());
+        List<Double> doubles = sql("SELECT d FROM test GROUP BY d")
+                .stream().flatMap(List::stream).map(Double.class::cast).collect(toList());
+
+        assertThat(floats, containsInAnyOrder(Float.NEGATIVE_INFINITY, Float.POSITIVE_INFINITY, Float.NaN, -1.0f, 1.0f, 0.0f, -0.0f));

Review Comment:
   is it ok - to await any ordering here ? I think it need to be deterministic, did it ?



-- 
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: notifications-unsubscribe@ignite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org