CASE-WHEN branches are not typed per SQL-99 rules.

[RCHowell]

Description

The current implementation of PartiQL (0.14) added static typing to queries, but the CASE-WHEN does not follow SQL-99 when branches have a minimal common supertype.

We expect CASE-WHEN to follow SQL-99 9.3 Data types of results of aggregations which defines how a CASE-WHEN should be typed.

To Reproduce

Steps to reproduce the behavior:

Consider two exact-numeric return values

 CASE
   WHEN a = 'hello' THEN 0
   ELSE 1.0
END

-- type
-- decimal | int32

Expected Behavior

SQL-99 defines the coercion rules of the output types of a CASE-WHEN. We expect this to be typed as just decimal

If all of the data types in DTS are exact numeric, then the result data type is exact numeric
with implementation-defined precision and with scale equal to the maximum of the scales of
the data types in DTS

For heterogenous data, PartiQL uses the dynamic type as the minimal common supertype

Otherwise, there shall exist a subtype family STF such that each data type in DTS is a
member of STF. The result data type is the minimal common supertype of each data type in
DTS.

This enables PartiQL to behave like ANSI SQL while still handling heterogenous branch types.

Additional Context

• Java version: 11
• PartiQL version: 0.14.3
• Add any other context about the problem here.