[IGNITE-23308] Calcite. Wrong numeric type coercion with set-op operations.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.14
• Fix Version/s: 2.17
• Labels: calcite ignite-2 ise
• Release Note: SQL Calcite: Fixed incorrect result in unions in case of different types of numeric results.
• Ignite Flags: Release Notes Required
• Assignee: Vladimir Steshin
• Reporter: Vladimir Steshin
• Created: 28/Sep/24 21:49
• Updated: 04/Oct/24 09:16
• Resolved: 04/Oct/24 09:16

Description

Wrong numers cast with operators like UNION / INTERSECT / EXCEPT.

Reproducer:

    public void testUnionWithNumerics() {
        sql("CREATE TABLE t0(id INT PRIMARY KEY, val INT)");
        sql("CREATE TABLE t1(id INT PRIMARY KEY, val DECIMAL)");
        sql("INSERT INTO t0 VALUES (1, 10)");
        sql("INSERT INTO t1 VALUES (1, 10)");

        assertQuery(ignite(0), "SELECT val from t0 UNION select val from t1")
            .returns(new BigDecimal(10))
            .ordered()
            .check();
    }

Error:

java.lang.AssertionError: Collections sizes are not equal:
Expected: [[10]]
Actual:   [[10], [10]] 

Or:

    public void testIntersectWithNumerics() {
        sql("CREATE TABLE t0(id INT PRIMARY KEY, val INT)");
        sql("CREATE TABLE t1(id INT PRIMARY KEY, val DECIMAL)");
        sql("INSERT INTO t0 VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50)");
        sql("INSERT INTO t1 VALUES (1, 10), (2, 20), (3, 300), (4, 400), (5, 50)");

        assertQuery(ignite(0), "SELECT val from t0 INTERSECT select val from t1")
            .returns(new BigDecimal(10))
            .returns(new BigDecimal(20))
            .returns(new BigDecimal(50))
            .ordered()
            .check();
    }

Error:

java.lang.AssertionError: Collections sizes are not equal:
Expected: [[10], [20], [50]]
Actual:   [] 

Adoption of #IGNITE-21827 and #IGNITE-20649

Issue Links

• relates to: IGNITE-20649 SQL: class cast exception in EXCEPT SELECT with different column types
• relates to: IGNITE-21827 Sql. Union returns incorrect result for DECIMAL and INT columns
• links to: GitHub Pull Request #11557