SFORMAT() formats NULL INT/REAL/DECIMAL as 0 (and wrongly null-rejects LEFT JOIN)

Summary

Wrong result: SFORMAT() formats NULL INT/REAL/DECIMAL as 0 (and wrongly null-rejects LEFT JOIN)

Description

SFORMAT uses libfmt. For STRING_RESULT arguments it correctly returns SQL NULL when val_str() is NULL. For INT_RESULT / REAL_RESULT (and DECIMAL routed as numeric) it pushes val_int() / val_real() into the arg store without checking null_value.

Consequently:

• SFORMAT('{}', NULL) (untyped literal) → SQL NULL
• SFORMAT('{}', CAST(NULL AS SIGNED)) / column INT NULL / DOUBLE NULL / DECIMAL NULL → string '0' (or '0.00' for float format)
• VARCHAR NULL still correctly → SQL NULL

That inconsistency is wrong by itself. It also breaks outer-join simplification: the optimizer treats SFORMAT(..., inner_col) as null-rejecting (argument can be NULL), so WHERE SFORMAT('{}', t2.b) IS NOT NULL turns LEFT JOIN into an inner join and drops NULL-extended rows — even though runtime formatting of a NULL INT produces non-NULL '0'.

Root cause in Item_func_sformat::val_str() (sql/item_strfunc.cc): the INT_RESULT / REAL_RESULT branches never test args[carg]->null_value after val_int() / val_real().

How to repeat

CREATE TABLE t (i INT, d DOUBLE, c VARCHAR(10), de DECIMAL(10,2));

INSERT INTO t VALUES (NULL, NULL, NULL, NULL);

SELECT SFORMAT('{}', NULL) AS lit_null;

-- NULL

SELECT SFORMAT('{}', i) AS si,

       SFORMAT('{}', d) AS sd,

       SFORMAT('{}', c) AS sc,

       SFORMAT('{}', de) AS sde

FROM t;

-- Actual:   0 | 0 | NULL | 0

-- Expected: NULL | NULL | NULL | NULL

SELECT SFORMAT('{}', CAST(NULL AS SIGNED)) AS cast_i,

       SFORMAT('{:.2f}', CAST(NULL AS DOUBLE)) AS cast_d;

-- Actual:   0 | 0.00

-- Expected: NULL | NULL

-- LEFT JOIN consequence

CREATE TABLE t1 (a INT, b INT);

INSERT INTO t1 VALUES (1,1),(2,3);

SELECT t1.a, t2.b, SFORMAT('{}', t2.b) AS sf

FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b;

-- row (2, NULL, '0') — sf should be NULL if NULL ints are NULL

SELECT COUNT(*) AS c_where

FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b

WHERE SFORMAT('{}', t2.b) IS NOT NULL;

-- Actual: 1

-- Expected: 2  (both rows: matching '1' and null-extended '0'/NULL once fixed)

SELECT COUNT(*) AS c_subq FROM (

  SELECT SFORMAT('{}', t2.b) IS NOT NULL AS p

  FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b

) s WHERE p;

-- 2  (SELECT-list evaluation disagrees with WHERE pushdown)

DROP TABLE t, t1;

Observed on: 12.3.2-MariaDB

Actual result

• Numeric NULL args → formatted as zero ('0' / '0.00')
• WHERE SFORMAT(...) IS NOT NULL on LEFT JOIN → COUNT = 1
• VARCHAR NULL still correctly → SQL NULL

Expected result

• Any SQL NULL argument → SQL NULL result (same as literal NULL / string NULL)
• WHERE and subquery filter counts agree (2 if zero-formatting were kept — but primary fix is return NULL)

Suggested fix

In Item_func_sformat::val_str, after val_int() / val_real(), if args[carg]->null_value then set null_value=true and return NULL (mirror the STRING_RESULT path). Optionally override not_null_tables() only if the function can still return non-NULL for NULL inputs after that fix.