COALESCE: returns `int|null` even if one argument is not nullable

[glaubinix]

I am not quite sure if or how this is fixable but if COALESCE is called with multiple arguments and one of them is not nullable then the value should also not be nullable.

Example:

Table:

CREATE TABLE ak (
    akid int NOT NULL DEFAULT 0,
    eladaid int DEFAULT NULL,
    eadavk decimal(12,2) NOT NULL
);

Query:

SELECT COALESCE(eladaid, akid) as id FROM ak

I would expect that the value for id is always int

[staabm]

This requires an sql-AST which we don't yet have .. but who knows ;-)

[staabm]

@glaubinix please try the latest release. SQL Ast type narrowing is a opt-in feature right now, enabled via RuntimeConfiguration.

See release notes

Please give it a try

[glaubinix]

Thanks, will give this a try next week!

[glaubinix]

Apologies for not getting back to you earlier. I just tried this in our code base. Unfortunately, we are using PostgreSQL and it looks like sqlftw/sqlftw only supports MySQL so far. This currently fails with several internal errors like Internal error: Unable to resolve column TIME in file in ParserInference::narrowResultType line 91.

[staabm]

thanks for the feedback. I added a new todo item for supporting AST in postgres.

for now we will explore the AST features in mysql only, until sqlftw/sqlftw stabilizes and natively supports postgres.