Fix Count_Distinct on a Boolean column in Snowflake

[radeusgd]

Currently, when trying to perform Count_Distinct aggregate on a Boolean column in the Snowflake backend, it fails with:

An unexpected dataflow error (There was an SQL error: Failed to cast variant value {"enso-null-replacement-marker":"084954a3-9f58-4a2d-aa63-3f56bc270800"} to BOOLEAN. [Query was: SELECT COUNT("Tinfo-31365771-714d-4377-bb92-da160dccbf19"."Concatenate strs") AS "Concatenate strs", COUNT("Tinfo-31365771-714d-4377-bb92-da160dccbf19"."Sum ints") AS "Sum ints", COUNT("Tinfo-31365771-714d-4377-bb92-da160dccbf19"."Count Distinct bools") AS "Count Distinct bools" FROM (SELECT "Tinfo-31365771-714d-4377-bb92-da160dccbf19"."Concatenate strs" AS "Concatenate strs", "Tinfo-31365771-714d-4377-bb92-da160dccbf19"."Sum ints" AS "Sum ints", "Tinfo-31365771-714d-4377-bb92-da160dccbf19"."Cou (...) "strs") END, ?) || (?) AS "Concatenate strs", SUM("Tinfo-31365771-714d-4377-bb92-da160dccbf19"."ints") AS "Sum ints", COUNT(DISTINCT COALESCE("Tinfo-31365771-714d-4377-bb92-da160dccbf19"."bools", {'enso-null-replacement-marker':'084954a3-9f58-4a2d-aa63-3f56bc270800'}::variant)) AS "Count Distinct bools" FROM "Tinfo-31365771-714d-4377-bb92-da160dccbf19" AS "Tinfo-31365771-714d-4377-bb92-da160dccbf19") AS "Tinfo-31365771-714d-4377-bb92-da160dccbf19") AS "Tinfo-31365771-714d-4377-bb92-da160dccbf19"]) has been matched (at C:\NBO\enso\test\Snowflake_Tests\src\Snowflake_Spec.enso:189:13-109).

Apparently the 'special null replacement value' that we use does not play well with the Boolean type.

[radeusgd]

Adding a special test to Aggregate_Spec for this case: should work correctly with Boolean columns:

        group_builder.specify "should work correctly with Boolean columns" <|
            table = table_builder [["A", [True, True, True]], ["B", [False, False, False]], ["C", [True, False, True]], ["D", [Nothing, False, True]]]

            t_with_nulls = table.aggregate columns=[..Count_Distinct "A", ..Count_Distinct "B", ..Count_Distinct "C", ..Count_Distinct "D"]
            m1 = materialize t_with_nulls
            m1.column_count . should_equal 4
            m1.at "Count Distinct A" . to_vector . should_equal [1]
            m1.at "Count Distinct B" . to_vector . should_equal [1]
            m1.at "Count Distinct C" . to_vector . should_equal [2]
            m1.at "Count Distinct D" . to_vector . should_equal [3]

            t_without_nulls = table.aggregate columns=[..Count_Distinct "A" ignore_nothing=True, ..Count_Distinct "B" ignore_nothing=True, ..Count_Distinct "C" ignore_nothing=True, ..Count_Distinct "D" ignore_nothing=True]
            m2 = materialize t_without_nulls
            m2.column_count . should_equal 4
            m2.at "Count Distinct A" . to_vector . should_equal [1]
            m2.at "Count Distinct B" . to_vector . should_equal [1]
            m2.at "Count Distinct C" . to_vector . should_equal [2]
            # The NULL is ignored, and not counted towards the total
            m2.at "Count Distinct D" . to_vector . should_equal [2]

[radeusgd]

Additionally a test in Snowflake_Spec is marked as pending, so it should be re-enabled once this is fixed.