SQL: Promoting return types to common type of arguments

[philrz]

With conditionals in particular, it appears SuperDB is not currently promoting the return type to the common type of arguments in the same way Postgres does. Here's three such examples:

$ super -version
Version: 03130efdf

$ super -c "SELECT NULLIF(53, 80.0) / -71;"
{"NULLIF(53, 80.0)/-71":0}

$ super -c "SELECT COALESCE(19, 104.0) / 74;"
{"COALESCE(19, 104.0)/74":0}

$ super -c "SELECT CASE WHEN 1=1 THEN 19 ELSE 104.0 END / 74;"
{"case when 1=1 then 19 else 104.0 end/74":0}

Details

Repro is with super commit 03130ef. These queries are simplifications of sqllogictests such as random/expr/slt_good_0/q7944 and random/expr/slt_good_2/q10793.

Here's Postgres and DuckDB each giving similar results as each other for these, which differ from the ones super gave.

$ psql postgres
psql (17.7 (Homebrew))
Type "help" for help.

postgres=# SELECT NULLIF(53, 80.0) / -71;
        ?column?         
-------------------------
 -0.74647887323943661972
(1 row)

postgres=# SELECT COALESCE(19, 104.0) / 74;
        ?column?        
------------------------
 0.25675675675675675676
(1 row)

postgres=# SELECT CASE WHEN 1=1 THEN 19 ELSE 104.0 END / 74;
        ?column?        
------------------------
 0.25675675675675675676
(1 row)

$ duckdb
DuckDB v1.4.3 (Andium) d1dc88f950
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D SELECT NULLIF(53, 80.0) / -71;
┌────────────────────────────┐
│ ("nullif"(53, 80.0) / -71) │
│           double           │
├────────────────────────────┤
│    -0.7464788732394366     │
└────────────────────────────┘
D SELECT COALESCE(19, 104.0) / 74;
┌────────────────────────────┐
│ (COALESCE(19, 104.0) / 74) │
│           double           │
├────────────────────────────┤
│    0.25675675675675674     │
└────────────────────────────┘
D SELECT CASE WHEN 1=1 THEN 19 ELSE 104.0 END / 74;
┌──────────────────────────────────────────────────────┐
│ (CASE  WHEN ((1 = 1)) THEN (19) ELSE 104.0 END / 74) │
│                        double                        │
├──────────────────────────────────────────────────────┤
│                 0.25675675675675674                  │
└──────────────────────────────────────────────────────┘

There's admittedly some variation in how different SQL implementations behave here. For instance, SQLite returns what super does.

$ sqlite3
SQLite version 3.51.1 2025-11-28 17:28:25
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT NULLIF(53, 80.0) / -71;
0
sqlite> SELECT COALESCE(19, 104.0) / 74;
0
sqlite> SELECT CASE WHEN 1=1 THEN 19 ELSE 104.0 END / 74;
0

In Postgres, all of these functions happen to be documented at the same Conditional Expressions page, and it looks like the effect is explained by the "conversion to a common data type" that apparently happens with multiple arguments to these functions. So as I understand it, in NULLIF(53, 80.0), even though 53 is the value to be returned by the function and starts life as an INTEGER type, the fact the second argument is NUMERIC means both arguments are promoted to type NUMERIC and that becomes the return type. And the return value 53 being NUMERIC has the follow-on effect of a fractional result when dividing by an INTEGER.

postgres=# SELECT NULLIF(53, 80.0),pg_typeof(NULLIF(53, 80.0)),pg_typeof(53),pg_typeof(80.0),pg_typeof(NULLIF(53, 80));
 nullif | pg_typeof | pg_typeof | pg_typeof | pg_typeof 
--------+-----------+-----------+-----------+-----------
     53 | numeric   | integer   | numeric   | integer
(1 row)

[philrz]

@mccanne's reaction upon seeing this is that it would probably not be too hard to match the Postgres behavior in SQL context for compatibility reasons. That said, he also pointed out that we may want to maintain something like our current behavior in pipe context, since given that SuperDB is well-suited for working with heterogeneous data, for COALESCE in particular, being able to return one of many types may sometimes be desirable.

Indeed, @mccanne's response reminded me that the Postgres "conversion to a common data type" with the multiple arguments means that some calls to COALESCE, NULLIF, etc., are rejected entirely with some combinations of heterogeneous types, e.g.,

$ psql postgres -c "SELECT COALESCE('a', 1);"
ERROR:  invalid input syntax for type integer: "a"
LINE 1: SELECT COALESCE('a', 1);
                        ^
$ duckdb -c "SELECT COALESCE('a', 1);"
Conversion Error:
Could not convert string 'a' to INT32

LINE 1: SELECT COALESCE('a', 1);
                        ^

Whereas super works can work fine in such a call. Based on @mccanne's current proposal, we might move to reject the former in the same way Postgres does, but permit the latter.

$ super -c "SELECT COALESCE('a', 1);"
{COALESCE:"a"}

$ super -c "values coalesce('a', 1);"
"a"